How to Build a CLI to Auto-Generate PostgreSQL Migrations from Markdown Specs Using OpenAI Structured Outputs and Pydantic

Stop Writing Boilerplate DDL by Hand

Writing database migrations is one of those routine tasks that sits right in the uncanny valley of developer productivity. It is critical enough that a single typo in a FOREIGN KEY or index definition can corrupt your database state, yet tedious enough that you would rather be doing almost anything else.

With the release of Structured Outputs, LLMs have become incredibly reliable engines for translation. We can now feed a plain markdown file—written during a product scoping session or architectural draft—into an LLM and expect a perfectly structured SQL migration plan out of the other side. No weird markdown wrappers, no half-finished blocks of text, and no hallucinated syntax.

This guide will show you how to build a local CLI tool in Python that processes raw markdown database specifications and outputs clean, syntactically correct PostgreSQL migration files.

We will be using the OpenAI Platform and Pydantic to enforce exact response formatting. If you run into any structural issues or API exceptions during your build, you can check the developer docs or head over to OpenAI Support.

Why Structured Outputs Matter for Database Schema Design

Until recently, using an LLM to generate code or structured text was a bit of a gamble. You had to prompt the model with complex templates, hoping it would not inject a polite "Certainly! Here is your SQL script:" before your actual DDL.

Using Structured Outputs guarantees that the response matches a strict JSON Schema generated directly from a Pydantic model. If the API fails to match the schema, the request fails outright, preventing corrupt or half-baked code from entering your file system. To understand more about how JSON schemas and model outputs align behind the scenes, take a quick peek at our glossary definition of structured outputs.

Step 1: Define Your Python Environment

Let us build a simple directory structure for our generator:

`text migration-gen/ ├── spec.md ├── generate_migration.py └── requirements.txt `

Create a requirements.txt file with the essential libraries:

`text openai>=1.40.0 pydantic>=2.0.0 click>=8.0.0 `

Install the packages locally by running pip install -r requirements.txt.

Step 2: Define the Output Schema Using Pydantic

We need to tell our model exactly what a PostgreSQL migration looks like. It is not just raw SQL; we want to extract structural metadata, including what tables are being created, what columns are modified, and why the changes are being made. This makes the generated migration logs highly readable for human review.

Open up generate_migration.py and write the Pydantic classes:

`python import os import click from typing import List, Optional from pydantic import BaseModel, Field from openai import OpenAI

Define the schema we expect from OpenAI class ColumnSpec(BaseModel): name: str = Field(description="The exact name of the column") data_type: str = Field(description="The SQL data type, e.g., VARCHAR(255), INTEGER, TIMESTAMP WITH TIME ZONE") constraints: List[str] = Field(description="Constraints like PRIMARY KEY, UNIQUE, NOT NULL, or FOREIGN KEY reference")

class TableChange(BaseModel): table_name: str = Field(description="Name of the table being created or altered") action: str = Field(description="Whether we are 'creating', 'altering', or 'dropping' the table") columns_affected: List[ColumnSpec] = Field(description="List of columns added, modified, or dropped") raw_sql: str = Field(description="The raw, executable PostgreSQL DDL statement for this specific table action")

class MigrationPlan(BaseModel): migration_name: str = Field(description="A snake_case name summarizing the migration, e.g., create_users_and_profiles") justification: str = Field(description="A brief explanation of why these database changes are needed based on the spec") changes: List[TableChange] = Field(description="The sequential table modifications required for this migration") `

Step 3: Implement the CLI Generation Logic

Now, we will add the core generation engine and Click interface to the same file. This CLI will read your specification document, pass it to gpt-4o, construct the strict response structure, and output a clean .sql file prepended with an explanation header.

Add this code to generate_migration.py:

`python # Initialize the OpenAI client # Make sure OPENAI_API_KEY is exported in your terminal session. client = OpenAI(api_key=os.environ.get("OPENAI_API_KEY"))

SYSTEM_INSTRUCTIONS = """ You are an expert systems database engineer. Your job is to take raw markdown design specifications and generate highly optimised, safe PostgreSQL schema migrations.

Ensure that you: 1. Adhere strictly to modern PostgreSQL standards (use timestamptz over timestamp, appropriate indexing, etc.). 2. Order the raw SQL changes sequentially to respect foreign key constraints (e.g., create parent tables before child tables). 3. Use explicit transactions if necessary. """

@click.command() @click.argument('spec_file', type=click.Path(exists=True)) @click.option('--output-dir', default='migrations', help='Directory to output the generated SQL files') def main(spec_file, output_dir): """Generates PostgreSQL DDL migrations from a markdown architecture spec.""" # Load the markdown file with open(spec_file, 'r', encoding='utf-8') as f: markdown_spec = f.read() click.echo("Analyzing specification and building schema mapping...") try: # Call the OpenAI Chat Completions API with structured output enforcement response = client.beta.chat.completions.parse( model="gpt-4o-2024-08-06", messages=[ {"role": "system", "content": SYSTEM_INSTRUCTIONS}, {"role": "user", "content": f"Please translate this markdown specification into a migration plan:\n\n{markdown_spec}"} ], response_format=MigrationPlan, ) migration_data = response.choices[0].message.parsed # Create target directory if it doesn't exist os.makedirs(output_dir, exist_ok=True) # Prepare file name filename = f"{migration_data.migration_name}.sql" filepath = os.path.join(output_dir, filename) # Write the migration file with open(filepath, 'w', encoding='utf-8') as f: f.write(f"-- Migration: {migration_data.migration_name}\n") f.write(f"-- Reason: {migration_data.justification}\n") f.write("-- Generated automatically. Verify constraints before running in production.\n\n") f.write("BEGIN;\n\n") for table_change in migration_data.changes: f.write(f"-- Table: {table_change.table_name} ({table_change.action.upper()})\n") f.write(f"{table_change.raw_sql.strip()}\n\n") f.write("COMMIT;\n") click.echo(click.style(f"🎉 Successfully generated migration: {filepath}", fg='green')) except Exception as e: click.echo(click.style(f"💥 Error generating migration: {e}", fg='red'), err=True)

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

Step 4: Testing Your Tool with a Real Spec

Let us test the tool with a realistic markdown database specification. Create a file called spec.md with the following contents:

`markdown # Project Architecture: E-Commerce Subscriptions

We need to track system users and recurring billing subscriptions.

Users Table Every user must have a system ID, a unique validated email, and a dynamic subscription level.

• ID (Auto-incrementing primary key)
• Email (String, unique, must not be null)
• Joined At (Timestamp, defaults to current time)

Subscriptions Table Each subscription is bound to a single user.

- ID (UUID primary key) - User ID (references users table, cascades on delete) - Status (String, e.g., active, past_due, canceled) - Price in Cents (Integer, must be positive) - Next Billing Date (Timestamp) `

Export your API key in your terminal and run the script:

`bash export OPENAI_API_KEY="your-openai-api-key" python generate_migration.py spec.md `

Go inspect your newly created file under migrations/. You will find a clean, formatted SQL file with explicit foreign keys structured in the correct sequence—with users created before subscriptions to prevent constraint violations.

Designing with Safety in Mind

This simple command-line script ensures your code generations tick all the technical boxes while cutting down on brainless schema editing. Always review your generated DDL manually before committing changes to database schema systems in production environments. Have fun automating your migration pipeline!