safe-migrate is a powerful Command-Line Interface (CLI) tool designed to safeguard PostgreSQL database migrations, effectively preventing production outages due to problematic locking issues. By leveraging static SQL analysis and real-time database statistics, it ensures that database alterations are handled safely.

Key Features

• Comprehensive Analysis: Unlike standard SQL linters that solely focus on the SQL syntax, safe-migrate assesses both the SQL commands and the size of the affected tables. For example, adding a column to a 500-row table is safe, but the same operation on a 50 million-row table could trigger an ACCESS EXCLUSIVE lock, jeopardizing application availability.

How It Works

1. Synchronization: The tool connects to the database to gather approximate row counts from pg_class and writes this data to a .safe-migrate-stats.json cache file without storing any credentials. This step prepares the necessary information for the subsequent analysis.

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

safe-migrate sync

2. **Linting:** Using the cached data, `safe-migrate` analyzes migration files for potential locking problems. Each SQL statement is parsed into a typed Abstract Syntax Tree (AST), and the tool checks for dangerous locks against cached row counts.
```bash
safe-migrate lint --file migration.sql

Output and Risk Assessment

The tool provides detailed output regarding migration risks, classified into tiers based on the types of locks they generate:

For instance, it might output a warning if adding a column to a table is risky, along with suggestions for safer procedures.

Configuration Options

Customize the behavior of safe-migrate by creating a safe-migrate.toml file in your repository's root directory, where thresholds can be adjusted per migration rule.

# Global row count threshold (default: 100,000)
default_threshold = 200000

[rules.adding-field-with-default]
tier = "Tier2"
threshold = 50000

CI/CD Integration

Integrate safe-migrate into Continuous Integration/Continuous Deployment (CI/CD) pipelines to continually safeguard migrations. An example YAML configuration is provided to show how to use it in a GitHub Actions workflow:

name: Safe Migrate

on:
  pull_request:
    branches: [ "main" ]

jobs:
  lint-migrations:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repository
        uses: actions/checkout@v4
      - name: Install safe-migrate
        run: curl -fsSL https://raw.githubusercontent.com/dsecurity49/safe-migrate/main/install.sh | bash
      - name: Sync database stats
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: safe-migrate sync --out prod-cache.json
      - name: Lint new migrations
        run: |
          FILES=$(git diff --name-only origin/${{ github.base_ref }}...HEAD -- '*.sql')
          if [ -z "$FILES" ]; then
            echo "No SQL migrations changed. Skipping."
            exit 0
          fi
          for f in $FILES; do
            echo "Linting $f..."
            safe-migrate lint --file "$f" --cache prod-cache.json
          done

With safe-migrate, database migrations become more manageable and less prone to producing downtime, ensuring the stability of production environments.