SQL Database Diagrams with SchemaSpy, HSQLDB and Groovy

The source code is available at github. Enjoy !
This post shows how to generate nice database diagrams using SchemaSpy, HSQLDB and Groovy. And, what is crucial, to do so in programmatic way, that is without clicking any GUI.

Problem

What I have right now is a couple of .sql, looking like this:

CREATE TABLE inventory ( id INTEGER PRIMARY KEY, 
    product VARCHAR(50), 
    quantity INTEGER,  price DECIMAL(18,2) );
CREATE TABLE whatever ( id INTEGER PRIMARY KEY, 
    whatever VARCHAR(50),
    something INTEGER, inventory_id INTEGER);

ALTER TABLE PUBLIC.whatever
    ADD FOREIGN KEY (inventory_id)
    REFERENCES PUBLIC.inventory(id)

What I want to have is this:
diagram of tables

Why? Because I want to include such diagrams in the documentation of my project. And additionally, I want the diagrams to be always up-to-date.

Little Help From My Friends

I asked my colleagues how to do that. I got few responses, mostly pointing out to Enterprise Architect, or another super-duper tool with 10000 features that I don't need, 100 configuration screens and a lot of clicking required to generate the diagrams.
GUI?! We don't need no stinkin' GUI! :)

No, thank you, I was not interested in clicking. I wanted diagrams to be always up-to-date which would not be possible if I had to click some GUI every time a change is introduced to my database schema.

Maciek saved my life by pointing me in the right direction - SchemaSpy, a "Graphical Database Schema Metadata Browser" GNU tool.

Solution

The problem is, that no tool that I am aware of - including SchemaSpy - takes .sql files as an input. What they do instead, is they analyze schema of some running database. You give them url, credentials and database name, and they generate diagrams.

So, I needed to create a database from my .sql files, and then make SchemaSpy connect to it, and generate diagrams. And what is the easiest way to create a running database? Well, some cheap, possibly in-memory database, like H2, HSQLDB, Apache Derby etc.

So, let us do it!

BTW. You can see the real code on github - here I will paste only the most important snippets.

BTW. there is a Maven plugin for SchemaSpy

Groovy & Gradle

I decided to write this little program using Groovy and build it with Gradle. Why Gradle ? Because:

  1. I need a lot of flexibility (Maven is not an option),
  2. I want to keep it short (which rules Ant out).

The build script - build.gradle - looks like this:

apply plugin: 'groovy'
apply plugin: 'idea'

repositories {
    mavenCentral()
    flatDir name: 'localDirRepo', dirs: 'lib'
}

dependencies {
    compile 'schemaspy.org:schemaspy:5.0.0'
    compile 'hsqldb:hsqldb:1.8.0.10'
    groovy 'org.codehaus.groovy:groovy-all:1.7.6'
}

// GENERATION OF "FAT-JAR"
jar {
    from configurations.compile.collect { it.isDirectory() ? it : zipTree(it) }
    from configurations.groovy.collect { it.isDirectory() ? it : zipTree(it) }
}

I haven't found SchemaSpy in any Maven repository, so I need to download it by hand and put it in lib directory of my project. As you can see Gradle has no problem with fetching some jars from maven repository and some from project's subdirectory.

Also a fat-jar is generated so I can easily run this application without need to enter classpath information. I'm lazy, I know.

HSQLDB

For a database I used HSQLDB (which is now called HyperSQL). All I had to do is to start it, make it run all SQL statements from the file and stop it.

// START SERVER
def hsqlServer = new Server()

// CREATE DB
hsqlServer.setDatabaseName(0, "mydb");
hsqlServer.setDatabasePath(0, "mem:testdb");
hsqlServer.start()

// CONNECT
def embeddedDbSettings = [url:'jdbc:hsqldb:hsql://localhost/mydb,
            user:'sa', password:'', driver:'org.hsqldb.jdbcDriver'];

def sql =  Sql.newInstance(embeddedDbSettings);

// EXECUTE SQL STATEMENTS FROM FILE
String[] commands = new File("mysqlfile.sql").text.split(";");

for(String command: commands) {
  // new line is a delimiter in hsqldb
  sql.execute command.replace("\n", " ");
}

// HERE SchemaSpy will do its job

// KILL DB SERVER
hsqlServer.stop()

SchemaSpy

After the database is ready, SchemaSpy can do its job. All you have to do is to give it connection information and voila!

// CONFIGURATION OF CONNECTION
conf.setOutputDir "someOutputDir"
conf.setUser "sa"
conf.setDbType "hsqldb"
conf.setHost "localhost"
conf.setSchema "PUBLIC"
conf.setDb "mydb

// OTHER CONFIGURATION OPTIONS
conf.setHighQuality true

// GENERATE DIAGRAMS
def sa = new SchemaAnalyzer()
sa.analyze(conf)

Run It

Please refer to readme.txt file at github.

The End? Almost

And that is all. It works and generates really nice and informative diagrams (AFAIK SchemaSpy uses Graphviz and Dot underneath).

In fact, what I presented here is just a piece of a bigger process. The whole thing looks roughly like this:

  1. .sql files are fetched from SVN (by executing svn command from Groovy script),
  2. for each of the files an in-memory database (HSQLDB) is created,
  3. SchemaSpy connects to this database and generates diagrams,
  4. selected diagrams are copied to some directory, which is used by AsciiDoc as a source for images that are included in generated documentation.

Links

Please comment using