Documenting Databases With Schemaspy

Photo by Kevin Ku on Unsplash

When I think about database’s documentation remember me of centralizing tools where everything about databases was born on the DBA’s hands, design, to change a column type was needed open a ticket and thing like that. In a world without migrations and CI\CD makes totally sense give that power to the right guy, huge databases with tons of objects and data sometime attend more than one system at time make us need a benevolent dictator.

In this new micros service's world, CI\CD and migrations all the decision power of design was handled to the application, databases each time got smaller and focus and its own system and free of structures that holds some kind of business rules. In this new world is still worth have some kind of centralizing tools to manager databases? Well, at this point I found SchemaSpy and really believe that the answer is no.

Schamespy is an application build on Java that scan you entire database and generates a static web site containing all tables, views, functions and relations between them besides creation script from some. Imagine “to browse” through your databases in a totally visual way included graphics and simple anomaly analysis. Yes it’s the guy.

Let’s put our hand on it. Will be necessary install graphviz, java and download the last versions from SchemaSpy and the jdbc drive from the database that you want document, that simple. The basic call is by command line as showed below

java -jar schemaspy.jar -t mssql05 -dp C:/sqljdbc4–3.0.jar -db DATABASE -host SERVER -port 1433 -s dbo -u USER -p PASSWORD -o DIRECTORY

from the command we have to see -t that indicates which databases type, you can check all supported here, -dp this one indicates where is the jdbc drive or the folder and -o indicates the destiny to generates the static site. We also have -s to indicate schema, to sql server is not mandatory but to mysql is and pretty helpful to oracle.

Until here everything is perfect isn’t? Well, not for me, when I found this solution helps me a lot, but shortly thereafter came to my mind that my database server hold more than 40 databases and schemaspy just document one per time. Behold, it came to me an idea to wrapper schemaspy in my own solution to at least documenting a whole server per time and was born Database Diagrams.

Being straight forwards is a docker image with nginx to show the sites, a cron job to schedule and a small java application to configure and manager schemaspy executions. How to use? The image is available on docker hub

docker pull krismorte/databasediagrams:2.0

If you want to test first i recommend clone the git repo and run the docker-compose

git clone https://github.com/krismorte/database-diagrams.git

cd database-diagrams

docker-compose up

Up a container with your won configuration,first of all configure one or more .prop files as I’m showing you below

schemaspy.db.type=mysql

db.server=172.17.0.1

db.user=root

db.password=secret

from there you need to call you container indicating the folder with all .prop files

docker run -it — rm -p 80:80 -v $PWD/dbconf:/dbconf — name databasediagrams krismorte/databasediagrams:2.0

now enjoy your documented databases documenting http://localhost/

At first sight on schemaspy I wanna contribute to the project but I didn’t get the time yet. My solution wasn’t perfect because just coordinate the schemaspy executions so there’s a lot of javascript duplication. Who knows when I finally will contribute to the project making my solution faster and smaller but for now it’s get the job done.

There’s a Portuguese version here

Musician, DBA, developer, tech enthusiast and maybe devops

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store