How to Create an ER Diagram From SQL (No Redrawing)

Knowing how to create an ER diagram from SQL saves you from the most cursed task in data engineering: redrawing 40 tables by hand from a schema that already exists. The short answer: export the schema as DDL, feed the CREATE TABLE statements to a tool that parses them, then check that every foreign key made it onto the diagram.

TL;DR: pg_dump --schema-only or mysqldump --no-data, paste the DDL into a parser, read the crow's feet, and verify the relationship count against the database catalog. Ten minutes, not ten hours.

Here's the thing nobody says out loud: the ER diagram in your wiki is probably from 2023, two migrations and one "temporary" table ago. The SQL is the source of truth. So stop drawing and start generating.

How to create an ER diagram from SQL: generate it, don't draw it

A hand-drawn ER diagram is an opinion about the schema. A generated one is a fact about it. That difference matters the moment you use the diagram for something real:

• Migration planning. You need the full dependency graph to know which tables move together.
• Onboarding. New engineers trust the diagram. If it lies, they learn the wrong model.
• Design reviews. Reviewers comment on what they see. If a foreign key is missing from the picture, nobody asks about its ON DELETE behaviour.
• Audits and docs. "Where does customer data live?" is an ER question.

The catch: a generated diagram can only show what the schema declares. If your app enforces relationships in code and never declared a foreign key, the database doesn't know about it, and neither will the diagram. We'll deal with that in the troubleshooting section.

If you want the bigger picture of rebuilding architecture docs from what's actually deployed, the reverse engineering playbook for code repos covers the same idea one level up.

→ Paste your DDL into the free SQL DDL to ER Diagram tool and see the real schema

Step 1: Export the schema as SQL DDL (no data)

You want table definitions only. No rows, no customer data, nothing you'd regret pasting into a browser tab.

PostgreSQL. The pg_dump docs describe -s / --schema-only as "Dump only the object definitions (schema), not data". Add -O (--no-owner) to drop the ownership statements, which are noise for a diagram:

# Whole database, definitions only
pg_dump --schema-only --no-owner -d shop > schema.sql

# One Postgres schema (namespace) only
pg_dump --schema-only --no-owner -n billing -d shop > billing.sql

# Just a few tables
pg_dump --schema-only --no-owner -t orders -t order_items -d shop > orders.sql

Heads-up: --schema-only and --schema (-n) are different things. The first means "definitions, no data", the second means "only this Postgres schema namespace". The docs call this out too.

MySQL. Per the MySQL 8.4 manual, --no-data makes the dump contain only the statements that create the tables:

mysqldump --no-data shop > schema.sql

Got migrations instead of a live database? Run them against a throwaway local database, then dump that. It's the cleanest way to get the schema as it actually ends up, not as the first migration imagined it.

→ Planning a schema change next? Diff two DDL versions with the free Schema Diff Checker

Step 2: Convert SQL to an ER diagram with a parser

Now the fun part. Here's a small but realistic PostgreSQL schema you can use to test any tool. It covers the three relationship shapes you'll see everywhere: one-to-many, many-to-many through a join table, and one-to-one.

CREATE TABLE customers (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text NOT NULL UNIQUE,
  name        text NOT NULL,
  price_cents integer NOT NULL CHECK (price_cents >= 0)
);

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),
  status      text NOT NULL DEFAULT 'pending',
  placed_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id   bigint NOT NULL,
  product_id bigint NOT NULL,
  quantity   integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT
);

CREATE TABLE shipments (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   bigint NOT NULL UNIQUE REFERENCES orders (id),
  carrier    text,
  shipped_at timestamptz
);

Paste that into the SQL DDL to ER Diagram tool and you should get five tables and four relationships: orders → customers, order_items → orders, order_items → products and shipments → orders. The tool parses the DDL in your browser, reports table and relationship counts, and gives you a Mermaid ER diagram you can paste into docs or tickets.

Notice both foreign key styles in that script. PostgreSQL's constraints documentation shows the column-level form (customer_id bigint REFERENCES customers (id)) and the table-level form (FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)), which you need for multi-column keys. A decent parser should handle both.

When the schema outgrows a quick check, move it into DB Visualizer. It gives you an editable, interactive schema canvas, imports and exports across SQL, DBML and JSON, exports to PDF and video, and has AI chat for reasoning about schema structure and documentation. If you'd rather sketch a brand-new schema next to your other diagrams, Flow IQ includes a dedicated database schema designer alongside its UML, sequence and ETL designers.

→ Open the schema in DB Visualizer and explore every table interactively

Step 3: Read the crow's foot notation (and get cardinality right)

Most ER diagram tools draw relationships in crow's foot notation. Learn four symbols and you can read any of them.

Apply it to the sample schema:

• customers → orders: one-to-many. The crow's foot sits on orders, because one customer has zero or many orders. customer_id is NOT NULL, so every order has exactly one customer.
• orders ↔ products: many-to-many. You never draw this directly. order_items is the join table, with two one-to-many relationships and a composite primary key.
• orders → shipments: one-to-one. The UNIQUE on shipments.order_id is what makes it one-to-one. Without it, an order could have many shipments.

That last one is the classic gotcha. Parsers see a foreign key and often default to many-to-one, because detecting a one-to-one means also noticing the UNIQUE constraint on the same column. Always eyeball one-to-one relationships and fix the cardinality by hand if needed.

Two more things worth surfacing on the diagram, because reviewers care:

• Delete behaviour. ON DELETE CASCADE on order_items.order_id means deleting an order deletes its items. RESTRICT on product_id means you can't delete a product that's been ordered. PostgreSQL's default is NO ACTION, per the constraints docs.
• Missing indexes. The same docs say declaring a foreign key "does not automatically create an index on the referencing columns". If your diagram shows a hot FK with no index, that's a review comment waiting to happen.

