MVP – SQL Schema Migrator (Tracking Issue)

[stifskere]

This issue tracks the core tasks for building the MVP of atern — a schema migration tool that compares actual database dumps with migration definitions and generates PostgreSQL, MySQL, MariaDB, and SQLite-compatible SQL statements to bridge the difference.

Goal

The MVP should be a tool that

• Parses SQL/PSQL schemas (starting with CREATE TABLE)
• Constructs dialect-agnostic Abstract Syntax Trees (ASTs)
• Computes diffs between two versions of a schema (e.g., dump vs. migration file)
• Generates dialect-specific SQL migration statements from the diff

Example

Given a dump:

CREATE TABLE xxx (
  id INTEGER AUTOINCREMENT PRIMARY KEY
);

And a migration file:

CREATE TABLE xxx (
  id INTEGER AUTOINCREMENT PRIMARY KEY,
  name VARCHAR(250) NOT NULL
);

atern should generate:

ALTER TABLE xxx
  ADD COLUMN name VARCHAR(255) NOT NULL;

---

MVP Task Overview

• Build a Generic AST Builder
  Create a reusable, extensible AST layer to represent SQL structures across dialects (tables, columns, constraints, etc.).

• Implement CREATE TABLE Parser
  Parse CREATE TABLE definitions from at least one dialect (e.g., MySQL or SQLite) into a structured format.

• Construct AST from Parsed Components
  Convert parsed output into structured AST nodes, representing the full table definition.

• Develop Tree Diffing Algorithm
  Compute differences between the dump's AST and the migration AST. Output a forest representing add/remove/modify operations.

• Convert Forest to SQL Migration Queries
  Transform diff results into valid SQL ALTER TABLE or DDL statements compatible with each target dialect.

• Write Comprehensive Tests
  Ensure correctness through:

  • Unit tests (AST, parser, diff logic)
  • Integration tests (end-to-end: parse → diff → generate)

---

This issue serves as the meta-tracker. Each task is split into its own issue with detailed implementation notes and acceptance criteria.