TECH BLOG #6: How to Automatically Create PlanetScale Database Branches From Bitbucket Pipelines

PlanetScale offers many features but one of the most powerful and interesting ones is the ability to branch databases and their schemas. We can use this feature to implement updates and changes to a database schema in a controlled way while also being able to test changes in an isolated environment that mirrors production.

However, while that all sounds amazing (and it is), there is one rather large annoyance with it and that’s the limit of database branches you’re allowed to use at once with PlanetScale. Currently, unless you opt for their enterprise level plan you’re only allowed 5 development branches. This means if you’re in the situation of needing more than 5 database branches at once but you don’t need any of the other features offered by the higher plans, you get a rather large bill just for more branches which isn’t the most ideal or economic solution.

Our Solution

To get around this limitation, we decided to create new database branches only for certain git branches because after all not all git branches you work on will require their own database branch with a unique schema. Instead, only certain git branches will require their own database branch, these are the ones that change or manipulate the schema in some way that would cause conflicts with other git branches that don’t have those changes.

So, what we do is give the power to the developer to control if they need a new database branch or not by how they name their git branch. This means there are three paths they can go down.

1. Schema changes not required → Just use any normal git branch type feature/, fix/, etc.

2. Schema changes required → Use our pre-defined git branch type feature-db/

3. Schema changes not originally required but become required → Start by using the normal type like feature/ and then once schema changes are required, rename the branch to feature-db/.

At this point, you may be asking how does the git branch name control if a database branch is created or not and that’s where our CI environment Bitbucket Pipelines comes in. We have a pipeline step that runs for every feature-db/ branch that is created, this pipeline step is what creates the PlanetScale database branch.

So, with all of that covered, let’s jump into getting this setup and configured. The only things you’ll need prior to going through this tutorial is a working PlanetScale database with a production branch configured and a repository configured on Bitbucket with pipelines enabled.

Setting It Up

PlanetScale CI Scripts

The first thing we need to do to configure this setup for a repository is to configure the PlanetScale CI scripts in our repository. To do this, you’ll want to create a couple of new folders in the root directory of your project, the first one is .pscale and then the second one is a sub-directory of that folder called cli-helper-scripts.

After creating these folders, we’re then going to copy some script files into the cli-helper-scripts folder from PlanetScale’s GitHub which you can find here. The script files we’ll need to copy are.

  • ps-create-helper-functions.sh
  • use-pscale-docker-image.sh
  • wait-for-branch-readiness.sh
  • authenticate-ps.sh

After we’ve copied those script files, we’ll need to make a small adjustment to the create-db-branch function inside of the ps-create-helper-functions.sh file. Inside that function replace the line pscale branch create "$DB_NAME" "$BRANCH_NAME" --region us-east --org "$ORG_NAME" with the below code.


local raw_output=`pscale branch create "$DB_NAME" "$BRANCH_NAME" --region  --org "$ORG_NAME"`
if [ "$raw_output" == "Error: Name has already been taken" ]; then
    echo "Branch $BRANCH_NAME already exists. Skipping ..."
    exit 0
fi

The reason we make this change is so if we try to create a branch that already exists on PlanetScale we exit with a successful code. This is important because this script will be triggered each time the branch is pushed to Bitbucket and without this change we could end up with multiple failures when in fact everything is working as expected.

After making this amendment, we also need to create a new script that will handle the actual branch creation for us on PlanetScale. So, create a new file alongside the other scripts called create-branch.sh and add the below code to it.


#!/bin/bash

# Get the root directory path of the git project
projectRoot=$( git rev-parse --show-toplevel )

echo "Running docker-image"
. use-pscale-docker-image.sh

echo "Running wait-for-branch"
. wait-for-branch-readiness.sh

echo "Running authenticate"
. authenticate-ps.sh

BRANCH_NAME=$(node $projectRoot/scripts/extract-branch-name.js $1)

echo "BRANCH_NAME = $BRANCH_NAME"

. ps-create-helper-functions.sh

create-db-branch "$DB_NAME" "$BRANCH_NAME" "$ORG_NAME"

Related blogs

Tweet from Neal Riley saying "Commercial freedom and flexibility is a must for any digitally enabled business. @SalableApp gives you the tools to build your SaaS business."

Developers love how easy it is to grow a SaaS business with Salable

Learn more →

