Automating Neon schema deployments with Prisma and GitLab

Neon’s Branching feature enables developers to create isolated branches that contain production data so new additions to their applications can be tested safely.

However once testing is complete, it is still up to the developer to safely merge schema changes into the main branch for the production version of the application.

In this article, you’ll learn how to automate this process using Prisma and GitLab.

What are database migrations?

A database migration is a SQL script that executes to make some change to a database in a controlled manner. Each required database change will have a script associated with that change, resulting in a series of timestamped SQL scripts that show the changes to the database over time.

These migrations are often stored in version control and can be executed in order to bring a fresh database to match the current state of another. As the scripts are executed, the program that manages migrations often tracks which scripts are applied in a dedicated table so that the program knows which scripts have been executed and which havent.

Creating and applying migrations with Prisma

In this example I’m using Prisma ORM which supports creating and applying database migrations with it’s native tooling. With Prisma, you can make changes to the type definitions in the repository and use those changes to create migration:

pnpm prisma migrate dev --name add_category_fields

Which results in the following script being created:

-- AlterTable
ALTER TABLE "Show" 
ADD COLUMN "primaryCategory" TEXT,
ADD COLUMN "primarySubcategory" TEXT,
ADD COLUMN "secondaryCategory" TEXT,
ADD COLUMN "secondarySubcategory" TEXT,
DROP COLUMN "category";

The following command will deploy any outstanding scripts in the migrations directory to the database:

pnpm prisma migrate deploy

This results in the above changes being applied to the database, as well as an entry being added to the _prisma_migrations table in the database:

Automatically applying migrations with GitLab CI

GitLab CI is a feature of GitLab that lets you define workflows that trigger based on events within your GitLab project. This can be automating changes to infrastructure, running and reporting on tests, sending notifications, and executing various scripts, etc.

Workflows are defined in the .gitlab-ci file at the root of the project, where GitLab will automatically execute all of the defined steps based on the rules defined within.

The following workflow file will automatically apply the database changes using the Prisma tooling when changes are detected in the prisma folder at the root of the project:

image: node:20-alpine

variables:
  PNPM_VERSION: 9

# Run only on commits to `main` or manually
workflow:
  rules:
    - if: $CI_COMMIT_BRANCH == "main"
    - if: $CI_PIPELINE_SOURCE == "web"

stages:
  - build
  - deploy

cache:
  key:
    files:
      - pnpm-lock.yaml
  paths:
    - .pnpm-store
    - node_modules/.cache

# Set up the runner with the correct packages
.install-deps:
  before_script:
    - apk add --no-cache git
    - corepack enable
    - corepack prepare pnpm@$PNPM_VERSION --activate
    - pnpm config set store-dir .pnpm-store
    - pnpm install --frozen-lockfile

# Build the application
build:
  extends: .install-deps
  stage: build
  script:
    - pnpm prisma generate
    - pnpm tsc
  artifacts:
    paths:
      - dist
  rules:
    - if: $CI_COMMIT_BRANCH == "main"
    - if: $CI_PIPELINE_SOURCE == "web"
      when: manual

# Use Prisma to apply migrations
deploy-db:
  extends: .install-deps
  stage: deploy
  script:
    - pnpm prisma generate
    - pnpm prisma migrate deploy
  rules:
          # Only run this step if there are commits applied to `main` and
          # there are changes in the `prisma` folder
    - if: $CI_COMMIT_BRANCH == "main" && $CI_PIPELINE_SOURCE == "push"
      changes:
        - prisma/**/*
    - if: $CI_PIPELINE_SOURCE == "web"
      when: manual
  environment:
    name: production
  variables:
    DATABASE_URL: $DATABASE_URL

Setting pipeline secrets

You’ll notice in the last line of the workflow I have $DATABASE_URL defined. This is the connection string to my main Neon branch that is stored as a variable in GitLab. GitLab allows you to define variables within a project that can be used with pipelines by navigating to the project settings then CI/CD > Variables.