Choosing a PostgreSQL client

We use PostgreSQL as a relational database in webKnossos, our a browser-based 3D microscopy data annotation tool. To inspect the data, perform migrations and optimize the database we use the command-line tool psql as well as graphical clients. The latter clearly have a better usability and allow explorational interactions with Postgres and the data.

When choosing a graphical PostgreSQL client, you will be surprised by the plenitude of tools. Here we focus on web-based, dockerized clients, as they provide an easy setup independent of the operating system.

We analyzed the popular open-source clients Adminer, OmniDB, pgAdmin 4 and TeamPostgreSQL, as well as the newcomer pgweb. Further candidates might be franchise, SIDU and Tadpole DB Hub, but we did not include them so far.

Criteria

We used the following criteria to choose between those tools:

1. general usability (UI), including speed (looking at you, TeamPostgreSQL)
2. supported data types for viewing and editing
3. ability to

• run queries
• visualize the Entity-Relationship Diagram (ERD)
• show analytics about the data and the data-access
• navigate through the data via foreign-keys
• support other databases

PostgreSQL-Clients Overview

As we find this overview quite helpful, we would like to share it with you, as well as a simple environment to try the tools yourself:

To try all clients locally with docker-compose simply run

git clone git@github.com:jstriebel/pg-clients.git
cd pg-clients
docker-compose up -d web

and open localhost. This gives you the overview with links to the running clients included in the table.

Being able to test different clients quickly and switching between them helped us to understand their pros and cons and to try the features. Based on this analysis we are going to use OmniDB for many tasks, as it supports many features and is quite usable.

If you are interested in exploring more possibilities of data-driven applications, please get in touch with us.