Creating an ER Diagram From an Existing Database
Create an ER diagram from an existing database: connect over JDBC, read the catalog, add undeclared foreign keys, check rows and license it for a team.
On this page
DbSchema saves you from drawing the ER diagram by hand: it builds it from your existing database. It connects over JDBC, reads the system catalog and lays out the tables with their foreign keys. The free Community Edition already does this. For a team, Pro and Architect are licensed per developer, and a floating licence lets the whole team share a number of seats.
This guide is for the engineer handed a database they did not build. It covers what the catalog reports, what it misses, and how to check the diagram against real rows.
Know what the catalog can and cannot tell you
Every database keeps a machine-readable inventory of itself, and a reverse-engineering tool reads it over the connection. PostgreSQL exposes it through the SQL-standard information_schema views. They describe tables, columns, data types and constraints[1]. Other engines have their own dictionary views. The tool does not guess any of this. It queries.
- Tables and views, with their columns and data types
- Primary keys and unique keys
- Foreign keys that were declared as constraints
- Indexes
- Comments stored on tables and columns
The gaps matter more than the list. The catalog cannot show a relation that lives only in application code or an ORM mapping, because no constraint was ever declared. It cannot tell you what a column means: a column called status_flag of type int says nothing about its values. And it cannot tell you whether the rows obey the declared keys. So a diagram built this way shows the declared structure, not what the application enforces.
Sort out the driver and the privileges first
On enterprise engines, the connection is the first hurdle. You need a JDBC driver for the engine and an account that may read the catalog. A low-privilege login often connects fine and then returns half the schema. That looks like a tool bug, but it is a permissions problem.
DbSchema downloads the right driver the first time you connect, so you do not hunt for a jar file. The JDBC Drivers Manager also accepts your company's own driver, for example a standard build for Db2 or SAP HANA. The Db2 JDBC driver page lists the driver class, URL format and default port. Our IBM Db2 design tool guide walks through the whole connection.
Two settings protect a live database while you explore it. A Read Only Connection stops DbSchema from changing the schema. The highlight colours the connection as Production, Development or Test, so you always see where you are. Use both on the first connection to a database you did not build.
Use a connected tool to get an actual picture
There are three ways to turn a live database into something you can read. Hand-written catalog queries are free and exact, but they give you rows, not a picture. Nobody finishes laying out two hundred tables by hand. A schema-only DDL dump gives you portable text, but the relations are buried in CREATE statements.
A connected diagram tool is the only route that produces the picture. It shares the limit described above: it shows declared structure, and relations enforced in code appear only once you add them. The rest of this guide follows that route.
Connect, pick the schemas and let the diagram draw itself
Community Edition connects to the database, lets you pick the schemas and reverse-engineers them into a laid-out ER diagram. It supports more than 100 SQL and NoSQL databases, so one workflow covers both the MySQL instance and the Db2 warehouse. The result is a design model. It holds structure, not data, so no rows are copied out of the database.
One model can hold many diagrams, and a table can show on several of them. That is how a large inherited schema becomes readable: draw one diagram per module instead of one canvas where nothing can be found. You can also group related tables on a shared diagram. Our database diagram tool guide shows what the result looks like.
Expect each engine to hide some relations
Engines report foreign keys differently, and that changes what your first diagram shows. Four cases cover most integration work.
- MySQL and MariaDB keep foreign keys only on storage engines that support them[2]. A legacy table on another engine shows no relations at all.
- SQLite turns foreign key enforcement off by default for each connection[3]. Declared keys may not be enforced.
- Oracle's ALL_ views show only objects the login can access[4]. A low-privilege account returns a partial model that looks like missing keys.
- SQL Server lists declared foreign keys in the sys.foreign_keys view, one row per constraint[5].
The per-engine guides go deeper: reverse-engineering a MySQL database, reverse-engineering a PostgreSQL database, reverse-engineering a SQLite database and reverse-engineering an Oracle database. To generate a PostgreSQL ER diagram step by step, with a sample schema whose missing foreign key you add as a virtual one, see How to Generate an ER Diagram from a PostgreSQL Database.
Add the relations nobody declared as virtual keys
Inherited schemas often have no declared relations at all. The application writes the right values and the column names line up, but the catalog has nothing to report. So the first diagram shows lonely tables. The fix is a virtual foreign key. Drag from the referencing column to the target column, and DbSchema asks whether you want a real or a virtual foreign key.
A virtual key exists only in the design model. It draws the line on the diagram, and the query builder and data explorer use it for joins. But no constraint is created in the database. So you can record what the application enforces without changing a schema you do not own. With Pro, the virtual key is saved in the model file, so the next person sees it too.
Check the diagram against real rows, then document it
A diagram built from the catalog and your own virtual keys is a hypothesis until you look at the data. Bernie Pruss, a data architect with about 30 years in the field, wrote on LinkedIn that DbSchema gave him a visual model of his database. Right-clicking a table to see its data, profile it and query it showed him relationships that needed work[6]. That is the right order: trust the diagram once the rows agree.
In Pro, the Relational Data Explorer follows real and virtual keys from a row to its related rows. A wrong relation shows up when the child pane comes back empty. In Community Edition, the SQL editor runs the same check. This query finds child rows whose parent no longer exists:
SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
Every row it returns is a relation the diagram claims and the data contradicts. Once the diagram holds, Pro exports it as interactive HTML5 documentation, with table and column comments as tooltips. Colleagues and clients open it in a browser without installing DbSchema.
License it per developer or share seats across the team
Community Edition connects, reverse-engineers every supported database, draws interactive diagrams and runs the SQL editor. Pro adds saving the design to a file, working offline, relational data browsing, HTML5, PDF and Markdown documentation, and schema synchronization.
Pro and Architect licences are per developer: one person installs DbSchema on up to two computers and uses one at a time. A floating licence is the one a team shares. You install DbSchema on every machine and pay only for the people who use it. Our guide on how floating licences work for a database team covers the setup.
The quickest test is your own inherited database. Download the free Community Edition, connect it, and the diagram draws itself.
Frequently asked questions
How can I generate an ER diagram?
Connect DbSchema to your live database. It reverse-engineers the schemas you select, reads their tables, columns and declared foreign keys, and lays them out as a diagram.
How to generate an ER diagram automatically?
Connect DbSchema over its JDBC driver. It reads the schema from the engine and draws the relationships from the primary and foreign keys, with no manual drawing.
What is a reverse engineering database?
Database reverse engineering reads the structure of an existing database: its tables, views and relationships. A tool then turns that structure into an ER diagram. The Entity-Relationship model itself was published in 1976.
Can AI replace reverse engineering?
No. AI can read a schema dump, but it cannot replace reading the live catalog. Reverse engineering queries the database directly, so the diagram matches the real constraints. An AI model can misread or invent them.
What is the best way to document a database?
Generate interactive HTML5 documentation from a reverse-engineered model, which in DbSchema is a Pro feature. It captures the real tables, types and constraints, and you can add virtual keys and comments without touching the live system.
Can I create foreign keys in a diagram without creating them into the database?
Yes. In DbSchema you draw virtual foreign keys for relations the application enforces. They live in your design model, and with Pro they are saved to the model file. They are never created as constraints in the database.
Can a whole team share one DbSchema licence?
Regular Pro and Architect licences are per developer. A floating licence is the one a team shares: you install DbSchema on every machine and pay only for the people who use it.
Sources
Turn the database you inherited into an ER diagram
DbSchema connects over JDBC, reverse-engineers the schema into an interactive diagram and lets you add the relations nobody declared, free Community Edition included.
