Easy visualisation of database schemas using SQLFairy

BioSQL schema

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)

9 thoughts on “Easy visualisation of database schemas using SQLFairy

  1. Glad you dig our work. Try using the “–color” option to make it easier to read. Also consiser the “sqlt-diagram” for a simpler GD-based output. It doesn’t organize the tables as nicely as Graphviz, but sometimes it’s easier to see with a small schema. Viva la difference.

  2. Glad you found the post useful, dalloliogm. It is a nice way to view BioSQL – I wanted to view that schema in particular, as it’s quite complex.

  3. Hi Neil – you were probably aware that there is an ERD in the BioSQL distribution. I agree it’s not perfect as it’s strictly speaking not up-to-date, but the changes that have happened aren’t significant. More importantly, yours can actually be updated at any time :-)

    My gripes with SQLFairy are that i) has been crashing for me on the PostgreSQL and MySQL source scripts, and ii) I haven’t been able to get GraphViz to create actual ERDs that use the ERD conventions correctly. Any successful forays you’ve tried into either of those?

  4. Thanks for the info Hilmar. I have only dabbled with SQLFairy up to now, so can’t really comment on advanced usage or ERD conventions. Will look into it sometime.

  5. Couldn’t make it work with the ouput of mysqldump nor sqlyog.

    ERROR (line 58): Invalid statement: Was expecting comment, or use, or
    set, or drop, or create, or alter, or insert, or
    delimiter, or empty statement

    This is all I get when reading the mysql database dumps from my current project.

  6. Interesting – looks like it doesn’t like your dump, for some reason. I’d have a look at line 58 in the dump and see if there’s some syntax error.

Comments are closed.