PlanetScale and Ghost were previously incompatible due to differences in their support for foreign key constraints. With PlanetScale now supporting foreign key constraints, a seamless collaboration between the two is achievable. Nonetheless, there remain minor incompatibilities that require resolution.

The first part of this post will show you how to set up Ghost using Docker and PlanetScale. In the second part, we'll talk about the issues when putting PlanetScale and Ghost together and how to fix them. If you just want to get things going fast, you can skip the other parts.

Part 1 - Setting up Ghost and PlanetScale

Some warning

For PlanetScale to work with Ghost, make sure foreign key constraints are turned on. It's important to note that foreign key constraints are in beta on PlanetScale. If you decide to revert your database from beta, it could potentially disrupt your Ghost website. Additional information can be found here and here. This is a crucial point to weigh before changing providers.

A. Setting up database

1. Create a database in PlanetScale

Begin by creating a new database in PlanetScale. Follow the official quickstart guide available here. Assign a name to your database and make sure to jot down your connection details, such as the host, username, and password.

2. Activate "Foreign key constraints" in your database.

Ghost requires the use of foreign key constraints to function properly. Head to your database settings by selecting Database > Settings > Beta Features. Then, click on "enroll" next to "Foreign key constraints" to enable this crucial feature.

3. Great! Let's now move on to setting up Ghost itself.

B. Setting up Ghost

1. Install Ghost.

Installing Ghost can be done in various ways, and you can choose the one that suits you best by referring to its official installation guide. However, for the sake of reproducibility, this guide extends Ghost's official Docker image.

2. Customize your Ghost installation.

At this point, you have the option to add themes and adapters. However, delving into these aspects is beyond the scope of this article.

3. Patch the Ghost installation.

Ghost and PlanetScale have a minor incompatibility, which can be resolved by applying a patch. To do this, create a script named patch.sh with the following content:

Finally, execute the patch by running:

chmod +x patch.sh
./patch.sh

Please be aware that, at the time of writing, this patch has only been tested with the latest Docker image (ghost:5.75.3) and its effectiveness may vary in future versions (or might become unnecessary). The specifics of the patch will be elaborated upon in the second part of this guide.

4. For Docker installation, here's the corresponding Dockerfile

FROM ghost:latest
COPY patch.sh patch.sh
RUN chmod +x patch.sh && ./patch.sh

Assuming everything goes smoothly, Ghost should now be configured to work seamlessly with PlanetScale. Now, let's proceed to connect them.

C. Connecting to the database

For configuring the database connection, use the PlanetScale connection details obtained from the previous steps and paste them into your Ghost configuration. Keep in mind that the value for database__connection__ssl should be set to [{"rejectUnauthorized":true}].

database__client=mysql
database__connection__database=<Your Planetscale Database Name>
database__connection__host=<Your Planetscale Database Host>
database__connection__password=<Your Planetscale Database Password>
database__connection__user=<Your Planetscale Database User>
database__connection__ssl=[{"rejectUnauthorized":true}]

The rest of the configuration process is akin to setting up a standard Ghost installation, which you can refer to in the official reference found here.

D. Run your Ghost instance

For Docker installation, launch your Docker container using the following command:

docker build . --tag ghost_example
docker run --env-file ./env ghost_example

Assuming a smooth process, you should observe your Ghost blog initializing and generating the necessary database tables. Once this initialization is complete, your Ghost blog integrated with PlanetScale should be live and ready!

E. Deployment (Optional)

I've created an example repository here for those looking to swiftly deploy a Ghost blog using PlanetScale. Simply follow the aforementioned steps for setting up the database and connecting to it. Beyond that, all that's required is to build and run the Dockerfile.

I tested this using Railway, where all that's needed is to provide the repo fork and the necessary environment variables. Railway then automatically identifies the Dockerfile for building and running. The outcome is the blog website you're currently accessing.

F. Conclusion

That concludes the setup and deployment guide. Thank you for reading! If any parts are unclear or if you encounter issues with replication, feel free to reach out. I'd be happy to help. Just shoot me an email (thepiesaresquared@gmail.com) or DM/tweet me at @justfizzbuzz.

If you're intrigued by the challenges I encountered while integrating PlanetScale and Ghost, along with the eventual solution in the form of a patch, you can proceed to Part 2. 😄

Part 2 - Integrating Ghost 🤝 PlanetScale

In my view, there are three significant hurdles that both myself and fellow self-hosters faced when attempting to integrate Ghost and PlanetScale:

1. Configuring the value for database.connection.ssl
2. Ensuring foreign key constraints are supported by PlanetScale.
3. Addressing Ghost setup failures during initialization.

A. Configuring the value for database.connection.ssl

To overcome this initial challenge, it's crucial to establish a secure connection to the database since it resides outside the same network as the server. Attempting a direct connection without SSL/TLS results in the following error:

While seemingly minor, locating the value for database.connection.ssl can be challenging. Experienced developers might find it obvious, but for those who simply copy-paste from PlanetScale, it may not be as apparent. The community has resolved this configuration value since March last year, as discussed in this forum. From the discussion, the correct value for the configuration should be:

database__connection__ssl=[{"rejectUnauthorized":true}]

B. Foreign key constraints support for PlanetScale.

The second and primary challenge arose from the absence of foreign key constraints support in PlanetScale at that time. This presented a significant hurdle as Ghost heavily relies on this feature. In a Github discussion, PlanetScale's rep said that:

It looks there are at least a couple of big blockers here ... Seems like [Ghost] makes heavy use of foreign key constraints, a feature we don't support on PlanetScale

This is the point where most discussions on integration come to a halt and encounter a roadblock.

