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

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:

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.

About Webucator

Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, Azure, Windows, Java, Adobe, Python, SQL, JavaScript, Angular and much more. Check out our complete course catalog.