Now all we need to do is create the JavaScript file you see mentioned on line 15 to extract the branch name from the string passed to it. For example, we can pass the entire branch name such as feature-db/example-branch and this script would extract the example-branch portion so we can then use it as the branch name on PlanetScale.

To set this script up, create a new file called extract-branch-name.js inside a scripts folder in the root of your repository and add the below code to it.


const [branch_name] = process.argv.slice(2);

const regex = /[^\/]+$/;
let clean_branch_name;

if (branch_name.match(regex)) {
  clean_branch_name = branch_name.match(regex)[0];
} else {
  clean_branch_name = branch_name;
}

if (!/^[a-zA-Z0-9_-]+$/.test(clean_branch_name)) {
  const error = `The branch name contains illegal characters: ${clean_branch_name}`;
  console.error(error);
  process.exit = 1;
  return;
}

console.log(clean_branch_name);

With this JavaScript file created, we’ve configured all of the scripts required for the pipeline to run so all we need to do is configure our Bitbucket Pipeline step and the environment variables for it to function so let’s do that next.

Bitbucket Pipeline & ENVs

To add the step to our Bitbucket pipeline, we can add the below code to our bitbucket-pipelines.yml file under the branches trigger.


pipelines:
  branches:
    'feature-db/**':
          - step:
              name: Create PScale Branch
              image: ubuntu:latest
              services:
                - docker
              script:
                - apt-get update && apt-get install jq curl unzip git grep -y
                - curl -o- https://raw.githubusercontent.com/nvm-sh/nvm/v0.39.1/install.sh | bash
                - source ~/.bashrc
                - nvm install v16
                - cd .pscale/cli-helper-scripts
                - . ./create-branch.sh $BITBUCKET_BRANCH
                

This configuration will trigger the create branch script every time a branch is created or pushed to that starts with feature-db, this is why earlier we needed to update the create-db-branch function to not fail if we try to create an existing branch which would cause excess failures in our pipeline logs.

With our pipelines now configured, all we need to do is add in our environment variables on Bitbucket for the scripts to function correctly. To do that, head to “Repository Settings” and then “Repository Variables” for your repository. Then you’ll want to add in the below variables.

You’ll need to create a PlanetScale Service Token to perform the actions we’ve configured and run the CI scripts. To do this, you can follow their guide here.
  • DB_NAME: Name of your database on PlanetScale.
  • ORG_NAME: Name of your oragnization on PlanetScale.
  • PLANETSCALE_SERVICE_TOKEN: The value of your PlanetScale Service Token.
  • PLANETSCALE_SERVICE_TOKEN_ID: The ID of your PlanetScale Service Token.

After adding these variables, everything should be configured and we can test that everything works!

Testing

To test if everything has been setup correctly, create a new branch for your repository by using the Bitbucket UI or by creating one locally and pushing it. Ensure your new branch is using the pre-defined naming structure, in our case this is feature-db/YOUR_BRANCH_NAME.

Then after your branch has been created, watch your pipeline run and hopefully if all goes to plan, a new branch should have been created on PlanetScale using the name of your git branch. If this is the case then everything is working as expected!

Closing Thoughts

In this post, we’ve covered how to configure a Bitbucket repository to use Bitbucket pipelines to create dedicated PlanetScale database branches based on if the source git branch uses a pre-defined naming structure. This aids us in not going above the 5 development branches and needing to upgrade to a more expensive plan just for a higher allowance.

I hope you found this post helpful.

Coner Murphy, Salable Team - The Adaptavist Group

Tweet from Neal Riley saying "Commercial freedom and flexibility is a must for any digitally enabled business. @SalableApp gives you the tools to build your SaaS business."

Tech Blog #4: How to version all packages synchronously, in a monorepo using Lerna

Today we are setting up automatic versioning of packages inside of a monorepo using Lerna. There are many ways in which you can do this with different technologies...

Coner Murphy
22 Nov 2022

Tech Blog #5: Automatically sync multiple repositories versions using GitHub Actions

We have a custom JavaScript library for our pricing table, at a high level this JS library is responsible for displaying a product’s various pricing tables...

Coner Murphy
9 Dec 2022

Tech Blog #3: Using a private GitHub repository as an NPM package in another repository

Creating NPM packages is an easy and convenient way to share code across multiple projects. But, if you want to keep the package private and are unwilling to pay for the

Coner Murphy
07 Nov 2022