Ghost 5.x on fly.io
When I set up this blog earlier in the year, I followed an article from Curiositry.
Curiositry
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.
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"
}
}
}
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 tablepostsadd constraintposts_newsletter_id_foreignforeign key (newsletter_id) referencesnewsletters(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:
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"
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.