Home > PostgreSQL > Using SchemaSpy for DB diagrams

Using SchemaSpy for DB diagrams

A couple of months back I came across this very useful tool “SchemaSpy” ( http://schemaspy.sourceforge.net/ ) which really helped me out in generating automated (nightly) database diagrams but never got enough time to share it with everyone.

“SchemaSpy is a Java-based tool that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database tables via child and parent table relationships as represented by both HTML links and entity-relationship diagrams. It’s also designed to help resolve the obtuse errors that a database sometimes gives related to failures due to constraints”

It works great with PostgreSQL and very easy to cron as you just need to execute a single command like this:

java -jar schemaSpy.jar -t dbType -db dbName [-s schema] -u user [-p password] -o outputDir

…that will generate very nice and user-friendly DB diagrams and a whole lots of information about the database. You can have a quick look at the sample on http://schemaspy.sourceforge.net/sample/

Update: Also found an easy to use GUI for SchemaSpy http://www.joachim-uhl.de/projekte/schemaspygui/ where you don’t need to remember all the option but you can easily pick them up from the frontend.

Make sure you grab the latest beta from http://schemaspy.sourceforge.net/schemaSpy.jar as mentioned by John Currier.

Shoaib Mir

Categories: PostgreSQL Tags: ,
  1. May 2, 2010 at 2:22 am

    Thanks for Sharing. Seems interesting !!!

  2. May 2, 2010 at 11:56 am

    My one gripe with SchemaSpy is that it is pretty unable to cope with databases consisting of multiple schemas/namespaces (e.g. – CREATE SCHEMA/pg_namespace).

    This is an artifact (pretty clearly) of it being DBMS-agnostic – some databases it supports don’t support that.

    With that caveat, it is indeed an excellently useful tool, and I know a number of folks at work use it pretty heavily.

    Its diagramming approach is *wonderful*, if only it could cope with multiple namespaces!

    • May 2, 2010 at 12:04 pm

      Thanks Christopher for pointing it out, that is true as I too saw similar behavior but most of my DBs were using public schema and when different they did not have a lot of schemas so didnt encounter a lot of trouble with SchemaSpy.

    • May 3, 2010 at 8:13 am

      There is a -schemas directive for listing individual schemas, and a more generic -all directive for dumping all schemas. 🙂

      • May 3, 2010 at 10:04 am

        I guess that is the reason I didnt see much problem as I was using just the default settings and I guess -all is what was used in that case.

  3. May 5, 2010 at 2:14 am

    Make sure you grab the latest beta from http://schemaspy.sourceforge.net/schemaSpy.jar. Yes, I’m terrible about publishing new releases.

    • May 5, 2010 at 8:07 am

      Thanks John for such a nice tool 🙂 I have also updated the post to point out the correct JAR for latest beta.

  4. January 6, 2011 at 6:25 am

    Maybe you will find interesting another blog post about creation of diagrams out of .sql files (with use of SchemaSpy, Groovy and HSQLDB), see http://tinyurl.com/27tbw6w

    Tomek Kaczanowski

  5. April 13, 2011 at 9:22 am

    Cool thing

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: