How to Create an ER Diagram for a MySQL Database with Free Tools
Nov 20, 2018
For a new PHP course I’m working on, I created a poetry website. For the manual, I needed to create an ER diagram for the MySQL database on a Mac. I found the following options, both of which are free:
While the community edition of MySQL Workbench does not include a simple feature for creating Entity Relationship diagrams, there is a way to do it. Tushar Soam documented it in Create ER Diagram of a Database in MySQL Workbench and it worked fine for me. The only downside I found is that the relationship connections do not point to the primary and foreign key fields. While you can move things around, you do not have fine control over those lines.
The steps for creating the ER in MySQL Workbench Community Edition are:
- Select Reverse Engineer from the Database menu.
- Select your connection and click Continue.
- It will fetch the information it needs. Click Continue.
- Select your database schema and click Continue.
- It will retrieve the information it needs. Click Continue.
- Leave Import MySQL Table Objects and Place imported objects on diagram selected and click Execute.
Here’s the diagram it created for me (after a little manipulation):
Sequel Pro is a free, open source database management application for MySQL on the Mac.
Graphviz is a free, open source graph visualization software that runs in the Mac Terminal.
To create the ER diagram:
- Open the database in Sequel Pro.
- Select File > Export….
- Click on the Dot tab:
- In Terminal, navigate to the folder to which you exported the .dot file and run:
dot -Tsvg poetree.dot > poetree.svg
“poetree” is the name of my database. You should change this to the name of your database.
- Open the resulting SVG file in your browser.
Here’s the diagram it created for me:
I prefer this one as it makes it clear which fields the tables are connected on. However, I didn’t initially have Graphviz installed. To install it, I needed to install MacPorts. And to install MacPorts, I needed to install Xcode from the Apple Store. It took some time, but I’m happy with the ER diagram.