Unifying your database and source control has never been easier
At Buildless, we focus on developer productivity to the point of obsession. Like many teams, we are perpetually searching for new solutions that can simplify the complex process of shipping our code; a critical component of this pipeline is our database.
Ever since we built the Open AI plugin for Planetscale at a hackathon, we've been big fans of Planetscale and use it in our app as our primary database. This post continues a series we're writing about devops in the post-cloud era.
In the past, we've had to use several tools and moving parts that keep code and database changes in sync. At least we had a process, sure, but the result was still cumbersome and error-prone. We are big fans of tools like Liquibase, Flyway, Prism, etc., which can handle schema migrations automatically. I remember the first time I saw an automatic schema migration happen: it was like magic! But these tools only help with one-half of the equation.
When do you run them? Where do you run them? Are you sure all these database operations flying by before you aren’t obliterating your DB? Locally, that’s an easy answer. Things aren't so easy when you factor in CI, staging environments, and live systems.
That is until we discovered PlanetScale and their awesome new GitHub Actions.
Keeping code in sync with databases is hard
It’s not a rare case where we have a GitHub PR which modifies code and changes the underlying database. In the past 30 days, here are some examples:
- We added a user-controlled label column to our API keys table
- We added new tables for features we’re building
- We renamed a table column to be consistent with our naming scheme
Some schema changes are easier than others, of course. Adding columns and tables is usually very easy because old code doesn’t know about it. New code often can’t go live until your database changes are live.
Compounding the problem, anything more complex than purely additive changes is a different story entirely. Deleting or changing existing schema has a good chance of breaking existing code. In these cases, your old and new code will break, necessitating the database changes and code changes go live simultaneously.
Conducting simultaneous deploys across systems is… difficult. You could try to time it right, which we all did for a long time, or you could transition across independent databases, which we did for a long time. But there is a better way.
Database branching ftw
Database branching as an application primitive is a compelling concept. First and foremost, this feature aligns our database change procedure with our source code. In a familiar way, developers can branch our main database, create a password for themselves, and freely apply schema changes without disrupting the main branch where our application lives.
Branching is built-in to PlanetScale; we’ll use this primitive to integrate dev and db ops in this post. You can manually create a branch from the dashboard or use the new PlanetScale GitHub Action to create a branch for you on each PR.
Planetscale: A Game Changer in Database Operations
PlanetScale is not just another database service. It's a solution that integrates seamlessly with our development workflow, making database operations feel like an organic part of our DevOps processes. What sets PlanetScale apart is its native ability to branch, just like our source control, and links database operations with code changes using GitHub Actions.
PlanetScale GitHub Actions: Automating Database Operations
PlanetScale has four GitHub Actions that perform common functions on a database:
1. `create-branch-action` create a branch within a PlanetScale database
2. `create-branch-password-action` create a new password for a database branch
3. `create-deploy-request-action` create a DR between two branches (more on this later)
4. `deploy-deploy-request-action` merge the changes in an existing Deploy Request.
These actions combine to create a unified changeset flow. While this flow may look complicated initially, we’ll break it down into individual steps. In the end, it’s quite simple:
Let’s look at how this works step-by-step.
Step 1: Converging database branching with source branching
The first step in the flow is to make sure a database branch always stays in sync with a source branch; this takes two constituent steps:
- Whenever a branch is created in your repository, create a matching database branch.
- Run your latest migrations against that branch.
This first component is probably the most important because it ensures that a “branch” is just one thing across your database and source code. As of `v3`, the `create-planetscale-branch` action can check if a branch already exists and gracefully fail, allowing an easy “always converge” trigger pattern with Actions.
It is important to run your migrations so your database branch always reflects the state of your code branch; this is where your Liquibase, Flyway, Rails, et al. enter the picture.
Step 2: Creating and storing secrets
In order to facilitate running your migrations against your new branch, you will need to persist the password you just created. This can be done using additional GitHub Actions, and using GitHub’s Secrets feature for repositories.
(Code sample)
With our secrets stored, we can pass them in to our migrations step:
(Code sample)
For an extra smooth devex flow, we comment these secrets on the PR where the developer can access them. Remember, these secrets point directly to our branch, so it’s safe to share on a pull request!
The developer can install these secrets in their local environment, and use their branch as needed:
(Code sample)
Step 3: Filing a Deploy Request
In PlanetScale, a Deploy Request (“DR”) is the equivalent primitive of GitHub’s infamous Pull Request. Your DR is where you can gather your changes, and propose them to be merged into the base branch of your database.
In our case on the Elide team, we prefer a draft/ready-for-review workflow, so we file the DR when the PR is ready for review. This is totally optional, though, thanks to the flexibility granted by PlanetScale’s GitHub Actions.
(Code sample)
Step 4: Closing and merging
GitHub Action workflows can run on nearly any Pull Request event, including the close event. It’s possible to make steps conditional based on the outcome of the close: was it merged, or just closed? If it was merged, we can trigger a merge of the Deploy Request in PlanetScale accordingly:
(Code sample)
Now, when our PR is merged, our DB schema will be merged, too.
> Note:
It’s important to run your migrations again when you close the DR, as it only contains the schema changes gathered on your database branch. If you create fixtures in your migrations (or otherwise create data instead of just structure), these changes will be lost without one more migration run against the base branch.
Putting it all together
After wiring together our PR events to the PlanetScale actions in full, our workflow looks roughly like this:
With this flow, we’ve achieved a completely unified change → review → apply cycle across our source code and our database! 🎉🥳
Real-world unified devops
With the hard part over, let’s take a look at the outcome of our flow. I mentioned at the top of this post that we had some real-world schema changes over the past few months; I’d like to share some screenshots that show just how smooth and awesome this flow can be:
(Series of screenshots)
Next up: Integrating with deployment infrastructure
In a follow-up post, we plan to detail how we integrated this workflow with our CD provider, Octopus. Coordinating live DB migrations can be a pain, but with PlanetScale, GitHub, and Octopus, we’ve seen some amazing results.
A new level of unified db-ops with PlanetScale
PlanetScale has transformed the way we handle database changes in our DevOps pipeline at Buildless. By treating database changes as an integral part of our codebase and automating database operations using GitHub Actions, we've made our processes more efficient and less prone to errors. This is the future we envision for DevOps – a world where code and database changes are seamlessly integrated, and manual interventions are minimized.
If you're in search of a solution that can streamline your DevOps pipeline, we highly recommend exploring PlanetScale. It's not just about managing databases; it's about reimagining how database operations can be seamlessly integrated into your development workflow.
