Matt at Keyboard Writes Code

A blog about software development, technology and other things that interest me

Hacky tools for PostgreSQL

June 14, 2019 — Matt Forrester

Hacky tools for PostgreSQL that makes interacting / extracting / analysis of data in PostgreSQL easier.

screenshot

NOTE: Examples are from a PostgreSQL version of ergast.

psql-out

Sometimes I find I have requests like the following:

Run this query and give me the results [in an Excel file so I can look at them in Excel].

Also sometimes I want to:

Run a complicated query and get the results as ndjson so I can process them in JavaScript.

But sometimes I'm:

Looking at data trying to figure out if a hypothesis makes sense and want to throw things into graphs very, very quickly.

It's not cool, it's not pretty, but this is what psql-out is for!

If you've got your environmental variables set up (see pgpass-env lower down) you can do something like the following:

echo 'select name, position from competitors' | psql-out

And get a well formed CSV file out into STDOUT.

To write it to a file you just need to add > your_file.csv to the end to pipe STDOUT to the desired file:

echo 'select name, position from competitors' | psql-out > your_file.csv

If you want it as an ndjson file you can run

echo 'select name, position from competitors' | psql-out -f ndjson

To get an ndjson file out. You can also get a TSV (Tab seperated CSV) out by passing -t tsv.

termgraph-runner

When you've got your nice CSV using the tools from above and you want to see if the data looks correct quickly one of the best ways I know to do this is to draw a quick graph. You could fire up Excel or LibreOffice and pointy-clicky to get your graph. This is a really bad solution if you're still finding out whether your data is correct because that feedback loop of command-line -> csv -> spreadsheet > graph is pretty long. What if you could quickly draw graphs right in your terminal... You can using termgraph-runner (which is backed by the awesome termgraph.


echo '
    select
        code,
        sum(CASE WHEN position = 1 THEN 1 ELSE 0 END) as win,
        sum(CASE WHEN position <= 3 THEN 1 ELSE 0 END) as podium
    from results
    natural join drivers
    group by code
    order by 2 desc limit 5' | psql-out -f csv | termgraph-runner --stacked

pgpass-env

I found that I have files that look like the following in the root of most of my source code repositories (and in my .gitignore of course):

export PGHOST="127.0.0.1"
export PGDATABASE="my_product"
export PGUSER="my_product"
export PGPASSWORD="a_good_password"
export PGPORT="5432"
export LISTEN_PORT="4040"

However for my database administration GUI I also have a ~/.pgpass file in my home directory with the following:

127.0.0.1:5432:my_product:my_product:a_good_password

This is a duplication of data and is kinda ridiculous.

Enter pgpass-env which is a simple bash script that converts the former into the latter

The idea is to store a name above the lines in the ~/.pgpass like the following

# local_my_product
127.0.0.1:5432:my_product:my_product:a_good_password
# local_another_product
127.0.0.1:5432:another_product:another_product:a_good_password

Running just pgpass-env it gives you a list of possible options:

$ pgpass-env
Pass one of the following
    * local_my_product
    * local_another_product

But if you would pass the name of a connection it would output:

$ . pgpass-env local_my_product
> postgres://my_product@127.0.0.1:5432/my_product

While at the same time performing the required EXPORT PGUSER=my_product etc. Using the preceding . means those environmental variables will be brought into the current environment, which is probably what you want.

It also adds adds $DATABASE_URL which I use in vim-dadbod but I also understand is used by Heroku.

NOTE: Look at the BASH source code, pgpass-env is quick, simple code to get the job done, not perfect code. You can see that the .pgpass fields are separated by : but I have put no thought in how to escape a : should one be included in a password. If your password includes a : it'll probably break.

Installation

Installation is simple with some BASH tomfoolery:

mkdir -p ~/.local/bin && find . -maxdepth 1 -type f -executable | parallel ln -s "$PWD/{/}" ~/.local/bin

Other interesting tools I've found to do portions of this...

I've not found anything that I can use to draw pie charts simply in the terminal - ideas welcome.

Software
  • graph-cli Can accept input from STDIN and will pop up your graph in a window.
  • Veusz Is a desktop app which looks like a mix of Tableau and a DTP application. It's file format is plain text and it even has a Python API. It should be possible to wrap this and spit out a great image file.
  • feedgnuplot looks like the thing I'd use if I wanted to draw a line graph. Because it's based on gnuplot it can draw your line graph right in the terminal or can popup a window.
Libraries
  • ggplot is an R library for drawing graphs, could probably whip something up again to output an image file.
  • vega and vega-lite are by far the best JavaScript graphing libraries I've ever come across. You can specify a graph using just json and they have a CLI interface that can spit out png's etc.

You can find this project at GitHub.

Tags: linux, unix, cli, command-line, postgresql