Ask HN: What PostgreSQL client do you use?

[iameoghan]

There are seemingly a plethora of clients for Postgres [1].

What client(s) do HN use day-to-day? Why have you chosen it?

I'm looking for recommendations to use for light usage. Ideally, something that would help build the schema visually.

[1] https://wiki.postgresql.org/wiki/PostgreSQL_Clients

[OJFord]

DBeaver.

It suffers from 'oh God this was definitely written in Java, wasn't it', but it's mostly good.

Then I happened across pgAdmin recently which probably does everything I want from DBeaver, and has a docker image (it's browser based) but I haven't really tried it properly. Keep meaning to though, I have a hopeful feeling that it'd be 100% what I recommended if I had.

[bzzzt]

I like some small features in DBeaver like clicking through on a foreign key takes you to that row in another table. I've seen lots of clients where you have to write another query.

Written in Java has the advantage of supporting any database which supports Java (which means every serious database on the planet)

[lykr0n]

DBeaver is great, in part because it's purely functional. The UI is straight forward and well thought out.

It also supports everything.

[brightball]

Very happy with DBeaver too

[zippoxer]

TablePlus - Love it! Cross-platform without Electron and yet still pretty. They got UX right, it's minimal yet powerful enough for me.

[jcoletti]

Another vote for TablePlus. Really solid and support for tons of other DBs. Came from Navicat which crashed a lot. Pricing is way better too. They also make DBngin, a great free tool for spinning up local DBs in a clean, self-contained way.

[conorh]

Just discovered this recently, great app, it even replaced sequel pro which was my go to for MySQL DBs.

[adperry]

I’ve been using it for a awhile. It’s great.... much better than pgadmin.

[zaszrespawned]

they need to allow more than 2 tabs in the free version

[kmstout]

psql. It's installed everywhere I need to use it, it reads from stdin, and it writes to stdout. It's often the case that I want to take a SQL query that I've developed, generalize it a bit, and use something like

  psql OPTIONS <<EOF
  SELECT ...
  EOF

in a shell script (probably wrapped in a function for convenience).

[e12e]

I do use psql for scripting, but for more interactive use, pgcli is quite nice too:

https://www.pgcli.com/

It doesn't help with visualizing schemas, however.

[Justsignedup]

to be fair. psql is not only available everywhere, but with a well customized .psqlrc file you can really get that tool to do what you want it to do.

On rare occasions I still need a GUI tool to explore a database because GUI definitely wins during the exploration phase by a lot.

Ironically enough for report writing I use metabase web ui. it serves most purposes I need from it.

[bmdavi3]

+1. Be sure to add \x auto and \timing to your ~/.psqlrc file to make the experience even better!

[atonse]

I use and love Postico. But I do very basic stuff (SQL, DDL, editing rows, etc)

[earthboundkid]

I have enjoyed using Postico. It’s very easy to use and not ugly, which is rare in the world of database tooling.

[evo_9]

MS added PostgresSQL support to their excellent cross platform dB tool Azure Data Studio:

https://azure.microsoft.com/en-us/blog/azure-data-studio-an-...

[ComputerGuru]

(If anyone is wondering: it was forked from VS Code and, yes, it is electron.)

[nsxwolf]

I use the database tool built into IntelliJ. It’s amazing. It’s available as a stand-alone product named DataGrip.

[Doxin]

As a pycharm user I can second that the editor integration is rad. The interface itself could use some work in my opinion. The way it deals with datetime inputs is particularly heinous. But it's definitely the least-bad GUI database client I've used so far.

[scabbycakes]

Seconded - the code integration is fantastic as well. Intellij magically complains when I'm typing a SQL query in a string and get a column name wrong and all sorts of weird little things I miss when I don't have them, IJ is unbelievable.

[aarpmcgee]

Postico seems to be heavily optimized for the 80% of what I need in a postgres tool. I love it.

[oliwarner]

Django.

It's not the answer you thought you were looking for, but it's a bloody good framework for modelling data, reporting, and also scripting things around it.

[nickreese]

For visual schema building the best I’ve found is pgmodeler. It is buggy and isn’t really a client but it is great at modeling, viewing schemas in a model, and overall making sense of how complex data connects.

Edit: For day-to-day I use Postico but am playing with beekeeper as soon as JSON and JSONB support are figured out.

[oftenwrong]

pgcli https://www.pgcli.com/

[pella]

For PostGIS / Spatial data

#1. DBeaver: Working with spatial/GIS data

https://dbeaver.com/docs/wiki/Working-with-Spatial-GIS-data/

#2. pgAdmin4: now offers PostGIS geometry viewer

https://www.bostongis.com/blog/index.php?/archives/272-pgAdm...

---------

Any other?

[rgoulter]

I'd guess in your case ("light usage"), just choose one of the open-source GUI clients would be best. Probably DBeaver.

Here's the decision-tree I'd go with:

Do you want to pay money for it? If not, use a free or open source one.

Are you comfortable with running SQL commands from the command line? If not, go for a GUI one.

This leaves "OS-specific or cross platform" (which should be straightforward to resolve), and "desktop client or web interface". I'd expect a desktop client to be easier to setup.

[alexandernst]

Navicat , it has everything I can ask from a RDBMS client (it even supports NoSQL!), from days transfer, database diffing, scheme reversing, advanced DBA related tasks...

[tren]

I really enjoy using navicat too, it is much more intuitive for me coming from mssql

[soheilpro]

My very own pgcmd [1]. I usually pipe the output to jq [2] or catj [3] for further processing.

[1] https://github.com/soheilpro/pgcmd

[2] https://stedolan.github.io/jq

[3] https://github.com/soheilpro/catj

[wolfgang000]

dbeaver It's really good and surprisingly lightweight considering it's java app.

[rboyd]

Most of these comments don't seem to cover what you're asking for, which was building the schema visually.

SQLEditor (https://www.malcolmhardie.com/sqleditor/) is quite good if you're on OS X. It'll generate entire schema or migration since last save.

[tomashertus]

I'm using pgAdmin4 and I'm super happy with that. For the day-to-day tasks it's a great tool!

[sbuttgereit]

I liked that pgAdmin had thorough support for the more obscure PostgreSQL features (e.g. domains). However, back in the pgAdmin III days I found it clunky, occasionally unresponsive, and just not fun to work with. With pgAdmin 4... my sense is they doubled down on that with a convoluted architecture where the "client" is a sort of front end to a web server like process they spin up.... really I just wanted a desktop client.

Truth is I never found a PostgreSQL graphical tool that I was happy with. The one with best PostgreSQL support was pgAdmin and the others were all lacking in that regard. In the end I just bit the bullet and took a week to get psql into my muscle memory. Best decision ever; in combination with a good .psqlrc I rarely find myself wanting for anything more. I work with fairly complex highly normalized schemas (100s of tables) and it tells me what I need to know. Of course, I'm doing a huge amount of database work so a more casual database user may not readily be able to get as much mileage out of psql (graphical data models help for some).

I think OmniDB (https://omnidb.org/) is a tool that seems to try to cover the same bases as pgAdmin, but doing a better job of it. I think I'd likely look there if I really needed graphical tool.

For the record, the last graphical tool I used that I was very pleased with was a tool for Oracle database development PL/SQL Developer (https://www.allroundautomations.com/products/pl-sql-develope...). For me, it hit a sweet spot that I've just not seen duplicated... but I stopped working with Oracle over a decade ago and that was when last I used this tool.

[war1025]

They switched pgAdmin from being a native app to a webapp and it degraded the usability quite a bit in my opinion.

[pvsukale3]

PGWeb : https://github.com/sosedoff/pgweb Web-based PostgreSQL database browser written in Go.

very lite and minimal.

[iameoghan]

Oooh, that's very nice. Deffo adding this to my toolkit.

[yen223]

Psql when I want to do anything programmatic

TablePlus when I want to do data exploration.

I've heard good things from colleagues about JetBrain's DataGrip, but I haven't given it a fair go.

[dataminded]

I've been using aquadata for years. It's been a fairly good time.

I have started to transition more of my work to datagrip because I use pycharm and it's right there.