Running SchemaSpy for Postgres

24 May 2016

Note: I’ve since found better software for visualizing sql data models. I highly recommend dbeaver please consider buying this free software

This was harder than it should have been so I’m documenting it.

  1. Install Java®️
  2. Download SchemaSpy it’s a .jar file
  3. Download PostgreSQL JDBC Driver also a .jar
  4. Run java -jar <schemaspy jar> -t pgsql -db <database name> -u <user> -host localhost -o <output directory> -s "public" -dp <jdbc driver>

for a remote database (say on Amazon RDS) try

java -jar <schemaspy jar> -t pgsql -db <database name> -u <user> -p <password> -host -o <output directory> -s "public" -dp <jdbc driver>

If you get a complaint about the schema, it defaults to the username, pass in the correct schema name after -s.

now you can open <output directory>/index.html to get amazing info on your postgres database.

Dellstore2 DataModel

Above is the output for the dellstore2 datamodel. You can drill down to focus on a single table and its relationships. Let’s isolate ‘Orders’.

Dellstore2 Orders

You can test it out yourself dellstore SchemaSpy.

Hopefully someday I’ll be able to get a postgres schema to load in wwwsqldesigner but I couldn’t figure it out. This way I could send my friends a link to my data model.

For other applications, like weka you would want to place the jdbc driver in the classpath.

If you need help solving your business problems with software read how to hire me.

comments powered by Disqus