Database Documentation and SchemaSpy

Introduction:

Database is the fundamental layer and understanding the DDL is very vital. Having a strong knowledge of DB DDL will help in gaining sound understanding of application business logic and helps greatly while designing new features. It is equivalent to knowing 50% of the overall application.

Still, we rarely see teams documenting their databases. These are some of the common reasons (aka excuses) for lack of DB documentation – Folks either think it is too obvious or too complex to document database. Often, databases evolve over time and so there may not be one person who knows it all. More importantly, product owner or customer may not care for documentation and may not be willing to spend any money on it. So, we need some free or inexpensive tool that takes very less developer time to generate DB documentation.

In this blog, we will look at DB documentation tools available out there and also look into SchemaSpy, which is one of the very good tool for DB documentation.

Documentation Tools:

Currently, there are several different ways that people use to document their databases. Primitive way is to use MS Office tools like Word and Excel to document databases. DB Management Tools like SQLServer Management Studio, Oracle SQLDeveloper etc., also offer ways to generate database diagrams. People also use diagramming tools like Visio, Gliffy etc., to document their databases. Next comes database documentation generators – Some are read only, but some are advanced and support read-write, meaning changes to documentation will also update the DDL.

Below are some of the available database documentation generators

1. ApexSQL Doc – https://www.apexsql.com/sql_tools_doc.aspx/
2. Redgate SQL Doc – https://www.red-gate.com/products/sql-development/sql-doc/
3. dbForge – https://www.devart.com/dbforge/sql/documenter/
4. SqlSpec – http://www.elsasoft.com/
5. Documenter – https://www.spectralcore.com/documenter/
6. Dataedo – https://dataedo.com/
7. dbdesc – http://dbdesc.com/
8. DBScribe – http://www.leadum.com/products.php?prod_id=10
9. SchemaToDoc – http://www.schematodoc.com/
10. SQL Documentation Tool – http://www.winnershtriangle.com/sql-documentation-tool/
11. SchemaSpy – http://schemaspy.sourceforge.net/

I was surprised to see so many tools out there. The one that stood out for my needs is SchemaSpy.

SchemaSpy:

SchemaSpy is very easy to use and it is free as well. I like the various output and drill down options that it offers. I was able to get up and running and was able to generate detailed documentation for my database in less than an hour.

You can see sample output here, which shows DB schema relationship – http://schemaspy.sourceforge.net/sample/relationships.html. Here, you can drill down by clicking on each of the tables and then view tables with either one or two degrees of separation.

Generating SchemaSpy:

Below are simple steps on how to generate SchemaSpy database documentation for your database.

1. Download SchemaSpy
2. Download GraphViz (Note that we need this mainly for the dot executable, which is used by GraphViz)
3. java -jar schemaspy.jar <options>

Below is a sample schemaspy command connecting to a SQLServer DB using jtds driver:

java -jar schemaSpy_5.0.0.jar -t mssql-jtds -db -host localhost -port 1433 -u -p -s dbo -hq -o

-gv

Note that I initially used mssql, as

I had Microsoft SQL Server driver, but that didn’t work as as the classname that SchemaSpy was looking was not in the same package in Microsoft Driver. I could have customized the driver name by overrding databasetype.properties, but then I found another alternative. I downloaded jtds driver for Microsoft, placed it under Program Files\Java\Shared\jtds folder and used mssql-jtds for db type. It worked out of the box without any issues.

How to get the most out of SchemaSpy:

As you can see, SchemaSpy gives very nice interactive database documentation right out of the box. To enhance this, it would be good to have comments added to tables and columns in the DDL. These comments would also then appear in the generated db documentation. As always, it is good to document the “why” part so that future developers understand the design decisions and why database is designed in this particular manner.

One common problem with most database documentation generators is that the generated documentation might go stale very easily. That’s where schemaspy maven plugin comes in handy. We can hook this into the build and so anytime documentation changes, we get the updated schemaspy diagram automatically. Having this live documentation of database would be very helpful for developers to understand the application better.