When working on bigger systems it helps having your database model visualized. There are many great solutions but a few of them are free.
If you fit in the “small enough” not to use ER/Studio Data Architect (the best tool IMHO) but big enough to want to see data visualized-category, then read on…
As you can see you’ll end up with this nice crow’s foot notation.
We have a PostgreSQL database that we need to visualize via reverse-engineering. The whole project is in Rails 5.
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.
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
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.Category: Database, Rails