Documentation

diagram2code converts Mermaid ER and PlantUML entity-relationship diagrams into production-ready SQL DDL for PostgreSQL, MySQL, SQLite, and Oracle 19c+.

How It Works

The pipeline has three stages:

1. Parse — The diagram text (Mermaid or PlantUML) is lexed and parsed into an Intermediate Schema Model (tables, columns, relationships).
2. Normalize — The schema is cleaned: tables without a primary key get a synthetic id column, duplicate columns are removed, and warnings are emitted.
3. Generate — The normalized schema is emitted as SQL DDL for the chosen database dialect, with dialect-specific type mapping, quoting, constraints, and comments.

┌──────────────┐     ┌─────────────────┐     ┌──────────────────┐
│  Mermaid ER   │────▶│   Intermediate   │────▶│  PostgreSQL DDL  │
│  PlantUML     │     │  Schema Model    │     │  MySQL DDL       │
└──────────────┘     └─────────────────┘     │  SQLite DDL      │
                                              │  Oracle DDL      │
                                              └──────────────────┘

Quick Start Example

Here's a simple diagram and the SQL it generates for each database:

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

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

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

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

Mermaid ER Syntax

Mermaid ER diagrams begin with erDiagram. Tables are defined as blocks with columns, and relationships connect tables with cardinality notation.

Table & Column Syntax

erDiagram
    TABLE_NAME {
        type column_name MARKERS "optional comment"
    }

Each column line has:

• type — Any of the abstract types (int, string, decimal, etc.) or native database types (varchar2(255), bigint, etc.).
• column_name — The column identifier (lowercase, underscored).
• markers — Optional: PK, FK, UK (unique key), or combinations.
• comment — Optional: a quoted string after the markers.

erDiagram
    PRODUCT {
        int id PK "Primary key"
        string name "Product name"
        decimal price
        boolean active
        string sku UK "Stock keeping unit"
        int category_id FK
        timestamp created_at
    }

PlantUML Syntax

PlantUML diagrams are wrapped in @startuml / @enduml. Entities use the entity keyword, columns use name : type order (note: reversed from Mermaid), and stereotypes mark PK/FK/UNIQUE.

@startuml

entity User {
  * id : int <<PK>>
  name : string
  email : string <<UNIQUE>>
}

entity Order {
  * id : int <<PK>>
  user_id : int <<FK>>
  total : decimal
}

