Apply database migrations to PlanetScale from GitHub Actions

Automating most parts of the software development lifecycle is encouraged in software engineering to benefit from productivity and improved developer experience.

A CI/CD pipeline is the preferred way to automate the release of software applications. While it is expected to automate the deployment of all kinds of applications (backend, frontend, mobile, web extension, etc...) through a CI/CD pipeline, we can achieve the same for a database when applying schema changes.

This article shows how to build a CI/CD pipeline with GitHub Actions to apply database migrations hosted on PlanetScale.

The CI/CD pipeline requirements

As a developer building a feature that includes performing database changes such as creating, altering, or deleting a database table:

• When I open a pull request, if there is a database change, I want to see the difference in changes with the production database in the pull request comment.
• When I merge the pull request on the main branch, I want to apply the migration to the production database and delete the database development branch.

Note: The CI pipelines must run only if there is a database change.

The use case

In this tutorial, we build a REST API in Node.js using Prisma and PlanetScale. The API exposes endpoints for creating, updating, retrieving, and deleting blog posts.

We want to allow users to:

• Define tags on their blog post to better categorize them.
• Mark the blog post slug as unique to prevent duplicates.
• Add a user to set their avatar.

These requirements imply changing the database structure, and we want to automate the process by applying them.

Our goal is to define a CI/CD pipeline for the database migrations of this API.

Prerequisites

To follow this tutorial, make sure you have the following tools installed on your computer.

• A PlanetScale database with a production branch.
• The PlanetScale CLI installed - Installation guide.
• You have basic knowledge of using GitHub Actions.
• Node.js 20.6 or higher - Download link
• NPM or Yarn (I will use Yarn)
• Docker is installed to run a shadow database for Prisma migrations.

Set up the project

We are starting from the final project we built in this tutorial, which demonstrates how to create a Node.js REST API with Prisma and PlanetScale.

Let's clone the project locally using the Git sparse checkout.

git clone --no-checkout https://github.com/tericcabrel/blog-tutorials.git

cd blog-tutorials

git sparse-checkout init --cone

git sparse-checkout set node-prisma-rest-api

git checkout @

mv node-prisma-rest-api planetscale-cicd-github-actions

cd planetscale-cicd-github-actions

yarn install

cp .env.example .env

If you followed the previous tutorial, your production database schema is up to date, so you can skip the section below.

Populate the production database with existing migrations

If you are starting from this tutorial, you must apply the existing database migrations before continuing.

Run the following commands to authenticate and create a database branch:

docker run -d -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=blog --name core-shadow-db -p 3307:3306 mysql:8.0.37

pscale auth login

pscale branch create core-db create-main-table

# Wait for a few seconds for the database branch to be ready

pscale connect core-db create-main-table

You will get an output similar to the one below:

The database is accessible through port 3306, and the connection URL is mysql://root@localhost:3306/core-db

The shadow database is accessible through port 3307, and the connection URL is mysql://root:secret@localhost:3307/blog

Why you need a shadow database when using the Prisma ORM

When running a Prisma migration on a cloud-hosted database, you need a shadow database to see your migration succeed. In this post, we will see why it is required.

Teco TutorialsEric Cabrel TIOGO

Open the ".env" file and replace the database value of the DATABASE_URL field with the above value.

DATABASE_URL=mysql://root@localhost:3306/core-db
SHADOW_DATABASE_URL=mysql://root:secret@localhost:3307/blog

Open a new terminal and run the command below to generate and execute the database migration.

yarn prisma migrate dev --name create-main-table

Create a deploy request to update the production database from the database branch "create-main-table"

pscale deploy-request create core-db create-main-table

The command will print the deploy request number.

Use the deploy request number to approve the changes and update the production branch.

pscale deploy-request deploy core-db <DEPLOY_REQUEST_NUMBER>

Let's try to start the REST API service by running the following command:

yarn start

The Node.js API service is connected to the database with an up-to-date schema.

Create a database migration with Prisma

We want users to add tags to their posts, set their avatar, and create unique blog posts based on the slug.

We must create a new database branch from the main

pscale branch create core-db add-post-tags-table

# Wait for a few seconds for the database branch to be ready

pscale connect core-db add-post-tags-table

Let's update the Prisma schema to define the database schema:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider          = "mysql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
  relationMode      = "prisma"
}

