How to use SchemaSpy to document your database

Sometimes we need to generate the proper documentation for our database and usually the documentation process can become tedious, fortunately nowadays there are several documentation tools like SchemaSpy to make this task easier and faster.

This post will show you how to generate the database documentation from a simple MySQL schema (User/Roles relationship).

SchemmaSpy (Official description)

SchemaSpy is a Java-based tool (requires Java 5 or higher) that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format

Prerequisites

Configuration and Execution

  1. Download SchemaSpy jar file (schemaSpy_5.0.0.jar) from → https://sourceforge.net/projects/schemaspy/files/
  2. Download the MySQL Java Connector jar file (mysql-connector-java-5.0.8-bin.jar) from → https://dev.mysql.com/downloads/connector/j/
  3. Execute the next command updating the parameter values based on your MySQL installation, MySQL credentials and the files downloaded previously (SchemaSpy and Java Connector)

java -jar C:\SchemaSpy\schemaSpy_5.0.0.jar -t mysql -host localhost -db spring-security-tutorial -u root -p admin -dp C:\SchemaSpy\mysql-connector-java-5.0.8-bin.jar -o C:\SchemaSpy\DB_Documentation\

Note: if you are getting the next error message, please verify your Graphviz installation or use the -gv parameter to indicate the Graphviz folder location.

Command with the -gv parameter

java -jar C:\SchemaSpy\schemaSpy_5.0.0.jar -t mysql -host localhost -db spring-security-tutorial -u root -p admin -dp C:\SchemaSpy\mysql-connector-java-5.0.8-bin.jar -o C:\SchemaSpy\DB_Documentation\ -gv C:\software\graphviz-2.38

Commonly used parameters

Here you can find the complete SchemaSpy documentation.

Output

Open the index.html file in order to refer the generated database documentation.

Tables

Relationships

Constraints

Anomalies

Columns

If you want to know what databases are supported by SchemaSpy please type the next command in your terminal or command line.

java -jar schemaSpy_5.0.0.jar -dbhelp

That’s all folks, I hope this can help you in your documentation process.