Generate an ER Diagram from SQL Schema with AI
Your database schema already contains everything an entity-relationship diagram needs: tables are entities, columns are attributes, and foreign keys are relationships. The only missing step is translation — turning a wall of CREATE TABLE statements into a picture someone can read in ten seconds. This guide covers exactly how that translation works: how cardinality is expressed in crow's foot notation, how foreign key constraints become relationship lines, why junction tables deserve special handling, and how AI tools now do the whole conversion from pasted SQL. We will walk through a complete five-table blog schema and compare the AI approach against MySQL Workbench and pgAdmin reverse-engineering.
Why generate an ER diagram from SQL instead of drawing it by hand?
A schema with fifteen tables and thirty foreign keys is trivial to read as SQL if you wrote it, and nearly impossible if you didn't. New engineers joining a project, reviewers evaluating a migration, and architects auditing data models all need the visual form: boxes for entities, lines for relationships, symbols for cardinality. Drawing that by hand in draw.io or Lucidchart means manually transcribing every table name, every column, and every constraint — an hour of error-prone copying for information that already exists in machine-readable form.
Hand-drawn ERDs also rot. The schema changes with every migration, but nobody goes back to update the diagram, so six months later the wiki shows a users table with columns that were dropped in March. Generating the diagram from the actual DDL — whether via a database tool's reverse-engineering feature or an AI generator — means the picture reflects reality at the moment you generated it, and regenerating after a migration takes seconds instead of another manual pass.
The input can come from anywhere you can get DDL. Run pg_dump --schema-only on Postgres, mysqldump --no-data on MySQL, or .schema in SQLite, and you have a complete, canonical description of your database ready to paste into a generator.
How do foreign keys become relationships in an ERD?
Every FOREIGN KEY constraint in your DDL is a relationship in disguise. When posts has user_id REFERENCES users(id), that single line of SQL encodes three facts: there is a relationship between posts and users, the relationship runs from many posts to one user, and the posts side is the child. An ERD generator reads the constraint and draws a line between the two entity boxes with cardinality markers at each end.
Crow's foot notation is the standard way to mark those ends, and it is worth learning to read precisely. The three-pronged 'crow's foot' symbol means many; a single perpendicular bar means one; a circle means zero (optional). Symbols combine: a bar plus crow's foot reads 'one or many,' a circle plus crow's foot reads 'zero or many,' and two bars read 'exactly one.' So the posts-to-users line gets a crow's foot with a circle on the posts side (a user may have zero or many posts) and two bars on the users side (every post belongs to exactly one user).
Nullability determines optionality. If posts.user_id is NOT NULL, the users end of the line is mandatory — bar plus bar. If the column is nullable, a post can exist without an author, and the notation becomes circle plus bar, meaning zero or one. This is a detail hand-drawn diagrams routinely get wrong and generated ones get right, because the answer is sitting in the column definition.
How should junction tables appear in the diagram?
Relational databases cannot express many-to-many relationships directly, so schemas use junction tables (also called join, bridge, or associative tables): a table whose primary key is a composite of two foreign keys, like post_tags(post_id, tag_id). Conceptually this is one many-to-many relationship between posts and tags; physically it is two one-to-many relationships meeting in the middle.
You have a notation decision to make. A physical ERD shows the junction table as its own entity box with two crow's-foot lines, one to each parent — accurate to the database, and necessary when the junction table carries extra columns such as created_at or a sort order. A conceptual ERD collapses the junction table into a single line with crow's feet on both ends, which is cleaner for stakeholder-facing diagrams but hides the implementation.
Most reverse-engineering tools and AI generators default to the physical view, and for engineering documentation that is the right call. A pure junction table is easy to recognize on sight — two foreign keys, a composite primary key, few or no other columns — so the many-to-many reading stays obvious even in physical form. If you want the collapsed conceptual version, say so explicitly when prompting an AI generator; it is a one-sentence instruction rather than a redraw.
Worked example: a 5-table blog schema and its ERD
Here is a compact Postgres blog schema. CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, name VARCHAR(100)); CREATE TABLE posts (id SERIAL PRIMARY KEY, user_id INT NOT NULL REFERENCES users(id), title VARCHAR(200) NOT NULL, body TEXT, published_at TIMESTAMP); CREATE TABLE comments (id SERIAL PRIMARY KEY, post_id INT NOT NULL REFERENCES posts(id), user_id INT NOT NULL REFERENCES users(id), body TEXT NOT NULL); CREATE TABLE tags (id SERIAL PRIMARY KEY, name VARCHAR(50) UNIQUE NOT NULL); CREATE TABLE post_tags (post_id INT REFERENCES posts(id), tag_id INT REFERENCES tags(id), PRIMARY KEY (post_id, tag_id));
The resulting ERD has five entity boxes. users connects to posts with a one-to-many line: exactly-one on the users end (user_id is NOT NULL), zero-or-many crow's foot on the posts end. comments has two parents — a one-to-many line from posts and a second one-to-many line from users — which is your signal that a comment always has both an author and a home post. tags stands alone except for its link through the junction.
post_tags is the textbook junction table: composite primary key, two foreign keys, no other columns. Drawn physically, it sits between posts and tags with a crow's foot pointing at itself from each side. Read conceptually, it means one post carries zero or many tags and one tag appears on zero or many posts — a pure many-to-many. Anyone who can read crow's foot notation reconstructs the entire domain model from this one picture: users write posts, users comment on posts, posts are tagged.
MySQL Workbench and pgAdmin vs an AI generator: which should you use?
Traditional reverse-engineering is built into the major database GUIs. MySQL Workbench's Database > Reverse Engineer wizard connects to a live server or reads a dump file and produces an EER diagram with proper crow's foot notation; pgAdmin's ERD Tool does the same for Postgres schemas. These tools are accurate by construction — they read the catalog directly — and they are the right choice when you need a guaranteed-complete diagram of a large production database you have credentials for.
Their friction shows up elsewhere. Both require a live connection or an import step, both produce diagrams locked inside their own tooling (Workbench's .mwb format does not open in anything else), and both give you limited control over layout and styling. Auto-layout on a 40-table schema often produces a spiderweb you spend an hour untangling, and exporting to a format your team can actually edit — rather than a static PNG — is awkward. Mermaid's erDiagram syntax is a solid text-first alternative if you want diagrams-as-code in your repo, though you write the entity definitions yourself and layout control is minimal. Lucidchart can import SQL DDL directly on paid plans and produces polished output, at SaaS-subscription cost.
The AI approach trades catalog-level guarantees for flexibility. You paste DDL — from a dump, a migration file, or a schema.rb — and the model parses the CREATE TABLE statements, infers relationships from the foreign keys, and emits an editable diagram. You can also give instructions no wizard accepts: show only these six tables, collapse the junction tables, group the auth entities together, add a note explaining the soft-delete column. The honest caveat: an AI generator is only as complete as the DDL you paste, so verify the output against your schema — a missed constraint in the input means a missing line in the diagram.
How do you generate an ER diagram from SQL with AI?
With AIDrawIO, the workflow is paste-and-describe. Open the free ERD generator at aidrawio.com/en/tools/erd-generator, paste your CREATE TABLE statements, and add one sentence of intent, for example: 'Generate a crow's foot ER diagram from this schema. Show all columns with types, mark primary and foreign keys, and use one-to-many notation based on the foreign key constraints.' The AI parses the DDL and returns a draw.io-compatible diagram — real draw.io XML, not a static image — so every entity box and relationship line remains editable in AIDrawIO or in diagrams.net.
That editability matters for ERDs specifically, because the generated layout is a starting point. Drag the users entity to the top-left, straighten the line through post_tags, delete the audit tables you do not need in this view — then export as draw.io XML for your repo, SVG for the wiki, or PNG for a slide. Version history keeps prior generations available, which is useful when you regenerate after a migration and want to compare against the previous schema's diagram.
The free tier gives you 5 generations per hour with no account required, running on Gemini 3 Flash; subscribers get Claude Opus 4.8, Claude Sonnet 5, and Gemini 3.1 Pro, which handle larger schemas and more specific layout instructions more reliably. For the five-table blog schema above, the full round trip — paste DDL, generate, nudge two boxes, export SVG — takes under two minutes. Try it against your own schema dump and compare the result to what Workbench gives you.
無料で試す ERD generator
AI entity-relationship diagrams. Describe in plain English, get draw.io XML in seconds. No account required.
よくある質問
Can I generate an ER diagram directly from SQL CREATE TABLE statements?
Yes. Foreign key constraints in DDL contain everything needed to draw relationships, so tools can parse CREATE TABLE statements and emit a complete ERD. Paste the output of pg_dump --schema-only or mysqldump --no-data into an AI generator like aidrawio.com/en/tools/erd-generator, or use the built-in reverse-engineering in MySQL Workbench or pgAdmin.
How do foreign keys map to relationships in an ER diagram?
Each FOREIGN KEY constraint becomes a relationship line between the child and parent entities. The child side gets a crow's foot (many) and the parent side gets a bar (one); if the foreign key column is nullable, the parent end also gets a circle, meaning zero or one.
How is a many-to-many relationship shown in an ERD?
Physically, it appears as a junction table (like post_tags) with one-to-many lines to each parent table. Conceptually, you can collapse the junction into a single line with crow's feet on both ends. Keep the physical form when the junction table has extra columns of its own.
What does crow's foot notation mean in database diagrams?
Crow's foot notation marks cardinality at each end of a relationship line: a three-pronged fork means many, a bar means one, and a circle means zero or optional. Combined symbols read as ranges — circle plus crow's foot is zero-or-many, bar plus bar is exactly one.
Is an AI ERD generator better than MySQL Workbench reverse-engineering?
Different trade-offs. Workbench and pgAdmin read the database catalog directly, so they are guaranteed complete but require a live connection and lock output in their own formats. An AI generator works from pasted DDL anywhere, accepts layout and filtering instructions in plain English, and outputs editable draw.io XML — but you should verify the result matches your schema.