How to Reverse-Engineer a Database into a Diagram
You have inherited a database with a hundred tables and no documentation. Reverse-engineering it into a diagram is how you turn an opaque schema into a map you can actually navigate.
Reverse-engineering a database means recovering its structure from the database itself and turning it into a diagram - the opposite of designing a schema on paper first. It is one of the most common real-world tasks in data work, because engineers inherit databases far more often than they build new ones. A production system with a hundred undocumented tables is nearly impossible to hold in your head, but as an entity relationship diagram it becomes a map you can read, navigate, and reason about.
This guide covers the whole process: extracting the schema from a live database, mapping its tables and foreign keys onto entities and relationships, recovering the connections that constraints do not spell out, and laying the result out so it is genuinely readable. Build and refine the diagram in Atlas Diagram Studio at /diagrams with the ERD tool at /diagram-tools/erd-tool. Because the diagram comes from the real schema, it is accurate by construction - the same anti-drift property described in the guide on generating diagrams from code at /guides/how-to-generate-diagrams-from-code.
Extract the schema first
Reverse-engineering starts with getting the schema out of the database in a form you can read. Every relational database can produce its data definition language - the CREATE TABLE statements that define every table, column, primary key, and foreign key - through a schema dump or by querying the system catalog that describes the database's own structure. This DDL is the raw material for the diagram, because it contains, in principle, everything about the schema's shape. Getting a clean, complete dump is the foundation everything else builds on.
With the DDL in hand, the translation to a diagram is mechanical, exactly as covered in the guide on generating an ERD from SQL. Each CREATE TABLE becomes an entity, each column an attribute, each PRIMARY KEY a key marker, and each FOREIGN KEY a relationship line with cardinality read from whether the foreign key column is nullable and unique. For a database managed by an ORM, the schema definition - a Prisma schema, for instance - is an even cleaner source than raw DDL, since it already groups the model coherently.
Recover the relationships constraints do not show
The catch in reverse-engineering is that not every relationship is declared as a foreign key. Many real databases, especially older ones or those built for raw performance, enforce relationships only in application code, leaving columns that clearly reference another table with no constraint to prove it. A column named customer_id that holds values matching customer.id is obviously a foreign key in intent, but a schema dump will not mark it as one, so a purely mechanical translation misses the relationship entirely.
Recovering these implicit relationships is where human judgment earns its keep. Naming conventions are the strongest clue - a column called something_id almost always references the id of a something table - and matching data values confirms the guess. Add these inferred relationships to the diagram, but mark them as inferred rather than declared, because the distinction matters: a declared foreign key is enforced by the database, while an inferred one is only a convention the application is trusted to honor. A reverse-engineered diagram that shows both, clearly distinguished, is far more truthful than one that shows only the declared constraints.
Make a huge schema navigable
A raw diagram of a hundred tables is technically complete and practically useless. The work that makes reverse-engineering pay off is turning that wall of boxes into something navigable.
- Identify the core entities first - the heavily referenced tables like Customer, Order, Product - and build the diagram outward from them.
- Group tables into subject areas by domain, with a labeled region and consistent color for each.
- Make a high-level overview showing only the core entities and their relationships, hiding most attributes.
- Make separate detailed diagrams per subject area, each showing full columns and keys for that domain.
- Set aside pure lookup and configuration tables, which clutter the main view without adding to the story.
- Name relationships in business language as you understand them, turning foreign keys into meaningful verbs.
- Mark inferred relationships distinctly from declared foreign keys so readers know which the database enforces.
Verify and keep it alive
A reverse-engineered diagram is a hypothesis about the database until you verify it. Check your inferred relationships against actual data, confirm that the tables you treated as central really are, and validate your understanding with anyone who knows the system's history. The act of building the diagram is itself the best way to discover what you do not understand, because every box and line you cannot explain is a question worth asking. Expect the first pass to surface gaps in your knowledge, which is exactly its value.
Once verified, keep the diagram alive by regenerating its structure from the schema whenever the database changes, and reapplying your layout and grouping on top. Because the base comes from the real schema, regeneration keeps it accurate, while your curation keeps it readable - the division of labor that makes generated diagrams sustainable. Store it as living documentation the whole team can reach, and pair it with the guide on documenting a database schema for the layout craft, and the database design guide at /guides/database-design for judging the design you have uncovered.