U+237B NOT CHECKMARK

Ghost 5.x on fly.io

When I set up this blog earlier in the year, I followed an article from Curiositry.

Host a Ghost 5.0 Blog for Free on Fly.io In 1 Minute — SQLite edition
Ghost is one of the fastest-growing publishing platforms. However, for those looking to dip their toes in the water, one aspect can be off-putting: the shiny Ghost(Pro) hosting (referral link) starts at $11/month or $108/year — $300/year a year if you want to use your own theme

At the time, I set using the ghost:4-alpine image and everything worked fine. I chose 4.x because I wanted to use SQLite in production and for 5.x, Ghost dropped production SQLite support.

Ghost has a history of dropping database support. Early in the product, they supported Postgres but support was removed to ease the maintenance burden and they said they were doing the same for production SQLite. I accept that it's easier for them as a small maintainer team, so I looked into using MySQL.

It's been a really long time since I've used MySQL in production. One thing I knew is that I never want to worry about versions, security, or backups. Fly.io, the hosting provider for this blog, doesn't offer a managed MySQL, so I looked elsewhere. One of the fly.io engineers said they recommend PlanetScale – great. Especially since they offer a free tier.

PlanetScale is pretty slick. It took me a few minutes to create an account and create a database. They have a pop-up that shows you how to connect with your framework or language of choice.

Run this command to connect with the proper SSL settings

I fought with the SSL settings for a while and eventually figured it out. It's frustrating that MySQL's default 3306 port is used for both plain text and SSL connections. The official Ghost documentation tells you to specify the server public key, which is not correct.

"database": {
  "client": "mysql",
  "connection": {
    "host": "your_cloud_database",
    "port": 3306,
    "user": "your_database_user",
    "password": "your_database_password",
    "database": "your_database_name",
    "ssl": {
      "ca": "-----BEGIN CERTIFICATE-----\nMIIFY... truncated ...pq8fa/a\n-----END CERTIFICATE-----\n-----BEGIN CERTIFICATE-----\nMIIFY... truncated ...wn8v90/a\n-----END CERTIFICATE-----\n"
    }
  }
}
This is a lie

I tried using openssl s_client to dump the PlanetScale MySQL server certificate but MySQL's design prevents this. As far as I can tell, there's no easy way for a client to get the server certificates.

I eventually pulled up the Ghost source, specifically the source for their Knex, the ORM, and looked at how it connects.

We just need to specify two flags in the SSL object: rejectUnauthorized and secureProtocol. I shoved these into my fly.toml and pushed a new deploy.

# fly.toml file generated for notcheckmark on 2022-05-20T23:24:53-04:00

app = "notcheckmark"

kill_signal = "SIGINT"
kill_timeout = 5
processes = []

[build]
  image = "ghost:alpine"

[env]
  database__client = "mysql"
  database__connection__host = "us-east.connect.psdb.cloud"
  database__connection__user = "wd1ieunfdb7l9xqyrld"
  database__connection__database = "notcheckmark-ghost"
  database__connection__port = 3306
  database__connection__ssl__rejectUnauthorized = "true"
  database__connection__ssl__secureProtocol = "TLSv1_2_method"

<snip>

It connected, but it threw a failure! On connect, Ghost tried to create the database and it already existed. This was a little curious, Ghost should be able to handle the database already existing. It's a pretty simple change in the SQL to add IF NOT EXISTS after CREATE DATABASE.

I found the source I'd need to change to get it working, but I definitely don't want to maintain my own fork or build my own docker images. The whole point of using Ghost on fly.io was because it was effectively turn key.

Ghost publishes a tool called knex-migrator, which they use to initialize their database and apply migrations. Since it's only the CREATE DATABASE call, I could make the change locally and populate the database myself.

I make the change to CREATE DATABASE IF NOT EXISTS and then run the migrator.

