Simple database documentation with SchemaSpy

Last Updated:  December 15, 2019 | Published: January 20, 2019

Recently I was looking for a simple solution to generate visual documentation of the underlying database. The solution should be easy to use, database vendor-independent and automatable e.g. during the CI pipeline. Therefore I found SchemaSpy (GitHubHomepage) which is a single jar file and is able to generate nice-looking web-based documentation like the following: Sample SchemaSpy documentation.

In this blog post, you'll get an introduction to SchemaSpy and an instruction on how to use it for your project.

SchemaSpy works for every well-known database vendor like Oracle, MS-SQL, DB2, PostgreSQL, MySQL, etc. To connect to the database you can either pass command-line arguments to the execution of the .jar file:

or use a schemaspy.properties configuration file:

and run:

To connect to the database you have to download the required JDBC driver and specify its location. The output (HTML/JS/CSS/Images) will be placed in the specified folder.

The generated documentation will contain information about all your tables, views, columns, constraints, routines (stored procedures and functions) and the number of rows for each table:

Another important feature is the generation of visual database relationship diagrams. To generate them you need Graphviz installed on your machine. Without Graphiz you'll just get the general information about your database without any diagrams like the following:

This solution can easily be automated during your CI pipeline and the result can be hosted on a web server like Nginx or Apache to always have a visual reference of your current database schema. You can also store previous documentation and see how your database evolves from time to time.

For a quick example, I created a dockerized Postgres 11 with a simple database schema and prepared all required files for you. You can find this in my GitHub repository with detailed instructions on how to use it.

Have fun generating your database documentation,

Phil

>