SQL DDL to ER Diagram: Complete Guide to Visualizing Database Schemas

Entity-relationship diagrams are one of the most effective ways to understand a database schema at a glance. This guide covers how to turn raw SQL CREATE TABLE statements into a visual ER diagram — what gets parsed, how relationships are detected, what the notation means, and how to export diagrams for documentation.

What Is DDL?

DDL — Data Definition Language — is the subset of SQL responsible for defining and modifying database structure. The most common DDL statements are CREATE TABLE, ALTER TABLE, and DROP TABLE. When you export a database schema from SQL Server Management Studio, pgAdmin, MySQL Workbench, or any other database client, the output is DDL.

DDL contains everything you need to reconstruct the structure of a database: table names, column names and data types, nullability constraints, primary keys, foreign keys, unique constraints, defaults, and indexes. For the purpose of ER diagram generation, the most important elements are the table definitions, primary keys, and foreign key references.

What Is an ER Diagram?

An entity-relationship diagram (ERD) is a visual representation of a database schema. Each table is drawn as a box containing its columns, and lines between boxes represent foreign key relationships. ER diagrams are used during database design, code reviews, onboarding new team members, and documentation.

Logical vs. Physical ER Diagrams

A logical ER diagram shows abstract entities and their relationships without data types or implementation details. A physical ER diagram maps directly to the database — it includes column types, constraints, and indexes. The DDL to ER Diagram tool generates physical ER diagrams because it works from actual DDL statements that contain all of this detail.

How DDL Parsing Works

The tool uses a DDL-focused parser that extracts CREATE TABLE blocks from the input SQL. It does not attempt to parse the full SQL language — it targets the specific patterns that define tables, columns, and constraints.

What Gets Parsed

• Table names — including schema-qualified names like dbo.Orders, "sales"."invoice", or [HumanResources].[Employee]
• Column definitions — name, data type (including precision like VARCHAR(255)), and NOT NULL constraints
• Primary key declarations — both inline (id INT PRIMARY KEY) and table-level (PRIMARY KEY (col1, col2))
• Foreign key declarations — FOREIGN KEY (col) REFERENCES OtherTable(col) with optional CONSTRAINT names
• SQL comments — both -- line comments and /* block comments */ are stripped before parsing

What Gets Ignored

The parser intentionally skips statements that do not contribute to the ER diagram: ALTER TABLE, CREATE INDEX, INSERT INTO, stored procedures, views, triggers, and any non-DDL SQL. This keeps the parser simple and tolerant of mixed-content SQL scripts.

Primary Keys and Foreign Keys

Primary Key Detection

Primary keys are detected from two patterns. The inline form places PRIMARY KEY immediately after the column type definition. The table-level form uses a separate PRIMARY KEY (col1, col2) constraint, which also supports composite primary keys spanning multiple columns. Both forms can appear in the same table — the parser merges them.

-- Inline PK
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100)
);

-- Table-level PK (composite)
CREATE TABLE OrderItems (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

Foreign Key Detection

Foreign keys establish the relationships between tables. The parser supports the standard FOREIGN KEY ... REFERENCES syntax, with optional CONSTRAINT naming. Multi-column (composite) foreign keys are fully supported.

-- Named constraint
CONSTRAINT FK_Orders_Customers
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

-- Anonymous FK
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)

-- Composite FK
FOREIGN KEY (OrderID, ProductID)
    REFERENCES OrderItems(OrderID, ProductID)

Relationship Types

One-to-Many

The most common relationship. A foreign key in the child table references the primary key of the parent table. In the diagram, this is shown with a crow's foot on the "many" side (the child table) and a single line on the "one" side (the parent).

Many-to-Many

Represented by a join table (also called a junction or bridge table) that has a composite primary key of exactly two columns, where both columns are foreign keys pointing to two different tables. The tool detects this pattern automatically and adds a note to the warnings panel identifying the join table.

Self-Referencing

A table with a foreign key that references itself — the classic example is an Employees table where ManagerID references EmployeeID in the same table. The diagram draws a curved loop from the table back to itself.

Mermaid ER Syntax

The tool generates Mermaid erDiagram syntax alongside the visual diagram. Mermaid is a text-based diagramming language that renders in GitHub Markdown, Notion, GitLab wikis, Obsidian, and many other platforms.

erDiagram
  Customers {
    INT CustomerID PK
    VARCHAR Name
  }
  Orders {
    INT OrderID PK
    INT CustomerID FK
    DATE OrderDate
  }

  Customers ||--|{ Orders : "CustomerID"

The relationship notation uses symbols: || for "exactly one", |{ for "one or more", and o{ for "zero or more" (optional). The tool selects the notation based on whether the FK column is nullable.

Export Formats

Limitations and Edge Cases

• ALTER TABLE ADD CONSTRAINT — foreign keys added via ALTER TABLE after the initial CREATE TABLE are not detected. Include them as table-level constraints inside the CREATE TABLE block instead.
• Dialect-specific syntax — features like MySQL's ENGINE=InnoDB or PostgreSQL's SERIAL type are tolerated but not specially handled. The core column definition and constraints still parse correctly.
• Very large schemas — schemas with more than ~150 tables will render but may become difficult to navigate. Use the zoom and drag features, or filter to a subset of related tables.
• Implicit relationships — columns that follow naming conventions (e.g., CustomerID matching Customers.CustomerID) but lack a formal FOREIGN KEY declaration are not detected. Only explicit FK constraints create relationship lines.

Frequently Asked Questions

What is DDL and why visualize it?

DDL (Data Definition Language) is the subset of SQL that defines database structure. Visualizing DDL as an ER diagram makes it easier to understand table relationships, audit foreign keys, and communicate schema design to team members who may not read SQL fluently.

What is the difference between a logical and physical ER diagram?

A logical ER diagram shows entities and relationships without implementation details. A physical ER diagram includes column data types, constraints, and indexes — it maps directly to the DDL. This tool generates physical ER diagrams from actual DDL statements.

How does the tool handle composite foreign keys?

The parser detects multi-column FOREIGN KEY constraints such as FOREIGN KEY (col1, col2) REFERENCES OtherTable(col1, col2). Both columns are marked as FK in the diagram, and the relationship line connects the two tables.

Can I use Mermaid ER syntax in GitHub README files?

Yes. GitHub natively renders Mermaid diagrams in Markdown. Wrap the generated syntax in a code fence with the mermaid language identifier and it will render as a diagram when viewed on GitHub.