» DEBUG=knex-migrator:* yarn knex-migrator  init
yarn run v1.22.19
warning ghost@5.22.6: The engine "cli" appears to be invalid.
$ /Users/ryan/public-src/Ghost/node_modules/.bin/knex-migrator init
  knex-migrator:database Create database notcheckmark-ghost +0ms
  knex-migrator:database Destroy connection +6s
  knex-migrator:database Creating table: migrations +1s
  knex-migrator:lock-table Ensure Lock Table. +0ms
  knex-migrator:field-length Ensure Field Length. +0ms
  knex-migrator:use-index Ensure Unique Index. +0ms
  knex-migrator:lock-table Add primary key to the lock table. +0ms
  knex-migrator:lock-table Primary key constraint for: lock_key already exists for table: migrations_lock +165ms
  knex-migrator:locking Lock. +0ms
  knex-migrator:index Before hook +0ms
  knex-migrator:utils [ '1-create-tables.js', '2-create-fixtures.js' ] +0ms
  knex-migrator:utils [
  knex-migrator:utils   {
  knex-migrator:utils     up: [AsyncFunction (anonymous)],
  knex-migrator:utils     down: undefined,
  knex-migrator:utils     config: undefined,
  knex-migrator:utils     name: '1-create-tables.js'
  knex-migrator:utils   },
  knex-migrator:utils   {
  knex-migrator:utils     up: [AsyncFunction: insertFixtures],
  knex-migrator:utils     down: undefined,
  knex-migrator:utils     config: { transaction: true },
  knex-migrator:utils     name: '2-create-fixtures.js'
  knex-migrator:utils   }
  knex-migrator:utils ] +4ms
  knex-migrator:index Migrate: init with 2 tasks. +693ms
  knex-migrator:index Tasks: [{"name":"1-create-tables.js"},{"config":{"transaction":true},"name":"2-create-fixtures.js"}] +0ms
  knex-migrator:index Running up: 1-create-tables.js +386ms
[2022-11-13 22:49:42] INFO Creating table: newsletters
[2022-11-13 22:49:43] INFO Creating table: posts
  knex-migrator:locking Unlock. +4s
  knex-migrator:index Rolling back: alter table `posts` add constraint `posts_newsletter_id_foreign` foreign key (`newsletter_id`) references `newsletters` (`id`) - foreign key constraints are not allowed, see https://vitess.io/blog/2021-06-15-online-ddl-why-no-fk/ +2s
  knex-migrator:utils [ '1-create-tables.js', '2-create-fixtures.js' ] +3s
  knex-migrator:utils [
  knex-migrator:utils   {
  knex-migrator:utils     up: [AsyncFunction (anonymous)],
  knex-migrator:utils     down: undefined,
  knex-migrator:utils     config: undefined,
  knex-migrator:utils     name: '1-create-tables.js'
  knex-migrator:utils   },
  knex-migrator:utils   {
  knex-migrator:utils     up: [AsyncFunction: insertFixtures],
  knex-migrator:utils     down: undefined,
  knex-migrator:utils     config: { transaction: true },
  knex-migrator:utils     name: '2-create-fixtures.js'
  knex-migrator:utils   }
  knex-migrator:utils ] +0ms
  knex-migrator:index No down function provided 2-create-fixtures.js +2ms
  knex-migrator:index No down function provided 1-create-tables.js +67ms
  knex-migrator:index Shutdown hook +69ms
  knex-migrator:index Destroy connection +2ms
  knex-migrator:index Destroyed connection +0ms
[2022-11-13 22:49:45] ERROR alter table `posts` add constraint `posts_newsletter_id_foreign` foreign key (`newsletter_id`) references `newsletters` (`id`) - foreign key constraints are not allowed, see https://vitess.io/blog/2021-06-15-online-ddl-why-no-fk/

alter table `posts` add constraint `posts_newsletter_id_foreign` foreign key (`newsletter_id`) references `newsletters` (`id`) - foreign key constraints are not allowed, see https://vitess.io/blog/2021-06-15-online-ddl-why-no-fk/

{"name":"1-create-tables.js"}
"Error occurred while executing the following migration: 1-create-tables.js"

Error ID:
    300

Error Code:
    ER_UNKNOWN_ERROR

----------------------------------------

