How to Create an ER Diagram for a MySQL Database with Free Tools

See SQL: Tips and Tricks for similar articles.

In our Introduction to PHP course, students create 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:

MySQL Workbench Community Edition

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:

  1. Select Reverse Engineer from the Database menu.
  2. Select your connection and click Continue.
  3. It will fetch the information it needs. Click Continue.
  4. Select your database schema and click Continue.
  5. It will retrieve the information it needs. Click Continue.
  6. 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):
MySQL Workbench ER Diagram

Sequel Pro and graphviz

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:

  1. Open the database in Sequel Pro.
  2. Select File > Export….
  3. Click on the Dot tab:
    Sequel Pro - Export dot
  4. In Terminal, navigate to the folder to which you exported the .dot file and run:
    dot -Tsvg > poetree.svg

    “poetree” is the name of my database. You should change this to the name of your database.

  5. Open the resulting SVG file in your browser.

Here’s the diagram it created for me:
ER Diagram from Sequel Pro
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.

Written by Nat Dunn. Follow Nat on Twitter.

Related Articles

  1. Reset Root Password in MySQL on Windows
  2. How to Create an ER Diagram for a MySQL Database with Free Tools (this article)
  3. How to Round Up in SQL
  4. How to Concatenate Strings in SQL
  5. How to Select All Columns in a Row in SQL
  6. How to Check Multiple Conditions in SQL
  7. How Work with White Space and Semicolons in Simple SQL Selects
  8. How to Sort Records in SQL
  9. How to Write Subqueries (Simple and Correlated)