Auto Generate Database Diagram MySQL

Question

I'm tired of opening Dia and creating a database diagram at the beginning of every project. Is there a tool out there that will let me select specific tables and then create a database diagram for me based on a MySQL database? Preferably it would allow me to edit the diagram afterward since none of the foreign keys are set...

Here is what I am picturing diagram-wise (please excuse the horrible data design, I didn't design it. Let's focus on the diagram concept and not on the actual data it represents for this example ;) ):

diagram see full size diagram

You can also use in-build [database designer](https://www.devart.com/dbforge/mysql/studio/database-designer.html#header) in dbForge Studio for MySQL for this purpose. — Devart, Sep 11 '18 at 10:37
DBVisualizer has a clean presentation of tables in its References section. It shows tables with all the relations neatly stacked and reader-friendly. — Harish, Jul 28 '21 at 04:48

score 438 · Accepted Answer · edited Jun 25 '18 at 07:53

438

Try MySQL Workbench, formerly DBDesigner 4:

http://dev.mysql.com/workbench/

This has a "Reverse Engineer Database" mode:

Database -> Reverse Engineer

edited Jun 25 '18 at 07:53

vijay

10,276
11
64
79

answered Aug 05 '08 at 15:48

Michael Stum

177,530
117
400
535

Takes "7 days or longer" to be approved for the download...? :/ – ingh.am Feb 22 '10 at 01:32
8

@James: Still works fine, click on Download Workbench 5.2.16 (upper right corner), then on "No Thanks, just take me to the downloads" and it should work - at least for Windows, haven't tried any other platform. – Michael Stum Feb 22 '10 at 01:40
6

@michael-stum I find MySQL workbench very helpful, but find that the reverse engineer database function doesn't automatically link the tables so the tables still requires rearranging. – David LeBauer Dec 14 '10 at 18:45
2

My tables have non-standard key field names. The reverse engineering feature did not work at all. Doesn't really matter to me. I find it problematic to link the tables to the right fields manually though. Overall its not really commercial grade app. Anyone find it useful let us know. – Andy N Feb 08 '11 at 20:30
6

Note that this is in the Ubuntu repositories as mysql-workbench. – mlissner Feb 27 '13 at 06:45
2

I have 237 tables. it seems to work and generate the diagram but it can't do the auto-layout and then it crashes. – Jan 03 '14 at 00:58
3

I don't usually have any problems with the program-- then again I started being better at normalizing my tables and not doing anything 'creative' when I started using it. The diagrams it creates are nice. The gripe I have with it is that I have to move every table representation into position, everything at the very least needs a tweak. That being said, for quick and dirty no rearranging just for me, I use SchemaSpy. Workbench is pretty, professional looking, and great for giving to people that need superficial pretty. The caveat is that it takes time and why I use other tools for me. – BradChesney79 Jun 20 '14 at 01:41
This is wonderful... I just done 2 assignments for college at once :) Wish I knew about this a couple weeks ago! – alexander7567 Aug 19 '14 at 01:54
1

Dude this saved me hours of working. Had a intense db that I needed some documentation for and a visual chart for those who didnt understand things. Took minutes. THANK YOU! – DevNinjaJeremy Aug 22 '14 at 18:05
38

You'll find this in "Database -> Reverse Engineer" menu under the DB tabs. – h2ooooooo Oct 14 '14 at 13:06
12

You don't even need to connect to the database for this. Export your MySQL database using the "structure only" option. In MySQL Workbench go to "File -> New Model", then "File -> Import -> Reverse Engineer MySQL Create Script..." and check the "Place imported objects on a diagram" box. – Liam Aug 10 '15 at 17:06
1

I'm working on a database with 545 tables and 16 views. It seems like this layout is limited to a physical paper size and doesn't seem to like anything like 100"x200". Is there a way to layout very large diagrams? – jocull Sep 02 '16 at 16:58
this looks like an overkill to me – phil294 Feb 20 '17 at 02:18
@jocull, see https://stackoverflow.com/q/4933000/82216 – May 16 '18 at 22:18

score 67 · Answer 2 · edited Jun 24 '21 at 19:24

67

I've recently started using https://github.com/schemaspy/schemaspy . It strikes me as having a good balance between usability and simplicity. (GraphViz now optional)

edited Jun 24 '21 at 19:24

Hernán Eche

6,529
12
51
76

answered Mar 11 '11 at 00:50

Reinderien

11,755
5
49
77

4

I am using it too it's great tool, here how configure it http://progrnotes.blogspot.com/2011/05/auto-generate-databaseer-diagram-mysql.html – Sergey May 08 '11 at 15:01
1

