UPDATE-6 July 2020: I have updated this post to be the landing page for all my posts on Open Source SQL Editors. There are many aspects of SQL editors that I will post in separate and probably smaller posts.
Introduction
Working in Business Intelligence (BI), I write and execute SQL statements every day. Actually it is more like every other minute. The SQL tool I use is very important to me. Over the years I have mainly stuck to SQuirreL SQL Client and wandered off every once in a while to try out different tools. One thing is for sure, no one tool will have everything you need. At some point you have to make a choice based on the features most important to you.
Different database vendors will most likely have their own front-end specifically developed for their database. Oracle has SQL Developer and Sybase has Interactive SQL and so on. I would recommend any budding BI practitioner to probably start with the tool supplied by the database vendor. You are not likely to have compatibility and connectivity issues allowing you to get productive much quicker.
SQL Editors
There are a large number of OSS SQL editors. This is the list of editors I have worked with and recommend. The main selection criteria I was used ability to connect to multiple databases. I want to be able to use the same editor when connecting to different databases.
Don’t be fooled by the dated screen shots on the web pages, these tools look better than that. They are incredibly feature filled tools that with some learning will meet you SQL development needs. Each tool needs a series of blog posts to do justice to the available features. Here are some features technical and not technical that they have in common that have led me to recommend these tools.
Reasons
- Open Source Software. Can be used (if allowed to be installed) in all environments. No licensing considerations. I have worked in environments where licensing of SQL editors is an issue. Software audits resulted in some having to uninstall the editor they were using.
- All under active development with regular builds being released. Probably about a build (new version) a month. I use the latest testing builds and don’t stick to the stable versions. For the faint at heart stick to the stable release versions.
- Active forums, community members and primary developers, to some degree answer some questions posed on the forums or mailing list.
- Cross platform, all applications run on different platforms ,Windows, Linux and Mac, due to the fact that are written in Java.
Technical Reasons
- JDBC used for database connectivity, which means that 99.9999% of the time you will be able to connect to any database. Most databases provide a JDBC driver.
- Data explorer component from all products are quite customisable. It is possible to customise the schema’s that display, customise the SQL statements generated such as the INSERT, UPDATE or DDL statements created.
- Data import and export wizards for data in various formats including CSV, Microsoft Excel formats XLS and XLSX.
- All the tools keep SQL history. You can customise how many statements are kept in history.
Honourable Mentions
As I stated I chose to focus on the three tools listed above because they are multi-platform. There some tools that shine for specific databases and I mention these ones here:
Can be used on MariaDB, MySQL, Microsoft SQL, PostgreSQL and SQLite. Primarily MS Windows but can run in Linux using Wine. This is a really good lightweight but powerful SQL Editor. The only reason I did not include it in my list is because I use Oracle database in my current job and I cannot use HeidiSQL. I used it quite successfully on SQL Server.
Oracle SQL editor. I have only ever spent a few days using TOra but reading through the feature list I was impressed. RedHat uses this as the official front end for Oracle development.
This is the best OSS SQL builder. This tool is powerful beyond its rather dated looking screenshots. If you ever need to explore a database and write many queries at a time then this the tool. You can connect to any database that has a JDBC driver.
As the name suggests this SQL front editor is specifically for SQLite. This is the only DBMS specific tool I use on a regular basis particularly when designing a SQLite database. I use this as my alternative to MS Excel. I import CSV files and analyse using SQL. I have never been great at VLOOKUPS.
Links
Links to pages with more information.
| Link | Description |
| Open Source SQL Clients in Java | List of Java OSS sql clients. Includes Java libraries for connection to databases. |
| AlternativeTo | Good place to see alternative SQL OSS editors. Select one of the editors listed and the site will show you a list of alternatives. Updated regularly and includes information if editor has been disconnected. |