New pgfence 0.6.1: Trust Contract fixes for ORM extraction, domain checks, stats precedence, and public boundaries →

Free and open source

Catch dangerous Postgres migrations
before they merge.

pgfence is an open-source Postgres migration safety CLI that maps every DDL statement to its lock mode, blocked operations, and a safe rewrite recipe.

Every DDL statement in Postgres takes a lock. Some block all reads and writes for minutes. pgfence uses PostgreSQL's actual parser to map each statement to its exact lock mode, with safe rewrite recipes you can paste into the next migration. CLI, LSP, GitHub Action, and ORM extractors all open source.

View docs GitHub

Migration formats

Works with TypeORM, Prisma, Knex, Sequelize, Drizzle, and raw SQL

pgfence pulls the SQL out of your migration files no matter how they are written. TypeORM migrations are scanned for queryRunner.query() calls. Prisma's migration.sql files are read directly. Knex migrations are walked for knex.raw() and schema-builder operations. Sequelize migrations are parsed for queryInterface calls. Drizzle's generated SQL snapshots are handled the same way. Plain .sql files need no extractor. Five ORMs plus raw SQL, six input formats, one rule catalog.

TypeORM → Prisma → Knex → Sequelize → Drizzle → Raw SQL →
Free OSS, exploratory governance

The analyzer stays free. Governance is shaped with design partners.

The CLI, LSP, docs, GitHub Action, and reporting outputs are open source and free forever. Organizations that want approvals and policy enforcement can join the design partner program while the governance layer is shaped with real production migrations.

Pricing

A free OSS analyzer today, plus a private design partner program for teams that own production migration risk. No fixed governance pricing yet.

How access works

Cloud

The governance layer is exploratory and only available through the design partner program. We are shaping approvals, shared policies, and audit history with teams that already have real migration review pain.

About the program

Security

No production database credentials, explicit trust boundaries, and a clear disclosure path for security and privacy review.

Read trust docs

Contact

Questions about the design partner program, governance direction, or a security questionnaire? Reach us directly.

Contact us
This migration takes an ACCESS EXCLUSIVE lock on your users table
migrations/20260225_add_verified.sql 
ALTER TABLE users
  ADD COLUMN email_verified boolean NOT NULL;

CREATE INDEX idx_users_email
  ON users(email);
↓ Blocks every read and write until the rewrite finishes.
On a 10M-row table, that's a p0 incident, minutes of downtime, pages firing, users unable to log in.

pgfence catches it before your users do

pgfence analyze
$ pgfence analyze migrations/20260225_add_verified.sql
migrations/20260225_add_verified.sql  [HIGH]
Lock: ACCESS EXCLUSIVE | Blocks: reads+writes+DDL | Risk: HIGH | Rule: add-column-not-null-no-default
# Statement Lock Mode Blocks Risk Message
1 ALTER TABLE users ADD COLUMN email_verified BOOLEAN NOT NULL ACCESS EXCLUSIVE reads, writes, DDL HIGH ADD COLUMN "email_verified" with NOT NULL but no DEFAULT, fails on non-empty tables
2 CREATE INDEX idx_users_email ON users(email) SHARE writes, DDL MEDIUM CREATE INDEX without CONCURRENTLY, blocks writes for duration
Safe Rewrite Recipes:
add-column-not-null-no-default: Add nullable column, backfill, then add NOT NULL constraint
    ALTER TABLE users ADD COLUMN IF NOT EXISTS email_verified boolean;
    -- Backfill out-of-band in batches
    ALTER TABLE users ADD CONSTRAINT chk_nn CHECK (email_verified IS NOT NULL) NOT VALID;
    ALTER TABLE users VALIDATE CONSTRAINT chk_nn;
    ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;
    ALTER TABLE users DROP CONSTRAINT chk_nn;
Policy Violations:
ERROR Missing SET lock_timeout, lock queue death spiral risk
→ Add SET lock_timeout = '2s'; at the start of the migration
Analyzed: 2 statements  | Unanalyzable: 0  | Coverage: 100%

Captured workflow

See the shipped surfaces, not just the pitch.

These images are captured from local demo artifacts built around the current CLI, editor, and PR review flows, so the homepage shows what the workflow looks like in practice.

pgfence CLI review artifact showing lock risk, blocked operations, and safe rewrite guidance.
CLI review Quick start

A risky migration becomes a reviewable artifact in one command.

Lock mode, blocked operations, rewrite guidance, and policy issues stay in one place, which makes the migration conversation concrete before merge.

Run the CLI →
pgfence editor artifact with inline diagnostics and a supported quick-fix hint.
Editor diagnostics VS Code

Catch the lock story while the SQL is still open in your editor.

Inline diagnostics and hover details help reviewers understand what a statement blocks before the pull request stage.

Open editor docs →
pgfence PR review artifact showing markdown summary, findings, and coverage line.
PR summary CI workflow

Bring the same migration summary directly into review.

Teams can keep the markdown output in CI so the reviewer sees the same findings, coverage line, and safer path without context switching.

See output formats →

How it works

1

Parse

