Brugge Blog

If you have ever had to understand a database structure, with or without printed documentation, you will appreciate SchemaSpy, the kind of tool I always like (generating documentation from a source) and which I’ve needed of late. Need an entity-relationship diagram? Done. Need a data dictionary? Done. Point this at your database and it does the work.

Hand-crafted documentation of a database is ideally what you would like, since you can emphasize important tables and relationships that aren’t apparent by the raw structure, but that is an ideal, and most documentation I’ve seen is far short of that. I’ll take up-to-date reference docs generated from the database definition itself any time.

The tool is written in Java, so a command-line invocation might look like this for an Oracle database.

java -jar schemaSpy_3.0.0.jar -t orathin -db mydbname 
     -u user -p password 
    -s schema -cp /path/to/db/driver -h host -p port -o dbreportdir

If you have Ant running builds on command or on a schedule, you might script the same thing like this.

<target name="schema" depends="check-driver" if="db.driver.present">
    <java jar="schemaSpy_3.0.0.jar"
        output="${report.dir}/schemaspy-out.log"
        error="${report.dir}/schemaspy-error.log"
        fork="true">
        <arg line="-t orathin"/>
        <arg line="-db ${database}"/>
        <arg line="-u ${userid}"/>
        <arg line="-p ${password}"/>
        <arg line="-s ${schema}"/>
        <arg line="-cp ${db.driver}"/>
        <arg line="-host ${host}"/>
        <arg line="-port ${port}"/>
        <arg line="-o ${report.dir}"/>
    </java>
</target>

Run that and you’ll get a set of HTML pages that have table definitions and parent/child relationships in both table and diagram form. For the diagrams, it uses the open-source graphing tool GraphViz, but if you don’t have that installed it will still generate the table definition pages.

You will want to take the small effort it takes to get GraphViz, though, since diagrams are what most people look to first to understand the structure. Besides, SchemaSpy creates several graphs, all hyperlinked together. Besides an overall diagram of the database, it creates diagrams for each table that show its relationships to other tables within one or two degrees of separation. Here’s an example from the SchemaSpy website to whet your appetite.

There aren’t many tools in this space, and I haven’t found any others that are open-source that come remotely close to being as easy to setup and use. Put this into a regular build process and you’ll always have your reference data up to date. For free.