If you’re a modern web developer, chances are you’ve interacted with PostgreSQL (“Postgres” for short). It’s the second most popular database among developers overall, and almost 70% say they love working with it. PostgreSQL ships with a built-in CLI called psql, but nobody wants to write queries via the command line, even if you’re a veteran DBA. This post will review the best GUI tools available for querying, visualizing, and analyzing your Postgres data.

1) pgAdmin


Overview

PgAdmin is one of the most popular GUIs available for Postgres users. Database objects are immediately easy to find on a left hand menu. PgAdmin is on it’s 4th major version and supports all of PostgreSQL’s features while also being open source.

screenshot

Strong Points

PgAdmin’s greatest strength is that anybody can use it, anywhere. You can configure it to run on any cloud server and then access it from all of the major operating systems; Windows, Linux, and MacOS.

PgAdmin runs as a web application, meaning it can be deployed on any server, including your computer. This is convenient if you’re running Postgres as a distributed database across multiple servers, as you can include PgAdmin on each. Admittedly, this is a feature more targeted at the Database Administrator (DBA) level than the Analyst or Engineer level, which involves more SQL than production database management.

Useful Shortcuts for your Editor

SQL Query Editors are where most Postgres users spend their time when manipulating data. PgAdmin’s SQL Editor provides an extensive list of useful shortcuts for quality of life improvements. They cover most of what you’d want to accomplish when writing queries that need maintenance friendly white space.

Drawbacks

The main drawback of PgAdmin is installation barriers, especially for SQL developers who aren’t experts at the command line. Running a Postgres GUI as a web application from your terminal is something that newcomers should not be expected to figure out from the ground up. Managing multiple servers, databases, and the usage that comes with them, is definitely a more advanced terminal skillset.

2) Navicat


Overview

Navicat is not as popular as PgAdmin but definitely comes with many of the features you would expect from software that makes talking to databases easier. Navicat is not open source and is a paid tool, so it comes with many more features than the typical open source tool. Unlike PgAdmin, Navicat supports multiple SQL dialects; MongoDB, MySQL, and PostgreSQL.

02.Product_01_Premium_Windows_01_Mainscreen15

Strong Points

Installation of Navicat requires no more than 3 lines of terminal commands in Ubuntu. It is easy to get up and running and has multiple features which make it a great choice for team collaboration. Team productivity for the SQL they write can be instantly improved with Navicat’s job scheduler. An add-on feature called Navicat Cloud allows for Navicat SQL specific team collaboration. This cloud friendliness extends to how easy it is to connect to data sources; cloud databases, local flat files, or SSH Tunneling and SSL.

Modern Aesthetic

The aesthetic of Navicat’s GUI is slicker and more modern than pgAdmin’s. One of the neat things about Navicat is that it gives the user a choice of dark or light theme from the start (for those dark mode die hards out there).

Drawbacks

The main drawback to Navicat is price. This may not be an issue for a business looking to maximize their database users’ efficiency, but could be frustrating if you’re an individual looking for a simpler way to query. The trial is only available for 14 days and licenses have to be bought in order to be able to work with PostgreSQL or MySQL.

Navicat for PostgreSQL Pricing is three tiers; non-commercial, standard, and enterprise. They are $119, $199, and $299, respectively.

3) DBeaver


Overview

DBeaver is open source like PgAdmin. However, it supports different types of databases like Navicat. DBeaver also has an enterprise version which provides advanced plugins for productivity. You can run DBeaver on all of the common Operating Systems; Windows, Linux, and MacOS.

02.Product_01_Premium_Windows_01_Mainscreen15-1

Strong Points

At least for beginners, the best thing about DBeaver as it compares to the other GUIs so far is that it runs as a desktop application. Database Objects are easy to find on the left hand side menu, and connecting to my local Postgres database was intuitive. No CLI experience is necessary to get started.

DBeaver, being open source, has both a free and paid option. An easy to sell concept: start with a deadline-less free DBeaver and transition into a paid tier as your organization’s needs scale.

Drawbacks

DBeaver’s main strength is also its major weakness: desktop applications are limited to how much power your machine has. In order to unlock the needed productivity levels needed in distributed development situations, the enterprise download is recommended.

4) HeidiSQL


Overview

HeidiSQL is the only GUI on this list that was built exclusively for the Windows Operating System. Just like DBeaver and Navicat, HeidiSQL can connect to different database drivers such as MySQL, Microsoft SQL Server, and PostgreSQL. HeidiSQL is free and open source.

