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;
'';
};