DEV Community

Subodh Singh
Subodh Singh

Posted on

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);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Run it against your migrations:

npx pg-safe-migrate
Enter fullscreen mode Exit fullscreen mode

Or check changed SQL migrations:

npx pg-safe-migrate --changed origin/main
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)