screen shot 2017-01-26 at 11 26 02

As you can see you’ll end up with this nice crow’s foot notation.

Starting position

We have a PostgreSQL database that we need to visualize via reverse-engineering. The whole project is in Rails 5.

1. Check that you’ve enforced database constraints

When using Rails it is easy to map ER entity connections via Active Record Associations. Still, in order for our visualization to be successfully generated we need to have these constraints in our database as this is the only place the tool will be looking into.

If you don’t have constraints I suggest you make them anyways as I feel a database is a fortress and you should always protect your data at the lowest possible level. If you think about it, even if you lose all of your source code you can still somehow bounce back. If you lose your data, the whole project is doomed for sure.

For the Rails ecosystem you can use existing gems to help you create database constraints from Active Record associations listed in your model’s source code. For example with immigrant, you can automatically generate a migration that will add any foreign key constraints you might have missed.

2. SchemaSpy and Graphviz

The best tool I’ve found is SchemaSpy. In order to make it work I needed the correct version of Graphviz and PostgreSQL JDBC driver. On OSX I had to use version 2.36 of Graphviz.

To make it work:


    #execute in terminal
    sudo java -jar schemaSpy.jar -t pgsql -db gym_trainer_development -host localhost -dp postgresql-9.4.1212.jre6.jar -noschema -u postgres -o .

What you end up with is a nice-looking generated documentation in which you can see your models visualized. It’s also clickable. Each and every model can be inspected in detail

screen shot 2017-01-26 at 11 26 10

An easy way of publishing your documentation is by creating an S3 bucket and pushing it with the AWS client tool to end up with static web pages.

This is what I did (to push to bucket named accordingly):


    #execute in terminal
    aws s3 sync /Users/kbojcic/schema  s3://schema-gymtrainer/

If you still can’t decide whether or not this is a good idea for you, you can check out an example of a live database visualization here.

As a side note, if you don’t want your data to be public you can use http basic auth as explained here.

Updated:

Comments