pgfence

Introduction

pgfence is a PostgreSQL migration safety CLI that analyzes SQL migration files and reports lock modes, risk levels, and safe rewrite recipes. It uses PostgreSQL's actual parser (libpg-query) to understand exactly what each DDL statement does, what locks it acquires, and what it blocks.

Prisma Migrate generates plain SQL files at prisma/migrations/*/migration.sql. pgfence can analyze those files directly, catching dangerous patterns before they reach production.

Common issues pgfence detects include:

• CREATE INDEX without CONCURRENTLY (blocks writes)
• ALTER COLUMN TYPE (full table rewrite with ACCESS EXCLUSIVE lock)
• ADD COLUMN ... NOT NULL without a safe default (blocks reads and writes)
• Missing lock_timeout settings (risk of lock queue death spirals)

For each dangerous pattern, pgfence provides the exact safe alternative -- the expand/contract sequence you should use instead.

Prerequisites

• Node.js v20+
• A Prisma project using PostgreSQL as the database provider
• Existing migrations in prisma/migrations/

1. Install pgfence

Add pgfence as a development dependency in your project:

npm install -D @flvmnt/pgfence

2. Analyze your migrations locally

Run pgfence against your Prisma migration files:

npx @flvmnt/pgfence analyze prisma/migrations/**/migration.sql

pgfence parses every SQL statement and reports the lock mode, risk level, and any safe rewrites available.

Understanding the output

pgfence assigns a risk level to each statement based on the PostgreSQL lock it acquires:

Here is an example of pgfence analyzing a migration that adds an index without CONCURRENTLY:

-- prisma/migrations/20240115_add_index/migration.sql
CREATE INDEX "User_email_idx" ON "User"("email");

npx @flvmnt/pgfence analyze prisma/migrations/20240115_add_index/migration.sql

pgfence will flag this as a MEDIUM risk because CREATE INDEX takes a SHARE lock, which blocks all writes to the table for the duration of the index build. It will suggest using CREATE INDEX CONCURRENTLY instead.

3. Use JSON output for programmatic checks

pgfence supports JSON output, which is useful for integrating with other tools or scripts:

npx @flvmnt/pgfence analyze --output json prisma/migrations/**/migration.sql

You can also set a maximum risk threshold for CI pipelines. The command exits with code 1 if any statement exceeds the threshold:

npx @flvmnt/pgfence analyze --ci --max-risk medium prisma/migrations/**/migration.sql

4. Add pgfence to your CI pipeline

Add pgfence as a safety check that runs before prisma migrate deploy in your CI/CD pipeline. This catches dangerous migration patterns before they reach your production database.

Here is a GitHub Actions workflow that runs pgfence on every pull request that includes migration changes:

name: Migration safety check

on:
  pull_request:
    paths:
      - prisma/migrations/**

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

      - name: Setup Node.js
        uses: actions/setup-node@v4
        with:
          node-version: "20"

      - name: Install dependencies
        run: npm ci

      - name: Run pgfence analysis
        run: |
          shopt -s globstar
          npx @flvmnt/pgfence analyze --ci --max-risk medium prisma/migrations/**/migration.sql

This workflow only triggers when migration files change. If pgfence detects any statement with risk higher than MEDIUM, the check fails and blocks the pull request from merging.

Combining pgfence with deploy

If you have an existing deployment workflow, add pgfence as a step before prisma migrate deploy:

- name: Run pgfence migration safety check
  run: |
    shopt -s globstar
    npx @flvmnt/pgfence analyze --ci --max-risk medium prisma/migrations/**/migration.sql

- name: Apply pending migrations
  run: npx prisma migrate deploy
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}

5. Size-aware risk scoring (optional)

pgfence can adjust risk levels based on actual table sizes. A CREATE INDEX on a 100-row table is very different from the same operation on a 10-million-row table.

To use size-aware scoring without giving pgfence direct database access, export a stats snapshot from your database and pass it to pgfence:

npx @flvmnt/pgfence analyze --stats-file pgfence-stats.json prisma/migrations/**/migration.sql

The stats file contains row counts and table sizes from pg_stat_user_tables. Run npx @flvmnt/pgfence extract-stats --db-url <connection-string> to generate this file, or see the pgfence README for details.

Next steps

• pgfence documentation and source code
• pgfence on npm
• Prisma Migrate overview
• Deploying database changes with Prisma Migrate