2016-06-08 data

You should use DBeaver as your database IDE

If you often work with PostgreSQL or sqlite3, you should be using DBeaver.

When I first started with Postgres, I was introduced to pgAdmin 3. pgAdmin is Postgres’ de facto database management software (but practically speaking, IDE) that comes bundled with the installation of Postgres. The thing is: pgAdmin is terrible. It performs better than it looks, but not by much.

When I first started using pgAdmin, I believed this is how the SQL world was. A shoddy development environment was a simple fact of life. I would always have to live with two open windows (one for the database manager and one for the SQL query manager), typing out “SELECT …” just to see the first few rows of my table, a lack of ER diagrams, and non-existent auto-formatting or auto-completion. Does pgAdmin work? Yes. Does pgAdmin work well? Not a chance. Using pgAdmin for writing SQL queries is like using gedit for your scripts - you can do it, but you’re not going to like it.

You can imagine my delight when I learned about DBeaver. When all you’ve ever known was pgAdmin, DBeaver is an oasis in the desert. Not convinced? Here are some reasons why:

1) DBeaver is free. #

Yes, there are other Postgres IDEs on the market (too many, in fact, which is why out of convenience everyone uses pgAdmin), but most aren’t free. Why pay for a product whose quality is uncertain when you have great free products already on the market? This points to the success of editors/IDEs such as Atom, Sublime Text and Anaconda.

2) DBeaver is open-source. #

I don’t think I need to explain this one, save for the fact that many of its competitors are not.

3) DBeaver features one of the few post-2000 UIs. #

DBeaver doesn’t just look better than pgAdmin (which, honestly, isn’t saying much) and many competitors, DBeaver looks good. The fonts are well-chosen, tab corners are rounded, color scheme is non-obstrusive, alternating rows are shaded, and icons are intuitive. Querying one database versus another - often, your development or production environment - is a breeze: just a simple dropdown up top. Most importantly, if you don’t like a window, you can get rid of it. Your setup can be as busy or minimalist as you please. Personally, the UI of my IDE makes a major difference in my productivity.

4) DBeaver features a data view. #

I released an audible sigh of relief when I double-clicked a table for the first time and, voilà, my data was just there. No “SELECT * FROM …”, no opening new windows.

Seeing my data alone is awesome. But DBeaver offers more… much more. I can click on column headers to sort my data. I can use the filter box above my results to specify which columns I want to filter and how. If I’m feeling downright lazy, I can right click on a cell and DBeaver will suggest a dozen ways to filter the data - this is especially useful if I don’t feel like typing out long column names (admittedly, autocomplete doesn’t work in the filter box). All of this is done without requerying the database.

For DBeaver, viewing data is not enough of course! DBeaver also lets you UPDATE, DELETE and INSERT data into your tables, all from the data view. Simply clicking on a cell, changing the value (just like Excel), and a CTRL+S sends an UPDATE command to the database. DELETEs work the same way. If you want to INSERT a row, there’s a + icon at the bottom of the table to do just that. Naturally, this is no substitute for learning your CRUD commands, but these features make those operations all too convenient.

5) DBeaver offers ER diagrams #

Sufficient, concise and informative.

6) DBeaver uses just one window! Your SQL scripts lie in multiple tabs #

Frankly, it’s not that this is an impressive feat - it’s just disappointing that pgAdmin doesn’t offer this. You can also use CTRL+/ to cycle through your tabs.

Reading poorly formatted SQL is just as bad as reading poorly formatted code - sometimes worse because an unintended join can call into question the results of any data analysis. DBeaver has a setting which capitalizes your code as you type, and DBeaver can restructure your code to conform to commonly accepted syntax standards. 

DBeaver also has autocomplete, which is a boon for those of you who are used to pgAdmin - ie. no auto-complete!

Finally, DBeaver includes updates SQL objects you type in the editor to be hyperlinks. A CTRL+click leads to the object and its associated metadata. I haven’t really used this feature yet, but it seems like it could be a cool trick to show people at parties.

8) DBeaver shows metadata for database objects #

Finally, DBeaver offers an abundance of metadata. Column names and their references, a table’s DDL, estimated row counts, table constraints and more. This is information I use daily and it can sometimes be a pain in the ass to get with a more parsimonious IDE.

9) DBeaver has customizable… well, everything #

Changing the hotkeys for autocomplete and new SQL script were priorities for me, but then I got a little carried away. Font faces, font sizes and tab colors all got some form of modification. I think my DBeaver layout is actually uglier now, but hey, at least I made it ugly.

10) DBeaver’s creator and maintainer is extremely active #

Serge, the creator and maintainer, does an amazing job at responding to issues, bugs and questions. Serge monitors the forums and his GitHub page. In the two times that I’ve had issues, both were attended to within hours. Did I mention he does this all for free? He’s just a good guy.

11) DBeaver is cross-platform. #

Windows? Linux? OS X? No problem. This doesn’t seem like a major point until you decide to switch from Windows to Linux (as I did) and want to take your software with you. Some of it goes (sorry Microsoft Excel), some if it stays, and I’m glad to say that DBeaver made it over.



I would be remiss if I did not point out two flaws of DBeaver. The first: there is no “Server Status” like the one in pgAdmin. This is huge because I have wearily boot up pgAdmin to check for slow or hung queries and database locks. The second: you can’t see postgres jobs. Personally, this is not a necessity, but a nice-to-have. Unfortunately, these are likely to never be implemented (for reasons that Serge has explained), so pgAdmin still lives on my machine at the very end of my task panel. 

If you skipped to the very end of this article, I’ll save you the trouble: just use DBeaver.