User ||--o{ Order : places

@enduml

Column Markers

Markers control constraints generated on each column:

Relationships

Relationships define foreign key constraints between tables. Both Mermaid and PlantUML use similar cardinality notation:

-- From this Mermaid relationship:
USER ||--o{ ORDER : places

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

PostgreSQL postgres

The PostgreSQL generator produces idiomatic PG DDL:

• Auto-increment PK: SERIAL PRIMARY KEY
• Identifier quoting: double-quotes ("user")
• IF NOT EXISTS: supported
• Enums: separate CREATE TYPE ... AS ENUM(...) statements
• Comments: COMMENT ON COLUMN "table"."col" IS '...'
• Boolean: native BOOLEAN type
• UUID: native UUID type

CREATE TABLE "product" (
    "id" SERIAL PRIMARY KEY,
    "name" TEXT NOT NULL,
    "price" NUMERIC,
    "active" BOOLEAN DEFAULT true,
    "created_at" TIMESTAMPTZ
);

COMMENT ON COLUMN "product"."name" IS 'The display name of the product';

MySQL mysql

The MySQL generator produces idiomatic MySQL DDL:

• Auto-increment PK: INT AUTO_INCREMENT PRIMARY KEY
• Identifier quoting: backticks (`user`)
• IF NOT EXISTS: supported
• Enums: inline ENUM('a', 'b', 'c') in column definition
• Comments: inline COMMENT 'text' on column definition
• Boolean: mapped to TINYINT(1)
• UUID: mapped to CHAR(36)
• String: mapped to VARCHAR(255) (not TEXT)

CREATE TABLE `order` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `status` ENUM('pending', 'shipped', 'delivered') COMMENT 'Order status',
    `total` DECIMAL,
    `user_id` INT
);

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

SQLite sqlite

SQLite uses a simplified type system with type affinity:

• Auto-increment PK: INTEGER PRIMARY KEY (SQLite auto-increments INTEGER PKs)
• Identifier quoting: double-quotes ("user")
• IF NOT EXISTS: supported
• Enums: not supported — falls back to TEXT
• Comments: not supported — comments are omitted
• Boolean: mapped to INTEGER (0/1)
• Date/Time: all mapped to TEXT (ISO-8601 strings)
• Parameterized types: parameters are dropped (e.g. varchar(255) → TEXT)

CREATE TABLE "product" (
    "id" INTEGER PRIMARY KEY,
    "name" TEXT,
    "price" REAL,
    "active" INTEGER,
    "created_at" TEXT
);

-- Note: No COMMENT syntax, no ENUM, no UUID.
-- boolean → INTEGER, decimal → REAL, timestamp → TEXT.

Oracle 19c+ oracle

The Oracle generator targets Oracle Database 19c and later:

• Auto-increment PK: NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
• Identifier quoting: double-quotes ("user")
• IF NOT EXISTS: not supported — Oracle lacks this syntax
• Enums: not supported — falls back to VARCHAR2(255)
• Comments: COMMENT ON COLUMN "table"."col" IS '...'
• Boolean: mapped to NUMBER(1)
• UUID: mapped to RAW(16)
• String: mapped to VARCHAR2(255)
• Text/CLOB: mapped to CLOB
• Parameterized types: preserved (e.g. varchar2(4000) → VARCHAR2(4000))

CREATE TABLE "product" (
    "id" NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    "name" VARCHAR2(255) NOT NULL,
    "price" NUMBER,
    "active" NUMBER(1),
    "created_at" TIMESTAMP
);

COMMENT ON COLUMN "product"."name" IS 'The display name of the product';

Abstract Type Mapping

Abstract types are the recommended portable types. They map to the best native type for each database:

Native / Pass-through Types

You can also use native database types directly in your diagrams. They are mapped to the closest equivalent in each dialect:

Parameterized Types

Types with size or precision parameters like varchar2(4000) or number(10,2) are preserved in dialects that support them:

Unknown Types

If you use a type not in the mapping tables (e.g. jsonb, geometry, citext), it is passed through as-is in UPPERCASE:

-- Input: my_custom_type col_name
-- Output (all dialects):
"col_name" MY_CUSTOM_TYPE

This means you can use database-specific types even if they aren't in the built-in mapping. They will work for your target dialect and pass through unchanged for others.

Comments

Column comments from the diagram are emitted using dialect-specific syntax:

erDiagram
    USER {
        int id PK "Primary identifier"
        string email UK "Login email"
    }

CREATE TABLE "user" (
    "id" SERIAL PRIMARY KEY,
    "email" TEXT NOT NULL UNIQUE
);

COMMENT ON COLUMN "user"."id"
    IS 'Primary identifier';
COMMENT ON COLUMN "user"."email"
    IS 'Login email';

Enums

Enum handling varies significantly by dialect:

Constraints & Indexes

The following constraints are generated from diagram markers:

• PRIMARY KEY — from PK marker. Single integer PKs get auto-increment. Composite PKs use PRIMARY KEY ("col1", "col2").
• FOREIGN KEY — from relationships. Generated as ALTER TABLE ... ADD CONSTRAINT.
• UNIQUE — from UK / <<UNIQUE>> marker. Adds UNIQUE inline.
• NOT NULL — Non-PK, non-nullable columns get NOT NULL.
• INDEX — Indexed columns generate CREATE INDEX "idx_{table}_{col}" ON "table"("col").

Default Values

Default values are emitted with automatic quoting: numeric values are unquoted, string values are single-quoted.

-- Numeric default:    DEFAULT 0
-- String default:     DEFAULT 'active'
-- Boolean default:    DEFAULT true  (postgres) / DEFAULT 1 (mysql/sqlite/oracle)

Schema Normalization

Before generating SQL, the schema goes through normalization:

• Synthetic PK: Tables without any PK column automatically get an id int PK column added. A warning is emitted.
• Duplicate columns: If a table has duplicate column names, only the first is kept. A warning is emitted.
• Warning severity: Normalization issues produce warnings (not errors), so SQL is still generated.

Full Example: E-Commerce Schema

erDiagram
    CUSTOMER {
        int id PK
        string name
        string email UK
        timestamp created_at
    }
    PRODUCT {
        int id PK
        string title
        decimal price
        text description
        boolean in_stock
    }
    ORDER {
        int id PK
        int customer_id FK
        timestamp ordered_at
        decimal total
    }
    ORDER_ITEM {
        int id PK
        int order_id FK
        int product_id FK
        int quantity
        decimal unit_price
    }
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ ORDER_ITEM : contains
    PRODUCT ||--o{ ORDER_ITEM : "listed in"

CREATE TABLE `customer` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255),
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `created_at` DATETIME
);

