Skip to Content

Database Modelling

I've been working on a web based tool to create database diagrams - aka Entity Relationship Diagrams. While I've had some success, I also have run into some issues. Tonight while I was browsing the web I came across Mogwai ERDesigner NG. This project has many more features than my simple application does, and most importantly to me at the moment is the ability to connect to an existing database and reverse engineer it to come up with the diagram. This step is a HUGE time saver when you are dealing with large databases. In addition, it allows your diagram to be exported to various image formats, including PNG and SVG. So I decided to give it a try. If you are not a developer or database designer, you should probably stop reading here. :)

The install instructions (located a little lower on the linked page above) are accurate and easy.

  1. Download the latest version of ERDesigner
  2. Run the following command:
    java -jar 
  3. Run the program. In my case, I found an icon in my menu under "Lost & Found". Failing that, drop to command line, change into the installation directory and run the "run.bat" or "run.sh" script.

Read the manual! It is located in the installation directory under the "userdoc" folder. Or it can be downloaded from the website.

BUT! Before you can do anything, you need to tell ERDesigner where your JDBC drivers are. (These are the java database connection drivers, for those who may not know Java terminology.) This may be a stumbling point if you have not had a need to install any JDBC drivers before. Luckily the fix is fairly simple.

On Ubuntu, you install the PostgreSQL and MySQL drivers with this command:

sudo apt-get install libpg-java libmysql-java

If you need other database drivers, there is probably a "libXXXX-java" package for it.

Once that is done, the driver files reside in the /usr/share/java directory on my KUbuntu workstation - your paths may be different. Armed with that information we can now boot up Mogwai ERDesigner NG and set it up properly.

  1. Set up the ClassPath:
    • Click on Database -> Classpath
    • In the resulting window, click the Add button (the folder icon on the right with the green circle).
    • Navigate to /usr/share/java
    • Hold down the CTRL key and click all the drivers that are relevant for your project. (In my case, I selected "mysql-5.1.10.jar" and "postgresql.jar", seeing as I routinely work with MySQL and PostgreSQL databases.)
    • Click the Open button. The selected files are listed in the Classpath window.
    • Click the OK button to close the Classpath window.
  2. Create a database connection:
    • Click Database -> DB - Connection
    • Select a "Dialect". i.e. what kind of database are you connecting to (Oracle, PostgreSQL, MySQL, etc.) This will preset the remaining fields for you where applicable.
    • Enter the name of your database or project in the Alias text box.
    • Modify the JDBC-URL text to match your database. For instance, instead of "jdbc:postgresql://<host>:<port>/<db>", I entered "jdbc:postgresql://localhost:5432/mydatabase_name".
    • Enter the username and password for a user that is allowed to connect to the database.
    • Test the connection. If you get an error, you need to fix that. Unfortunately the error reporting is pretty limited, but if you do have problems, it is likely because the Classpath is not correct, or you don't have the right drivers installed.
    • Click OK

Now that you have a connection, you can either reverse engineer the existing database (if any tables exist) by clicking Database -> Reverse Engineer. OR, you can start creating a new database by clicking the "table" button and creating new tables.

I'm still playing with the system and learning as I go, but I do have previous experience with ER-Win which is very similar. So far, I can see that this tool will save me a lot of time and effort. If it fails to live up to my expectations, I'll report back here with an update.