How to Create an ERD from SQL

Often you do not need to design a schema from scratch; you need to understand one that already exists. The SQL that created a database, its data definition language or DDL, is a complete and precise description of its structure, and an ERD is simply that structure made visual. Turning SQL into an ERD is one of the most useful reverse-engineering skills a developer can have, because a diagram reveals the shape of a database far faster than reading dozens of CREATE TABLE statements one at a time.

This guide shows how to read DDL and map each construct to its diagram equivalent, then how to let a tool do the tedious parts. The mapping is remarkably direct once you see it: tables become entities, columns become attributes, and foreign keys become relationship lines. You can build the resulting diagram in the editor at /diagrams or the ERD tooling at /diagram-tools/erd-tool, and generate a first pass from a description at /diagram-tools/ai-diagram-generator.

Reading the CREATE TABLE statements

Start with the tables, because each one becomes an entity in your diagram. A CREATE TABLE statement names the table and lists its columns with data types, and that maps one to one onto an entity box with its attributes. Consider this DDL: `CREATE TABLE customer (id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, name VARCHAR(120), created_at TIMESTAMP);` This becomes a Customer entity with four attributes, and the PRIMARY KEY marker on id tells you which attribute is the identifier.

As you read each table, note three things about every column: its name and type (which become the attribute), whether it is part of the PRIMARY KEY (mark it as the identifier), and whether it has constraints like NOT NULL or UNIQUE (which you can annotate). These constraints carry real meaning that belongs on the diagram. A UNIQUE email tells a reader that email is a natural key even though id is the primary key, and NOT NULL columns indicate mandatory data. Capturing them keeps the ERD faithful to the database rather than a lossy sketch of it.

Foreign keys become relationships

The relationships between tables are hiding in the foreign key constraints, and finding them is where a pile of tables turns into a connected model. A foreign key declaration like `FOREIGN KEY (customer_id) REFERENCES customer(id)` on an order table tells you that order relates to customer: each order references exactly one customer, and by implication each customer can have many orders. That is a one-to-many relationship, drawn as a line from Order to Customer with a crow's foot on the Order end.

Reading foreign keys correctly gives you the cardinality almost for free. A plain foreign key column implies one-to-many from the referencing table (many) to the referenced table (one). If the foreign key also carries a UNIQUE constraint, the relationship is one-to-one instead. And when you find a table whose entire purpose is to hold two foreign keys, with their combination as the primary key, you have found a junction table implementing a many-to-many relationship, which you can draw as a direct many-to-many line between the two tables it links. Every relationship in the diagram traces back to a foreign key in the DDL.

Spotting junction tables and inferring cardinality

Junction tables deserve special attention because how you draw them is a choice. A table like `CREATE TABLE post_tag (post_id INT REFERENCES post(id), tag_id INT REFERENCES tag(id), PRIMARY KEY (post_id, tag_id));` is a pure junction: two foreign keys forming a composite primary key and nothing else. You can either show it explicitly as its own entity with two one-to-many relationships, or collapse it into a single many-to-many line between Post and Tag. The explicit form is more literal; the collapsed form is cleaner for a high-level overview.

The choice depends on your audience. For a physical, implementation-accurate diagram, show every table including junctions, because that is what the database really contains. For a conceptual overview aimed at understanding the domain, collapsing pure junction tables into many-to-many lines reduces clutter and makes the real relationships pop. If a junction table carries extra attributes of its own, always show it explicitly, because those attributes have nowhere to go on a collapsed line.

Automating the conversion

Doing this by hand is educational for a small schema and tedious for a large one. Fortunately the mapping from SQL to ERD is mechanical enough that tools do it reliably, and using one for a big database is the sensible default.

• Many database tools and IDEs can reverse-engineer an ERD directly by connecting to a live database and reading its catalog.
• Schema-as-code tools can import SQL DDL and render the diagram automatically, preserving keys and relationships.
• In Atlas Diagram Studio you can import from formats like Mermaid or draw.io, or describe the schema to the AI generator at /diagram-tools/ai-diagram-generator for a first draft.
• After auto-generating, manually adjust the layout so related tables sit near each other; automatic placement is rarely ideal.
• Verify the tool captured optionality (nullable versus NOT NULL foreign keys); some tools default every relationship to mandatory.
• Keep the generated ERD in version control alongside the schema so it stays current as migrations change the database.