Conveniently query db from the shell

8 February 2024

Sometimes you want to use a database (e.g. postgres) to hold personal data that you would like to be able to query quickly and easily.

A nice way to do this and to keep it very simple is to keep a shell function in your .bashrc/.zshrc which you can then query on the terminal.

By way of example, let’s assume that you need help remembering the birthdays of your family and friends. You could just put a recurring event into your digital calendar, but you want to use a database, which is something I can entirely understand.

select person, birthday.birthday from birthday;
person birthday
Jill 1988-04-06
Frodo 1985-09-28
Bob 1989-09-11
Bill 1956-06-01

You want output that looks like this, and tells you which birthdays are coming up:

person birthday days
Jill Saturday 06 April 58
Bill Saturday 01 June 114
Bob Wednesday 11 September 216
Frodo Saturday 28 September 233

With this function in you .zshrc or similar, you can just run f-birthdays and have that output in your shell whenever you want it:

f-birthdays () {
	cat <<EOF | psql -U postgres -h dbhostname personal
with cte_a as (select person,
                              extract(year from current_date)::int,
                              extract(month from birthday)::int,
                              extract(day from birthday)::int) as bday
               from birthday
               where birthday is not null),
     cte_b as (select *,
                      bday - current_date as day_delta
               from cte_a),
     cte_c as (select person,
                      to_char(bday, 'Day DD Month') as birthday,
                          when day_delta < 0
                              then day_delta + 365
                          else day_delta end as days
               from cte_b)
select *
from cte_c
order by days;

(Note that this assumes a table called birthday in a database called personal on the hostname dbhostname, and we query with the user postgres)

To summarise:

Drake says use psql in the shell