PostgreSQL Extensions with Nix and devenv

Today, at $CURRENT_JOB, we began working on an application that leverages a PostgreSQL extension called periods. According to its README:

This extension recreates the behavior defined in SQL:2016 (originally in SQL:2011) around periods and tables with SYSTEM VERSIONING.

Fortunately, Nix and devenv do all the hard work for us. In order to have a PostgreSQL 15 package with the desired extensions, one can do the following with Nix:

pkgs.postgresql_15.withPackages (p: [ p.periods ]);

Well, this expression only results in a PostgreSQL package with the periods extensions available. However, it doesn't do anything other than that, not even installs it on our machine. Devenv solves this issue for us, letting us declaratively configure a PostgreSQL instance for local development:

# omitted code for brevity
services.postgres = {
  package = pkgs.postgresql_15.withPackages (p: [ p.periods ]);
  enable = true;
  port = 5432;
  listen_addresses = "127.0.0.1";
};

Running the command nix develop --impure followed by devenv up will spin up a local PostgreSQL instance. Let's check the extensions we have:

$ psql
db=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

Where's the periods extension? Well, it is available, not installed!

db=# select * from pg_available_extensions where name = 'periods';
  name   | default_version | installed_version |                               comment
---------+-----------------+-------------------+----------------------------------------------------------------------
 periods | 1.2             |                   | Provide Standard SQL functionality for PERIODs and SYSTEM VERSIONING
(1 row)

Installing it "by default" is as easy as adding this new attribute on devenv:

services.postgres = {
  # ...
  initialScript = ''
    CREATE EXTENSION btree_gist;
    CREATE EXTENSION periods;
  '';
};