Over the last week I've been finalizing the SQL schema for a new database-driven web project. The client is technically savvy to a degree, so I decided to make a graphical representation of the schema. There are lots of tools to make such graphics, but I decided to try a Perl one that I had recently discovered: SQL::Translator.
This package parses SQL schemas (i.e., a sequence of "create table", etc commands) from many different SQL dialects. It has a Producer interface that allows you to export the schema to either another SQL dialect or some other representation. In my case, I chose the GraphViz export option to make a pretty picture.
I had to fight with it a little to get a font that my Mac-Fink install of graphviz would understand (see RT #22028 for a patch against SQL::Translator v0.08_01). But after that, I'm able to produce useful and readable schema diagrams as PNG or SVG from the command line.
As an example, below is a trivial schema. The following command:
sqlt-graph -o schema.png -t png --font-name=cour -d MySQL schema.sqlcreates a PNG representation from that schema.
create table client (
id int primary key,
first_name char(50),
last_name char(50)
);
create table account (
id int primary key,
client_id int not null references client (id),
balance decimal(10,2),
accttype enum('checking','savings') default 'checking'
);
create table transactions (
id int primary key,
fromaccount_id int not null references account (id),
toaccount_id int not null references account (id),
amount decimal(10,2),
status enum('pending','completed') default 'pending'
);