Menu

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

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

DEV Community: postgres·Subodh Singh·about 9 hours ago
#DGJAhLUe
#devtopostgresql#devto#dev#safe#migrate
Reading 0:00
15s threshold

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…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.