Uses libpg_query, PostgreSQL's own parser compiled to a C library. Full AST, not regex pattern matching.

2

Analyze

Maps each statement to its exact lock mode, blocked operations, and risk level. Optionally adjusts by table size.

3

Rewrite

Outputs the safe expand/contract sequence for every dangerous pattern. Copy it straight into your migration.

Real PostgreSQL parser

Built on libpg_query, the same C library Postgres uses internally. Full AST for every statement, not regex guessing.

No database credentials

Analyzes migration files statically. Optional table-size scoring uses a JSON stats snapshot, never needs direct DB access.

Safe rewrite recipes

Common dangerous patterns include safe rewrite guidance. Use it as a practical starting point for the expand/contract sequence you ship.

Multi-ORM support

Extracts SQL from TypeORM, Prisma, Knex, Sequelize, and Drizzle migrations. Plain .sql files work out of the box. Five ORMs plus raw SQL, six input formats in total.

4 risk levels
8 lock modes
PG 14+ default assumption
6 input formats

Checks across 4 risk levels

Every check maps to a specific PostgreSQL lock mode.

CRITICAL ACCESS EXCLUSIVE, blocks everything
  • DROP TABLE
  • TRUNCATE
  • TRUNCATE CASCADE
  • REINDEX SCHEMA/DATABASE
  • DROP SCHEMA
  • DROP SCHEMA CASCADE
  • DROP DATABASE
HIGH Long-held exclusive locks
  • ADD COLUMN NOT NULL without safe pattern
  • ADD COLUMN DEFAULT <volatile>
  • ADD COLUMN GENERATED STORED
  • ALTER COLUMN TYPE cross-family rewrite
  • ADD FOREIGN KEY without NOT VALID
  • ADD UNIQUE without concurrent index
  • ADD EXCLUDE constraint
  • DROP COLUMN
  • RENAME TABLE
  • VACUUM FULL
  • REINDEX TABLE / INDEX
  • ATTACH PARTITION
  • DETACH PARTITION
  • REFRESH MATERIALIZED VIEW
  • ADD PRIMARY KEY without concurrent index
  • ALTER DOMAIN ADD CONSTRAINT
  • DELETE without WHERE
  • CLUSTER new in 0.6
  • REPLICA IDENTITY FULL new in 0.6
  • ENABLE ROW LEVEL SECURITY new in 0.6
  • DISABLE ROW LEVEL SECURITY new in 0.6
  • INHERIT / NO INHERIT new in 0.6
MEDIUM Blocks writes or requires attention
  • CREATE INDEX without CONCURRENTLY
  • DROP INDEX without CONCURRENTLY
  • SET NOT NULL without constraint
  • ADD CHECK without NOT VALID
  • ALTER TYPE ADD VALUE (PG < 12)
  • CREATE TRIGGER
  • DROP TRIGGER
  • REFRESH ... CONCURRENTLY
  • CONCURRENTLY inside transaction
  • Missing lock_timeout
  • Missing statement_timeout
  • Timeout value too permissive
  • REFRESH ... WITH NO DATA
  • CREATE DOMAIN with constraints
LOW Safe patterns, metadata-only, or best practices
  • ADD COLUMN DEFAULT <constant> (PG11+)
  • ALTER TYPE ADD VALUE (PG12+)
  • ADD UNIQUE USING INDEX
  • RENAME COLUMN (PG14+)
  • DETACH PARTITION CONCURRENTLY
  • ENABLE TRIGGER
  • DISABLE TRIGGER
  • ALTER COLUMN TYPE text/varchar widening
  • ADD PRIMARY KEY USING INDEX
  • VALIDATE CONSTRAINT (standalone)
  • ADD COLUMN ... json (prefer jsonb)
  • integer/int type (prefer bigint)
  • varchar(N) type (prefer text)
  • timestamp without TZ (prefer timestamptz)
  • char(N) type (prefer text)
  • serial type (prefer IDENTITY)
  • CREATE TYPE ... AS ENUM new in 0.6
  • CREATE POLICY new in 0.6

Works with your stack

PostgreSQL TypeORM Prisma Knex Sequelize Drizzle PostgreSQL TypeORM Prisma Knex Sequelize Drizzle

Real-time analysis in your editor

See lock modes, risk levels, and safe rewrites as you type. One-click quick fixes appear for supported executable rewrites.

Inline diagnostics

Warnings and errors appear directly in your SQL files with lock mode and risk level details.

Quick fixes

Click the lightbulb when pgfence has a supported executable rewrite for the flagged statement.

Hover info

Hover over any statement to see its lock mode, what it blocks, and the safe alternative.

Install from Marketplace Extension docs →

One line in CI

Fail the build when a migration exceeds your risk threshold.

# GitHub Actions, GitLab CI, any runner
pgfence analyze --ci --max-risk medium --format typeorm src/migrations/*.ts

# Exit code 1 on HIGH or CRITICAL findings
# Exit code 0 otherwise
exit 0

Free, open source, and ready for your next migration.

Add pgfence to your pipeline in under a minute. Try it on one migration and open an issue with what it missed.

Get started GitHub → Contact →