5 Open-Source SQL IDEs for You to Learn and Explore

If you’ve done a lot with SQL, you’ve probably used some form of SQL IDE to help you complete that work. Yes, it’s possible to do everything in SQL from the command line; but creating or even maintaining databases and tables that way is an exercise in masochism. There are some nice commercial IDEs such as dbArtisan and SQL Server’s Management Studio, but IDEs is one area where open-source can do just as well (or in some cases, even better).

This is particularly relevant as the impact of the cloud (i.e., SAAS) is beginning to be felt in the commercial database market. Reasons include service providers making greater use of open-source databases, plus the rise of alternatives such as NoSQL. The statista website suggests that global revenue from commercial databases will continue to decline in the years ahead.

For this article, the five SQL clients I chose are Squirrel, HeidiSQL, DBeaver, Tora and OmniDB. I’ve used HeidiSQL and Squirrel for a while, so let’s start with the latter.

Squirrel

Squirrel was a popular choice of client in the bank where I worked five years ago, as they preferred Java, and that’s what it’s written in. Sybase was also used extensively (it wasn’t exactly the most popular database), and Squirrel is one of the few clients that can talk to it. (If you’re keeping score at home, Sybase was also the ancestor of SQL Server.) Squirrel ticks the boxes for all the major databases (Oracle, DB2, Informix, Ingres, SQL Server, SAPDB and Sybase), plus many more, and has a 17-year track record.

But Squirrel was eventually banned in production at the bank, mostly because of its ability to support simultaneous sessions with multiple databases. A couple of times, it ran out of memory, causing issues with live databases; that earned it the red card. For breadth of coverage (thanks to JDBC), it’s highly recommended, but watch how many sessions you run; the more memory you have, the better.

HeidiSQL

Just over half the age of Squirrel, HeidiSQL has been my personal favorite for the past three years. It’s more restricted in its range of databases (you only get MySQL/MariaDB, SQL Server and PostgreSQL) but for many, that’s enough. I’ve found it very fast, which is vital for projects such as a 29-million-rows table; a select count (*) SQL command against MariaDB running on my PC takes around two minutes.

I think a lot of the speed is because it uses its own drivers and connects directly, not through ODBC. Particularly good is the import/export features and batch import: You can run large SQL files (such as backup exports) directly without loading them into the query window.

Another nice feature: When you are writing a SQL query, the currently selected table’s columns are shown in another window along with SQL functions and keywords, plus your own snippets. When you’re working with both live and development databases, being able to move large blocks of data easily is very important.

HeidiSQL is for Windows but can be used elsewhere via the Wine Emulator on Linux and Mac (but to be honest, I’d just as soon use either Squirrel or DBeaver).

DBeaver

DBeaver is another Java-powered SQL client; like the JDBC connector, it has a large set of databases it can connect to. There’s an Enterprise version, which includes NoSQL database support (Cassandra, MongoDb and Redis), but the community edition is the one I’ve examined.

There’s a large list of databases to try from the connection wizard; I chose SQLite, as I have a 600 MB database created for a project a few years back. It worked with that database flawlessly, and I was impressed. While I still prefer HeidiSQL, I would be very happy to use DBeaver if the opportunity arose.

One thing to note: DBeaver doesn’t install drivers at the start, but fetches them as you need them (this is very “Eclipse-like”). That’s probably because it’s built on the Eclipse platform, so if you’re used to that, you’ll feel right at home. Certainly I had no problems with it; it felt very snappy. The range of export formats includes CSV, HTML and XML, as well as JSON and Markdown.

The advantage of Java for both this and Squirrel is that you aren’t restricted by platform; DBeaver includes both Debian and RPM installs for Linux, as well as Windows and Mac.

Tora

Written in C++, I chose Tora as one of the five because it supports Oracle and has a PL/SQL debugger. It’s cross-platform and tested with GCC, Clang and MSVC 2013/2015. In addition to Oracle, it supports MySQL, PostgreSQL and other databases via ODBC. It became open source 13 years ago.

As you’d expect, Tora uses Qt for the user interface and is speedy, but it does feel a bit dated, especially when connecting to MySQL. If you’re working with Oracle, and you don’t have commercial software, use Tora; but for MySQL/PostgreSQL, or any of HeidiSQL, Squirrel and DBeaver is probably better.

OmniDB

OmniDB is a little bit different from the rest. It’s written in Python and installs a local webserver and browser. Databases supported include Oracle, MySQL, MariaDB, and PostgreSQL. The SQLite, SQL Server, DB2 and Firebird databases aren’t currently supported, but are clearly on the roadmap.

I’m not a fan of web GUIs generally, but this looked great and works very well. HeidiSQL and DBeaver provide better import/export, but OmniDB is open to suggestions for future development. Just like the two Java clients, OmniDB is cross-platform, and has the most modern look and feel.

Conclusion

I’m used to HeidiSQL, but DBeaver’s features impressed me. Squirrel is very solid and mature. OmniDB looks nice, though it’s a bit lightweight in features at the moment; you probably wouldn’t guess that it uses the Django CMS. Tora is pretty good but could do with better connectivity.

Related

2 Responses to “5 Open-Source SQL IDEs for You to Learn and Explore”

  1. Lawrence Rafiky

    Relational data bases are thing from the past. The current is no sql data bases for cloud computing, and I think Cloudant (Json based) from IBM is the most powerful data base in the market. Forget relationships between tables; through a basic Json document, you can contain all form the entity, for example: the banking account, their balance, cards, loans, movements for product and customer information.