Installing Schemaspy for Database Documentation
Database Management & Table Analysis
Effective database documentation is crucial for good database management and engineering practices. Proper documentation ensures that both existing staff and new hires can quickly understand and work with the database.
If you need a solution for detailed database documentation that includes constraints, tables, views, and more, SchemaSpy is worth considering. As your database grows, tools like PgAdmin or DBeaver can become cumbersome for tracking and auditing purposes. SchemaSpy provides a more efficient way to manage and document large databases and simplifies audits. It lacks is some areas which can be covered up by other applications.
There are many alternatives for database documentation, both free and paid. However, in this write-up, I’ll focus on setting up SchemaSpy in your Windows environment. This guide is based on my personal experience and the documentation I referenced during my installation and setup process.
Become a Data Mentor or Mentee with Semis
Prerequisites
Before setting up SchemaSpy, you need to have three applications installed on your PC. If you already have them, you’re good to go:
1. Java JDK
2. JDBC Driver
3. GraphViz
Become a Data Mentor or Mentee with Semis
Java
Schemaspy uses Java version 8 or higher which can be downloaded from https://www.oracle.com/java/technologies/downloads.
JDBC Driver
The JDBC (Java Database Connectivity) is specifically designed for use with Java, requiring Java installation on your system. The JDBC is included as part of the Java bundle, but each individual database requires a specific driver. You need to install the correct JDBC driver version for your database. In my case, I’m using Postgres.
JDBC is an API built to interact with the more familiar ODBC (Open Database Connectivity) API. When you download the driver, you get a `.jar` file, which is a Java file. This file should be moved from your Downloads folder to a generally accessible location. If you plan to deploy the application or share it with others, it might be worthwhile to place it in the project directory.
Note: The downloaded JDBC `.jar` file should be in the same folder as the downloaded SchemaSpy `.jar` file.
You can download the JDBC Postgres driver here.
GraphViz
As stated in the official installation documentation, GraphViz is necessary to render graphical representations and images of the database relationships. However, SchemaSpy version 6.1.0 and higher now includes viz.js, so you do not need to download GraphViz unless you are using SchemaSpy version 6.0 or lower.
For SchemaSpy version 6.1.0 or higher, simply include `-vizjs` as a command line argument when executing the SchemaSpy command.
Join Semis to mentor in big tech & AI
If you are using a version of SchemaSpy that requires GraphViz, make sure to add the folder containing Graphviz’s `dot.exe` application to your PC’s system PATH variable. You can do this by typing “env” in your start menu search and clicking “Edit the system environment variables”.
C:\Program Files (x86)\Graphviz2.38\binDownload Graphviz here.
Schemaspy
Download the schema jar file here. This download may start automatically.
Note: This file must be in the same folder as the JDBC `.jar file`
Join Semis to mentor in big tech & AI
Creating your Configuration File
For my PostgreSQL database, I’ve written the following configuration file (saved as config.file in the same folder as the jar file):
# type of database. Run with -dbhelp for details
#default config file run code-;
#java -jar schemaspy-6.1.0.jar -configFile ./config.file -o OUTPUTFOLDER
#how i ran mine
#my path PS C:\Users\Username>
#java -jar Documents\Project\schemaspy_dir\schemaspy-6.1.0.jar -configFile Documents\Project\schemaspy_dir\config.file -o OUTPUTFOLDER
schemaspy.t=pgsql
#schemaspy.dp=/usr/local/pgsql/share/java/postgresql.jar
schemaspy.dp = Documents\Project\schemaspy_dir\postgresql-42.5.4.jar
# database properties: host, port number, name user, password
#schemaspy.host=....
#schemaspy.port=...
#schemaspy.db=...
#schemaspy.u=....
#schemaspy.p=....
# output dir to save generated files
#schemaspy.o=output/
#schemaspy.o=C:\Users\Username\Documents\Project\schemaspy_dir
# db scheme for which generate diagrams
#schemaspy.s=listings
schemaspy.s=public
#schemaspy.s=information_schema
schemaspy.illegal-access=warn
schemaspy.imageformat=svgRunning Schemaspy
In Windows Powershell run this code to start Schemaspy
#edit to your system
java -jar Documents\Project\schemaspy_dir\schemaspy-6.1.0.jar -configFile Documents\Project\schemaspy_dir\config.file -o OUTPUTFOLDERI hope this helps with your installation. If you have any questions or need further assistance, feel free to reach out to me on LinkedIn or via email.
Join Semis to mentor in big tech & AI
Cheers,
Tolulade
Consultant
References:
