Ask HN: What PostgreSQL client do you use? | Hacker News Reader

Ask HN: What PostgreSQL client do you use?

61 pointsiameoghan57 comments

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

View on HN

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.

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

It also supports everything.

Very happy with DBeaver too

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)

Those other little features are like you can modify and edit the resultset of a query. It won't stop for a full minute if a db connection breaks. It saves sql querys you wrote Notepad++ style without asking "ARE YOU SURE?".

Its the little things that adds up and make a much better experience when coming from sqlDeveloper and PgAdmin4.

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

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

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

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.

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

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).

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.

What does your .psqlrc look like? I keep meaning to look into wrangling readline and getting history working well.

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.

Ooh, that looks interesting (even though it doesn't do visual schemas as you say)

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

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

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

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-...

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

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

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.

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.

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

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.

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.

pgcli https://www.pgcli.com/

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?

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.

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...

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

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

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

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.

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

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

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.

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

very lite and minimal.

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

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.

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.

postico is great

I used Aqua Data Studio for years until they changed to $500/yr license. Tried using DataGrip but couldn't used to it, now I just use pgAdmin4.

I suggest: https://www.sql-workbench.eu/

lightweight and JDBC compatible

DbVisualizer has been my main SQL “IDE” for years. https://www.dbvis.com

Datagrip. I love the tight integration with PyCharm.

https://github.com/Paxa/postbird

PgAdmin but I've not really looked around, it's the one I used when I learned postgres and stuck with it.

Valentine studio rocks. Free ver is sufficient.

Cool, will check this out!

Valentina Studio does the job for me.

DBeaver

Can’t live without postico

Postico and Pgweb

psql and dbeaver

pgcli

1. pgAdmin 2. DBeaver 3. Navicat 4. DataGrip 5. OmniDB

This article breaks down the pros and cons, features, and pricing for each of the top PostgreSQL GUI tools: https://scalegrid.io/blog/which-is-the-best-postgresql-gui-2...

Reply on news.ycombinator.com