SQL DDL Generation

Diagram2Code generates SQL DDL for four database dialects: PostgreSQL, MySQL, SQLite, and Oracle 19c+. All dialects share the same normalisation and validation pipeline — only the final rendering differs.

Generation Pipeline

1. Parse the diagram into the intermediate Schema model.
2. Normalise — add synthetic id PKs where no PK is defined, deduplicate columns, set FK column names from relationship endpoints.
3. Validate — check that FK targets exist, composite PKs are consistent, enum directives are present for enum-typed columns.
4. Emit
   1. Enum type definitions (PostgreSQL: CREATE TYPE … AS ENUM)
   2. CREATE TABLE statements with column definitions and inline constraints
   3. ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY statements
   4. CREATE [UNIQUE] INDEX statements
   5. COMMENT ON statements (PostgreSQL & Oracle)

Primary Keys

For composite primary keys (multiple PK markers), the individual columns have no inline constraint; instead a table-level PRIMARY KEY (col1, col2) clause is appended.

Foreign Keys

Every relationship line in the diagram generates an ALTER TABLE foreign key constraint after all tables are created. FK columns are identified by matching the FK marker on a column with a relationship that references the same table.

erDiagram
    USER { int id PK }
    ORDER {
        int id PK
        int user_id FK
    }
    USER ||--o{ ORDER : places

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

Cascade Actions

Use the %% ::RELATIONSHIP[onDelete: X, onUpdate: Y] directive before the relationship line (Mermaid) or ' ::RELATIONSHIP[…] (PlantUML). Supported values:

• CASCADE — delete/update child rows automatically
• SET NULL — set FK column to NULL
• RESTRICT — prevent parent deletion if children exist
• NO ACTION — same as RESTRICT, checked at end of statement
• SET DEFAULT — set FK to its DEFAULT value

Constraints

Indexes

Indexes are emitted after all CREATE TABLE statements and foreign keys.

The INDEX marker on a column (without a directive) generates a single-column index with an auto-generated name.

Enums

ORDER {
    int id PK
    %% ::ENUM(pending,shipped,delivered)
    enum status
}

CREATE TYPE "order_status_enum"
    AS ENUM('pending','shipped','delivered');

CREATE TABLE "order" (
    "id"     SERIAL PRIMARY KEY,
    "status" "order_status_enum"
);

MySQL: Generates inline ENUM('pending','shipped','delivered') on the column.

SQLite & Oracle: Generates TEXT / VARCHAR2 with a CHECK (status IN (…)) constraint.

Column Comments

Quoted strings at the end of a column definition become comments.

string email UK "Login email"

COMMENT ON COLUMN "user"."email" IS 'Login email';

MySQL generates inline COMMENT 'text' on the column definition. SQLite ignores comments.

Dialect Differences