enum PostStatus {
  draft
  published
  archived
}

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique @db.VarChar(100)
  password  String    @db.VarChar(100)
  fullName  String    @map("full_name")
  birthDate DateTime  @map("birth_date") @db.Date
  avatar    String    @db.VarChar(255)
  createdAt DateTime  @default(now()) @map("created_at")
  updatedAt DateTime  @updatedAt @map("updated_at")
  posts     Post[]
  PostTag   PostTag[]
}

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  slug       String     @unique
  content    String     @db.Text
  status     PostStatus @default(draft)
  viewCount  Int        @default(0) @map("view_count")
  isFeatured Boolean    @default(false) @map("is_featured")
  userId     Int        @map("user_id")
  createdAt  DateTime   @default(now()) @map("created_at")
  updatedAt  DateTime   @updatedAt @map("updated_at")
  author     User       @relation(fields: [userId], references: [id], onDelete: Cascade)
  PostTag    PostTag[]
}

model PostTag {
  post    Post     @relation(fields: [postId], references: [id])
  postId  Int      @map("post_id")
  user    User     @relation(fields: [userId], references: [id])
  userId  Int      @map("user_id")
  addedAt DateTime @default(now()) @map("added_at")

  @@id([postId, userId])
}

Generate a new migration for the above changes:

yarn prisma migrate dev --name add-post-tags-table

The following SQL will be executed on the production branch

-- AlterTable
ALTER TABLE `User` ADD COLUMN `avatar` VARCHAR(255) NOT NULL;

-- CreateTable
CREATE TABLE `PostTag` (
    `post_id` INTEGER NOT NULL,
    `user_id` INTEGER NOT NULL,
    `added_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`post_id`, `user_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateIndex
CREATE UNIQUE INDEX `Post_slug_key` ON `Post`(`slug`);

Let's achieve that by defining the CI/CD pipeline on GitHub Actions.

Generate a PlanetScale service token

You can create a service token from:

• The PlanetScale Web console: In your PlanetScale organization account settings, go to the service token page and generate a token.
• From the PlanetScale CLI.

Run the following commands to create a service token using the PlanetScale CLI:

pscale service-token create

pscale service-token add-access <TOKEN_ID> approve_deploy_request connect_branch create_branch create_comment create_deploy_request delete_branch delete_branch_password read_branch read_comment read_database read_deploy_request write_database --database core-db

The first command creates the service token and prints the ID and the Value in the terminal, while the second command grants access to the database.

Replace the Token ID in the second command with your own.

Check out this link to see the list of available permissions.

Add GitHub Actions secrets for PlanetScale

To interact with your PlanetScale database from the GitHub Actions workflow, you must define the required variables injected in the CI pipeline's runner.

These variables are:

• PLANETSCALE_ORG_NAME: Your PlanetScale organization
• PLANETSCALE_DATABASE_NAME: The PlanetScale database in your organization.
• PLANETSCALE_SERVICE_TOKEN_ID: The Service token ID
• PLANETSCALE_SERVICE_TOKEN: Service token value.

The video below shows you how to add a GitHub Actions secret in a repository.

Check the migrations on a Pull Request opening

When a developer opens a pull request, we will check if there has been a database migration, then generate the difference between the production database and display it in the pull request comment.

PlanetScale provides a GitHub Action to simplify the configuration of our CI pipeline. Let's use it to make our life easier.

GitHub Actions for a repository lie in the folder ".github/workflows"; let's create a file "migration-db-check.yml" and add the following code:

name: Check database migration
on:
  pull_request:
    branches:
      - 'main'
    paths:
      - 'prisma/migrations/**'

env:
  PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PLANETSCALE_SERVICE_TOKEN_ID }}
  PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PLANETSCALE_SERVICE_TOKEN }}

