safe-migrate

safe-migrate is a powerful PostgreSQL migration linter designed to ensure safe database migrations by simulating potential lock scenarios before they disrupt production environments. This tool utilizes a bi-directional state machine simulation by combining static typed Abstract Syntax Tree (AST) analysis with live database statistics, effectively preventing blocking locks that can lead to application downtime.

Understanding the Problem

Database migrations can seem simple, such as the command ALTER TABLE users ADD COLUMN status TEXT, which may work smoothly on smaller datasets. However, on larger tables with millions of rows, this command can acquire an ACCESS EXCLUSIVE lock, causing significant application outages. Unlike standard linters that only analyze SQL syntax, safe-migrate evaluates both the SQL commands and the size of the affected tables, providing a more comprehensive safety net for migrations.

Key Features in Version 0.3.2

• Typed AST Parsing: The new version utilizes a robust parsing method via squawk_syntax, moving away from prone-to-error string matching techniques.
• Accurate Transaction Simulation: It correctly simulates transactions, allowing rollback operations to restore previous database states and manage locks effectively.
• Thorough Dependency Evaluation: The tool evaluates dependencies for cascading drops to prevent unintended loss of views, constraints, and indexes.
• Live Database Stat Tracking: The new sync command offers integration with PostgreSQL catalogs to gather accurate statistics without requiring application credentials. It checks for:
  • Estimated row counts
  • Page estimates for TOAST threshold crossings
  • Staleness detection of tables
  • Column width determinations for effective compression decisions

Quick Start Instructions

1. Sync Database Statistics: Begin by syncing the database statistics to create a JSON file containing critical database metadata that is safe for source control.

   export DATABASE_URL="postgres://user:password@localhost:5432/mydb"
   

safe-migrate sync

2. **Lint Your Migration Code:** Use safe-migrate to analyze migration scripts for potential issues before deploying them. The linting process evaluates the SQL migrations and provides warnings or errors based on lock impacts.
```bash
safe-migrate lint --file migration.sql

This tool will classify issues into tiers indicating their severity.

The Trust Model

The confidence of the analysis provided by safe-migrate is categorized into levels based on the complexity of the SQL being evaluated. When it encounters opaque statements or dynamic SQL, it issues warnings about the reliability of the identified risks.

CI/CD Integration

For effortless integration into CI/CD pipelines like GitHub Actions or GitLab CI, safe-migrate can be configured to automatically lint migrations upon pull requests, ensuring any potential migration issues are detected before reaching production.

Why This Matters

Understanding the hidden consequences of database migration commands is crucial to maintaining application uptime. Safe-migrate offers developers and database administrators the insights required to execute migrations confidently, avoiding costly disruptions in production environments.