schemaspy creates interractive schema (see http://schemaspy.sourceforge.net/sample/) that is simply static html files that can be stored and used everywhere. MySQL Workbench do not create interractive schema (only image files or pdf) and on top of that do not automatically and nicely rearrange the tables. For automation (nightly builds create updated schema), schemaspy fits the bill too as it is entirely command-line. – Long Vu Nov 20 '14 at 20:19
+1 This was the only tool that did what I wanted: generate DB documentation at release time automatically. – Mr. Developerdude Apr 14 '15 at 09:12
It strikes me with amount of errors I am getting and installations I need to do to make it work on Linux. – 3xCh1_23 Dec 07 '16 at 17:45
1

@DamirOlejar you need to have a stable graphviz (alias dot) release installed, I do not recommend compiling it yourself. this will lead to a different version which does not recognize pngs and thus will fail schemaspy. / anyway, once it's set up, this tool is pretty neat +1 – phil294 Feb 20 '17 at 03:22
@Blahurim, +1 for a reply, however a lot of time has passed since and I have no time to test it. Thanks, I hope it helps someone else. – 3xCh1_23 Feb 20 '17 at 04:07
There's also SchemaCrawler. It's an and up-to-date alternative to SchemaSpy. https://www.schemacrawler.com/ – vctls Mar 12 '19 at 08:55

score 26 · Answer 3 · answered Sep 22 '08 at 01:48

26

On a Mac, SQLEditor will do what you want.

answered Sep 22 '08 at 01:48

mlambie

7,467
6
34
41

It seems to have the same problem I had with MySQLWorkbench, the relation between the tables is not displayed. – Dec 16 '19 at 02:19

score 6 · Answer 4 · answered Jul 27 '09 at 13:51

6

This http://code.google.com/p/database-diagram/ will reverse engineer your database. Just do an export 'structure only' then paste the SQL into the tool.

answered Jul 27 '09 at 13:51

Does not connect the foreign keys (mysql), simply displays the tables with values, and does not allow for a greater screen to fit them all together. – 3xCh1_23 Dec 07 '16 at 17:51

score 6 · Answer 5 · edited Oct 25 '12 at 18:50

6

Try MySQL Maestro. Works great for me.

edited Oct 25 '12 at 18:50

Kijewski

25,517
12
101
143

answered Mar 14 '11 at 21:30

Juan Ignacio

3,217
8
33
53

score 4 · Answer 6 · answered Jul 11 '14 at 10:31

4

In MySql Workbench (6.0) its possible generate one diagram based on tables created. For that you should access to the tools bar, press Model and forward Create Diagram from Catalog Objects and done!

answered Jul 11 '14 at 10:31

Hugo Silva

517
1
6
11

score 4 · Answer 7 · answered Aug 05 '08 at 15:48

4

I believe DB Designer does something like that. And I think they even have a free version.

edit Never mind. Michael's link is much better.

answered Aug 05 '08 at 15:48

Mark Biek

146,731
54
156
201

score 4 · Answer 8 · answered Feb 20 '12 at 11:45

4

MySQL Workbench worked like a charm.

I just backed up database structure to SQL script and used it in "Create EER Model From SQL Script" of MWB 5.2.37 for Windows.

answered Feb 20 '12 at 11:45

Tauri Alfa

51
2

score 3 · Answer 9 · answered Jun 01 '12 at 14:30

3

Visual Paradigm for UML 9.0

It's awesome I used to work with mysql bench but for big databases (something like more than 300 tables) won't work very well but visual paradigm reverse database works so much better

answered Jun 01 '12 at 14:30

mb2015

168
4

Which edition of VP do you have? I have an old copy of v7 but in that version the reverse engineering is only in the (expensive!) enterprise edition. – Guy Rixon Nov 08 '12 at 16:23
Can I generate the diagram from a live database using the community edition? – David Ferenczy Rogožan Nov 08 '19 at 19:04

score 3 · Answer 10 · answered Aug 31 '12 at 13:59

3

phpMyAdmin has what you are looking for (for many years now): It takes a small bit of configuration, but gives you additional benefits too: http://www.phpmyadmin.net/documentation/#pmadb

answered Aug 31 '12 at 13:59

Chris Murray

31
1

Bartek · Answer 11 · 2014-04-12T16:13:40.797

3

Try out Vertabelo!

It's an online database modeler that supports reverse enginnering.

Just create free of charge Vertabelo account, import an existing database into Vertabelo and voila - your database is in Vertabelo!

It supports following databases:

PostgreSQL,
MySQL,
Oracle,
IBM DB2,
HSQLDB,
MS SQL Server.

edited Apr 12 '14 at 16:13

answered Apr 12 '14 at 16:06

Bartek

277
2
4

score 0 · Answer 12 · answered Oct 07 '09 at 10:04

0

Try SchemaBank. They support reverse engineering too.

answered Oct 07 '09 at 10:04

Leigh Pyle

238
2
4

1

seems to be discontinued – Wolfgang Jacques Oct 06 '19 at 09:33

score 0 · Answer 13 · edited Aug 06 '16 at 07:54

0

The "Reverse Engineer Database" mode in Workbench is only part of the paid version, not the free one.

edited Aug 06 '16 at 07:54

Michael Currie

13,721
9
42
58

answered Aug 05 '08 at 16:08

John Downey

13,854
5
37
33

37

If this was true, it is no longer. – Ian Hunter Dec 07 '11 at 19:58
1

It works fine on a fresh, free copy of Workbench downloaded today. – Guy Rixon Nov 08 '12 at 16:21
Yeah, it works in the free version, but since it is fairly opinionated about modifying the schema for certain types of relationships so it is not as useful as you might think for documenting an existing schema that does not follow its conventions. – jerseyboy Feb 21 '14 at 19:24
I am not sure where to find the sql bench version with reverse engineering tab, as per link it is supported on commercial version only not on community https://dev.mysql.com/doc/workbench/en/wb-data-modeling-menus.html :( – Dreamweaver Jan 04 '18 at 07:55

score -1 · Answer 14 · answered Sep 09 '14 at 10:13

-1

Here is a tool that generates relational diagrams from MySQL (on Windows at the moment). I have used it on a database with 400 tables. If the diagram is too big for a single diagram, it gets broken down into smaller ones. So you will probably end up with multiple diagrams and you can navigate between them by right clicking. It is all explained in the link below. The tool is free (as in free beer), the author uses it himself on consulting assignments, and lets other people use it. http://www.scmlite.com/Quick%20overview

answered Sep 09 '14 at 10:13

Ludovic Aubert

9,534
4
16
28

3

Currently that domain does not exist – dstudeba Sep 15 '16 at 16:01
@dstudeba, this project is now located here:https://github.com/ludoaubert/linkedboxdraw – Ludovic Aubert Jul 08 '21 at 08:25

Auto Generate Database Diagram MySQL

14 Answers14

Linked