Want to go deeper on keys? The foreign key design explainer walks through the trade-offs, and the normalization explainer helps when the diagram reveals a table doing three jobs.

→ Brush up on FK trade-offs with the foreign key design explainer

Step 4: Verify the ER diagram against the database catalog

A diagram that's 90% right is dangerously convincing. Do a 60-second sanity check: count the declared foreign keys in the database and compare with the relationship count on the diagram.

PostgreSQL (catalog query):

SELECT conrelid::regclass AS referencing_table,
       conname            AS constraint_name,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE contype = 'f'
ORDER BY 1, 2;

You can also use information_schema.referential_constraints. Its documentation notes it only shows foreign keys where the current role owns the referencing table or has a privilege other than SELECT on it, so a read-only user may see fewer rows than actually exist. Run the check as a role with the right access.

MySQL:

SELECT table_name, column_name, constraint_name,
       referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'shop'
  AND referenced_table_name IS NOT NULL
ORDER BY table_name;

Same number of rows as relationship lines? Ship it. Different? Read the next section.

→ Turn the verified schema into a living design doc with HyperDocs

Troubleshooting: why your ER diagram from SQL schema is missing relationships

This is where most "the tool is broken" moments come from. It's usually the input.

1. The foreign keys live in ALTER TABLE statements. Many dump tools create all tables first and add foreign keys afterwards with ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY, so the load order never breaks. Some parsers only read CREATE TABLE bodies. Our free DDL tool is one of them, so if it reports zero relationships on a dump that definitely has foreign keys, move the constraints into the CREATE TABLE statements (or trim the dump down to the tables you care about first).

2. The foreign keys were never declared. Common in older apps, some ORMs' default setups, and schemas that dropped FKs for "performance". The database can't tell anyone about a relationship it doesn't know. Your options: add the constraints (best, if the data is clean), or document the implied relationships by hand and label them as "not enforced" on the diagram.

3. Dialect quirks. Quoted identifiers, schema-qualified names, PARTITION BY, generated columns and vendor-specific types trip up some parsers. If one table is missing, isolate its CREATE TABLE and try it alone.

4. Polymorphic associations. A commentable_type + commentable_id pair can't be a real foreign key, so no tool will draw it. Annotate it manually.

5. Too many tables to read. If the diagram looks like a plate of spaghetti, split it by Postgres schema (pg_dump -n) or by domain. One diagram per bounded context beats one diagram nobody can read.

→ Re-run the cleaned-up DDL through the free SQL DDL to ER Diagram tool

Reverse engineer database to ER diagram: tool options compared

You've got choices, and the right one depends on whether you need a quick look, a long-lived model, or docs.

If you're weighing dedicated schema tools, the Architecto vs dbdiagram comparison goes through the workflow differences honestly. And if your team designs in DBML, the free DBML to SQL converter gets you back to runnable DDL.

→ See how Architecto stacks up in the dbdiagram comparison

Key takeaways

• Generate, don't draw. The SQL schema is the source of truth; the ER diagram should be derived from it.
• Export definitions only: pg_dump --schema-only --no-owner or mysqldump --no-data.
• Learn four crow's foot symbols and check one-to-one relationships by hand, since UNIQUE foreign keys are easy to misread.
• Verify the count. Compare relationship lines with the foreign keys in the catalog before you trust the diagram.
• Missing lines usually mean ALTER TABLE constraints, undeclared FKs, or dialect quirks, not a broken tool.
• Index your foreign keys. PostgreSQL won't do it for you.

→ Keep your schema docs honest with the database documentation template

FAQ

How do I create an ER diagram from an existing SQL database? Export the schema without data (pg_dump --schema-only for PostgreSQL, mysqldump --no-data for MySQL), then paste or import the CREATE TABLE statements into an ER diagram tool that parses DDL. The tool turns tables into entities and FOREIGN KEY or REFERENCES clauses into relationship lines. Finally, compare the relationship count on the diagram with the foreign keys reported by the database catalog, so nothing went missing.

Why are relationships missing from my ER diagram? Most often the foreign keys were never declared, so there is nothing for a parser to read, or the dump defines them in separate ALTER TABLE ... ADD CONSTRAINT statements that the tool does not parse. Query the catalog for declared foreign keys, and if the count does not match the diagram, move the constraints into the CREATE TABLE statements or use a tool that reads ALTER TABLE.

How do you show foreign keys in an ER diagram? Draw a line from the referencing table (the one holding the foreign key column) to the referenced table, and mark the column with FK. In crow's foot notation the crow's foot sits on the "many" side, and a single bar or circle on the other end shows whether the relationship is mandatory or optional, which maps to whether the foreign key column is NOT NULL.

What is crow's foot notation in an ER diagram? Crow's foot notation uses symbols at each end of a relationship line to show cardinality: a three-pronged crow's foot means many, a single bar means one, and a circle means zero or optional. Combined, they read as phrases like "one and only one" or "zero or many", which is how most database ER diagram tools display relationships.

→ Ask your next schema question inside DB Visualizer's AI chat

Stop redrawing, start generating

Your schema already knows its own shape. Export the DDL, generate the diagram, verify the foreign keys, and put the result where reviewers will actually see it. Then do it again after the next migration, because it takes minutes, not an afternoon.

Start with the free tool for a quick look. When the schema needs to live in reviews, migration plans and docs, bring it into DB Visualizer and keep it next to the rest of your architecture.

→ Start free on Architecto and turn your SQL into a living ER diagram