Documenting Databases With Schemaspy
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
