Good tool to visualise database schema?

Question

Are there any good tools for visualising a pre-existing database schema? I'm using MySQL if it matters.

I'm currently using MySQL Workbench to process an SQL create script dump, but it's clunky, slow and a manual process to drag all the tables about (which would be okay if it wasn't so slow).

there is no alternative to reading the thing... unless you think this is helpfull http://apidocs.sugarcrm.com/schema/6.5.14/ce/tables/campaign_log.html — gcb, Oct 26 '13 at 23:53
[I've asked this question again, on softwarerecs.stackexchange.com](http://softwarerecs.stackexchange.com/q/1747/1008), and highly welcome any alternatives to my own current solution. — Jeroen, May 22 '14 at 18:34
[Visualize mysql schemas](https://www.devart.com/dbforge/mysql/studio/database-designer.html) using dbForge Studio for MySQL. Database Designer is included to the free Express edition. — Devart, May 31 '18 at 07:44
MySQL workbench is pretty good in my opinion. I followed this tutorial and was able to create a decent ER diagram. "Create ER Diagram of a Database in MySQL Workbench": https://medium.com/@tushar0618/how-to-create-er-diagram-of-a-database-in-mysql-workbench-209fbf63fd03 — uudaddy, Oct 15 '20 at 09:25

score 89 · Accepted Answer · edited Mar 16 '21 at 04:03

89

I found SchemaSpy quite good - you have to run the script every time schema changes but it is not so big deal.

As pointed out in the comments there is also a GUI for it.

Another nice tool is SchemaCrawler.

edited Mar 16 '21 at 04:03

user13158507

5
2

answered Jan 11 '09 at 15:40

rkj

8,787
2
29
35

3

Thanks, I thought the command line interface was kind of a pain, of course once I got it working I noticed the link to the GUI off the front page, http://www.joachim-uhl.de/projekte/schemaspygui/. It should also be noted that program inserts Google ads into the resulting HTML. – James McMahon Apr 14 '09 at 20:20
1

This is good. And its worth noting that plenty of yak shaving must be done to get it working. – Ken Prince Dec 10 '14 at 23:35
This is great! It was easy to get it working. Just follow the instructions. Also, it should be noted that it works not only with MySQL and: DB2, Derby, Firebird, HSQLDB, Informix, MaxDB, MS SQL, Oracle, PostgreSQL, SQLite, Sbase, Teradata. – iled Feb 02 '16 at 21:27
Save yourself the work and go with DbVisualizer (http://www.dbvis.com/). It works with a variety of databases including SQLite, and their EULA allows the free version to be used for commercial projects. – Aralox Jul 29 '16 at 05:05

score 17 · Answer 2 · answered Nov 30 '10 at 11:03

17

I usually use SchemaSpy to do this, but recently I found a really simple article on sqlfairy that just uses the dump file to create the structure graph

answered Nov 30 '10 at 11:03

Hons

3,804
3
32
50

2

+1 for sqlfairy not required the JRE (like most of the other answers). – mikewaters Dec 02 '11 at 12:13
I just got what I needed from sqlfairy. Simple and quick. – fivedogit May 21 '15 at 20:37
sqlfairy can use huge amounts of disk space for large databases. I ran it for a database that was 615mb large and it broke down after having filled the 18gb that remained free on my harddisk. – Pascal Nov 25 '15 at 13:34
That's an old comment... Are this tools still under development? – Hons Nov 26 '15 at 10:43
sudo aptitude install sqlfairy (in ubuntu) – ozma May 10 '22 at 05:58

score 13 · Answer 3 · answered Jun 23 '09 at 13:23

13

I like this tool, called simply DbSchema. It's written in Java so it runs on OS X, Windows, or Linux. It's a little clunky, especially when it comes to printing, but from my experience they're all like that. This one is the best of the several I've tried. It makes nice, clear diagrams. Free trial. Costs about $120 depending on how many licenses you buy.

answered Jun 23 '09 at 13:23

Ethan

57,819
63
187
237

Excellent tool. There's also free edition that has most of the great features (synchronization with live db for example). – wheleph Apr 29 '10 at 14:48
this is a great tool, it can even generate virtual foreign keys, wow! – Nassim Jul 09 '15 at 09:04

score 11 · Answer 4 · answered Sep 19 '12 at 14:01

11

on Mac OS X you can use Sequel Pro

answered Sep 19 '12 at 14:01

1

`+1` Sequel Pro is highly recommendable. – Anne Sep 19 '12 at 23:24
6

I think this answer needs extra explanation: http://www.norbauer.com/rails-consulting/notes/erd-diagrams-from-sequel-pro.html – fguillen Jun 14 '13 at 10:24
1

Thanks @fguillen! Let's add the explanation right here (before the link might break…). It's only 4 easy steps: **1.)** Install [graphviz](http://www.graphviz.org/), it's available in MacPorts (`sudo port install graphviz`) and in Homebrew (`brew install graphviz`). **2.)** Export Dot file (in SequelPro go to File > Export > Graphviz Dot and export your database) **3.)** Open terminal and convert dot file to SVG `dot -Tsvg your_database.dot > your_database.svg` **4.)** Optionally convert generated SVG to JPG or PNG using any tool of your choice (Inkscape, ImageMagick, GraphicsMagick, etc.) – Arvid Nov 24 '16 at 13:22

score 9 · Answer 5 · edited Jun 06 '12 at 11:35

9

I would recommend Toad data modeller

edited Jun 06 '12 at 11:35

garrow

3,459
1
21
24

answered Jan 11 '09 at 15:36

Kb.

7,240
13
56
75

score 5 · Answer 6 · edited May 23 '17 at 12:34

5

How about the SQuirreL SQL Client? As mentioned in another SO question, this programs has the capability to generate a simple ER diagram.

edited May 23 '17 at 12:34

Community

1
1

answered Jan 11 '09 at 16:33

Marc Novakowski

44,628
11
58
63

score 2 · Answer 7 · answered Jun 23 '09 at 14:12

Visio professional has a database reverse-engineering tool built into it. You should be able to use it with MySQL through an ODBC driver. It works best when you reverse engineer the database and then create the diagrams by dragging them off the tables and views panel. It will drag any foreign key objects and put them on the diagram as well.

score 2 · Answer 8 · answered Aug 02 '09 at 07:38

2

I'm start to create own Perl script based on SQL::Translator module (GraphViz). Here are first results.

answered Aug 02 '09 at 07:38

mj41

2,459
2
16
10

score 2 · Answer 9 · answered Jun 02 '10 at 16:22

2

A different approach, but if you're using Ruby on Rails try RailRoad: http://railroad.rubyforge.org

answered Jun 02 '10 at 16:22

tmarkiewicz

515
5
12

You could also checkout [Schemapper](http://github.com/NathanielWroblewski/schemapper) for Rails 3.0+ – RubeOnRails Jan 04 '14 at 02:39
It appears that railroad is dead; long live railroady. https://github.com/preston/railroady `gem install railroady` – Duke Feb 18 '16 at 21:25

score 2 · Answer 10 · edited Aug 03 '19 at 05:09

2

Adminer (formerly phpMinAdmin), the web application for managing MySQL databases, draws simple diagram.

The software itself is similiar to phpMyAdmin, but has more features, its lightweight and it comes in single PHP file.

edited Aug 03 '19 at 05:09

Glorfindel

21,988
13
81
109

answered Jul 20 '10 at 21:44

Tom Pažourek

9,582
8
66
107

1

Maybe this works for trivial schemas, but I tried it on a schema with 28 tables and it just gave me a purely vertical list of all the tables in alphabetical order, with a mess of vertical lines attempting to illustrate relationships. Couldn't be any more useless. – Dolph Dec 31 '10 at 07:05
Exactly, in my case 40 tables. You can click to drag it, but once you select, you cant de select. – Ziyan Junaideen Feb 17 '13 at 09:11

score 2 · Answer 11 · answered Nov 14 '10 at 17:00

I looked for a long time for a decent, and preferably free, tool for linux and found this java application that is quite good (finally!!):

http://sqldeveloper.solyp.com/

Being Java it is cross-platform (I run it on Linux with no issues) and it will connect to any database you can get a JDBC driver for. ie: pretty much any database.

It is quite easy to import your database and get a visual (ERM) of the database schema. The auto-layout feature is good as well, but note that it is not done automatically and you need to click the "automatic layout" button after importing your objects into the diagram.

The application is also a pretty good generic database administration/browsing tool. As one small example, I use it instead of pgadmin for some base development work because of simple niceties like the column width of SQL query results automatically sizing to fit content (which drives me crazy in pgadmin).

I agree with this. I've been looking for any GUI tool to visualize postgresql databases on linux and most of them fall flat on their face. I've just started using SQL Developer but it is the best free option I've found so far. — Adam Plumb, Feb 25 '11 at 16:03
I can't provide a new answer since the question is now closed, but the tool you *actually* want is DbVisualizer (http://www.dbvis.com). It is fantastic... far better than what I recommended in my question (if my usage over the last few years is any indication). — Russ, Jul 12 '13 at 18:26

score 2 · Answer 12 · answered Jan 11 '09 at 16:11

2

Have you tried the arrange > auto arrange function in MySQL Workbench. It may save you from manually moving the tables around.

answered Jan 11 '09 at 16:11

Brian Fisher

23,519
15
78
82

2

Yeah, it kinda helped but wasn't great. – Mat Jan 11 '09 at 20:48
Worth a try, I always end up arranging myself too - but sometimes it can get me started. – Brian Fisher Jan 12 '09 at 07:47

score 2 · Answer 13 · answered Mar 03 '09 at 08:49

2

DeZign for Databases might be interesting for you. You can reverse engineer and modify existing databases. Has got an auto-layout function and diagram layout is not meshed up when synchronizing your data model with the database.

answered Mar 03 '09 at 08:49

Please note, that the autolayout feature is not suited for bigger databases - it is just a big mess. (e.g. the space for the diagram seems to be limited and thus there are lots of tables crammed on the right border (in our case 75 tables in one place). I played around with the different layout options but the result is not good in any of the options. – Lonzak Jan 21 '14 at 10:01

score 1 · Answer 14 · answered Aug 02 '09 at 07:45

I use SQL::Translator with DBIx::Class

I have DBIx::Class in my toolchain anyway, and combining it with SQL::Translator allows me to convert the schema into lots of different formats. Mostly initialization SQL scripts for different database servers (making it easy to develop on SQLite and move to Postgresql for production) but it can output GraphViz data too, so diagrams are trivial to generate.

score 1 · Answer 15 · answered Aug 03 '09 at 18:20

ER/Studio by Embarcadero is one of the costlier ones, but the hierarchical mode it present is by far the best one for understanding database models. It makes query writing the easiest task in the world.

It also is incredible with normalization, denormalization, warehousing, documentation, etc.

The downside is that it is a pretty expensive tool especially when you go multiplatform.

score 0 · Answer 16 · answered Jul 31 '12 at 14:29

0

Try PHPMyAdmin which has some really nice visualisation and editing feature. I am pretty sure you can even export to exel from it.

answered Jul 31 '12 at 14:29

byteSlayer

1,806
5
18
36

score 0 · Answer 17 · answered Aug 02 '09 at 07:41

0

SchemaBall is doing visualisation its own interesting way.

answered Aug 02 '09 at 07:41

mj41

2,459
2
16
10

score 0 · Answer 18 · answered Jan 11 '09 at 15:22

When you say visualize the database schema, are you referring to a text-document of all your tables, sprocs, etc., or are you referring to a visual representation of your schema and dependencies?

I know you mentioned MySQL (and it is very possible that it is supported now, but I am not sure), but RedGate has a number of tools that make that pretty simple. It's SQL Doc program can take your entire database and create full-blown database documentation for it. SQL Dependency Tracker can give you a visual representation of all your tables and show their linkings, or you can load in just certain tables or sprocs to show what is using them or what they are using themselves.

I was after a visual graphical representation rather than a full-blown database doc, but still sounds interesting (even if it is SQL Server only - have some other stuff using that). — Mat, Jan 11 '09 at 16:17

score 0 · Answer 19 · answered Jan 14 '09 at 13:57

Years ago, I used to use Data Architect. I don't know if it's still out there.

You could reverse engineer an existing schema into a relational table diagram.

Or you could go even further, and reverse engineer an Entity-Relationship model with an accompanying diagram. ER diagrams were really useful to me when discussing the data with people who were neither programmers nor database experts.

Sometimes a few manual fixups to the ER model and ER diagram were necessary before it was a useful communication tool with stakeholders.

score 0 · Answer 20 · answered Feb 27 '12 at 21:48

0

I tried DBSchema. Nice features, but wildly slow for a database with about 75 tables. Unusable.

answered Feb 27 '12 at 21:48

deeeptext

41
6

Good tool to visualise database schema?

20 Answers20

Linked