Easy visualisation of database schemas using SQLFairy

BioSQL schema

Here’s a common problem solved: how to generate a pretty picture of your database schema. A Google search throws up all manner of home-brewed solutions using graphviz, perl scripts and so on. Or you can make life easier and simply install SQLFairy.

Under Ubuntu: as simple as “sudo apt-get install sqlfairy”.

Next, dump your database tables, e.g. for MySQL:

mysqldump -u username -p -d mydatabase > mydatabase.sql

Finally, for a PNG image of your schema:

sqlt-graph -f MySQL -o mydatabase.png -t png mydatabase.sql

Too easy. Example shown is the BioSQL schema.

update: if your schema lacks explicit foreign keys, try the –natural-join options (man sqlt-graph, man sqlt-diagram)