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 (GitHub – Homepage) 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:
| 1 | 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:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 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:
| 1 | 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 instructions on how to use it.
Have fun generating your database documentation,
Phil
