Documenting your relational database using SchemaSpy

1. OVERVIEW

You joined a new organization, maybe asked to troubleshoot if a Java application has the N+1 select problem or to write new SQL queries.

You started looking at a couple dozen JPA entities and decided to take a look at the RDBMS Entity Relationship diagram. You asked your peers and there is none.

This blog post helps you to document your relational database using SchemaSpy in different ways. Via command line, using a Maven plugin, or using Docker so that you don’t have to install SchemaSpy required software.

Documenting your relational database using SchemaSpy

2. REQUIREMENTS

Java 8+, SchemaSpy 6.1.0.
Optional Graphviz http://www.graphviz.org/download/ or
Java 5+, Graphviz, Maven 2.2.1+ if you would like to generate the entity relationship diagram as part of your build pipeline.
Docker host if you would like to generate the entity relationship diagram using the Docker image this blog post covers. No need to install Java, SchemaSpy, or Graphviz.

For all options, you still need the JDBC driver relevant to your relational database. This post uses postgresql-42.2.16.jar.

Let’s assume you downloaded SchemaSpy to your working directory and the JDBC driver to <working-dir>/jdbc.

3. COMMAND LINE

Let’s use this SchemaSpy command line options (more options available at https://schemaspy.readthedocs.io/en/latest/configuration/commandline.html#commandline):

Option	Description
-t	Database type (db2, derby, firebird, h2, informix, mariadb, mssql, mysql, ora, orathin, pgsql) A few more if you run `java -jar schemaspy-6.1.0.jar -dbhelp \| grep "\-t"`
-host	The hostname or IP to connect to
-port	The port the RDBMS listens on
-db	The database name
-u	The database user name with read access
-p	The database password
-schemas	The database schema

java -jar schemaspy-6.1.0.jar -t pgsql -host localhost -port 5432 -db db_dvdrental -u user_dvdrental -p changeit -schemas public -dp ./jdbc/ -o ./output

You might get a console output like:

  ____       _                          ____
 / ___|  ___| |__   ___ _ __ ___   __ _/ ___| _ __  _   _
 \___ \ / __| '_ \ / _ \ '_ ` _ \ / _` \___ \| '_ \| | | |
  ___) | (__| | | |  __/ | | | | | (_| |___) | |_) | |_| |
 |____/ \___|_| |_|\___|_| |_| |_|\__,_|____/| .__/ \__, |
                                             |_|    |___/

                                              6.1.0

SchemaSpy generates an HTML representation of a database schema's relationships.
SchemaSpy comes with ABSOLUTELY NO WARRANTY.
SchemaSpy is free software and can be redistributed under the conditions of LGPL version 3 or later.
http://www.gnu.org/licenses/

INFO  - Starting Main v6.1.0 on Orlandos-MBP.3velopers.net with PID 61658 (/Users/ootero/Downloads/schemaspy/schemaspy-6.1.0.jar started by ootero in /Users/ootero/Downloads/schemaspy)
INFO  - The following profiles are active: default
INFO  - Started Main in 1.064 seconds (JVM running for 1.537)
INFO  - Analyzing schemas:
public
INFO  - Analyzing public
INFO  - Starting schema analysis
INFO  - Connected to PostgreSQL - 9.5.10
INFO  - Gathering schema details
Gathering schema details.........................(0sec)
Connecting relationships.........................(0sec)
Writing/graphing summary.INFO  - Gathered schema details in 0 seconds
INFO  - Writing/graphing summary
WARN  - Failed to query Graphviz version using 'dot -V'
Cannot run program "dot": error=2, No such file or directory
INFO  - Graphviz rendered set to ''
..ERROR - RelationShipDiagramError
Failed to produce diagram for: ./output/public/diagrams/summary/relationships.real.compact.dot
ERROR - RelationShipDiagramError
Failed to produce diagram for: ./output/public/diagrams/summary/relationships.real.large.dot
ERROR - RelationShipDiagramError
Failed to produce diagram for: ./output/public/diagrams/summary/relationships.implied.compact.dot
ERROR - RelationShipDiagramError
Failed to produce diagram for: ./output/public/diagrams/summary/relationships.implied.large.dot
......(1sec)
Writing/diagramming detailsINFO  - Completed summary in 1 seconds
INFO  - Writing/diagramming details
Exception in thread "main" java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:48)
	at org.springframework.boot.loader.Launcher.launch(Launcher.java:87)
	at org.springframework.boot.loader.Launcher.launch(Launcher.java:50)
	at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:51)
