Easy Database Schema Diagrams with DBVisualizer

Matthew Magain
Tweet

I’ve never particularly liked databases.

Don’t get me wrong–I love using them. You know, filling them with data, watching my users fill them with data, deleting my users’ data* … but as long as it’s all from the safety of a server-side programming language–I’ve never been a big fan of interacting with them directly. All that SQL just gives me a headache.

Sure, we’ve come a long way from the SQL command line. We have web-based tools, like phpmyadmin and phpPgAdmin. And if you don’t fancy doing your heavy lifting over http, there are desktop tools like MySQL Query Browser and TOAD. And frameworks like Rails have migrations now, so we’ve got all the tools we need, right?

Well, if your brain works anything like mine does, then you’ll agree that sometimes it’s nice to have a “big picture” view of things–a diagram of your data goes a long way to helping understand how it fits together. If your application only consists of three or four tables, then you may already have one of these sketched out on a bit of paper. But none of the tools I mentioned above can provide us a picture of what our database actually looks like–which tables have foreign key constraints that point where. Once your database schema grows beyond a handful of tables, this can become a significant issue. And if you’ve ever had to get your head around someone else’s schema in a short period of time, you’ll know what I’m talking about–sure, you could try and visualize this in your head, or sketch it out with pen and paper after poking around using a bunch of queries. But now there’s no need to.

DBVisualizer is a slick database client that creates a schema diagram of your database. It’s written in Java, so runs on just about any platform, and it’s dead easy to set up and use. In this post I’ll show you how to install DBVisualizer and use it to produce beautiful schema diagrams of your MySQL database.

A schema diagram of the Habari project

To demonstrate how to create your schema diagram, I’ll make a few assumptions:

  • You’re able to connect to a MySQL database–either locally or on your production server.
  • You have a Java Runtime Virtual Machine installed on your local machine. No problem if you haven’t–you can either download and install one manually or choose a version of the DBVisualizer installer that includes it.
  • You already have a schema in your database of which you wish to create a diagram.
  • You’re using the InnoDB engine for your tables. You can still create a schema diagram if you’re using MyISAM, but it won’t reflect any foreign key constraints, as these are ignored by MyISAM. If you don’t know, you’re probably using MyISAM.

Follow these instructions:

  1. Download

    There are a few versions of DBVisualizer. DBVisualizer Free performs all of the visualization coolness, but contains limited features (such as only being able to perform one SQL query at a time–possibly enough to make you want to upgrade to a personal license if you get addicted to the other stuff). The version at the time of writing is 5.1.1.

  2. Install

    Installing DBVisualizer is as simple as it gets. Whether you’re running Windows, Mac, Linux or a general Unix platform, you have the choice of a wizard-style installer or just unpacking a compressed archive to the folder of your choice. That’s it!

  3. Configure

    DBVisualizer doesn’t come with a MySQL driver out of the box, but adding one is easy. Download the MySQL JDBC driver, and unzip the archive to a folder of your choice (there’ll be a jar file in there somewhere, which is what we’re after).

    Then launch DBVisualizer and select Driver Manager from the Tools menu.

    On the left, select MySQL, and in the User Specified tab of the Driver File Paths form, click the Browse icon and choose the jar file that we extracted earlier.

    Loading the MySQL driver

  4. Connect

    To connect to your database, choose Database > Create Database Connection. You can either follow the built-in connection wizard to establish your connection, or enter it manually. There’s not really much to it; all you need is:

    1. the server name (usually just localhost) and port number that your database runs on (usually 3306 for MySQL)
    2. your database username and password

    To enter this information manually, set Database Type to MySQL, select MySQL from the Driver (JDBC) dropdown, and enter your Database URL and username/password. The URL will take the format:

    jdbc:mysql://<host>:<port3306>/<schema>

    I thought I’d take a poke around the schema used by the Habari open source project. After installing it locally, I therefore used the following. The schema can be omitted if you don’t want to specify one by default.

    jdbc:mysql://localhost:3306/habari

    Click Connect, and your connection should be established!

  5. Visualize

    Now that you’re connected, double-click on the schema you wish to explore, select Tables and then click on the References tab to view your schema diagram (complete with pretty animations). You have some control over what gets included in the diagram, including being able to view the schema using a variety of layouts, zoom in and out, and include or exclude things like column details and foreign key names.
    An animated diagram of the database schema for the Habari project

That’s all there is to it! Whether you’re trying to flesh out a schema for an application of your own design, or trying to understand an existing application in order to write a plugin or fix a bug, an application like DBVisualizer can certainly make your database design, development and maintenance a heck of a lot easier.

