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. Ken Youens-Clark

    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. nsaunders Post author

    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. drycafe

    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. nsaunders Post author

    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. Saiyine

    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. nsaunders Post author

    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.