Caused by: org.schemaspy.output.diagram.DiagramException: Failed to generate Table diagram
	at org.schemaspy.output.diagram.DiagramFactory.generateTableDiagram(DiagramFactory.java:71)
	at org.schemaspy.output.html.mustache.diagrams.MustacheDiagramFactory.generateTableDiagram(MustacheDiagramFactory.java:44)
	at org.schemaspy.output.html.mustache.diagrams.MustacheTableDiagramFactory.generateTableDiagrams(MustacheTableDiagramFactory.java:80)
	at org.schemaspy.SchemaAnalyzer.generateHtmlDoc(SchemaAnalyzer.java:426)
	at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:260)
	at org.schemaspy.SchemaAnalyzer.analyzeMultipleSchemas(SchemaAnalyzer.java:176)
	at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:118)
	at org.schemaspy.cli.SchemaSpyRunner.runAnalyzer(SchemaSpyRunner.java:98)
	at org.schemaspy.cli.SchemaSpyRunner.run(SchemaSpyRunner.java:87)
	at org.schemaspy.Main.main(Main.java:55)
	... 8 more
Caused by: org.schemaspy.output.diagram.DiagramException: Dot missing or invalid version
	at org.schemaspy.output.diagram.graphviz.GraphvizDot.generateDiagram(GraphvizDot.java:193)
	at org.schemaspy.output.diagram.DiagramFactory.generateTableDiagram(DiagramFactory.java:68)
	... 17 more

You should be able to open the report found at ./output/public/index.html. But not the tables relationships because Graphviz is not installed.

SchemaSpy 6.1.0 removed the need for Graphviz by using a new command argument -vizjs.

java -jar schemaspy-6.1.0.jar -vizjs -t pgsql -host localhost -port 5432 -db db_dvdrental -u user_dvdrental -p changeit -schemas public -dp ./jdbc/ -o ./output

which now ends successfully:

...
INFO  - Writing/graphing summary
........(23sec)
Writing/diagramming detailsINFO  - Completed summary in 23 seconds
INFO  - Writing/diagramming details
......................(23sec)
Wrote relationship details of 22 tables/views to directory './output' in 47 seconds.
View the results by opening ./output/index.html
INFO  - Wrote table details in 23 seconds
INFO  - Wrote relationship details of 22 tables/views to directory './output/public' in 47 seconds.
INFO  - View the results by opening ./output/public/index.html

Browsing ./output/public/index.html shows:

SchemaSpy 6.1.0 Tables db_dvdrental Tables

SchemaSpy 6.1.0 Relationships db_dvdrental Relationships

Now that you have the static HTML files documenting your relational database, you can share it with your team if you host the output folder ./output in Apache or Nginx. You could host HTML files in an AWS S3 bucket using s3_website.

4. MAVEN PLUGIN

You could also integrate documenting your relational database using Maven in your CI pipeline. Let’s look at this pom.xml file:

...
<properties>
  <maven-schemaspy-plugin.version>5.1.0</maven-schemaspy-plugin.version>
</properties>

<build>
  <plugins>
