ProjectOpen-source tool

DiffDDL: Semantic Schema Migrations

A schema migration is an operation, not a string comparison.

Documented July 19, 2024

Why I made it

I built this to give database agents a way to reason about a migration as a sequence of safe operations, instead of trusting a text diff with production state.

String-based diffs for database schemas are notoriously fragile. DiffDDL solves this by parsing Data Definition Language (DDL) statements into an Abstract Syntax Tree (AST) and computing the minimal operational graph to transition between states. This provides a deterministic foundation for LLM-driven database agents, allowing them to propose and validate schema migrations safely without dropping state.

Tools: AST Parsing · SQL · Schema Validation · Agent Tooling

If you have ever let an LLM agent manage a database schema, you know the terror of trusting it to write a migration script.

The problem

LLMs are text predictors. When asked to modify a database, they naturally want to output raw ALTER TABLE strings. But string-based diffs for database schemas are notoriously fragile. A missing comma, an altered constraint name, or a misunderstood foreign key relationship can result in dropped tables and catastrophic data loss.

To build safe database agents, we need to take the generation of SQL out of the LLM's hands.Not out of the loop — out of the generation step. The model still decides what change it wants; it just expresses that as an intent the tool compiles, rather than as SQL nobody reviewed. That is the premise of DiffDDL.

python examples/gdpr_compliance.py

Clone the repo and run that line — the output below is a real run of the bundled GDPR example, not a mockup.

Why string diffs fail

Imagine a table:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

You ask an agent to "add an email column." It might output:

ALTER TABLE users ADD email VARCHAR(255);

That seems fine, but what if the agent decides to rewrite the table entirely because it "looks cleaner"? What if it drops the primary key constraint? Text-based diffing tools (diff -u) will just show lines added and removed. They have no understanding of the database state or the operational safety of the transition.

How it works

DiffDDL abandons string comparisons entirely. Instead, it parses Data Definition Language (DDL) statements directly into an Abstract Syntax Tree (AST).

When DiffDDL compares State A (the current database) to State B (the desired state proposed by the agent), it does not compare text; it compares the semantic structure of the tables, columns, indexes, and constraints, then computes the minimal operational graph required to transition from A to B.

Example operational graphDiffDDL emits typed operations, not raw SQL: each can be checked against policy before execution.

Operation	Target	Risk class
CreateColumn	users.email VARCHAR(255)	Safe — additive
AddIndex	users.email	Safe — additive
AlterColumnType	orders.total NUMERIC(10,2)	Review — may truncate data
DropConstraint	orders.fk_user_id	Blocked — requires human approval
DropTable	legacy_sessions	Blocked — requires human approval

Because this graph is strongly typed, deterministic safety checks run before any SQL is executed.

A deterministic foundation for LLM agents

By sitting between the LLM and the database, DiffDDL acts as an impenetrable safety net. The workflow for an LLM database agent becomes:

Agent proposes state. The LLM outputs the desired end-state of the schema in plain DDL.
DiffDDL parses. The tool parses the current live schema and the agent's proposed schema into ASTs.
DiffDDL diffs. It computes the operational graph.
Safety validation. DiffDDL checks the operations against a strict policy (e.g., "never allow DropTable or DropColumn without human approval").
Deterministic execution. If safe, DiffDDL generates the exact, syntactically perfect dialect-specific SQL (Postgres, MySQL, etc.) to execute the migration.

Why this matters

This architecture completely neuters the LLM's ability to make syntax errors in the final SQL query. The LLM only needs to understand the "what": the semantic shape of the data. DiffDDL handles the "how": the exact DDL commands required to safely reach that state.

This is a recurring theme in robust AI engineering: don't use LLMs for tasks that can be solved deterministically. By pushing schema parsing and migration logic down into a traditional compiler-theory approach (ASTs), DiffDDL enables autonomous database engineering without the existential dread of dropping production state.

Limitations

Dialect coverage is partial. AST parsing currently targets Postgres and MySQL; less common dialects (e.g., Oracle PL/SQL extensions) are not yet supported.
It validates structure, not intent. DiffDDL can tell you a DropColumn is destructive; it cannot tell you whether dropping that specific column is the right product decision. That judgment stays with the human reviewer.

Browse the source on GitHub: OsamaMoftah/DiffDDL.