How to Build a Local Database Migration Guard and Schema Checker with Claude 3.5 Sonnet and Python

We have all been there. It is late on a Thursday, you push a seemingly innocent migration to add a column, and suddenly your production PostgreSQL instance is locked up, queries are stacking up like a bank holiday traffic jam on the M25, and your Slack is screaming.

Even with great testing suites, identifying schema migrations that cause exclusive table locks, long-running validation checks, or catastrophic data loss is surprisingly difficult. Databases are fussy creatures.

Instead of praying to the DBA gods every time you run a database migration, you can build a local, automated Migration Guard using Python and Claude 3.5 Sonnet. This tool scans your SQL migration files, analyses them for dangerous schema patterns, and flags structural hazards before they can touch your staging or production environments.

Here is how to build it from scratch.

Why Your CI Pipeline Needs a Migration Guard

Traditional linters look for syntax errors, but they do not understand database behaviour under load. They do not care if you are adding a NOT NULL constraint to a table with ten million rows, or if you are running an ALTER TABLE that triggers a full table rewrite.

Claude 3.5 Sonnet is uniquely suited for this because it understands both code context and operational database mechanics. By feeding it your schema diffs, it can simulate database engine behaviour and raise warnings with the dry sarcasm of a senior systems engineer who has seen it all.

Step 1: Set Up the Environment

First, let's set up our project directory and install the necessary Python packages. We will need the Anthropic SDK to interface with Claude, and rich to print lovely, readable terminal outputs.

`bash mkdir db-migration-guard cd db-migration-guard python3 -m venv venv source venv/bin/activate pip install anthropic rich `

Make sure you have your API key set up in your environment:

`bash export ANTHROPIC_API_KEY="your-api-key-here" `

Step 2: Write the Migration Guard Script

Create a file named migration_guard.py. This script will scan a designated directory for SQL files, inspect their contents, and pass them to Claude alongside a highly specific system prompt designed to catch PostgreSQL/MySQL hazards.

`python import os import sys from anthropic import Anthropic from rich.console import Console from rich.panel import Panel from rich.markdown import Markdown

Initialize Claude client client = Anthropic() console = Console()

Configure the system instructions for database safety SYSTEM_PROMPT = """ You are an expert, cynical Database Administrator. Your job is to audit raw SQL migration files for dangerous operations that could cause downtime, lock tables, destroy data, or degrade performance on high-traffic PostgreSQL or MySQL databases.

Look for the following hazards: 1. Adding columns with default values (which can rewrite the table depending on DB version). 2. Adding NOT NULL columns to existing tables without a safe default or migration plan. 3. Creating indexes without CONCURRENTLY (in PostgreSQL). 4. Dropping columns or tables without prior deprecation steps. 5. Table locks caused by renaming tables or columns under heavy write loads. 6. Heavy data-migration queries running in a single transaction without batching.

Your output must use strict Markdown formatting. For each migration file: - Provide a risk level: [SAFE], [WARNING], or [DANGER]. - List the specific queries that triggered the warning. - Explain why it is dangerous and provide the safer alternative SQL query. Keep your tone objective, professional, and direct. No fluff. """

def read_migration_files(directory_path): migrations = {} if not os.path.exists(directory_path): console.print(f"[bold red]Error:[/] Directory '{directory_path}' not found.") sys.exit(1) for root, _, files in os.walk(directory_path): for file in sorted(files): if file.endswith('.sql'): filepath = os.path.join(root, file) with open(filepath, 'r', encoding='utf-8') as f: migrations[file] = f.read() return migrations

def analyze_migrations(migrations): for filename, sql_content in migrations.items(): console.print(Panel(f"Scanning [bold cyan]{filename}[/bold cyan]..."), style="blue") try: message = client.messages.create( model="claude-3-5-sonnet-20241022", max_tokens=2000, system=SYSTEM_PROMPT, messages=[ { "role": "user", "content": f"Analyze the following SQL migration file:\n\nFile Name: {filename}\n\nSQL:\n{sql_content}" } ] ) analysis_result = message.content[0].text # Print formatted markdown to terminal console.print(Markdown(analysis_result)) console.print("\n" + "="*80 + "\n") # Simple exit code check to fail CI pipelines if 'DANGER' is found if "[DANGER]" in analysis_result: console.print("[bold red]❌ CRITICAL HAZARD DETECTED. Blocking deployment.[/]", style="red") sys.exit(1) except Exception as e: console.print(f"[bold red]Failed to analyze {filename}:[/] {e}") sys.exit(1) console.print("[bold green]✔ Schema analysis complete. Your database keeps ticking.[/]")

if __name__ == "__main__": target_dir = sys.argv[1] if len(sys.argv) > 1 else "migrations/" migration_data = read_migration_files(target_dir) if not migration_data: console.print("[yellow]No SQL migration files found to scan.[/]") else: analyze_migrations(migration_data) `

Step 3: Test the Guard Against Dangerous SQL

To ensure our guard actually flags bad practices, let's create a test folder filled with some genuinely terrifying SQL operations.

`bash mkdir migrations `

Create migrations/0001_add_user_bio.sql and paste a classic database-freezing operation:

`sql -- This will lock the entire users table on large databases during rewrite ALTER TABLE users ADD COLUMN bio VARCHAR(500) DEFAULT 'No bio yet' NOT NULL; `

Now, run the script against your test migrations:

`bash python3 migration_guard.py migrations/ `

Claude will immediately process the file, recognise the hazard of adding a NOT NULL column with a default value to an active table, and generate a step-by-step mitigation strategy (such as adding the column as nullable first, backfilling in batches, and then setting the constraint). If Claude detects [DANGER], the script will gracefully exit with status code 1, preventing any CI pipeline from running the migrations.

Fine-Tuning Your Custom Safety Prompt

Every engineering team has different risk profiles. If you are running MySQL 8.0, some migrations that are risky in PostgreSQL are completely online and safe.

You can easily customise the SYSTEM_PROMPT in your local script to match your specific tech stack. If you need some inspiration for refining your prompt rules, take a look at our interactive prompt generator to easily structure strict constraints for system instructions.

Should you run into any performance bottlenecks or API timeouts when analyzing massive, multi-megabyte schema dumps, you can check out our dedicated troubleshooting guide over on the Claude platform hub.

Keep your deployments clean, your locks short, and your databases functional. Happy building!