One Schema Model, N parsers, M generators

Published 2026-07-07 · diagram2code

I built a tool that turns ER diagrams into SQL DDL, ORM models, and API schemas. The interesting part isn't the input formats or the output targets — it's the small intermediate representation in the middle that means I almost never have to think about both at once. This is a writeup of that design decision and what it actually bought me, with real input and output.

The combinatorial trap

The naive version of this tool is a disaster waiting to happen. You start with one parser (Mermaid) and one target (Postgres DDL). Easy. Then someone wants PlantUML. Then someone wants MySQL. Then GORM models. Then SQLAlchemy. Then Prisma.

If every parser knows how to emit every target, you're writing N × M code paths, and worse, each new format means editing M places and each new target means editing N places. The blast radius of any change is the whole matrix. That's the kind of architecture that's fine for a weekend and miserable by month three.

The fix is the oldest trick in the compiler book: put an intermediate representation in the middle and refuse to let either side know about the other.

diagram text ──parser──► *Schema ──generator──► SQL / ORM / GraphQL / OpenAPI

Every parser's only job is to produce a *Schema. Every generator's only job is to consume one. Parsers don't import generators; generators don't import parsers. Adding a new diagram format is N + 1, not N × M. Adding a new output target is M + 1. Each is a strictly one-sided change.

The Schema Model

The whole thing pivots on one struct. The model is deliberately boring — it's the lowest common denominator of "relational schema," nothing more:

  • Schema → Table → Column + Relationship + Index
  • Relationships are "table.column" string references with a cardinality (one_to_many, etc.) plus OnDelete / OnUpdate behavior.

That's it. There's no Mermaid-shaped or Postgres-shaped concept anywhere in it. A relationship doesn't know whether it came from a Mermaid ||--o{ or a PlantUML connector, and it doesn't know whether it's about to become a Postgres foreign key or a GORM belongs_to tag. It's just a typed reference with a cardinality.

The discipline this enforces is the actual value. Every time I'm tempted to leak a format detail into the model, the structure pushes back: if the concept is Mermaid-specific, it belongs in the Mermaid parser; if it's Postgres-specific, it belongs in the Postgres generator. The model stays the neutral ground in between.

Per-format pipeline: lexer → parser → *Schema

Each input format has its own little front end, structured like a tiny compiler:

source ──lexer──► tokens ──parser──► *Schema

Mermaid and PlantUML each have their own lexer.go and parser.go and expose the same one-line contract: Parse(string) (*Schema, error). The diagram type is auto-detected from the input. The lexer turns raw text into tokens; the parser assembles tokens into tables, columns, and relationships. The two formats look nothing alike on the page, but they converge on the identical *Schema — which is exactly the property you want, and exactly the kind of thing that quietly rots if you don't test it.

So there's a test that does nothing but assert equivalence: feed Mermaid and the corresponding PlantUML through their respective front ends and assert the resulting *Schema values are identical. If the two parsers ever drift in how they interpret "the same" diagram, that test fails. Golden-file tests pin the rest of parser behavior.

Here's real Mermaid input:

erDiagram
    USER ||--o{ ORDER : places
    USER {
        int id PK
        string email
        string name
    }
    ORDER {
        int id PK
        int user_id FK
        decimal total
        datetime created_at
    }

And the Postgres DDL that comes out the other side:

CREATE TABLE "user" (
    "id" SERIAL PRIMARY KEY,
    "email" TEXT,
    "name" TEXT
);

CREATE TABLE "order" (
    "id" SERIAL PRIMARY KEY,
    "user_id" INTEGER,
    "total" NUMERIC,
    "created_at" TIMESTAMPTZ
);

ALTER TABLE "order"
    ADD CONSTRAINT "fk_order_user_id"
    FOREIGN KEY ("user_id") REFERENCES "user"("id");

A few things worth pointing out, because they're the generator's decisions and not the model's: identifiers are lowercased and double-quoted, table names are kept as written ("user", "order") rather than pluralized, string maps to TEXT and datetime to TIMESTAMPTZ, and foreign keys come out as a separate ALTER TABLE … ADD CONSTRAINT after the tables exist, so declaration order never trips you up. The parser never produced any of that. The generator never saw Mermaid. They met at *Schema.

Directives: metadata the diagram syntax can't express

ER diagram notations are good at structure — entities, attributes, cardinality — and bad at the dozen things you actually need to write a real schema. Mermaid erDiagram has no syntax for "this column is NOT NULL," "this has a default," "this is a CHECK constraint," "build a partial index over these two columns." If I extended Mermaid's grammar to add them, I'd be inventing a dialect of Mermaid that no other tool understands, and I'd be doing it twice (once for PlantUML).

So instead the extra metadata rides in as directives — annotations that live inside diagram comments, which means the source stays a valid Mermaid (or PlantUML) diagram that still renders anywhere. The parser picks them up; every other tool just sees comments.

PRODUCT {
    int id PK
    %% ::CHECK(price > 0), ::NN
    decimal price
    %% ::DEFAULT(0)
    int stock
}

::NN is NOT NULL, ::CHECK(price > 0) attaches a check constraint, ::DEFAULT(0) sets a default. They can be comma-chained on a single line. The set covers the things you reach for constantly: ::ENUM(a,b), ::UNIQUE([cols]), ::INDEX([col1,col2], name:"x", where:"...") for named and partial indexes, ::RELATIONSHIP[onDelete:CASCADE, onUpdate:...], named constraints via @col::UK(name:"..."), ::COMMENT("text"), and entity display labels like TABLE["Display Label"]. There are block-level and global @TABLE::col::DIRECTIVE forms too.

The important architectural bit: directives populate fields on the same neutral *Schema. A ::CHECK becomes a check constraint on a Column, full stop. The Postgres generator and the MySQL generator both consume that constraint without caring that it arrived as a comment annotation in a Mermaid file. The directive system is a parser-side concern; the generators never hear about the syntax.

A shared dialect config keeps four SQL backends thin

The SQL side supports PostgreSQL, MySQL, SQLite, and Oracle. The temptation is to write four separate code generators, which is four places for the same bug.

Instead there's one DDL emitter and a per-dialect DialectConfig that captures only the things that genuinely differ:

  • type mapping (int → SERIAL/INTEGER/NUMBER depending on dialect),
  • identifier quoting,
  • enum strategy (native enum vs. check constraint vs. lookup),
  • auto-increment primary keys.

The emitter walks the *Schema and calls into the config wherever a dialect actually diverges. The dialects stay thin — they're mostly data, not code. When I added Oracle, I didn't write an Oracle code generator from scratch; I wrote an Oracle DialectConfig and let the shared emitter do the walking. That's the one-sided-change property again, applied within the SQL side.

The same payoff showed up adding the API-schema generators. GraphQL schema output and an OpenAPI 3.1 document are both just new consumers of *Schema. Writing them touched the generator side only — no parser changes, no model changes. A Relationship with one_to_many cardinality already carries everything a GraphQL list field or an OpenAPI $ref needs.

What else the model gets you for free

Because everything is normalized into one structure, a few schema-level smarts live in one place and benefit every input and output:

  • Normalize() dedups and injects a synthetic primary key named id when a table has none.
  • Validate() catches dangling foreign keys, duplicate tables, and missing primary keys.
  • Many-to-many relationships get a junction table generated automatically; self-referencing foreign keys are inferred from an FK marker.
  • A linter runs six checks over the model — table with no columns (error), enum with no values (error), relationship referencing an undefined table (error), FK column with no matching relationship (warning), column name not snake_case (warning), and missing PK on a junction table (info).
  • A migration diff engine diffs two *Schema values into ordered operations (CREATE/DROP TABLE, ADD/DROP/ALTER COLUMN, ADD/DROP CONSTRAINT, CREATE/DROP INDEX, RENAME TABLE/COLUMN), emits migration SQL for all four dialects, and flags destructive changes — dropping a column or table, narrowing a column type — with warnings.

That last one plugs into CI. There's a GitHub Action that, on any PR touching a .mmd or .puml diagram, posts the generated SQL plus a human-readable schema diff as a sticky PR comment and can fail the build on a destructive change:

- uses: diagram2code/diagram2code@v1
  with:
    database: postgres
    fail-on-destructive: true

Honest scope

This does ER diagrams today — Mermaid erDiagram and PlantUML entity diagrams in, SQL DDL (four dialects), eight ORM frameworks (GORM, SQLAlchemy, TypeORM, Prisma, JPA, Django, Sequelize, EF Core), and GraphQL/OpenAPI out. It does not do class diagrams, project scaffolding, or anything beyond schema-shaped problems. The whole bet is that a small, neutral intermediate model is worth more than a pile of clever direct translations, and so far the bet keeps paying off every time I add a format or a target and only have to touch one side.

If you want to poke at it, there's a live converter with a real-time Mermaid preview and a free tier at diagram2code.com, and the code is at github.com/diagram2code/diagram2code.