Skip to content
Hacky tools for PostgreSQL that makes interacting / extracting / analysis of data in PostgreSQL easier.
Shell
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
LICENSE
README.md
pgpass-env
psql-out
screenshot.png
termgraph-runner

README.md

Hacky tools for PostgreSQL

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’t perform that action at this time.