Validate database schema changes in your CI pipeline before deploying to production

Nicole White — Tue, 03 Oct 2023 16:00:00 +0000

Introduction

At Autoblocks, we use Neon as the serverless Postgres provider for our transactional data. Because we are constantly iterating on our product based on user feedback, we make frequent, non-negligible changes to our database schema to best serve evolving business needs. Some of these migrations are not only schema modifications (adding a new table, column, etc.) but also data modifications, where we write scripts to e.g. backfill columns, change the schema of a JSON column, make destructive changes without downtime, etc.

We always test these kinds of modifications locally, but we appreciate the extra peace of mind we get from doing a dry run on our production database. Before Neon, building this using just RDS (the relational database service from AWS) would have required a substantial engineering investment. With Neon branching, though, it’s shockingly easy to spin up and tear down copies of your production database in a Continuous Integration (CI) environment.

At Autoblocks, we use GitHub Actions, and we’ve open-sourced the actions we wrote to achieve this:

Explore the repository

Creating Branches

While the actions support creating a Neon branch for every commit, we’ve opted only to create a Neon branch when there are migrations to run. In our case, this is when our Prisma migrations folder has been modified. Our migrations are located in packages/db/prisma/migrations and the package.json file in the packages/db subfolder in our monorepo contains scripts for running migrations:

"scripts": {
  "migrate:dev": "prisma migrate dev",
  "migrate:prod": "prisma migrate deploy"
}

GitHub Actions does support path filtering, but only at the workflow level. We use dorny/paths-filter since it allows us to add job-level path filters. Below is an abbreviated example of our main CI workflow:

name: CI

on:
  push:
    branches-ignore:
      - main

jobs:
  changes:
    runs-on: ubuntu-latest
    outputs:
      migrations: ${{ steps.changes.outputs.migrations }}
    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - uses: dorny/paths-filter@v2
        id: changes
        with:
          filters: |
            migrations:
              - 'packages/db/prisma/migrations/**'

  validate-migrations:
    needs: changes
    if: needs.changes.outputs.migrations == 'true'
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repository
        uses: actions/checkout@v3

      # Setup node, install dependencies, etc.

      - name: Create Neon branch
        uses: autoblocksai/neon-actions/create-branch@v1
        id: neon
        with:
          api-key: ${{ secrets.NEON_API_KEY }}
          project-id: ${{ vars.NEON_PROJECT_ID }}

      - name: Run DB migrations
        run: npm run migrate:prod
        working-directory: packages/db
        env:
          DATABASE_URL: postgres://${{ secrets.PG_USERNAME }}:${{ secrets.PG_PASSWORD }}@${{ steps.neon.outputs.direct-host }}:5432/neondb

Now, when I push a commit to a GitHub branch that modifies the packages/db/prisma/migrations folder, we will:

Create a Neon branch from the latest state of our production database,
Run our database migrations against that branch, and
Add a pass / fail commit status to the pull request.

The Neon branch name will be the GitHub branch name plus the first seven characters of the commit SHA: {branchName}-{commitSha}

For example, I created a GitHub branch called add-new-table, modified the migrations folder, then pushed three commits:

On each run of the CI workflow, a new Neon branch was created, and then our migrations were run against that branch. Below is an example of the console output from the autoblocksai/neon-actions/create-branch action:

-> Run autoblocksai/neon-actions/create-branch@v1
Creating new branch with name 'add-new-table-d105eb3'
Created new branch:
{
  "branch": {
    "id": "br-empty-voice-99968957",
    "project_id": "early-silence-295820",
    "parent_id": "br-smelly-top-392940",
    "name": "add-new-table-d105eb3",
    "current_state": "init"
  },
  "endpoints": [
    {
      "id": "ep-bold-glade-26143287",
      "host": "ep-bold-glade-26143287.us-east-1.aws.neon.tech",
      "current_state": "init"
    }
  ]
}
Notice: Your Neon branch is at <https://console.neon.tech/app/projects/early-silence-295820/branches/br-empty-voice-99968957>
Wating for branch br-empty-voice-99968957 to be ready
Retrying in 0.25 seconds (5 retries left)
Branch br-empty-voice-99968957 is ready!
Wating for endpoint ep-bold-glade-26143287 to be ready
Endpoint ep-bold-glade-26143287 is ready!
Direct host: ep-bold-glade-26143287.us-east-1.aws.neon.tech
Pooled host: ep-bold-glade-26143287-pooler.us-east-1.aws.neon.tech

You’ll notice that we have two hosts to choose from, either the direct host or the pooled host. These are available as outputs from the action as direct-host and pooled-host, respectively. We’re using the direct connection for migrations since Prisma requires it, but you’ll likely want to use the pooled connection for any other type of connection, like from a web application or smoke test suite.

Deleting Branches

When a GitHub branch is deleted, either directly or from merging a pull request, we want to clean up all Neon branches created from that branch. To do so, we just run the autoblocksai/neon-actions/delete-branches action on the delete event:

name: Delete Neon Branches

on: delete

jobs:
  delete-neon-branches:
    if: github.event.ref_type == 'branch'
    runs-on: ubuntu-latest
    steps:
      - name: Delete Neon branches
        uses: autoblocksai/neon-actions/delete-branches@v1
        with:
          api-key: ${{ secrets.NEON_API_KEY }}
          project-id: ${{ vars.NEON_PROJECT_ID }}

This will delete all of the Neon branches whose names start with the name of the GitHub branch that was just deleted:

-> Run autoblocksai/neon-actions/delete-branches@v1
Found 3 branches:
  add-new-table-d105eb3 (br-empty-voice-99968957)
  add-new-table-9c62e5b (br-autumn-silence-26803864)
  add-new-table-a58c909 (br-jolly-rice-40072094)
Deleting branches with prefix 'add-new-table-':
  deleting add-new-table-d105eb3 (br-empty-voice-99968957)
  deleting add-new-table-9c62e5b (br-autumn-silence-26803864)
  deleting add-new-table-a58c909 (br-jolly-rice-40072094)

More Examples

While we only use Neon branches to validate our migrations, you can also use them in other contexts, such as smoke tests or Vercel preview environments. In these cases, you’d likely want to drop the path filtering and then use the pooled host in subsequent steps:

name: CI

on: push

jobs:
  ci:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Setup node
        uses: actions/setup-node@v3

      - name: Install dependencies
        run: npm ci

      - name: Create neon branch
        id: neon
        uses: autoblocksai/neon-actions/create-branch@v1
        with:
          api-key: ${{ secrets.NEON_API_KEY }}
          project-id: ${{ vars.NEON_PROJECT_ID }}

      - name: Run prisma migrations
        run: npx prisma migrate deploy
        env:
          DATABASE_URL: postgres://${{ secrets.PG_USERNAME }}:${{ secrets.PG_PASSWORD }}@${{ steps.neon.outputs.direct-host }}:5432/neondb

      - name: Run smoke tests
        run: npm run test:smoke
        env:
          DATABASE_URL: postgres://${{ secrets.PG_USERNAME }}:${{ secrets.PG_PASSWORD }}@${{ steps.neon.outputs.pooled-host }}:5432/neondb

See the docs for more details

Forem: Nicole White

Validate database schema changes in your CI pipeline before deploying to production

Introduction

Creating Branches

Deleting Branches

More Examples