Polyglot Database Development with 0xDBE

This is an unsolicited review of OxDBE, a universal SQL IDE by JetBrains.  Universal SQL IDEs are meant to make your life easier developing code for multiple DBMSs.  Instead of learning the nuances of multiple DBMS IDEs you can spend your time learning the nuances of each vendor's SQL dialect.  The problem is universal SQL IDEs tend to be either pricey or buggy.  0xDBE is polished and has almost no learning curve if you are familiar with IntelliJ.   Try it while it's still free.  

(This is an unsolicited review of a product I really love.)  

From JetBrains, the folks that brought you IntelliJ, a premiere Java IDE, comes 0xDBE, a Universal SQL IDE.  0xDBE (I hope they change the name) supports SQL Server, Oracle, MySQL/MariaDB, PostgreSQL, Derby, and many others.  Why do we need a(nother) universal SQL IDE?  In today's world if you are a data developer you are probably working with multiple database managers.  It's bad enough that PL/SQL is syntactically night-and-day from TSQL, which is close enough to plpgsql in many respects that it just adds to the confusion, but add to the mix that each DBMS has its own "standard" IDE...some with autocomplete but each with a different set of shortcut keys ("Execute Query" is F5 in SSMS and pgadmin, but F9 in Oracle sqldeveloper and HeidiSQL (MariaDB's standard IDE)...and you get a recipe for total polyglot frustration.  I have no idea why the suicide rate among UN language translators isn't higher.  This becomes highly stressful work.  

There's another reason why Universal SQL IDEs are valuable...they are great tools to help you learn a new database manager.  They even help you translate PL/SQL to plpgsql (or TSQL or whatever).  

The Universal SQL IDE Landscape

For years I've had to support multiple database managers at ISVs.  I've used either SQuirreL or DBeaver (a play on the SQuirreL moniker...I've written about DBeaver while [[developing with Vertica]]).  Both are open source (read free), are Java-based, and are pretty good.  But they both have shortcomings.  I swear SQuirreL crashes every half hour.  And DBeaver's GUI is a bit cumbersome...example... .sql files will only execute in "script" mode and not in "command" mode.  SQL Server has no concept of this but it is common in Oracle and most other DBMSs.  However, you should always be able to pick and choose sections of .sql files to execute.  Further, as both of these are community-driven you really can't expect rock-solid software with bugfixes and new features.  

If you have the budget the best choice is Navicat which allows you to edit and work with multiple DBMSs side-by-side.  This makes testing and "translating" code much easier.  At about $800 per seat it's a steal.  But even that is too pricey for some organizations.  

TOAD is awesome but it's something like $1300 for only one database manager.  And it won't let you edit and work with multiple db managers side-by-side.  As a standalone query/DBA tool it is the de facto standard for Oracle folks, but I think it is lacking if you need a truly Universal SQL Manager.  

JetBrains 0xDBE

That leaves us with 0xDBE which I only learned about a few months ago.  It's pre-release, but you can sign up for it if you provide your email address.  JetBrains is a commercial company so I assume eventually this is going to cost some bucks, but if the price point is good I'd definitely buy it, and I'm a cheapskate.  

It runs on Unix/XWindows, OSX, and Windows.  It's based on the IntelliJ IDE platform which can be a bit confusing if you are more familiar with the conventions used in the Eclipse IDE.  But if you don't use Eclipse you should have no problems.  Since it is based on IntelliJ it has all of the IntelliJ goodness that a modern IDE should have...autocomplete, dropdowns with code completion suggestions, keyword highlighting, consistent auto-indenting, universal "execute" and "debug" command shortcuts, etc etc.  

Let's Look at Some Cool Features

When you install it you'll be asked to pick which "theme" you want to use but you can always change it later:  

I happen to love the IntelliJ IDE so that theme is a natural fit for me.  

When working with multiple DBMSs the thing that frustrates me the most is remembering which ones perform implicit COMMITs and which require you to explicitly COMMIT your work.  For instance, SQL Server autocommits unless you start an explicit transaction with BEGIN TRAN.  Oracle however has no concept of BEGIN TRAN and you MUST explicitly COMMIT.  0xDBE handles this exactly how I would...give the developer an Auto commit option that is configurable and right in your face.  I love this feature.  It essentially makes everything work just like SQL Server.  

There's nothing worse than working in Oracle sqldeveloper all day and then when you exit the app you get the warning asking you to commit.  Sometimes you just forget.  And if you lose your connection or the IDE crashes you risk losing your in-flight work.

Here's a nice benefit...you don't have to manually install database drivers whenever you need to work with a new DBMS.  In this case I'm missing the MySQL drivers:  

...but after clicking Download I have everything installed and am able to connect and am off and running.  This is so much easier than 10 years ago when, for instance, if you needed to connect to Oracle you had to manually download the Oracle drivers, configure a TNSNAMES.ORA file, and then deal with whatever other connectivity issues would invariably pop up.  You are literally up and running in seconds.  

If you are connecting to SQL Server you can use the native Microsoft JDBC driver or the jTds driver.  Since I already had the native MS driver I thought I'd try the jTds version...again, I was up and running in seconds:  

0xDBE has a "presentation mode" which is handy for obvious reasons:  

Some Limitations/Annoyances

One slightly annoying limitation is that 0xDBE wants you to use its project file hierarchy.  That's not generally a problem...and it has integrated git support to boot...but sometimes this is unnecessarily limiting.  Example:  let's say someone emails you a .sql file with a bunch of miscellaneous queries.  You diligently open the file in 0xDBE and decide you want to highlight and run the second query in the file.  But you don't get any option to do that:  

Instead you have to run the entire file.  You can of course copy the relevant query to another window and execute it there, but it shouldn't be so cumbersome.  BTW, this is not a unique constraint to 0xDBE...many other query tools work the same way.  Most database managers, SQL Server being the exception, have the concept of running in "script mode" vs "command mode".  Both essentially do the exact same thing...they run commands...so I don't really understand why there is differentiation.  

In 0xDBE if you copy the commands into a new command window you'll see the proper set of options to run individual commands:  

Here I get the option to not just execute a single command, but to get the EXPLAIN plan, etc.  The other way to overcome this limitation is to save your .sql files into the project hierarchy and then open it in 0xDBE.  But again, it would be nice if you just opened a .sql file and were able to get all available options without having to squirrel-away the file somewhere.  

No Support for Vertica or NoSQL Flavors 

0xDBE does not currently support Vertica or any of the NoSQL varieties.  I'm sure JetBrains will eventually fix this.  

PostgreSQL raise notice Functionality is Missing

Postgres' raise notice functionality is not output anywhere in 0xDBE.  raise notice is the equivalent of a PRINT statement in TSQL or DBMS_OUTPUT.PUT_LINE in Oracle.  This makes debugging in postgres a bit difficult.  Again, this is pre-release code so I'm sure they'll eventually fix it.    For instance, in pgadmin a raise notice is displayed in the output pane and is great for debugging dynamic sql like this:  

The workaround in 0xDBE is to simply SELECT out the variable just like you would in SQL Server.  The string then appears as part of any result sets.  Considering that MySQL doesn't even have PRINT or raise notice, I'd say this is annoying, but not a show-stopper.  

Let's See 0xDBE in Action

Here's a screenshot where I'm using MySQL, Oracle, PostgreSQL, and SQL Server all within the same GUI:

I've just run the CreateSchema command against a postgres instance, which is part of my MD3 suite of tools.  It builds a bunch of dynamic sql that creates various database objects based on simple metadata.  The command returns no results, which indicates success.  And here is a screenshot running the Oracle-equivalent command using Oracle's PL/SQL syntax and code:  

Note the output is slightly different because we are using different DBMSs, but the output is similar (no results returned == success).  

And of course everything in 0xDBE is context-sensitive to the target platform.  Here I'm using postgres and note that it is properly giving me autocompletion options based on plpgsql syntax:  

Summary

I won't bore you with more screenshots. If you find yourself working with multiple DBMSs and you tire of remembering not just different SQL dialects but also different IDEs, then consider giving 0xDBE a chance.  It is very polished for pre-release software.  This is definitely a tool I'd shell out my hard-earned money to have in my toolbelt.  

You have just read "[[Polyglot Database Development with 0xDBE]]" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.