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.
We used the following criteria to choose between those tools:
- general usability (UI), including speed (looking at you, TeamPostgreSQL)
- supported data types for viewing and editing
- 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
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: