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.
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
- ApexSQL Doc – https://www.apexsql.com/sql_tools_doc.aspx/
- Redgate SQL Doc – https://www.red-gate.com/products/sql-development/sql-doc/
- dbForge – https://www.devart.com/dbforge/sql/documenter/
- SqlSpec – http://www.elsasoft.com/
- Documenter – https://www.spectralcore.com/documenter/
- Dataedo – https://dataedo.com/
- dbdesc – http://dbdesc.com/
- DBScribe – http://www.leadum.com/products.php?prod_id=10
- SchemaToDoc – http://www.schematodoc.com/
- SQL Documentation Tool – http://www.winnershtriangle.com/sql-documentation-tool/
- 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 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.
Below are simple steps on how to generate SchemaSpy database documentation for your database.
- Download SchemaSpy
- Download GraphViz (Note that we need this mainly for the dot executable, which is used by GraphViz)
- 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