DEV Community

Willem van den Ende
Willem van den Ende

Posted on • Updated on

How to visualize a PostgreSQL schema as SVG with SchemaSpy

TLDR: Use the option -imageformat svg to generate an ER diagram in SVG format
with SchemaSpy.

SchemaSpy generates HTML documentation from your database schema, including
Entity Relationship diagrams. I find visualization helpful as a database model
grows.

Out of the box it produces diagrams in png format, like this one in the Hasura
ecommerce example
:

database schema with tables user, store, order, cart, product, inventory, and order item. the post linked above describes it in more detail.

I prefer SVG, because it scales, is smaller, and it is all text.

Using SchemaSpy to see what the relations are like is not complicated, once you
have downloaded the dependencies and figured out the command line options
. And
there are quite a lot of command line parameters. I had to download the
postgresql jdbc driver by hand, and specify the path with the -dp option:

<schemaspy-path>$ java -jar target/schemaspy-6.1.1-SNAPSHOT.jar \
  -t pgsql -host localhost -db postgres -o /tmp -u postgres -dp . -p postgres -imageformat svg
Enter fullscreen mode Exit fullscreen mode

The -t option is mandatory. Probably works for anything that has a JDBC
Driver. I use a local database inside a docker container that is only open to my
machine, so I left the passwords and the port at the default settings the
docker-compose configuration came with.

See the comment by Jon Lauridsen below on how to do all of this with a docker one-liner.

If you forget an option, Schema Spy will tell you what was missing. I stuck the
above in a shell script inside my project, so I can easily generate it again. I
left the output as /tmp, so when I reboot (rarely), I am forced to create a
fresh diagram. Next step: integrate it into the build script, so I get
a fresh diagram whenever I add database migration files in the migrations directory.

SchemaSpy uses graphviz to generate images, the .dot files it uses as input, and
the generated images can be found in <outputpath>/summary/

At first I had a one liner for this, and then while writing the post, I found
that SchemaSpy supports SVG natively. It is 'just' another output parameter:

-imageformat svg

For more details, see the Get Started page in the SchemaSpy documentation .

I hope you found this helpful. It took me a bit of searching to find SchemaSpy, and the svg option. I'd be interested to know better alternatives, if there are any.

Top comments (3)

Collapse
 
jonlauridsen profile image
Jon Lauridsen • Edited

Thanks for the article. I didn't want to download SchemaSpy so for anyone else interested you can run it straight via Docker:

docker run --rm -it schemaspy/schemaspy:latest --help
Enter fullscreen mode Exit fullscreen mode

I've cooked up this command to generate report from my locally-running DB:

docker run --rm -it -v "$(pwd)/output":/output  --network host schemaspy/schemaspy:latest -t pgsql -db <db> -host <host> -s <schema> -u <user> -p <password> -hq -imageformat svg
Enter fullscreen mode Exit fullscreen mode

Documentation is on schemaspy.readthedocs.io.

Collapse
 
andreasneuman profile image
andreasneuman

SchemaSpy is an interesting solution, I will definitely try it out, thanks. Now I'm using Studio for PostgreSQL with its reporting and visualizing tools, it works pretty well.

Collapse
 
sualeh profile image
Sualeh Fatehi