jobs:
  migration-detail:
    runs-on: ubuntu-latest
    permissions: write-all
    steps:
      - uses: actions/checkout@v4

      - name: Setup pscale
        uses: planetscale/setup-pscale-action@v1

      - name: Set database branch name
        run: echo "PSCALE_BRANCH_NAME=$(echo ${{ github.head_ref }} | tr -cd '[:alnum:]-'| tr '[:upper:]' '[:lower:]')" >> $GITHUB_ENV

      - name: Create deploy request on the production branch
        run: |
          DEPLOY_REQUEST_STATE=$(pscale deploy-request show ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.PSCALE_BRANCH_NAME }} --org ${{ secrets.PLANETSCALE_ORG_NAME }} -f json | jq -r '.state')

          echo "Deploy request State: $DEPLOY_REQUEST_STATE"

          if [ "$DEPLOY_REQUEST_STATE" = "open" ]; then
            echo "Deploy request exists: skipping creation"
          else
            echo "Deploy request does not exist : creating..."
            pscale deploy-request create ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.PSCALE_BRANCH_NAME }} \
            --org ${{ secrets.PLANETSCALE_ORG_NAME }} \
            --into main \
            --notes "Apply changes to the dev branch"
          fi

      - name: Get deploy request number
        run: |
          DEPLOY_REQUEST_NUMBER=$(pscale deploy-request show ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.PSCALE_BRANCH_NAME }} --org ${{ secrets.PLANETSCALE_ORG_NAME }} -f json | jq -r '.number')
          echo "DEPLOY_REQUEST_NUMBER=$DEPLOY_REQUEST_NUMBER" >> $GITHUB_ENV

      - name: Check deployment state
        continue-on-error: false
        run: |
          for i in {1..10}; do
            DEPLOYMENT_STATE=$(pscale deploy-request show ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.DEPLOY_REQUEST_NUMBER }} --org ${{ secrets.PLANETSCALE_ORG_NAME }} --format json | jq -r '.deployment_state')
            echo "Deployment State: $DEPLOYMENT_STATE"

            if [ "$DEPLOYMENT_STATE" = "ready" ]; then
              echo "Deployment state is ready. Continuing."
              echo "DEPLOY_REQUEST_OPENED=true" >> $GITHUB_ENV
              break
            fi

            echo "Deployment state is not ready. Waiting 2 seconds before checking again."
            sleep 2
          done

      - name: Collect the migration diff
        continue-on-error: false
        if: ${{ env.DEPLOY_REQUEST_OPENED }}
        run: |
          DEPLOY_DATA=$(pscale api organizations/${{ secrets.PLANETSCALE_ORG_NAME }}/databases/${{ secrets.PLANETSCALE_DATABASE_NAME }}/deploy-requests/${{ env.DEPLOY_REQUEST_NUMBER }}/deployment --org planetscale)
          CAN_DROP_DATA=$(echo "$DEPLOY_DATA" | jq -r '.deploy_operations[] | select(.can_drop_data == true) | .can_drop_data')

          echo "Deploy request opened: https://app.planetscale.com/${{ secrets.PLANETSCALE_ORG_NAME }}/${{ secrets.PLANETSCALE_DATABASE_NAME }}/deploy-requests/${{ env.DEPLOY_REQUEST_NUMBER }}" >> migration-message.txt
          echo "" >> migration-message.txt

          if [ "$CAN_DROP_DATA" = "true" ]; then
            echo ":rotating_light: You are dropping a column. Before running the migration make sure to do the following:" >> migration-message.txt
            echo "" >> migration-message.txt

            echo "1. [ ] Deploy app changes to ensure the column is no longer being used." >> migration-message.txt
            echo "2. [ ] Once you've verified it's no used, run the deploy request." >> migration-message.txt
            echo "" >> migration-message.txt
          else
            echo "When adding to the schema, the Deploy Request must be run **before** the code is deployed." >> migration-message.txt
            echo "Please ensure your schema changes are compatible with the application code currently running in production." >> migration-message.txt
            echo "" >> migration-message.txt

            echo "1. [ ] Successfully run the Deploy Request" >> migration-message.txt
            echo "2. [ ] Deploy this PR" >> migration-message.txt
            echo "" >> migration-message.txt
          fi

          echo "\`\`\`diff" >> migration-message.txt
          pscale deploy-request diff ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.DEPLOY_REQUEST_NUMBER }} --org ${{ secrets.PLANETSCALE_ORG_NAME }} -f json | jq -r '.[].raw' >> migration-message.txt
          echo "\`\`\`" >> migration-message.txt

      - name: Comment pull request with the migration diff
        uses: thollander/actions-comment-pull-request@v2
        if: ${{ env.DEPLOY_REQUEST_OPENED }}
        with:
          filePath: migration-message.txt

          

This action workflow has eight steps:

1. Check out the GitHub repository.
2. Install the GitHub Action to set up PlanetScale.
3. Infer the database branch from the pull request branch name and expose it as an environment variable. This implies that your Git branch name must be the same as your PlanetScale database branch.
4. Create a deploy request from the database branch to the production branch,
5. Retrieve the deploy request number.
6. Check the deployment state to ensure there is no error.
7. If the deploy request does not contain an error, we retrieve the migration difference between the production branch and the current one and save it in a text file.
8. Add a comment about the migration difference to the pull request.

This workflow is triggered only for a pull request on the main branch where there is a change in the folder "prisma/migrations" containing the generated database migrations.

Run the GitHub Actions workflow

Create a branch, commit your changes, push to the remote repository

git checkout -b add-post-tags-table

git add .

git commit -m "Apply migrations for adding post tags"

git push origin add-post-tags-table

Create a pull request. The CI pipeline will start automatically.

Once the GitHub Actions workflow finishes, the Pull request will include a comment highlighting the change that will be applied.

This is helpful because the pull request reviewers can see and evaluate the impact of the changes on the database. For example, a reviewer can spot a database-breaking change.

Apply the migrations on a Pull request merge

Once the pull request is merged, we want to apply the migration to the production database and delete the development branch.

