ER Diagrams from SQL files
A lot of people these days use graphical clients for creating their database schema's and therefor start with the Entity Relationship diagram and then have it generate the SQL for them. I prefer to write SQL directly as I seem to have an ongoing misunderstanding with most graphical clients but then I would need to somehow create an ER diagram for the SQL I wrote and this meant I'd double up on work a little bit. Today while searching for another application to do just what I want (and not cost me a cent) I found a post about something that will be extremely handy.
SQL Fairy is an ER Diagram creator for Mysql and other databases but uses the raw SQL files and includes relationships (like foreign keys etc).
Its not perfect by any means, but its easy and gives a great representation of the database relationships. The lines representing relationships actually go over the tables which is a bugger and as it outputs directly to an image, you cant edit it. But, for a quick and easy ER, its bloody good.
As noted in the post I found this little gem at, its super simple to install and use, particularly if you are using Linux. I am on Kubuntu (for the record, my distro of choice is Fedora) and installed it as easy as this;
sudo apt-get install sqlfairy
Then, to generate the diagram was as easy as this;
sqlt-diagram -d=MySQL -o=sample_schema.png schema.sql --color
Unfortunately, it did fall over when my schema contained a SOURCE within the SQL, but apart from that it was quick and simple enough for me.
Now, if I can find an app that does the same thing but allows me to edit my ER first and give me multiple saving options (XML, SQL, etc) then I'd be extremely happy.
sqlt Man page
sqlt-diagram Man page
EDIT: About a year ago, MySQL released an application named 'Workbench' for this very purpose. An ER diagram program with reverse engineering capabilities from .sql files. The application was used by me on Windows for a while before a Linux friendly version was released. But, when Sun bought MySQL earlier this year, they made most of the MySQL applications commercial and were charging for licenses. Thats why Workbench was not mentioned above.
I am pleased to announce that it appears Workbench is free for download again. More information on Workbench can be obtained at its website.
This is the same schema as the above diagram done in Workbench using reverse engineering from an SQL file.
No related posts.