scrKnfpXS

Strong Points

Like many Windows desktop applications, HeidiSQL is easy to download and install. If you’re siloed in Windows and your IT department is firm on that, HeidiSQL is a great option for connecting to a Postgres database.

I was able to connect to a Database with a handful of credential fields. The console prints out the commands that the GUI executes, in real time much like a log. This visibility into the backend makes it a useful tool for debugging and troubleshooting database problems.

Drawbacks

HeidiSQL is pretty lightweight, so it’s missing some of the power features that advanced users might need, like a debugger, and has no cross-platform support.

5) Datagrip


Overview

Datagrip is a cross-platform integrated development environment (IDE) from the folks over at JetBrains (makers of IntelliJ, PyCharm, etc.). That means, you can use it on Macs, Windows, and Linux. Unlike PgAdmin or Navicat, it is not a web application and can run as a local application like Spotify, VSCode, or RStudio.

query-console

Strong Points

Datagrip is intense, and it’s built from the ground up for customizability and power, ranging from dark themes to plugins for the many database systems and dialects available.

As an Ubuntu (Linux) OS user, installing dependencies requires two bash CLI commands. Installing Datagrip only requires one. A single terminal with an open log remains open as I launch Datagrip. This terminal prints out log information about the app as it's running. You can run it in the background but I like Datagrip’s balance between command line usage and desktop application.

image1

Drawbacks

With a range of plugins and up to date development, it is no surprise that Datagrip is a paid tool at $200/year, per user, for the first year. Unlike PgAdmin or Navicat, Datagrip is not built to be an administrator web application that can be deployed on any cloud server, from any other; it’s made for querying.

This is more than enough for many small businesses because a desktop application can help them solve most of their urgent database problems on one, or few, computers. However, another solution may be better suited to deploying and managing many Postgres DBs.

6) OmniDB


Overview

OminDB is open-source software that works on Mac, Windows, and Linux. It’s focused on lightweight, no frills, Postgres database management.

image8

Strong Points

Despite being open source, OmniDB retains a lot of the SQL development features that a lot of the paid GUIs have like SQL autocomplete and syntax highlighting, customizable charts for database metrics, and complete debugging tooling. Release notes show that Postgres is the Database system that OmniDB is most focused on.

Drawbacks

OmniDB is a lightweight tool, meaning it works great for single or few SQL Developers that need a free option for fundamental database work. However, it is not the best option for SQL developers that need a wide community of support, documentation, and at-scale deployment for their databases.

7) Retool


Overview

Retool is a web based tool for building internal tools and applications on top of your data. It connects to all modern databases (Postgres, MySQL, MongoDB, and even Oracle) and any REST API, as well as third-party APIs like Stripe and Twilio.

Strong Points

Retool connects to pretty much anything, which is useful if you’ve got more than one data source to work with. You can access and work with your Postgres DBs and your MongoDB collections in the same GUI, all from your browser. Retool also puts a GUI wrapper over write operations so you can avoid accidentally writing DROP TABLE and getting yelled at.

Screen-Shot-2020-03-30-at-4.21.59-PM

Retool saves Engineering time

If you’re building tools on top of your data, Retool saves you time by giving the reusable components you need: tables, buttons, text inputs, and even custom components. Retool is cloud hosted so the GUI is accessible from any major operating system and any major browser.

Drawbacks

Retool isn’t specifically focused on Postgres (like OmniDB), and is missing some advanced features like a query debugger.

Conclusion


Which is the best PostgreSQL GUI? The answer is it depends. If you’re a single analyst looking to move into transparent and reproducible workflows with a single instance of a database, then DBeaver, Datagrip, or HeidiSQL are your best bets.

However, if you are part of a larger team, then paying for Navicat may be the best option because of Navicat’s team collaboration focus. PgAdmin is the only PostgreSQL native GUI, it’s totally free, and it scales easily across multiple servers. PgAdmin makes for a great choice for Postgres focused Database Administrators. However, a need for distributed servers usually only arises for skilled developers who can tackle this kind of configuration.

Retool shines when you’re working with multiple data sources. Modern developers rarely stay in one ecosystem like Postgres. As an organization grows, data workers have a growing list of stakeholders that need and want visibility for their metrics. Integrating multiple data sources with clicks and transforming with SQL is my preferred method of data manipulation because it minimizes developer time and maximizes domain specific knowledge about your own data.