...
    <plugin>
      <groupId>nl.geodienstencentrum.maven</groupId>				
      <artifactId>schemaspy-maven-plugin</artifactId>
      <version>${maven-schemaspy-plugin.version}</version>
      <configuration>
        <databaseType>pgsql</databaseType>
        <database>db_dvdrental</database>
        <host>localhost</host>
        <port>5432</port>
        <user>user_dvdrental</user>
        <password>changeit</password>
        <schema>public</schema>
        <outputDirectory>target/site</outputDirectory>
        <pathToDrivers>[path to]/postgresql-42.2.16.jar</pathToDrivers>
      </configuration>				
    </plugin>
  </plugins>
</build>
...

And then run:

mvn schemaspy:schemaspy
...
Wrote relationship details of 22 tables/views to directory '/Users/ootero/Projects/bitbucket.org/schemaspy-demo/target/site/schemaspy' in 1 seconds.
View the results by opening /Users/ootero/Projects/bitbucket.org/schemaspy-demo/target/site/schemaspy/index.html
...

schemaspy-maven-plugin uses the legacy SchemaSpy 5.0.0. This version requires Graphviz for the Relationships tab to display the relations between the database tables.

SchemaSpy 5.0.0 Tables db_dvdrental Tables

You could host the site report in Apache or Nginx via their webdav protocol support.

5. DOCKER IMAGE

If your organization is invested in Docker, you could use this image to prevent installing the requirements.

FROM openjdk:8u275-jre

VOLUME /tmp

RUN \
  bash -c 'mkdir -p /opt/asimio/schemaspy/output' && \
  apt-get -qq update && \
  apt-get -y upgrade && apt-get -y autoclean && apt-get -y autoremove && \
  bash -c 'DEBIAN_FRONTEND=noninteractive apt-get install -qq -y graphviz' && \
  wget --output-document=/opt/asimio/schemaspy.jar https://github.com/schemaspy/schemaspy/releases/download/v6.1.0/schemaspy-6.1.0.jar && \
  rm -rf /var/lib/apt/lists/*

CMD \
  java -jar /opt/asimio/schemaspy.jar -t $DB_TYPE -host $DB_HOST -port $DB_PORT -db $DB_NAME -u $DB_USER -p $DB_PASSWD -schemas $DB_SCHEMAS -dp $JDBC_DRIVER_PATH -o /opt/asimio/schemaspy/output/$DB_NAME

asimio/schemaspy is already in Docker Hub.

You would need to run a command similar to:

docker run --net=host -e DB_TYPE=pgsql -e DB_HOST=`hostname` -e DB_PORT=5432 -e DB_NAME=db_dvdrental -e DB_USER=user_dvdrental -e DB_PASSWD=changeit -e DB_SCHEMAS=public -e JDBC_DRIVER_PATH=/opt/asimio/schemaspy/jdbc -v ~/Downloads/schemaspy:/opt/asimio/schemaspy asimio/schemaspy:latest

The important environment settings are:

-e JDBC_DRIVER_PATH=/opt/asimio/schemaspy/jdbc
-v ~/Downloads/schemaspy:/opt/asimio/schemaspy

This means the JDBC driver has to be copied in ~/Downloads/schemaspy/jdbc prior to running this container.

Your relational database documentation will be generated in ~/Downloads/schemaspy similar to that of SchemaSpy 6.1.0 discussed earlier.

I had to use --net=host so that the SchemaSpy Docker container could connect to a Postgres Docker container listening on port 5432, and mapped to the Docker host port 5432.

You could also modify the Dockerfile to host the resulting static HTML files in Apache, Nginx, or in an AWS S3 bucket to share the relational database documentation with your team.

6. CONCLUSION

SchemaSpy is a free, Java-based, and easy to use software to document your relational databases.

Documenting your relational databases might increase developer productivity, especially when ramping up new hires.

You can generate relational database ER models with SchemaSpy, Maven, or Docker, depending on the infrastructure your organization is invested in.

Thanks for reading and as always, feedback is very much appreciated. If you found this post helpful and would like to receive updates when content like this gets published, sign up to the newsletter.