Fast forwarding to last month, PlanetScale made a significant announcement regarding the introduction of foreign key constraints support as a beta feature:

With this recent development, achieving the integration is now technically feasible, a great time to resume and advance the integration efforts.

C. Addressing Ghost setup failures during initialization

i. The Problem

This particular challenge is a bit tricky. PlanetScale generates a database for you, and all you need to do is establish a connection. However, Ghost encounters startup issues because it erroneously assumes that the database still needs to be created. This manifests as either Ghost attempting to create the database despite its existence, or Ghost failing to detect that the PlanetScale database has already been established.

This concern was previously addressed in this discussion and ended with the following conclusion:

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.

To address this, my initial plan was centered around creating a Ghost fork and incorporating an option to skip the database creation step. Luckily, looking back, the solution turned out to be remarkably straightforward, with the bulk of my time dedicated to tracing the error and navigating through the code.

ii. Tracing Ghost's code

My initial strategy involves tracing the source of the error.

Given my limited familiarity with the majority of the codebase, I began following the execution flow of the source code, available at https://github.com/TryGhost/Ghost/blob/main/ghost/core. This journey starts from the initial Ghost initialization and progresses until I stumble upon a piece of code indicating the database creation. I tracked the code references, traversing through files such as index.js, ghost.js, boot.js, and DatabaseStateManager.js, until reaching what appeared to be a dead-end.

In DatabaseStateManager.js, Ghost triggers the initialization of the database by invoking the init method from a knexMigrator instance:

  await this.knexMigrator.init();

The knexMigrator is initialized using a class imported from another package named knex-migrator:

const KnexMigrator = require('knex-migrator');
...
this.knexMigrator = new KnexMigrator({
  knexMigratorFilePath
});
...

Given that it originates from another package, it seems like a dead-end because it likely adheres to a standard protocol for database initialization that might be fundamentally incompatible with how PlanetScale is configured. Additionally, tracing the code upward suggests there might be another package that needs forking to resolve this integration challenge between Ghost and PlanetScale.

Confronted with this challenge, I spent hours attempting to find a workaround to skip the database initialization within the ghost/core codebase. However, it turned out to be the REAL dead-end with the real breakthrough occurring when I decided to delve deeper into the "knex-migrator" package.

iii. Tracing knex-migrator package

Having exhausted other options, I decided to delve deeply into the "knex-migrator" package. The turning point in this ordeal occurred when I discovered that the "knex-migrator" package was owned by the TryGhost organization. You can view the repository here https://github.com/TryGhost/knex-migrator. This revelation indicated that the package was designed with Ghost in mind, and a potential solution might be just a simple pull request away from this repository.

Resuming the code tracing process, after investing some time, I successfully narrowed down the origin of the error when I identified the precise SQL command sent to PlanetScale. See line 126.

Notably, at line number 130, it became evident that the scenario where the database already exists had been accounted for. This discovery was somewhat surprising, considering the initial error reported by Ghost regarding the database already existing in PlanetScale. It implies that with PlanetScale, the SQL command triggers a different error number than the expected 1007 when attempting to create a database that already exists.

To confirm this, I inserted a console.error to log the error being caught at line 129, revealing:

Bingo! The error's errno is indeed different from 1007, which ultimately leads to the crash of the Ghost setup.

iv. Creating the patch

Based on the findings above, it appears that I don't necessarily need to create a Ghost setup option to skip database creation. Instead, I need Ghost to recognize that the error number originating from PlanetScale's database is not 1007.

To really resolve this issue, ensuring compatibility in error numbers is crucial. At this point, I don't have insight into where this difference in error numbers originates. It could be from the driver Ghost has utilized for communication with the MySQL database, or it might be specific to PlanetScale's implementation of the error. Individuals from the PlanetScale or Ghost's team reading this blog might have more ideas on this aspect than I do. 😀

But for my specific case, my primary goal is to achieve a functional deployment of Ghost. As a result, I am currently content with patching the currents/node_modules/knex-migrator/lib/database.js file with the following code:

  .catch(function (err) {
      // CASE: DB exists
      if (err.errno === 1007) {
          return Promise.resolve();
      }

      // Here's the patch
      const isPlanetScaleDBExists =
        err.errno == 1105 && 
        err.sqlMessage.endsWith("database exists");
        
      if (isPlanetScaleDBExists) {
          return Promise.resolve();
      }

      

      throw new errors.DatabaseError({
          message: err.message,
          err: err,
          code: 'DATABASE_CREATION_FAILED'
      });
}

To facilitate this, I crafted a script that patches Ghost during Docker build time.

file_path="current/node_modules/knex-migrator/lib/database.js"
text_to_add="const isDBExists = err.errno == 1105 && err.sqlMessage.endsWith('database exists');\\
if(isDBExists) return Promise.resolve();\\
"
line_number=129

sed -i "${line_number}i${text_to_add}" "$file_path"

The script above is designed to inject JavaScript code into the source code of the knex-migrator.

v. Final result

After crafting the patch, it took an additional hour or two to ensure my environment variables were correctly configured. Once everything fell into place, I was thrilled to finally deploy my Ghost website!

With the successful import of my blogs from the previous deployment, this Ghost site was up and running smoothly, with the database now hosted on PlanetScale! 🙌

Final Thoughts

Thank you for reading!

I appreciate you taking the time to read up to this point ❤️. If you find any parts confusing or have any issues with replication, I'd be happy to help. Just shoot me an email (thepiesaresquared@gmail.com) or DM/tweet me at @justfizzbuzz.

I thoroughly enjoyed the process of making PlanetScale and Ghost work seamlessly together! If you're interested in more posts like this, I invite you to subscribe to this blog, or let's connect and share our posts on Twitter!

Thank you 👨‍💻