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…