How to run PlanetScale as database for Ghost CMS?

[uncvrd]

I think I got real close, but ultimately ran in to a roadblock. I'm trying to run Ghost CMS locally before deploying to my server but ran into a CREATE DATABASE error. Here are the steps I took:

1. Created a new PlanetScale database in the UI.
2. Created a connection string
3. Created a branch so that Ghost CMS can run migrations on init and eventually merge back and connect to main (hack?)
4. Used pscale connect ghost branch-name to connect locally to my branch
5. Used 127.0.0.1 as my host

Received: Ghost was able to start, but errored during boot with: CREATE DATABASE ghost CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; - ER_UNKNOWN_ERROR: Can't create database 'ghost'; database exists

Which ....makes sense because my database I created in the PlanetScale UI is called ghost :D. Has anyone successfully initialized Ghost using PlanetScale?

[jgreet]

Hi @uncvrd 👋 I don't have any previous experience with Ghost so it's possible I'm missing something, but it looks there are at least a couple of big blockers here.

1. It doesn't appear that there's a ghost CLI option to run the initial setup without attempting to create the database, or any other built-in workaround for the problem you encountered.

2. To see what might be possible, I installed Ghost locally and dumped the database schema. Seems like it makes heavy use of foreign key constraints, a feature we don't support on PlanetScale (see How do I maintain referential integrity? #74). Attempting to import this schema directly will also fail.

$ mysqldump -u root --no-data ghost_dev > ghost_dev_schema.sql
$ pscale shell ghost setup < ghost_dev_schema.sql
ERROR 1317 (70100) at line 105: foreign key constraints are not allowed, see https://vitess.io/blog/2021-06-15-online-ddl-why-no-fk/
Error: exit status 1
$ grep CONSTRAINT ghost_dev_schema.sql
  CONSTRAINT `email_batches_email_id_foreign` FOREIGN KEY (`email_id`) REFERENCES `emails` (`id`)
  CONSTRAINT `email_recipients_batch_id_foreign` FOREIGN KEY (`batch_id`) REFERENCES `email_batches` (`id`),
  CONSTRAINT `email_recipients_email_id_foreign` FOREIGN KEY (`email_id`) REFERENCES `emails` (`id`)
  CONSTRAINT `members_email_change_events_member_id_foreign` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
  CONSTRAINT `members_labels_label_id_foreign` FOREIGN KEY (`label_id`) REFERENCES `labels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `members_labels_member_id_foreign` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
  CONSTRAINT `members_login_events_member_id_foreign` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
  CONSTRAINT `members_paid_subscription_events_member_id_foreign` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
  CONSTRAINT `members_payment_events_member_id_foreign` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
  CONSTRAINT `members_products_member_id_foreign` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `members_products_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
  CONSTRAINT `members_status_events_member_id_foreign` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
  CONSTRAINT `members_stripe_customers_member_id_foreign` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
  CONSTRAINT `members_stripe_customers_subscriptions_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `members_stripe_customers` (`customer_id`) ON DELETE CASCADE
  CONSTRAINT `members_subscribe_events_member_id_foreign` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
  CONSTRAINT `oauth_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
  CONSTRAINT `posts_authors_author_id_foreign` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`),
  CONSTRAINT `posts_authors_post_id_foreign` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
  CONSTRAINT `posts_meta_post_id_foreign` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
  CONSTRAINT `posts_tags_post_id_foreign` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
  CONSTRAINT `posts_tags_tag_id_foreign` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`)
  CONSTRAINT `products_benefits_benefit_id_foreign` FOREIGN KEY (`benefit_id`) REFERENCES `benefits` (`id`) ON DELETE CASCADE,
  CONSTRAINT `products_benefits_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
  CONSTRAINT `stripe_prices_stripe_product_id_foreign` FOREIGN KEY (`stripe_product_id`) REFERENCES `stripe_products` (`stripe_product_id`)
  CONSTRAINT `stripe_products_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
  CONSTRAINT `webhooks_integration_id_foreign` FOREIGN KEY (`integration_id`) REFERENCES `integrations` (`id`) ON DELETE CASCADE

I'd be happy to hear otherwise, but if this is what an average Ghost DB schema looks like I don't think Ghost and PlanetScale are very compatible in their current states.

[muhaimincs]

I hope there would be a tool/mechanism to restore the dump file as we do it with another mysql host

[StarryFire]

still no solution to this? :(

[jgreet]

I don't think so, no. We aren't likely to add support for foreign key constraints and AFAICT they're still used in Ghost.
See also https://forum.ghost.org/t/avoid-foreign-key-constraints-in-database-schemas-to-support-vitess/26306

[scottleechua]

This may be solved soon: Planetscale launches foreign key constraint support in beta 🚀 🎉

[JustroX]

PlanetScale now supports foreign key constraints!

There are some incompatibilities left, i.e., database creation mentioned by @jgreet, but I made a small patch to Ghost to finally make it work!

I created a guide if you want a quick setup here . I also made an example repository here if you want to play with it.

Let me know if you have any troubles with replication.