My PostgreSQL CLI setup – Negative Feedback

Motivatation

My database of choice is PostgreSQL. I often find myself wanting to do quick ad-hoc queries against a set of well-defined databases. Maybe I just want to know the internal id of a customer from their name, or check on the status of a job.

If I’m doing real reporting and producing outputs I will usually spin up PyCharm. It’s got amazing db support, which is the same as you would get from JetBrain’s dedicated database product DataGrip. Great ergonomics, but Pycharm takes a while to get started and frankly the setup of all our dbs is onerous enough that I haven’t registered all of them. I also use pgAdmin, but that’s also tedious to set up “just right”. And sometimes when you’re already in a terminal mindset, you don’t want to jump out to a GUI.

So I used to ssh into our host and fire up psql. I got laggy input and a bit of a startup-delay due to jumping through our bastion, but it worked well enough that I found myself staring at that no-frills psql prompt a lot.

But I kept thinking it could be better. I was right. After a few hours of ChatGPT and self-directed browsing and experimentation, I ended up assembling a surprisingly nice terminal-based workflow using:

• PostgreSQL service configs
• pgcli
• pspg
• duckdb

The result feels very “close-to-hand”: fast startup, pleasant interaction, integrated with the terminal environment, and has opened up a powerful cross-database workflow.

PostgreSQL Service Definitions

PostgreSQL already has a built-in mechanism for naming database connections: ~/.pg_service.conf

#~/.pg_service.conf
[prod-db1]
host=localhost
port=port1
dbname=prod_db1
user=readonly_prod_db1
sslmode=prefer

[dev-db1]
host=localhost
port=port2
dbname=dev_db1
user=readonly_dev_db1
sslmode=prefer

[prod-db2]
host=localhost
port=port3
dbname=prod_db2
user=readonly_prod_db2
sslmode=prefer

This lets you connect using

I’ve set up port forwarding for our dbs so that the different servers are reachable through different ports. The nice thing about a text-based system like this is you can create the entries programmatically, like if you have dev, qa and prod versions of all these dbs.

Password Management

PostgreSQL uses ~/.pgpass for password lookup.

Example:

#~/.pgpass
#host:port:dbname:username:password
localhost:port1:prod_db1:readonly_prod_db1:password1
localhost:port2:dev_db1:readonly_dev_db1:password2
localhost:port3:prod_db2:readonly_prod_db2:password3

Postgres won’t read from this if you don’t set these permissions:

Once configured, authentication becomes automatic. If it freaks you out to have passwords stored at rest like this, you can generate .pgpass on the fly and pass it through as an environment variable to a local terminal, too.

A Better Interactive Client: pgcli

psql is powerful and reliable, but pgcli makes interactive querying much more pleasant.

Install with Homebrew:

Then:

This gives you quite a nice interactive experience. As-you-type syntax highlighting, a visible autocomplete, even for those hard-to-remember backslash psql commands, great history searching:

I can’t say enough good things about pgcli (bonus: check out litecli which is the same thing for sqlite).

I also edit ~/.config/pgcli/config, which in the default homebrew install gets set to a nicely commented default config, to set table_format = fancygrid.

A Better Pager: pspg

Another huge win is having a better pager. Normally, large query results in psql are paged through less, which is not very pleasant for tabular data.

pspg is a terminal pager specifically designed for SQL tables.

Install using Homebrew:

Configure in ~/.psqlrc:

# ~/.psqlrc
# Some other config I like
\pset linestyle unicode
\pset border 2
\pset null ∅

# The actual pager
\setenv PSQL_PAGER 'pspg'

This gives you a table-aware browser instead of a line-based pager. It freezes headers, allows scrolling and searching in straightforward ways and has a built-in save-to-csv feature (I can never remember the syntax for outputing to csv in postgres). pgcli also supports this by setting pager = pspg in the config

Themes and Colors

The default coloring in pspg doesn’t quite match my preferences. Fortunately it has configuration in ~/.pspgconf

# ~/.pspgconf
theme = 17 # Solarized Dark
border_type = 2
force_uniborder = true
ignore_case = true

More theme information is available from their github page.

Maybe an even better pager: visidata

As I was writing this, I came across an intriguing idea, using visidata as a pager. This gives you even more interactivity, but has a steeper learning curve. I’ve been using visidata for a while for quick CSV viewing, and this intrigued me, I might do a different post on those results.

DuckDB Changes Everything

The biggest surprise for me was how nicely DuckDB integrates into this workflow.

DuckDB can attach PostgreSQL databases directly:

LOAD postgres;

ATTACH 'service=prod-db1'
AS prod_db1
(TYPE postgres, READ_ONLY);

ATTACH 'service=prod-db2'
AS prod_db2
(TYPE postgres, READ_ONLY);

At that point you can query across completely separate PostgreSQL databases:

SELECT
    a.field1,
    b.field2
FROM prod_db1.public.table1 a
JOIN prod_db2.public.table2 b
    ON a.id = b.foreign_id;

That’s an astonishingly powerful capability for ad-hoc investigation work.

DuckDB’s default output and paging is already pretty nice, but they support custom pagers, too.

Lazy Database Attachment

One downside of having all the attach statements in one file is that DuckDB will connect to all of them on startup. So I ended up using small helper files.

Example:

-- ~/.duckdb/prod-db1.sql

LOAD postgres;

ATTACH IF NOT EXISTS 'service=prod-db1'
AS prod_db1
(TYPE postgres, READ_ONLY);

Then inside DuckDB:

.read ~/.duckdb/prod-db1.sql

This gives explicit, lazy attachment only when needed.

Final word

This all took a bit of time to reserach and implement, even with ChatGPT giving me a lot of help. I’m reminded of the first time I spent some time customising my zsh.

For quick investigative work, it has really made some of the older parts of my workflow feel new again.