Validate database schema changes in your CI pipeline before deploying to production
Tags: cicd, ci, neon, githubactions
Published: 2023-10-03 16:00:00

## Introduction

At [Autoblocks](https://www.autoblocks.ai/), we use [Neon](https://neon.tech/) 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](https://neon.tech/docs/introduction/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**](https://github.com/autoblocksai/neon-actions)


## 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](https://www.prisma.io/) 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](https://docs.github.com/en/actions/using-workflows/workflow-syntax-for-github-actions#onpushpull_requestpull_request_targetpathspaths-ignore), but only at the workflow level. We use [dorny/paths-filter](https://github.com/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:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0y7nog9x630ocrk74gyr.png)

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](https://neon.tech/docs/connect/connection-pooling) 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
](https://github.com/autoblocksai/neon-actions)**