CREATE TABLE `product` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255),
    `price` DECIMAL,
    `description` TEXT,
    `in_stock` TINYINT(1)
);

CREATE TABLE `order` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `customer_id` INT,
    `ordered_at` DATETIME,
    `total` DECIMAL
);

CREATE TABLE `order_item` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `order_id` INT,
    `product_id` INT,
    `quantity` INT,
    `unit_price` DECIMAL
);

ALTER TABLE `order`
    ADD CONSTRAINT `fk_order_customer_id`
    FOREIGN KEY (`customer_id`)
    REFERENCES `customer`(`id`);

ALTER TABLE `order_item`
    ADD CONSTRAINT `fk_order_item_order_id`
    FOREIGN KEY (`order_id`)
    REFERENCES `order`(`id`);

ALTER TABLE `order_item`
    ADD CONSTRAINT `fk_order_item_product_id`
    FOREIGN KEY (`product_id`)
    REFERENCES `product`(`id`);

Full Example: Blog Platform

@startuml

entity Author {
  * id : int <<PK>>
  name : string
  email : string <<UNIQUE>>
  bio : text
}

entity Post {
  * id : int <<PK>>
  author_id : int <<FK>>
  title : string
  body : text
  published : boolean
  created_at : timestamp
}

entity Tag {
  * id : int <<PK>>
  name : string <<UNIQUE>>
}

Author ||--o{ Post : writes

@enduml

CREATE TABLE "author" (
    "id" NUMBER GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    "name" VARCHAR2(255),
    "email" VARCHAR2(255) NOT NULL UNIQUE,
    "bio" CLOB
);

CREATE TABLE "post" (
    "id" NUMBER GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    "author_id" NUMBER,
    "title" VARCHAR2(255),
    "body" CLOB,
    "published" NUMBER(1),
    "created_at" TIMESTAMP
);

CREATE TABLE "tag" (
    "id" NUMBER GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    "name" VARCHAR2(255) NOT NULL UNIQUE
);

ALTER TABLE "post"
    ADD CONSTRAINT "fk_post_author_id"
    FOREIGN KEY ("author_id")
    REFERENCES "author"("id");

Same Diagram → All 4 Dialects

Here's how the same simple diagram renders across all supported databases:

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

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

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

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

CREATE TABLE `user` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255),
    `email` VARCHAR(255)
);

CREATE TABLE `order` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT,
    `total` DECIMAL
);

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

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

CREATE TABLE "order" (
    "id" INTEGER PRIMARY KEY,
    "user_id" INTEGER,
    "total" REAL
);

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

CREATE TABLE "user" (
    "id" NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    "name" VARCHAR2(255),
    "email" VARCHAR2(255)
);

CREATE TABLE "order" (
    "id" NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    "user_id" NUMBER,
    "total" NUMBER
);

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