pgfence

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.

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

Add pgfence as a development dependency in your project:

bun add --dev @flvmnt/pgfence

pnpm add -D @flvmnt/pgfence

yarn add --dev @flvmnt/pgfence

npm install -D @flvmnt/pgfence

Run pgfence against your Prisma migration files:

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

pnpm dlx @flvmnt/pgfence analyze prisma/migrations/**/migration.sql

yarn dlx @flvmnt/pgfence analyze prisma/migrations/**/migration.sql

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.

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");

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

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

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

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.

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

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

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

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

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:

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

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

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

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

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.

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 }}

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:

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

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

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

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.

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