PgDot

Creates diagrams (using Graphviz) from a PostgreSQL database.

Why?

When developing with Django, I wanted to have some graphical way to design the Model classes and relationships. The "UML-like" approach would be to create a diagram and then write the Models to match them. But the translation would be a tedious and error prone process if done manually. There were a few tools to do it manually but they were very limited, meaning they couldn't help with the real interesting parts.

A much better solution was to write the models by hand, and create the diagrams with Django-Extensions. I usually wrote a one-line loop in the console window to update the graph every time I saved any Model file. It was a very quick workflow and would readily show the interdependencies not only of the models themselves, but also of each application module.

Lately I've been eyeing some database-backed tools that don't have any ORM; instead, they push more work into the DB. Arguably a much more efficient architecture, even if theoretically less scalable (for extremely high levels of scale). Missing the high-level view of the design, I chose to recreate the workflow experience with a small set of Database-to-Diagram tools.

Tools

So far, only the first and obvious one is working: a graph showing the fields and relationships between tables.

[x] Table names
[x] Fields
[x] Foreign keys
[ ] UNIQUE restrictions
[ ] Autogenerated fields
[x] Support multi-schema databases, each schema is a box surrounding its tables.

Ideas for other tools:

"birds eye" relationship diagrams, omitting any non-reference field
cardinality analysis. Maybe a hierarchy? some way to get a "gut feeling" of how costly joins and indexes become.
EXPLAIN. Not sure what to show, or where to get the statements to explain. From command line? from a file that the developer maintains as "interesting things"? from stored procedures?
Stored procedures interrelationships. Dependencies? (cascading) triggers?

Loose thoughts

I've never done any significant in-database coding. As many others, I consider putting complex code in stored procedures a sure strategy to make code unmaintainable. But at the same time, the high consistency of ACID foundation paired with the (relatively) high scalability of a declarative language is a tempting combination. Besides, if the system is adequately described by invariants, it should be almost straightforward where to add some constraint or trigger to support new features.

All that sounds really nice, and maybe it explains why such systems grow continuously until they become a huge mass that nobody can touch but everybody depends on. Often, it's seen as a failure in documentation; there's usually one or two "old timers" that can do anything with the system, and nobody else has any idea where to start. Perhaps throwing some visibility could help?

I usually mention that trigger-based hooks are a style of COMEFROM command: "the thing that makes GOTO seem well-structured". I have the notion that a graph showing dependencies and trigger chains could make it easier. And previewing how a change could make it "nicer" or "uglier" is much safer than trying and crossing fingers.