For a while now I’ve been looking for a tool which I could point to a database and it will generate a schema graph, now I usually use Navicat Data Modeler to initially design schemas which has this functionality but costs a premium of which I could not justify. So I went looking for an open source alternative, unfortunately most of the dependencies that these open source tools required weren’t available in the MacPorts repository or I had difficulties installing them.
Finally someone recommended today SQL Alchemy with SQL Alchemy Schema Display which I have heard of before in my brief encounter in the Python world and I knew it was a solid choice. I was sold and so these are the steps I took to get it installed on my setup
Please note this guide makes the assumption you’re using MacPorts on OS X with Python 2.7 and Pip
SQL Alchemy depends on GraphViz, which is available in MacPorts, but for some reason it requires you to deactivate “nawk” port to install it, why it couldn’t do this automatically and then re-active it is beyond me, perhaps it’s a bug. If you don’t have nawk you can simply run sudo port install graphviz.
Depending on what database you want to connect to you will need to install the required driver, I use MySQL at my place of employment but PostgreSQL personally so I needed both.
To enable MySQL support you need mysql_config in your $PATH variable, in my case MacPorts names mysql_config to mysql_config5 to possibly prevent conflicts. An alias may work, but I decided to create a symbolic link, if anyone knows of a better solution let me know.
To enable PostgreSQL support you need to install this pip module, no issues.
Another dependency is pyparsing, however by default MacPorts installs the latest which is 2.x and will not work with Python 2.7 it seems.
SQL Alchemy Schema Display depends directly on PyDot but PyDot indirectly depends on PyParsing so we need both.
Finally, the tool that will use all these components.
Now to generate an image of your schema you need to provide a host, engine, database name, username and password. I’ve created a sample script below which I use just populate your details and run “python /path/to/file.py”
That’s it, you’ve now got an image of your database schema with relationships and indexes or data types depending on your configuration. Hopefully I didn’t miss any of the dependencies as when I was installing this it was a lot of trial and error. If you do spot an error contact me on Twitter and I’ll happily edit it with accreditation.