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.
Comments
No comments yet. Start the discussion.