Navicat Database Client

With my latest project I had to work with Vagrant and PostgreSQL. It was my first project using PostgreSQL so I had no tool around to work visually with it.

I made up a list what I expect from my client:

• run on Mac OS X
• standalone
• fast
• easy to learn
• stable
• works with SSH tunnels
• Open Source
• Support for multiple queries
• Optionally some tool to visualize my structure

With this list in mind I checked some recommendations from PostgresQL.

Odyssey of DB GUIs

I dropped all tools from the list which were embedded in other huge tools. As a IntelliJ user I could have used the IDEs database features, but honestly I don’t like them that much (same with SCM). I haven’t tried what LibreOffice or OpenOffice.org can do with PostgreSQL. My preference is a tool which just does on thing right.

Then I scratched phpPgAdmin. I have used its brother for MySQL, phpMyAdmin, when I was younger or when I just need some basic features. This tool maybe can do a lot, but I was simply to lazy to install it on my VM when there should be tools which “just work”. I scratched all of the browser based tools actually.

Then the list diminished further when I removed all Windows tools. Finally I checked pgAdmin III. First I thought it looked nice, with reasonable performance. I didn’t like the Java UI that much but it was acceptable. However after multiple crashes within short time I decided to look further. pgAdmin III 1.16 is simply to unstable, at least on Mac OS X 10.9.

I thought Squirrel SQL might be a solution, but it didn’t have SSH tunnel support. I would need to create the tunnel manually which I wanted to avoid. I am simply lazy.

I could go on forever, but to keep this post short: I didn’t find exactly the tool I want. Some where ugly and complicated. Some where unstable, some where simply slow or were lacking features I enjoyed.

Pretty much frustrated I got a recommendation to try a commercial tool called DB Visualizer. My colleague on the project uses it a lot and was fond of it. Unfortunately the trial is very limited, so I was unsure if I should buy the product.

Finally I decided to give Navicat a try.

Navicat

PremiumSoft is a vendor for database GUI tools. You can buy Navicat for a specific database or for “all of them”. I was looking specifically for PostgreSQL support, but it’s nice that Navicat would support all of my databases.

Navicat can be tried with an unlimited feature set within 30 days. I just needed two days of work to decide this is the tool I want. It starts very quickly, looks like a Mac OSX native citizen and is very responsive.

Navicat lets you create database tables by form and afterwards view it in an “Objects” (ER) diagram.

If you close the schema before you end the application, the objects keep their position which is great especially with large databases. Turned out I am using this view almost always. With double click I can open the related details view of the table and change it.

Furthermore there is a similar “Model View” available, which lets you draw tables and finally create your schema from it. That’s very comfortable if you like to model in visual tools. Personally I prefer the ER view with existing entities and then write SQL by hand.

Just a side note: as a “MySQL Workbench” user I never succeeded with creating the same diagram. Either it broke in the middle of the generation or it told me the job couldn’t be done. I almost had no hope with Navicat but was surprised that the diagram is not only looking great, it’s generated very quickly (I tried with around 20 tables only).

Writing SQL is also charming. Besides TOAD which I used for Oracle, Navicat is the only tool which gave me code completion for SQL. Don’t get me wrong: a lot of tools promised me that feature, but it never worked. “MySQL Workbench” disappointed me again because it seems that all Users except myself seem to be able to use code completion.

One feature I really love is that Navicat allows you to store your SQL queries. They are accessible from the menu directly below your tables.

Navicat provides a so called “Query Builder” which lets you drag and drop your queries. To be honest I am not going to use that. I am quicker with writing the queries myself. I guess the “Query Builder” was created for SQL-Beginners. But when trying I was not sure how to work with it and if it would ever be an improvement over plain SQL.

Navicat provide a lot of functions for things I almost never need and use. In example, you can create Functions or Triggers for Postgre909QL by form. The outcome will be SQL which can be either applied or modified further.

Advanced Navicat features

There are some extraordinary features which I have not seen before. I haven’t tried them all, but they look very promising.

First there is a “backup” feature which might make sense for small databases. The backups can be scheduled. In serious environments cronjobs might do that job, but this feature is definitely of use.

Another interesting feature is the “transfer” mode.

You can transport your local changes easily from one database to another, maybe live one. In serious projects you might use tools like Flyway to perform such changes. But in some cases this makes perfect sense. In example, if you just work on some WordPress job and you would like to work locally first and then transport the new blogs to your production blog. This is actually one of the annoying things which made me move away from WordPress.

In the same vein there is a synchronization view available which even looks more easy. Both features seem to do a similar job. I haven’t tried them yet so I can’t say what the actual difference is.

You are able to import a remote database to your working database. Or, if you prefer, just reverse engineer the model which is a similar use case. Also very nice.

Finally there are some usual sysadmins tools like creating users or new databases. Also you are able (with the right permissions) to take a look at the process list and kick the one or other process.

Conclusion

I am impressed by the powerful features Navicat provides. The overall application was very responsive, fast and beauty. The other competitors didn’t convince me. Among them: “Valentina DB”, “pgAdmin III”, “PGModeler” and “DB Visualizer” (the trial was to limited).

So far I didn’t find a feature which I miss. Actually Navicat is providing the common developer more than they need. Luckily the UI is cleaned up and easy to understand. One quickly finds what one needs.

I think of “Navicat for PostgreSQL” as the better version of the free “MySQL Workbench”. It never crashed so far and the way to work with it is very intuitive.

It can connect via SSH which makes my life easy.

Navicat does not fulfill one requirement: it’s not an Open Source tool.

But I have to admit that there is no Open Source tool available which does have the quality of Navicat so far. Actually there isn’t a commercial tool which does have its quality. Almost 500$ is quite pricey for a database gui, but if you have some money left its worth checking it out. Also please note there are “non-commercial” licenses at around 250$ available too, in case you work for a charity.

Here is a full list of features.

Disclaimer

There are plans for Open Source projects available and I have been promised to get a license for my OS work. Since I cannot add the Navicat logo on the projects website as usually required I have wrote this blog post instead.

Please note that I wouldn’t do that if I don’t like the tool.

Actually I have considered to buy a “DB Visualizer” license but the quality of Navicat impressed me that much that I would have posted a blog on it anyway.

If you know a tool which has a similar quality to Navicat but is Open Source, please let me know.