Error: alter table `posts` add constraint `posts_newsletter_id_foreign` foreign key (`newsletter_id`) references `newsletters` (`id`) - foreign key constraints are not allowed, see https://vitess.io/blog/2021-06-15-online-ddl-why-no-fk/
    at /Users/ryan/public-src/Ghost/node_modules/knex-migrator/lib/index.js:1032:19
    at Packet.asError (/Users/ryan/public-src/Ghost/node_modules/mysql2/lib/packets/packet.js:728:17)
    at Query.execute (/Users/ryan/public-src/Ghost/node_modules/mysql2/lib/commands/command.js:29:26)
    at Connection.handlePacket (/Users/ryan/public-src/Ghost/node_modules/mysql2/lib/connection.js:456:32)
    at PacketParser.onPacket (/Users/ryan/public-src/Ghost/node_modules/mysql2/lib/connection.js:85:12)
    at PacketParser.executeStart (/Users/ryan/public-src/Ghost/node_modules/mysql2/lib/packet_parser.js:75:16)
    at TLSSocket.<anonymous> (/Users/ryan/public-src/Ghost/node_modules/mysql2/lib/connection.js:360:25)
    at TLSSocket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:293:12)
    at readableAddChunk (internal/streams/readable.js:267:9)
    at TLSSocket.Readable.push (internal/streams/readable.js:206:10)
    at TLSWrap.onStreamRead (internal/stream_base_commons.js:188:23)

error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.

It made it further but it didn't finish. Maybe I did something wrong?

The error says "foreign key constraints are not allowed". Maybe I made the wrong database type? I poked around the code a bit more and didn't see any obvious issues. MySQL supports foreign key constraints via the InnoDB table and has for decades and obviously PlanetScale would support InnoDB.

Looking closer at the error, it specifically says:

Error: alter table posts add constraint posts_newsletter_id_foreign foreign key (newsletter_id) references newsletters (id) - foreign key constraints are not allowed, see https://vitess.io/blog/2021-06-15-online-ddl-why-no-fk/

The vitess.io blog post goes into excruciating detail about why foreign keys don't even make sense in online DDL – whatever that means.

I searched my local copy of the Ghost and Knex source trees for the error and couldn't find it. The error was actually coming from PlanetScale. When I searched Google for "PlanetScale foriegn keys", this article came up:

Operating without foreign key constraints - Documentation - PlanetScale
How to manage your relational data without formal foreign key constraints in your schema

PlanetScale doesn't support Foreign Keys because they use Online DDL. Great.

If I wanted to continue using PlanetScale, I could go and delete all the foreign key constraints and hope the queries still worked. It would be a terrible hack but also may require me to fight this every time there was a new migration.

Solution

I was pretty discouraged and considered just throwing money at the problem and signing up to pay $11/month on ghost.org.

Knex should work on Postgres, maybe I could use fly's managed postgres. I could also just run a self-managed MySQL on fly – but I really didn't want to do that. I wanted to not think about the blog or the software.

It was pretty annoying, you could use SQLite locally in development mode. SQLite is a fine database. It's not any less robust than me hosting my own MySQL next to it.

Since I was already changing code, I decided I was just going to remove all the checks that force MySQL in production and I was going to continue using SQLite. After fiddling around a bit, I realized I didn't need to change any code, I just needed to explicitly configure the database to be SQLite.

# fly.toml file generated for notcheckmark on 2022-05-20T23:24:53-04:00

app = "notcheckmark"

kill_signal = "SIGINT"
kill_timeout = 5
processes = []

[build]
  image = "ghost:alpine"

[env]
  database__client = "sqlite3"
  database__connection__filename = "content/data/ghost.db"

[experimental]
  allowed_public_ports = []
  auto_rollback = true

[[services]]
  http_checks = []
  internal_port = 2368
  processes = ["app"]
  protocol = "tcp"
  script_checks = []

  [services.concurrency]
    hard_limit = 25
    soft_limit = 20
    type = "connections"

  [[services.ports]]
    force_https = true
    handlers = ["http"]
    port = 80

  [[services.ports]]
    handlers = ["tls", "http"]
    port = 443

  [[services.tcp_checks]]
    grace_period = "1s"
    interval = "15s"
    restart_limit = 0
    timeout = "2s"

[mounts]
    source="ghost_data"
    destination="/var/lib/ghost/content"
The complete fly.toml file

This worked fine and I felt really dumb. Now I'm running the latest Ghost and I don't have to think about versions.

I don't know if there's a moral for this story. It's all my fault for reading the ghost 5.x change log and trusting it.