# pgfence (/docs/guides/integrations/pgfence)

> For the complete Prisma documentation index, see [llms.txt](https://www.prisma.io/docs/llms.txt). A markdown version of any docs page is available by appending `.md` to its URL.

Analyze Prisma Migrate SQL files for dangerous lock patterns, risk levels, and safe rewrite recipes before deploying to production

Location: Guides > Integrations > pgfence

> [!NOTE]
> This guide uses Prisma ORM 7
> 
> The commands and code on this page target Prisma ORM 7, which remains fully supported. To start a new Prisma ORM 8 project, see [Create a new app with PostgreSQL](https://www.prisma.io/docs/prisma-orm/quickstart/postgresql); to add Prisma ORM 8 to an existing app, see [Add Prisma ORM to an existing PostgreSQL database](https://www.prisma.io/docs/prisma-orm/add-to-existing-project/postgresql). A Prisma ORM 8 version is not available yet: pgfence reads `migration.sql` files, and Prisma ORM 8 migrations are TypeScript and JSON files with no SQL output to scan.

## Introduction [#introduction]

[pgfence](https://pgfence.com) 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](https://github.com/launchql/libpg-query-node)) 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 [#prerequisites]

* [Node.js v20+](https://nodejs.org/)
* A Prisma project using PostgreSQL as the database provider
* Existing migrations in `prisma/migrations/`

## 1. Install pgfence [#1-install-pgfence]

Add pgfence as a development dependency in your project:

  

#### bun

```bash
bun add --dev @flvmnt/pgfence
```

#### pnpm

```bash
pnpm add -D @flvmnt/pgfence
```

#### yarn

```bash
yarn add --dev @flvmnt/pgfence
```

#### npm

```bash
npm install -D @flvmnt/pgfence
```

## 2. Analyze your migrations locally [#2-analyze-your-migrations-locally]

Run pgfence against your Prisma migration files:

  

#### bun

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

#### pnpm

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

#### yarn

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

#### npm

```bash
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 [#understanding-the-output]

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

| Risk level   | Meaning                                                                                                           |
| ------------ | ----------------------------------------------------------------------------------------------------------------- |
| **LOW**      | Safe operations with minimal locking (e.g., `ADD COLUMN` with a constant default on PG 11+)                       |
| **MEDIUM**   | Operations that block writes but not reads (e.g., `CREATE INDEX` without `CONCURRENTLY`)                          |
| **HIGH**     | Operations that block writes and competing DDL, but not plain reads (e.g., `ADD FOREIGN KEY` without `NOT VALID`) |
| **CRITICAL** | Operations that take `ACCESS EXCLUSIVE` locks on large tables (e.g., `DROP TABLE`, `TRUNCATE`)                    |

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

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

  

#### bun

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

#### pnpm

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

#### yarn

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

#### npm

```bash
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.

> [!WARNING]
> Prisma Migrate does not generate `CONCURRENTLY` variants automatically. If pgfence flags an index creation, you should manually edit the generated migration SQL file to add `CONCURRENTLY` before applying it. Note that `CREATE INDEX CONCURRENTLY` cannot run inside a transaction, so you will also need to ensure the migration runs outside a transaction block.

## 3. Use JSON output for programmatic checks [#3-use-json-output-for-programmatic-checks]

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

  

#### bun

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

#### pnpm

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

#### yarn

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

#### npm

```bash
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:

  

#### bun

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

#### pnpm

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

#### yarn

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

#### npm

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

## 4. Add pgfence to your CI pipeline [#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:

```yaml title=".github/workflows/migration-safety.yml"
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.

> [!NOTE]
> You can adjust the `--max-risk` threshold to match your team's risk tolerance. Options are `low`, `medium`, `high`, and `critical`.

### Combining pgfence with deploy [#combining-pgfence-with-deploy]

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

```yaml title=".github/workflows/deploy.yml"
- 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) [#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:

  

#### bun

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

#### pnpm

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

#### yarn

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

#### npm

```bash
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](https://github.com/flvmnt/pgfence#db-size-aware-risk-scoring) for details.

## Next steps [#next-steps]

* [pgfence documentation and source code](https://github.com/flvmnt/pgfence)
* [pgfence on npm](https://www.npmjs.com/package/@flvmnt/pgfence)
* [Prisma Migrate overview](https://www.prisma.io/docs/orm/v7/prisma-migrate)
* [Deploying database changes with Prisma Migrate](https://www.prisma.io/docs/orm/v7/prisma-client/deployment/deploy-database-changes-with-prisma-migrate)

## Related pages

- [`AI SDK (with Next.js)`](https://www.prisma.io/docs/guides/integrations/ai-sdk): Build a chat application with AI SDK, Prisma ORM, and Next.js that stores chat sessions and messages in Prisma Postgres.
- [`Datadog`](https://www.prisma.io/docs/guides/integrations/datadog): Learn how to configure Datadog tracing for a Prisma ORM project. Capture spans for every query using the @prisma/instrumentation package, dd-trace, and view them in Datadog
- [`Embedded Prisma Studio (with Next.js)`](https://www.prisma.io/docs/guides/integrations/embed-studio): Learn how to embed Prisma Studio directly in your Next.js application for database management
- [`GitHub Actions`](https://www.prisma.io/docs/guides/integrations/github-actions): Provision a Prisma Postgres database for every pull request with GitHub Actions and the Prisma CLI, apply your migrations and seed data to it, and delete it when the pull request closes.
- [`Permit.io`](https://www.prisma.io/docs/guides/integrations/permit-io): Learn how to implement access control with Prisma ORM with Permit.io