This article explains one way to get from zero to a psql shell inside a local PostgreSQL instance with pgTAP enabled. It’s based on the Nix shell template article.

Intro

To recap, all you need is to install Nix install Nix, declare your environment in a shell.nix file, and run nix-shell to enter the environment. We’ll start from the template shell.nix in the original article:

let
  pkgs = import (fetchTarball {
    name = "nixos-23.05_2023-06-30";
    url = "https://github.com/NixOS/nixpkgs/archive/b72aa95f7f096382bff3aea5f8fde645bca07422.tar.gz";
    sha256 = "1ndnsfzff0jdxvjnjnrdm74x8xq2c221hfr7swdnxm7pkmi5w9q5";
  }) { };
in
pkgs.mkShell { packages = [ pkgs.bashInteractive ]; }

Building it

(Feel free to skip this part if you just want the thrilling conclusion.)

How do we need to change the template to achieve this? We can split it into a few easily manageable tasks. First, we’ll need to install PostgreSQL itself. That means changing the packages list to include pkgs.postgresql. Then we need a small Bash script to set up and connect to the database:

  1. Set up some directives to exit early in case of failure in any of the subsequent commands.
  2. Configure an “exit trap” to stop the database and remove the files1 when exiting the interactive database session below.
  3. Create the database cluster using the initdb command.
  4. Start PostgreSQL itself using the pg_ctl command.
  5. Create a test database using the createdb command. This lets us work on a completely blank database, rather than an internal database.
  6. Open an interactive shell within the test database using the psql command.

The result so far:

let
  pkgs = import (fetchTarball {
    name = "nixos-23.05_2023-06-30";
    url = "https://github.com/NixOS/nixpkgs/archive/b72aa95f7f096382bff3aea5f8fde645bca07422.tar.gz";
    sha256 = "1ndnsfzff0jdxvjnjnrdm74x8xq2c221hfr7swdnxm7pkmi5w9q5";
  }) { };
in
pkgs.mkShell {
  packages = [
    pkgs.bashInteractive
    pkgs.postgresql
  ];

  env.PGDATA = "${toString ./.pgdata}";

  shellHook = ''
    set -o errexit -o noclobber -o nounset

    # Remove traces of running server when exiting this shell hook
    cleanup() {
      pg_ctl stop
      rm --force --recursive "$PGDATA"
    }
    trap cleanup EXIT

    # Create database cluster
    initdb --auth-local=trust --auth-host=trust

    # Start server
    pg_ctl --log="$PGDATA/db.log" --options="-c unix_socket_directories='$PGDATA'" start

    # Create test database
    db_name=test
    createdb "$db_name" --host="$PGDATA"

    # Connect to database
    psql --dbname="$db_name" --host="$PGDATA"

    # Return from Bash after exiting psql
    exit
  '';
}

Now we just need one more ingredient, pgTAP itself. We need to install it in such a way that the PostgreSQL package is aware of it, and then we need to enable it on our test database.

The result

let
  pkgs = import (fetchTarball {
    name = "nixos-23.05_2023-06-30";
    url = "https://github.com/NixOS/nixpkgs/archive/b72aa95f7f096382bff3aea5f8fde645bca07422.tar.gz";
    sha256 = "1ndnsfzff0jdxvjnjnrdm74x8xq2c221hfr7swdnxm7pkmi5w9q5";
  }) { };
in
pkgs.mkShell {
  packages = [
    pkgs.bashInteractive
    (pkgs.postgresql.withPackages (postgresqlPackages: [ postgresqlPackages.pgtap ]))
  ];

  env.PGDATA = "${toString ./.pgdata}";

  shellHook = ''
    set -o errexit -o noclobber -o nounset

    # Remove traces of running server when exiting this shell hook
    cleanup() {
      pg_ctl stop
      rm --force --recursive "$PGDATA"
    }
    trap cleanup EXIT

    # Create database cluster
    initdb --auth-local=trust --auth-host=trust

    # Start server
    pg_ctl --log="$PGDATA/db.log" --options="-c unix_socket_directories='$PGDATA'" start

    # Create test database
    db_name=test
    createdb "$db_name" --host="$PGDATA"

    # Enable pgTAP
    psql --command="CREATE EXTENSION pgtap" --dbname="$db_name" --host="$PGDATA"

    # Connect to database
    psql --dbname="$db_name" --host="$PGDATA"

    # Return from Bash after exiting psql
    exit
  '';
}

pgTAP is now available by simply running nix-shell! To verify, try running a trivial test suite:

SELECT * FROM no_plan();
SELECT is(2, 2);
SELECT * FROM finish();

Variants

As you can see, enabling other PostgreSQL extensions from nixpkgs is just a matter of adding two lines: postgresqlPackages.[…] and the psql --command="CREATE EXTENSION […]" --dbname="$db_name" --host="$PGDATA" command.

It’s also possible to run ancient versions of PostgreSQL by installing it from an older version of nixpkgs. Click on any of the versions above to get detailed instructions.

  1. If you want to keep the database files around after stopping PostgreSQL, simply delete the rm […] line in the cleanup function. ↩