How to Build a Local CLI Tool to Generate Database Migrations from Raw SQL Diffs Using Claude 3.5 Sonnet

Writing raw SQL is a joy. Designing database schemas is satisfying. Writing the migration scripts to get from version A to version B? That is a soul-crushing chore.

Sure, heavyweight ORMs will auto-generate migrations for you, but they often produce bloated, unoptimised SQL that doesn't respect your database's unique quirks. If you prefer to write raw SQL, you are usually left comparing two schema files by hand and writing the ALTER TABLE statements yourself. It is a slow, error-prone process where a single missed constraint can bring down a production database.

We can do better. By combining a small Python script with the reasoning power of Claude 3.5 Sonnet, we can build a local command-line tool that analyses two SQL schema dumps, identifies the differences, and generates clean, safe, and commented migration scripts (both up and down).

Here is how to build it.

Why Claude 3.5 Sonnet for SQL Migrations?

Database migrations are not just about finding structural differences; they require semantic understanding. If you rename a column from user_name to username, a simple AST (Abstract Syntax Tree) diffing tool will see this as dropping one column and adding another. This would result in data loss.

Claude 3.5 Sonnet understands context. It can infer that a dropped column and a newly created column with similar types and names are actually a rename operation, allowing it to write a migration that preserves your data.

To run this tool, you will need an API key from Anthropic. If you encounter any API-related snags while setting up your environment, check out the Anthropic Support Page for troubleshooting.

Step 1: Designing the System Prompt

The secret to reliable code generation is strict structural boundaries. We will use Claude's affinity for XML tags to cleanly separate our old schema, new schema, and generation constraints.

To make this tool run reliably, we must instruct the model to think before it writes. We will demand a brief explanation of its plan inside thinking tags before it outputs the raw SQL blocks. Here is the system prompt we will embed in our Python CLI:

`text You are an expert database administrator specialising in PostgreSQL. Your task is to compare an old schema and a new schema, then generate safe, idempotent migration scripts.

You must output two SQL scripts: 1. An 'up' migration to transition the database from the old schema to the new schema. 2. A 'down' migration to safely roll back those changes.

Constraints: - Do not drop tables or columns if you can perform an ALTER or RENAME to preserve data. - Ensure foreign keys are added in the correct order to prevent dependency issues. - Use 'IF EXISTS' and 'IF NOT EXISTS' where appropriate to make the scripts idempotent. - Output your response using this exact format:

<thinking> Briefly explain your analysis of the diff and your plan for preserving data. </thinking>

<up_migration> -- Up migration SQL here </up_migration>

<down_migration> -- Down migration SQL here </down_migration> `

You can experiment with tuning this prompt for other SQL dialects (like MySQL or SQLite) using our /prompts.

Step 2: Setting Up the Python CLI

First, install the necessary dependencies. We will use the official Anthropic Python SDK and click to build a clean command-line interface.

`bash pip install anthropic click `

Now, create a file named migrator.py. We will write a script that takes the paths to your old and new schema files, reads them, sends them to the /platforms/claude, and parses the resulting XML blocks.

`python import os import re import click from anthropic import Anthropic

Initialize the Anthropic client client = Anthropic(api_key=os.environ.get("ANTHROPIC_API_KEY"))

SYSTEM_PROMPT = """Your system prompt from Step 1 goes here..."""

def extract_tag_content(text, tag): pattern = f"<{tag}>(.*?)</{tag}>" match = re.search(pattern, text, re.DOTALL) return match.group(1).strip() if match else ""

@click.command() @click.option('--old', required=True, type=click.Path(exists=True), help='Path to the old schema SQL file.') @click.option('--new', required=True, type=click.Path(exists=True), help='Path to the new schema SQL file.') @click.option('--outdir', default='./migrations', help='Directory to save migration files.') def main(old, new, outdir): """Compare two SQL schemas and generate Up/Down migrations.""" click.echo("Reading schema files...") with open(old, 'r') as f: old_schema = f.read() with open(new, 'r') as f: new_schema = f.read()

click.echo("Analysing schemas with Claude 3.5 Sonnet...") prompt = f"""Please compare these schemas:

<old_schema> {old_schema} </old_schema>

<new_schema> {new_schema} </new_schema>"""

try: response = client.messages.create( model="claude-3-5-sonnet-20241022", max_tokens=4000, temperature=0.1, # Low temperature for precise, deterministic SQL system=SYSTEM_PROMPT, messages=[{"role": "user", "content": prompt}] ) raw_output = response.content[0].text thinking = extract_tag_content(raw_output, "thinking") up_sql = extract_tag_content(raw_output, "up_migration") down_sql = extract_tag_content(raw_output, "down_migration") if not up_sql or not down_sql: click.echo("Error: Claude failed to return migrations in the requested XML format.") click.echo(raw_output) return

Ensure output directory exists os.makedirs(outdir, exist_ok=True) # Write migrations up_path = os.path.join(outdir, "migration_up.sql") down_path = os.path.join(outdir, "migration_down.sql") with open(up_path, 'w') as f: f.write(up_sql) with open(down_path, 'w') as f: f.write(down_sql) click.echo(click.style("\nSuccess! Migrations generated successfully.", fg="green")) click.echo(f"\nAnalyst Notes:\n{thinking}\n") click.echo(f"Saved: {up_path}") click.echo(f"Saved: {down_path}") except Exception as e: click.echo(click.style(f"An error occurred: {str(e)}", fg="red"))

if __name__ == '__main__': main() `

It’s a neat way to make the whole process tick without introducing a heavyweight ORM wrapper around your project.

Step 3: Running the Tool

Let’s test our tool with a common real-world schema evolution scenario. Create two files in your project directory.

schema_old.sql: `sql CREATE TABLE users ( id SERIAL PRIMARY KEY, user_name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); `

schema_new.sql: `sql CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(100) NOT NULL, -- Renamed from user_name and length increased email VARCHAR(100) UNIQUE NOT NULL, is_active BOOLEAN DEFAULT TRUE, -- New column added created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); `

Run the command to generate your migrations:

`bash export ANTHROPIC_API_KEY="your-key-here" python migrator.py --old schema_old.sql --new schema_new.sql `

Claude will output the files to your ./migrations directory.

If you inspect migration_up.sql, you will see that instead of running a destructive DROP COLUMN user_name and ADD COLUMN username, it intelligently generates:

`sql ALTER TABLE users RENAME COLUMN user_name TO username; ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100); ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT TRUE; `

And the migration_down.sql will perfectly reverse this:

`sql ALTER TABLE users DROP COLUMN is_active; ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(50); ALTER TABLE users RENAME COLUMN username TO user_name; `

Best Practices for AI-Generated Migrations

While Claude 3.5 Sonnet is remarkably adept at generating database changes, you should never run automated SQL directly on production without human review. Make this CLI part of your local developer workflow. Run it to generate your migration files, commit them to your repository, and review the code as part of your standard pull request process.

With this lightweight script, you can maintain a clean, raw SQL codebase without wasting hours writing database boilerplate.