*I was of course, just joking about deleting my users’ data. Well, mostly.

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • http://www.saumendra.com Saumendra Swain

    The Tool is Excelent and the interface used are cool. I think the mysql driver and the inclusion of MyISM database by default, will make this tool a rocking one.

  • http://espenliland.no Loomy

    On the very same day that I need a tool like this, sitepoint pops up in my bloglines with this. That’s what I call good timing :D

  • http://exotic4.nipne.ro/~iacobs/ m0n5t3r

    ieah, it’s a nice software, too bad most of the useful features are locked in the free version…

    I tried a few such interfaces in the past, and in the end I chose Squirrel SQL (open source query browser, also written in Java) for database shell/browse interaction, and Clay (an Eclipse plug-in that has decent reverse engineering capabilities and stores its data in an XML format that is reasonably easy to transform into a Propel schema, for instance, with XSLT – if you’re not fed up with Propel’s idiocies already, that is) for graphical database design.

    P.S. working on Habari? :D

  • http://www.ex-designz.net dexterz

    This is a very handy tool. How much is this software cost?

  • http://www.realityedge.com.au mrsmiley

    Hmmm … new ad block in the blogs. Annoying and very distracting (maybe it should be between the blog text and the comments, or below the comments box) … but I digress.

    I was under the impression that phpMyAdmin has a tool to draw DB schema diagrams? From memory it even exports to PDF. The only time I’ve ever played with it (a few years ago) it was a pain to use with no WYSIWYG editor, but it does have one nevertheless.

  • James

    Its still a shame that most of these miss the entire point of an entity relationship diagram (ERD), its a design document, not something you should have produced after you made the application but ‘before’ in your design phase.

    The reason you do this before is so you can normalize your database, can you really do that through phpmyadmin? Heck no. They also fail to show the critical part of an ERD which is the true relationships between entities. One to many, many to many, one to one, cyclic relationships and also not all foreign keys are defined or atleast done correctly in archaic applications.

    So the next time you are designing an app, even if its a blog, please learn ERD’s, and just use paper and a pencil or something like visio and do this ‘before’ you build your database, for the love of god i have seen some shockers in my time.

    Oh and for the author, the above is not usually referred to as a schema diagram, as a schema is your database engine specific code to implement the actual ERD.

  • hannson

    I use DBDesigner (which will eventually be replaced by MySQL Workbench) and export the database to a SQL file where I review the code. Then I use SQL Fairy (SQL::Translator) to create a ORM schema in perl code.

    It’s very quick when using a framework like Catalyst! It saves you a lot of time ;)

  • http://www.sitepoint.com Matthew Magain

    James, you make some good points, albeit in a quaintly abrasive manner. Like I mentioned in the post, it’s often the case that a developer is presented with the task of reverse engineering a database (which was how I stumbled upon the tool in the first place). No it’s no substitute for a proper database design tool (like Rational or the Oracle toolset for example) but it still should serve as a useful tool for many of the developers out there. Re terminology, I opted not to use the term Entity-Relationship Diagram, partly because, as you say, it doesn’t show true relationships, just foreign key constraints, but also because it’s a term more familiar to traditional software engineers rather than web developers. If you have another suggestion then I’m all ears.

  • http://exotic4.nipne.ro/~iacobs/ m0n5t3r

    James: actually, that’s what I stopped to Clay: I needed a design tool, to make teh pretty pictures before coding, and being able to skip implementing the schema by hand was also a plus (after I checked the sql output and saw that it’s what I intended ;)

  • Marc

    With regards to James’ comment (and Matthew’s reply) As web databases become more complex do you think it would be beneficial for web developers to learn such skills as ERD and normalisation?

  • http://www.digitalgreenlight.com busy

    @Marc
    Normalization is SO not an option, its a necessity. And it’s really more of a concept than a skill, kind of like the idea of separating content from presentation is more of a concept than a skill.

    If anyone doesn’t get what normalization is about you should go do some googling right now (or at least before you mess with another database) It’ll take you maybe 30 minutes to grasp the concept and you’ll enjoy an aha! moment when you apply what you’ve learned.

  • beechfielder

    Thank you very much for this. I have downloaded and am using this tool. My situation is that I work with a badly designed database and at the same time am studying database design. For my assessment this semester I need to write on the following ‘Assess
    the importance of data modelling and relational theory when designing
    and developing a relational database’

    The table reference view is very helpful, even though the tables in this particular are not InnoDB and so have no referential integrity restraints (oh how my life would have been easier if only it had them!), I can still see a visual layout of tables and columns. My plan is to create new ER Diagrams for my project and show where the current database could be improved. I will use Toad Data Modeller I expect, or just create them in a graphics programme.
    The application now has a MySQL driver by default.

  • Jeet

    hi,

    i used db2 for connection. I followed the steps as you mentioned.
    the ER diagram shows only the different tables but not the links interconnecting these tables. Do i need to configure that?

    Thanks

  • Anonymous

    Wow..! It’s an excellent tool. Definitely I will suggest my friends:)
    Thanks

  • http://twitter.com/loansbadcredit1 loans with bad credi

    Hi,

    This looks great, but it doesnt show the relationships? Do I have to configure the foreign keys somewhere?

    Thanks in advance

    Adam