SQL tooling, the ranking

When you need to get up and running quickly with your database, the tooling becomes very important. When developing jOOQ and adding integrations for new databases, I really love those ones that provide me with simple ways to create new databases, schemata, users, roles, grants, whatever is needed, using simple dialogs where I can click next next next. After all, we’re in the 21st century, and I don’t want to configure my software with punchcards anymore.

Database tooling categories

So with jOOQ development, I’ve seen a fair share of databases and their toolings. I’d like to divide them into three categories. Please note, that this division is subjective, from the point of view of jOOQ development. With most of these databases, I have no productive experience (except Oracle and MySQL). Things may change drastically when you go into production. So here are the categories:

The “all-you-can-wish-for” ones

These friends of mine ship with excellent tooling already integrated into their standard deliverable for free. It is easy to start the tooling and use it right away, without any configuration. The tooling is actually an intuitive rich client and I don’t have to read thousands of manual pages and google all around, or pay extra license fees to get the add-on. This category contains (in alphabetical order):

• CUBRID with its Eclipse-RCP based CUBRID Manager. This is a very nice tool for a newcomer.
• DB2 with its Eclipse-RCP based IBM Data Studio. IBM created Eclipse. It would’ve been a shame if they hadn’t created the Data Studio.
• Postgres with pgAdminIII. Very very nice looking and fast.
• SQL Server with its SQL Server Management Studio. This is probably the most complete of all. You can lose yourself in its myriads of properties and configuration popups.
• Sybase SQL Anywhere and Sybase ASE, both share the same tooling called Sybase Central. It looks a bit out of date, but all administrative operations can be done easily.

The ones with sufficient tooling

These databases have tooling that is “sufficient”. This means that they ship with some integrated scripting-enabled console. Some of them are also generally popular, such that there exist free open source tools to administer those databases. This includes MySQL and Oracle. Here are to “OK” ones:

• H2. Its web-based console is actually quite nice-looking. It features DHTML-based auto-completion and scripting. I can live with that.
• Ingres. This dinosaur seems not to have upgraded UI components since Windows 95, but it works as good as it has to.
• MySQL, with phpMyAdmin. This is a very nice, independent, open source PHP application for MySQL administration. You can install it easily along with MySQL using XAMPP, a nice Apache, MySQL, PHP, Perl distribution. Yes, I like installing complete things using the next next next pattern!
• Oracle. It has sql*plus for scripting and there are many commercial and open source products with user interfaces. My favourite ones are Toad and Toad Extensions, a really nice and free Eclipse plugin. It is worth mentioning, that if you pay the extra license fee, you will have access to Oracle Enterprise Manager and other very very fancy tools. With money, you clearly can’t complain here.

The other ones…

Here, you’re back to loading *.sql files with DDL all along. No help from the vendors, here.

• Derby. I’m not aware of any tooling. Correct me if I’m wrong
• HSQLDB. Its integrated console can execute SQL, but it doesn’t provide syntax highlighting, checking, autocompletion, etc. I’m probably better off using SQuirreL SQL, or any other generic SQL tool.
• SQLite. Good luck there! This database is really minimal!

           

Reference: SQL tooling, the ranking from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog.