Sometimes it's nice to visualise a pre-existing database (particularly when a new staff member joins the team and you need to walk them through your database). I came across a useful open source tool called schemaspy (http://schemaspy.sourceforge.net/).

Basically you just need to drop in a JDBC driver for your database, update the connection details and tell it to generate the schema documentation.

It generates a bunch of html files (with dot diagrams showing table relationships), what's really sweet is that you can start at a high level, seeing every single relationship in your database and drill down to a specific table.

The next view down shows immediate table relationships (which can be dynamically set to different depths).

This is a fantastic way of exploring a database. We have a cron job that generates this doco of our test database on a nightly basis and drops the files onto an internal web server, meaning that we always have fresh schema documentation without having to install any fancy (and often expensive) client side database design/visualisation tools.

Here are a few notes on how I got it working with a postgresql database:

  1. Download the schemaspy jar and extract it into a new folder (jar -xvf [jarfile]).

  2. Modify ./net/sourceforge/schemaspy/dbTypes/pgsql.properties to point at your local postgresql jdbc driver.

description=PostgreSQL ** connectionSpec=jdbc:postgresql://localhost:5432/[SCHEMA] host=localhost:5432 db=[DBNAME] ** driver=org.postgresql.Driver ** # Sample path to the postgresql drivers. # Use -dp to override. driverPath=/path/to/postgresql-9.2-1002.jdbc4.jar**

  1. Recreate the jar file (with the updated config file).

rm schemaSpy_5.0.0.jar jar -cmf./META-INF/MANIFEST.MF../schemaSpy_5.0.0.jar.

  1. /usr/bin/java -jar /[PATH TO JAR]/schemaSpy_5.0.0.jar -t pgsql -host localhost -port 5432 -db [DBNAME] -s [SCHEMA] -u [USERNAME] -p [PASSWORD] -o [LOCATION OF OUTPUT]

  2. If all works well, you will see a bunch of static html files and graphviz generated images in the output directory.****

What are your favorite database tools?