Create a file ".github/workflows/migration-db-deploy.yml" and add the following code:

name: Deploy database migration
on:
  push:
    branches:
      - 'main'
    paths:
      - 'prisma/migrations/**'

env:
  PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PLANETSCALE_SERVICE_TOKEN_ID }}
  PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PLANETSCALE_SERVICE_TOKEN }}

jobs:
  apply-migration:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Setup pscale
        uses: planetscale/setup-pscale-action@v1

      - name: Get the branch name and the deploy request number
        run: |
          DEPLOYER_REQUEST_LIST=$(pscale deploy-request list ${{ secrets.PLANETSCALE_DATABASE_NAME }} --org ${{ secrets.PLANETSCALE_ORG_NAME }} -f json)
          DEPLOY_REQUEST_NUMBER=$(echo "$DEPLOYER_REQUEST_LIST" | jq -r '.[] | select(.into_branch == "main" and .state == "open") | .number')
          PSCALE_BRANCH_NAME=$(echo "$DEPLOYER_REQUEST_LIST" | jq -r '.[] | select(.into_branch == "main" and .state == "open") | .branch')
          
          if [ -z "$DEPLOY_REQUEST_NUMBER" ]; then
            echo "No open deploy request found in the production branch."
            exit 1
          fi
          
          echo "DEPLOY_REQUEST_NUMBER=$DEPLOY_REQUEST_NUMBER" >> $GITHUB_ENV
          echo "PSCALE_BRANCH_NAME=$PSCALE_BRANCH_NAME" >> $GITHUB_ENV

      - name: Check deployment state
        continue-on-error: false
        run: |
          for i in {1..10}; do
            DEPLOYMENT_STATE=$(pscale deploy-request show ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.DEPLOY_REQUEST_NUMBER }} --org ${{ secrets.PLANETSCALE_ORG_NAME }} -f json | jq -r '.deployment_state')
            echo "Deployment State: $DEPLOYMENT_STATE"
          
            if [ "$DEPLOYMENT_STATE" = "ready" ]; then
              echo "Deployment state is ready. Continuing."
              echo "DEPLOY_REQUEST_OPENED=true" >> $GITHUB_ENV
              break
            fi
          
            echo "Deployment state is not ready. Waiting 2 seconds before checking again."
            sleep 2
          done

      - name: Deploy schema migration in the production branch
        continue-on-error: false
        run: |
          pscale deploy-request deploy ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.DEPLOY_REQUEST_NUMBER }} --org ${{ secrets.PLANETSCALE_ORG_NAME }} --wait
          pscale deploy-request skip-revert ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.DEPLOY_REQUEST_NUMBER }} --org ${{ secrets.PLANETSCALE_ORG_NAME }}

      - name: Delete the database branch
        if: ${{ success() }}
        run: |
          pscale branch delete ${{ secrets.PLANETSCALE_DATABASE_NAME }} ${{ env.PSCALE_BRANCH_NAME }} --org ${{ secrets.PLANETSCALE_ORG_NAME }} --force
          echo "The branch \"${{ env.PSCALE_BRANCH_NAME }}\" has been successfully."

          

This action workflow has eight steps:

1. Check out the GitHub repository.
2. Install the GitHub Action to set up PlanetScale.
3. Get the database branch name and the deploy request number.
4. Check the deployment state to ensure there is no error.
5. Apply the migration on the production branch.
6. Delete the development database branch.

Make a commit, then push the changes on the pull request. Verify that the workflow is not deployed.

Once all checks pass, merge the pull request to the main branch and wait for the pipeline to complete.

You can now check your production database schema to ensure the changes have been applied.

Suggestion for better database migration automation

Now that you have automated the application of database changes in production, you should be more cautious to avoid introducing breaking changes.

Here are some recommendations:

• Do not include application code with database migration files in the same pull request. This will ease the code review since reviewers will only consider the impact of the changes on the database.
• When you make database-breaking changes, such as renaming a column or changing its type, follow the Expand and Contract pattern to safely achieve them.
• PlanetScale can notify you when there is a conflict in the deployment request; remember to check when it happens and solve it.
• Check the recommendation in your PlanetScale web console to improve your database's performance. Now that you have an automated CI/CD pipeline, it is easy to apply these recommendations.

Wrap up

Automating the deployment of database changes through a CI/CD pipeline on GitHub Actions is a great way to have a deterministic process for applying changes to your database.

With features like database branch, deploy request, and CLI provided by PlanetScale, you can include other developers in the code review to prevent breaking changes from being applied to the production database.

You can find the code source on the GitHub repository.

Follow me on Twitter or subscribe to my newsletter to avoid missing the upcoming posts and the tips and tricks I occasionally share.