Database Schema representation using SchemaSpy - PostgreSQL Example

Today I will be writing simple still very much useful topic on how to generate DB Schema Documentation right away using SchemaSpy. I will demonstrate example with Postgres DB.

SchemaSpy is great Java based library to generate visual representation of database schema. It generates schema metadata and put it in nicely formatted HTML pages. This is useful in many situations mainly for people who try to understand DB structures where databases design is not so well documented. Also if you want to embed your DB Design in your application / product, you can generate schema documentation at run time, instead  embedding it as static document.

I will show an example how to generate schema visual representation using SchemaSpy with PostgreSQL database in example below.

Let's get started.

SchemaSpy works with major Databases including db2, Oracle, PostgreSQL, MySQL. List of all databases supported is listed on SchemaSpy page.

I am using sample PostgreSQL database of  Dell DVD Store which comes up with few tables and sample data. This database is available for free download at Dell DVD Store Database

Download SchmeSpy jar from SchemaSpy download page. In this example I am using SchemaSpy 5.0.0. For demonstration purpose, I generate schema of Dell DVD Store database  from command line.

Go to command line (I am on Windows OS). Run the SchemaSpy jar using java command providing bunch of parameters.

java -jar schemaSpy_5.0.0.jar -t pgsql  -host localhost:5432 -db dellstore2 -u postgres -s public -p ****** -o c:\java\schemaSpy\outputDir -dp C:\java\schemaSpy\postgresql-9.3-1101.jdbc4.jar -gv C:\java\schemaSpy\tools\graphviz-2.38\release




Different variables used for in above command are explained here.

• -t Database Type. You will find list of supported databases on SchemSpy documentation page.
• -db Database name
• -dp Location of database driver jar. I worked this example with Postgres. So I used location of jar for database driver.
• -s  Name of schema. If not provided, SchemaSpy try to generate schema metadata for schema matching to username provided with -u option.
• -u  User name to access database
• -p  Password to access database. It is optional to use. If you do not put this parameter, it prompts you before execution
• -o  Output directory where SchemaSpy generaes HTML files for schema representation.
• -gv Path of Graphviz program. SchemaSpy uses Graphviz to generate graphical representations of the table/view relationship. You can download Graphviz from here. If this option is not provided, still SchemSpy works to generate SchemaMetadata but graphical relationship will not be generated.

There are some less usable parameters which  are listed on SchemaSpy website.

Below are screenshot of how HTMLs look like.

That is it. 

In this article I gave an example of how to generate database schema details with SchemaSpy with example of PostgreSQL Database. I hope this will be useful.