Generate an E-R Diagram by reverse-engineering a database

Question

Note: Originally this question was asked for PostgreSQL, however, the answer applies to almost any database which has a JDBC driver that can detect foreign-key associations.

---

Querying PostgreSQL data dictionary for foreign-keys and relationship between tables is very straightforward, but how can I use that information to generate a graph of the relations between tables?

Any recommendations about tools that can do this?

EDIT: I know GraphVIZ/DOT can be useful, however, I don't know have any idea regarding how to code an app that would generate the directed graph .DOT file.

Solution 1

Dot is part of the graphviz package, which is a pretty damn cool/useful tool. Of course, you'll need something to generate the dot files for graphviz. I've used SchemaSpy once or twice in the past, and it works pretty well, provided you have the relationships defined in the database.

Solution 2

Microsoft Visio will easily do this.

Solution 3

At least for Oracle I run this query or ask the DBA to run it and send me the results. Results can be copied directly to a text file to be interpreted by Graphviz's tools, resulting in a database diagram.

SELECT '"' || Source.TABLE_NAME || '" -> "' 
           || Destiny.TABLE_NAME || '";' AS For_GraphViz
FROM dba_constraints Source
JOIN dba_constraints Destiny
ON Source.owner='my_db_owner' AND Destiny.owner='my_db_owner'
AND Source.CONSTRAINT_TYPE='R'
-- theoretically this validation should be redundant
-- AND Destiny.Constraint_type = 'P'
AND Source.R_CONSTRAINT_NAME = Destiny.CONSTRAINT_NAME
ORDER BY Source.TABLE_NAME, Source.CONSTRAINT_TYPE, Source.CONSTRAINT_NAME
    , Source.R_CONSTRAINT_NAME, Source.INDEX_NAME;

A similar query can be created easily for SQL Server, don't know about MySQL, PostgreSQL et al.

Solution 4

DBVisualizer is also a free and nice alternative.

Solution 5

Dot is multiplatform and may be useful.

View more solutions