captain holly java blog

dbvisualizer – the very handy SQL client

Posted in Uncategorized by mcgyver5 on March 24, 2010

There are a lot of things to love about dbvisualizer. The tool is stable and fast. It has easy installation, a great SQL editor, built-in support (and drivers) for many databases, quick object and data editing, and most of all simplicity. Somehow these guys made a super flexible but simple and intuitive tool. I compiled a list of all the things I liked and disliked about the program. This list includes some nitpicking. Their people are really good about accepting bug reports and feature requests and I’ve nitpicked to them.

  • One of the most tedious things about any SQL client is drilling down to get to the object you need. DBVisualizer helps by allowing you to drag your most used database objects to a favorites bar for easy access. Favorites items have the same context menus as the regular item in the tree. Opening an item from the favorites bar opens the object tree to the correct place so that it is then easy to access other items near it. This feature gets the most enthusiasm from my coworkers. It is somewhat hidden at first as you need to show/hide the favorites toolbar from the view menu. Unfortunately, there does not seem to be keystroke access to the favorites bar.
  • The grid interface for editing data is well done. It has commonly accepted keystrokes for searching, editing cells, deleting rows, and saving changes.
  • The first upgrade process felt disconcerting because it wasn’t clear if running the exe was going to upgrade an existing install, erase my settings, or what. It created a brand new DBVisualizer install and left the old one and still imported my settings. Not expected behavior. The next day I mistakenly clicked the shortcut to the old version and was asked to upgrade again. “Pretty aggressive release schedule”, I thought.
  • There is complete support for exporting user settings (connections, bookmarks, preferences). This helps when migrating to a new machine or welcoming new employees.
  • Initial install was easy and it automatically detected all my drivers. It also includes the MySQL driver, again because this is a commercial product. This is an improvement over SQuirreL, which for better or worse, cannot package the MySQL driver due to licensing issues.
  • This review would not be complete without comparing it to Navicat, another popular cross-database multi-platform SQL Client known mostly as a MySQL client. Navicat has two powerful features not present in DBVisualizer. This is server performance monitoring and job scheduling. It would be nice to schedule backups inside DBVisualizer, but for the most part, the databases I connect to are managed by other people (DBAs) who perform backups. As a developer, this is not a key feature for me. Navicat is slightly more expensive than DBVisualizer. (~$200 for DB Visualizer and ~ 375 for Navicat) . This was the one feature that is compelling about Navicat. It would be nice to monitor server loads. I’m not even sure I’d have the rights to access that info, though.
  • Explain Plan is beautiful. Unfortunately, I’m using it mostly with PostgreSQL and DBVisualizer does not have explain plan for PostgreSQL. Navicat does have explain plan for its supported databases including PostgreSQL. PGAdmin III and SQuirreL have limited implementations. None match the clarity of the DBVisualizer explain plans. Except not…. for PostgreSQL.
  • DBVisualizer is missing some PostgreSQL specific syntax such as vacuum and analyze are not well supported. Compare this to SQuirreL, which has Vacuum and Analyze in the context menu for each table.
  • Another example of the flexibility of DBVisualizer is the ability to create Folders in the database tab. I organized all the disparate databases my applications use into Folders to make the list more manageable. I found that favorites will not follow as you change your folder structure. So, make your folders first and then your shortcuts in the favorites bar.
  • DBVisualizer isn’t afraid to package proprietary packages such as the yWorks (for the references graphs) and Install4j.
  • DBVisualizer has a monitor feature. This monitor is the kind that monitors your data over time, as in: “this table is growing at 1100 records per week”. This tool is more complex than I can manage for this post.
  • The SQL Editor is really well done. The autocomplete saves tons of time. jumping back and forth between a “SQL Builder” and SQL Editor is doable
  • You can use variables in the SQL Editor. This makes the query ask you for fill-in values when you run it. Makes repeated querying very easy.
  • In addition to data editing, it is really easy to edit table structure. An “Alter Table” Dialogue provides controls to change all table aspects and generates an SQL statement for you. A problem I encountered with PostgreSQL is that In Alter Table –> Constraints –> Drop constraint,
    The generated SQL produces a syntax error about the name of the constraint . PostgreSQL requires a slightly different syntax for dropping a non null constraint which this tool does not account for as I write this. I reported this to them through the forums and they said they would fix it.
  • By the way, the forums and documentation are really good. I’ve posted 8 or so posts to the forums as I was learning this tool and writing this post. They were all answered within an hour or two… by employees. Not only can you get answers, but find out a whole lot more about this product.
  • Also with PostgreSQL, it can be hard to edit keys or delete duplicate records postgreSQL has no rowid (oid) unless you specify one when building the table. One suggestion I have is a way to automatically add OID. Compare this to pgAdminIII – well, no comparison because pgAdminIII has no live editing of tables. So… compare this with SQuirreL, which has a clunky editing interface but still has the ability to delete an identical row. It noticed the duplicates, alerted me to the fact and then went ahead and deleted ONE of the duplicates. I wonder how it knew which to delete? This scenario is exactly the same for MySQL in both tools. The real solution is, of course, to always have a unique key.
  • I also inadvertently set my row limit to 4 for the data grid. No idea how it happened, but it stayed that way and as I navigated around, it affected all my tables. I don’t like the way that worked since it never LOUDLY told me that I wasn’t seeing everything. I now have my row display limit set higher, but if a table exceeds that, I sometimes find myself wondering where the hell my data went. There is no control like “show me the next page of results”.
  • Importing and exporting data via CSV was a bit clunky. I was thrown off by the presence of the previous action’s logs in my import dialog. As another user reported in the forums, it can be hard to know if you ran the import already. The import tool also allowed me to try and import an SQL file as CSV. That did not go well.
  • Using it with embedded hsqldb. It was easy to load the hsql.jar file as the driver. It was less easy to know how to point to the “database file” because there is no actual file. Instead, you are supposed to point it at theName.properties and then remove the “.properties”
  • There are plenty of configuration options in the Tool Settings dialog. There are even more in a file called dbvis-custom.prefs, where you can disable features and force JDBC to do unexpected things. Making uneducated changes to this file and others in the same directory could really screw up your install. And since it is a java application, there is a whole galaxy of startup options.
Advertisements

2 Responses

Subscribe to comments with RSS.

  1. periodic pg said, on January 24, 2012 at 12:20 am

    Thanks this is a very useful post (from a while back) because you have used DbVisualizer enough to have bumped into some limits, but are also past the initial learning curve so that you can see how efficient it is.

    I have been using a different tool for postgresql for my intermittent needs (Lightning Admin For PostgreSQL) and while that’s a great tool, support is weak. I am posting this because while evaluating Navicat for Postgres and DbVisualizer I found that the pricing for the Postgres version of Navicat to be much cheaper than what you mention (125 or 199 depending on std vs ent). I am sure you pricing the Premium version of Navicat which (like DbVisualizer) does multiple databases. I realize that you are probably using it for more than just Postgresql, as I would be, but thought I’d mention the lower cost in case someone come across this blog who is mostly focused on Postgres.

    There is also PostgreSQL Maestro which I’ve not looked at, but seems pretty good.

    • mcgyver5 said, on January 29, 2012 at 8:04 pm

      Thank you for pointing that out. You are correct. Navicat’s pricing is much more granular than I realized and you can get just the postgres version for 125 or 199!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: