SQL Workbench: the Swiss Knife SQL Query Tool

SQL WorkbenchSQL Workbench is an SQL query tool clearly built by engineers for engineers. The kind of attention these developers obviously pay to both feature design and details is really an example how professional tools should be built.

To begin with, it does not assume much about the environment. It is a Java application so it runs on most desktop operating systems and it uses JDBC to access databases, so it can work with basically any RDBMS out there. This means that you can derive value from its use in a wider set of use cases and that you don’t have to learn to use another tool to get the job done on another database.

While it is not FOSS, it does support some of the basic freedoms: the rights to distribute and use the software in any way (other than to sell it, apparently). Unfortunately, this does not include the right to modify it and it is a bit of a mystery why. I would very much like to see it released under GPLv3 or a similar licence and who knows? We might get to see the day…

It seems appropriate to start a review of an SQL query tool from its SQL handling capabilities. The SQL editor is quite feature rich: multiple query editors can be open at the same time, each tab can handle an arbitrary number of statements, it can execute a single selected or all statements, it can output results from multiple statements separately, run with auto-commit mode on or off, auto-complete table and column names, reformat (think e.g. troubleshooting hibernate-generated SQL), perform lower level text editing tasks like text lower- or upper-casing, highlight matching brackets and even extract an SQL query from a Java code snippet and detect prepared statements – among other things.

When it comes to how easily it does this, performance is usually satisfactory. Other than the sometimes noticeable pause when switching from tab to tab, it executes code and displays results as fast as could be expected. It has default limits on retrieved data size but can display a surprising amount of data, does not seem to require exorbitant amounts of memory to operate and has a special command – WbInclude, a part of its own language – to execute substantial scripts, e.g. on the order of 100MB or more.

Its scripting language is an invention going with the grain of the tools design and goal to support a wide variety of databases while at the same time requiring developers to only learn how to use one tool. The language allows variables to be defined, has placeholders representing selected text, selected statements, the current statement, the entire script text, etc. WbGrepSource and WbGrepData should be mentioned separately because they make it possible to search the entire database for a text pattern in the schema or the data, respectively. The fact that the user doesn’t have to know or go to any trouble to specify a table and column to search for data is really a valuable asset. I assume it is perfectly clear that this kind of thing is completely non-standard, but as I am not aware of a standard to cover the use case, I consider it acceptable and useful.

A rather powerful subset of its scripting language commands are commands which handle data migration and comparison. It allows data to be exported, imported, copied from database to database with transparent table creation, compare database schemas etc. This kind of functionality is extremely useful, especially knowing that the data model and the application often depend on one another, but the data model is often not versioned. Tools like SQL Workbench can help identify the changes in the schema so that the right database is used for a given task.

While the basics of an SQL query tool are query editing and result display, both give a lot of opportunities to shine. The editor we have covered already, so it makes sense to say a few words about its data management capabilities. For a start, results can be exported in effectively any format you could practically want, including various ways to export them to the clipboard (as data, as insert statements etc.). The results can be sorted effortlessly and while it is no big feat, SQL Workbench does this really fast. For a given row, the tool allows the user to navigate to related tables, e.g. to display the row of a stronger entity or to display a set of weaker entities. In-place editing of results is really straightforward and thoughtfully done: any change is local to the result set until explicitly saved. When a request to save changes is issued, it generates an SQL script to perform the change, it is able to optionally display the script or execute it, display a list of columns for key selection if primary keys are missing…I would be hard pressed to find a flaw.

It hides a myriad of other gems, as well. It can generate code for all kinds of scenarios: delete table content or drop tables in the proper sequence, respecting FKs. It persists editor state across sessions, allows execution of multiple queries and shows all their output (if any) in separate result tables. It can be used as a command line application, skipping the GUI altogether to allow use in scripts. It has a deterministic data export feature, meaning you can perform a diff of its output and see what exactly changed in any part of the database (if the export file content was not ordered deterministically, this would not be possible). It can use XSLT templates to display HTML reports based on XML data produced by some of its commands. It can compare two schemas or database contents of two databases…

Having seen a few query tools designed for PostgreSQL, MySQL and MSSQL, I would say that SQL Workbench is easily much more advanced and polished and can only humbly recommend it.


Leave a Comment

Popunite niže tražene podatke ili kliknite na neku od ikona za prijavu:

WordPress.com Logo

Ovaj komentar pišete koristeći vaš WordPress.com račun. Odjava / Izmijeni )

Twitter picture

Ovaj komentar pišete koristeći vaš Twitter račun. Odjava / Izmijeni )

Facebook slika

Ovaj komentar pišete koristeći vaš Facebook račun. Odjava / Izmijeni )

Google+ photo

Ovaj komentar pišete koristeći vaš Google+ račun. Odjava / Izmijeni )

Spajanje na %s