Generate ER diagram from a SQL-based database

2014-12-18T00:00:00+06:00

When you are “study”-ing (for whatever reason) someone else’s database, and the database has more than 20 tables, you might be in trouble to understand what’s going where.

Now, imagine a database with 300+ tables. It’s like spaghetti, just not as enjoyable.

I faced a similar challenge recently with a database of 250+ tables. Yes, I felt like in a deep sh*t. And, I started looking for tools that can describe the tables, or at least a decent ER diagram. If anything more, better. And, preferably free.

Then, I found SchemaSpy, originally authored by John Currier. It generates a complete in-depth HTML-based description (of course, including clickable ER-diagram) of the database, which you can then browse with your browser. This post is about its primary usage.

The output will be kind of like this:

It is based on Java, but it can work its magic on most of the major database products. However, it would require an appropriate JDBC connector for that database.

Quote from the author:

SchemaSpy uses JDBC’s database metadata extraction services to gather the majority of its information but has to make vendor-specific SQL queries to gather some information such as the SQL associated with a view and the details of check constraints.

In this post, as an example, I have shown how to use it with PostgreSQL. But, it’s not the only one that’s supported. You can use it with any proper RDBMS system as long as it has a JDBC-connector. Now, to use it, you need to get these stuffs.

First of all, your system should have Java runtime properly installed. Download from here.
SchemaSpy, which is a .jar file. Get it here. At the time of writing, it was version 5.0.0.
JDBC connector to PostgreSQL. Make sure to match your PostgreSQL version. You can download it from here.
You can check your PostgreSQL version by executing: SELECT version(); query on psql prompt.
Also, SchemaSpy depends on GarphViz to generate the ER diagrams, so you need to be installed it on your system. Get it from here.(http://www.graphviz.org/Download..php)
And, of course, make sure the target database instance is running & serving the database you are trying to visualize.

Quoting from the author:

SchemaSpy uses the dot executable from Graphviz to generate graphical representations of the table/view relationships. The visual representation of the connections is a fundamental feature of the tool.

Graphviz is not required to view the output generated by SchemaSpy, but the dot program should be in your PATH (not CLASSPATH) when running SchemaSpy, or none of the entity-relationship diagrams will be generated. Or, maybe use the -gv option).

I kept the .jar files (both the JDBC-connector and the SchemaSpy) in my home folder for convenience.
Now, in my case, my OS is Linux, and the database is hosted locally; hence address is 127.0.0.1, running PostgreSQL-9.3 at port 5432. So, I run the command like this:

 $ java -jar ./schemaSpy_5.0.0.jar -t pgsql -host 127.0.0.1:5432 -db your_database_name \
                                   -u your_DB_user_name -p your_password -s public \
                                   -dp ./postgresql-9.3-1102.jdbc3.jar \
                                   -o output_folder

It may take a little while, depending on the size of the schema of the database.
After that, you will find the output folder/directory named output_folder.
You’ll see some output when the magic is going on, similar to this below.

Using database properties: [./schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/pgsql.properties
Gathering schema details....................(6sec)
Writing/graphing summary....................(2sec)
Writing/diagramming detail..................(31sec)
Wrote relationship details of 113 tables/views to directory 'output' in 41 seconds.

View the results by opening output_folder/index.html

Now, all the generated files are in the output_folder. Start your journey by starting from the index.html in the output folder. Open it by using any browser.

Good luck. :)

If you find this post helpful, you can show your support through Patreon or by buying me a coffee. Thanks!

I think ... - dbms

Generate ER diagram from a SQL-based database