DEV Community

I Built a PostgreSQL Migration Linter to Catch Risky Schema Changes Before Deployment

PostgreSQL migrations can look harmless during code review but still cause problems when deployed to a busy production database. For example:

CREATE INDEX idx_users_email ON users (email);

This statement creates an index, but it can block writes while the index is being built. A safer alternative for many production workloads is:

CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

However, developers must also ensure that concurrent index creation runs outside a transaction block.

Introducing pg-safe-migrate

I built an open-source Node.js CLI called pg-safe-migrate to identify common PostgreSQL migration risks before deployment. It uses PostgreSQL AST parsing through libpg-query, rather than relying solely on regular expressions.

What it detects

  • PG001: CREATE INDEX without CONCURRENTLY
  • PG002: DROP COLUMN
  • PG003: ALTER COLUMN TYPE
  • PG004: ADD COLUMN NOT NULL without DEFAULT
  • PG005: ADD FOREIGN KEY without NOT VALID
  • PG006: RENAME

The CLI explains each finding and suggests a safer migration approach.

Try it yourself

Install the package:

npm install -D @subodh001/pg-safe-migrate

Run it against your migrations:

npx pg-safe-migrate

Or check changed SQL migrations:

npx pg-safe-migrate --changed origin/main

The tool supports Prisma, Drizzle, and raw SQL migration directories.

Why I built it

I wanted a simple tool developers could run locally or in CI to catch potentially dangerous SQL changes before merging a pull request. The goal is not to replace database migration testing. It's to make common risks easier to notice during development.

Current limitations

This is a static migration risk linter. It doesn't inspect live database state or guarantee zero downtime. The first public release includes six rules, Git-diff support, and GitHub Actions integration.

Open source

GitHub: https://github.com/subodhsingh001/pg-safe-migrate

npm: https://www.npmjs.com/package/@subodh001/pg-safe-migrate

I'd appreciate feedback from PostgreSQL developers, particularly examples of false positives, missing safety rules, or ORM-specific migration problems.

Read on DEV Community ↗ ← Back to News

Comments

No comments yet. Start the discussion.