#HOWTO: Simple database documentation with SchemaSpy

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:

java -jar schemaspy.jar -t dbType -dp C:/sqljdbc4-3.0.jar -db dbName -host server -port 1433 [-s schema] -u user [-p password] -o outputDir

or use a schemaspy.properties configuration file:

# database type
schemaspy.t=pgsql

# path to the database JDBC driver
schemaspy.dp=postgresql-42.2.5.jar

schemaspy.host=127.0.0.1
schemaspy.port=5432

# database name
schemaspy.db=postgres

# database user
schemaspy.u=postgres
schemaspy.p=postgres

# output folder for the generated resukt
schemaspy.o=output

# database schema
schemaspy.s=public

and run:

java -jar schemaspy.jar -configFile config/schemaspy.properties

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 instruction on how to use it.

Have fun generating your database documentation,

Phil

Leave a comment

Your email address will not be published. Required fields are marked *