How to Build a Local CLI Database Schema Generator with Claude 3.5 Sonnet and Pydantic

The Problem with Manual Database Migrations

We have all been there. You are in the flow, sketching out a brand-new feature on a virtual whiteboard or in a messy markdown file. You know exactly what the relational database structure needs to look like. But then comes the boring part: writing the raw SQL, setting up the foreign keys, writing the rollback scripts, and making sure the syntax is perfect for your specific dialect of PostgreSQL or MySQL.

It is repetitive, prone to human error, and a waste of cognitive load. While standard ORM migration generators exist, they require you to write database models first. If you want to rapidly prototype, you want to work from plain language notes first.

In this guide, we are going to build a local CLI utility that takes a raw text or markdown description of your data models and outputs structured PostgreSQL DDL (Data Definition Language) statements. To do this reliably, we will use the reasoning capabilities of Claude 3.5 Sonnet paired with Pydantic to guarantee we get clean, valid SQL instead of generic markdown garbage.

Why Claude 3.5 Sonnet and Pydantic?

If you ask a standard LLM to "generate SQL migrations," it will usually spit back a block of markdown with some conversational filler. It might skip a column, or drop a database constraint because it got lazy.

To make this production-ready, we need two things: 1. High-quality reasoning: Sonnet is exceptionally good at parsing loose relational descriptions and understanding relationships (like realizing that a user_id should automatically link to a users table). 2. Type-safe structure: By using Pydantic, we force Claude to return a structured JSON object that matches our exact schema. If the LLM misses a required field, the validation fails, and we can handle it safely. This is what makes the system tick.

Step 1: Setting Up the Environment

First, let us get your local environment ready. We will need Python 3.10+ and a few core libraries. Run the following command to set up your directory and install the necessary dependencies:

`bash mkdir db-schema-cli && cd db-schema-cli python3 -m venv .venv source .venv/bin/activate pip install anthropic pydantic click python-dotenv `

Create a .env file in the root of your project and add your Anthropic API key:

`env ANTHROPIC_API_KEY=your_actual_api_key_here `

Step 2: Defining the Schema Models with Pydantic

We need to define exactly what a "migration" looks like to our application. We do not just want a raw block of SQL; we want to split up the creation of tables, indexes, and drop-scripts so that we can customise the output later.

Create a file named models.py and write the following structures:

`python from pydantic import BaseModel, Field from typing import List

class Column(BaseModel): name: str = Field(..., description="The name of the database column, lowercase and snake_case.") data_type: str = Field(..., description="The SQL data type (e.g., VARCHAR(255), TIMESTAMP, INTEGER).") constraints: List[str] = Field(default=[], description="Column constraints like PRIMARY KEY, UNIQUE, or NOT NULL.")

class Table(BaseModel): table_name: str = Field(..., description="The name of the table in snake_case.") columns: List[Column] = Field(..., description="The list of columns for this table.") foreign_keys: List[str] = Field(default=[], description="Foreign key constraints (e.g., 'FOREIGN KEY(user_id) REFERENCES users(id)').") indexes: List[str] = Field(default=[], description="Any indexes that should be created for performance (e.g., 'CREATE INDEX idx_users_email ON users(email)').")

class MigrationPlan(BaseModel): tables: List[Table] = Field(..., description="A list of tables to create in dependency order.") raw_sql_migration: str = Field(..., description="The complete compiled, syntactically valid PostgreSQL migration script.") `

By splitting the schema into structured classes, we can inspect, modify, or save individual tables inside our python code before committing them to a SQL file.

Step 3: Writing the Claude CLI Engine

Now, let us create the core execution script, generator.py. This script will load our system prompt, call the Anthropic API, parse the response using Claude's tool-calling capabilities, and write the output files.

To ensure your prompts are robust and cost-effective, you can always test and iterate on prompt structures using our prompt generator.

`python import os import click from dotenv import load_dotenv from anthropic import Anthropic from models import MigrationPlan

load_dotenv()

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

SYSTEM_PROMPT = """ You are an expert database administrator. Your task is to translate loose, informal markdown descriptions of database requirements into structured, production-ready PostgreSQL migration plans. Always follow these rules: 1. Ensure tables are ordered by dependency (e.g., create parent tables before child tables containing foreign keys). 2. Standardize column names to lowercase snake_case. 3. Add reasonable indexes to foreign keys and unique columns to optimise query performance. 4. Ensure the output is completely standard, modern PostgreSQL. """

def generate_schema(prompt_text: str) -> MigrationPlan: # Using the native tools API to enforce Pydantic structured output response = client.beta.tools.messages.create( model="claude-3-5-sonnet-20241022", max_tokens=4000, system=SYSTEM_PROMPT, messages=[{"role": "user", "content": prompt_text}], tools=[ { "name": "output_migration_plan", "description": "Outputs a structured plan containing table objects andcompiled SQL.", "input_schema": MigrationPlan.model_json_schema() } ], tool_choice={"type": "tool", "name": "output_migration_plan"} ) # Extract and parse tool use payload tool_use = next(block for block in response.content if block.type == "tool_use") return MigrationPlan.model_validate(tool_use.input)

@click.command() @click.argument("input_file", type=click.Path(exists=True)) @click.option("--output-dir", "-o", default=".", help="The directory to save generated SQL migrations.") def main(input_file, output_dir): """Generates structured PostgreSQL migrations from a markdown file specification.""" click.echo(f"Reading specification from: {input_file}...") with open(input_file, "r") as f: content = f.read() try: click.echo("Calling Claude to generate structured database schemas...") migration_plan = generate_schema(content) # Prepare output filename os.makedirs(output_dir, exist_ok=True) output_path = os.path.join(output_dir, "0001_auto_migration.sql") # Write raw compiled SQL output with open(output_path, "w") as sql_out: sql_out.write(migration_plan.raw_sql_migration) click.echo(click.style(f"🎉 Migration successfully generated and written to {output_path}!", fg="green")) # Optional: Print summary of generated tables click.echo("\nGenerated Tables Summary:") for t in migration_plan.tables: cols = ", ".join([f"{c.name} ({c.data_type})" for c in t.columns]) click.echo(f" - {t.table_name}: [{cols}]") except Exception as e: click.echo(click.style(f"Error processing request: {str(e)}", fg="red"), err=True) click.echo("For troubleshooting tips on model calls, visit /platforms/claude/articles")

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

Step 4: Testing the CLI Tool

Let us test this on a realistic prototype specification. Create a file called todo_spec.md with some loose database ideas:

`markdown # Todo App Schema Spec

I need a system to manage user accounts. Every user has an email, a hashed password, and an optional profile bio.

Then I need a tasks table. Each task must have a title, a nullable description, a boolean status for whether it is completed, and should be linked back to the user who created it.

Also, let us add a tag system. Tasks can have many tags, and tags can belong to many tasks. Each tag just has a unique name. Create a join table for these. `

Now run your generator command from the terminal:

`bash python generator.py todo_spec.md `

You should see Claude go to work, validate the outputs against your Pydantic model, and write a shiny new PostgreSQL script directly to 0001_auto_migration.sql with correct dependency sorting (creating users before tasks, and handling the join table gracefully).

If your setup fails due to formatting errors or key mismatches, check out our Claude troubleshooting hub for solutions to common structured API output hiccups.

Next Steps

Now that you have a local engine capable of translating ideas to rigid SQL, you can easily integrate this directly into your local development shell aliases, or hook it up as a pre-commit command to ensure your design specs and database schemas always stay closely synchronised.