Wiki

Clone wiki

reldiagram / Home

PostgreSQL Relationship Diagrammer

This is a simple program to introspect a PostgreSQL database (or schema) and produce a Graphviz plot of the foreign key relationships. To paraphraze the USMC rifle creed, there are many programs like it but this one is mine. It all boils down to the observation that this query:

SELECT 
  tc.table_name as owning_table,
  ctu.table_name as destination_table
FROM
  information_schema.table_constraints tc,
  information_schema.constraint_table_usage ctu
WHERE
  constraint_type = 'FOREIGN KEY' AND
  ctu.constraint_name = tc.constraint_name

happens to produce a table that looks like this:

owning_tabledestination_table
barfoo
classdayssections
sectionsclasses
classesdepartments
sectionsinstructors
thingsthings

which, in turn, happens to correspond rather nicely to the Graphviz file:

digraph G {
  bar -> foo;
  classdays -> sections;
  sections -> classes;
  classes -> departments;
  sections -> instructors;
  things -> things;
}

and this file can at last be converted to various image formats such as PNG:

output

Existential Crisis

This project started life as a no-kidding 25 line Ruby script. The crux of the matter was basically this:

# connect to the database
def do_table(name)
  puts 'digraph G {'
  DBI.connect('DBI:Pg:dbhost', 'user', 'pass') do |db|
    db.select_all(sprintf(SQL_DOOM_TABLE, name, name)) do |row|
      puts '  ' + row ['owning_table'] + ' -> ' + row['destination_table'] + ';'
    end
  end
  puts '}'
end

do_table ARGV[0]

As usual, the Ruby code here is just north of utter trash. I edited the script each time I needed to run it. I hard-coded the database credentials. This is horrible, grotesque code. However, it took me about half an hour to write.

I'm on to day three or four of rewriting this 25 line script in Haskell. My codebase is nearing two hundred lines of code now. This is Haskell's fault, but not the way it's Java's fault because Java simply takes more lines; no, it's because Haskell makes me want to write a better program, and better programs require more code. This version has all the command line options you'd expect it to have if it came with the PostgreSQL distribution. All this complexity comes at a fairly high price.

Like most of my Haskell programs, I'm treating this as a didactic opportunity that happens to be a handy solution to a problem I run into periodically. I hope you find it useful somehow.

Note to the Reader

If you read the code and have suggestions, please share them with me! I am always hoping to learn more about Haskell. If you read the code and find any part of it confusing or just want to understand what I did or why, also please email me. I never tire of running off at the mouth about Haskell.

FAQ

Q. Can you please make it work for MySQL?

A. No. Please don't use MySQL. MySQL is the worst database in existence today.

Q. Can it be adapted to other real databases besides PostgreSQL?

A. Probably. I'm using the information_schema, which is in theory portable, and my queries are not all that sophisticated. However, I don't have anything else to experiment with and I'm not quite sure how to achieve database agnosticism with HDBC. If this is a need of yours, please email me and we'll collaborate on making it work.

Updated