This is what I have managed so far:
I managed to get the service option working really well and reliably. I will next try to spin that (with Haskell’s API executable) into its own container using Nix MicroVM.
Then after that, I’ll work on a straight Docker implementation. Hopefully I can do that where I have Nix read a docker file so it is nix agnostic (for people that don’t know better.)
Here’s what the service module I made looks like (with lots of little goodies added):
{ config, lib, pkgs, name, ... }: with lib; let cfg = config.services.${pgConfig.database.name}.postgresql; pgConfig = import ./postgresql-config.nix; in { options.services.${pgConfig.database.name}.postgresql = { enable = mkEnableOption "Cheeblr PostgreSQL Service"; package = mkOption { type = types.package; default = pkgs.postgresql; description = "PostgreSQL package to use"; }; port = mkOption { type = types.port; default = pgConfig.database.port; description = "PostgreSQL port number"; }; dataDir = mkOption { type = types.str; default = "/var/lib/postgresql/${config.services.postgresql.package.psqlSchema}"; description = "PostgreSQL data directory"; }; }; config = mkIf cfg.enable { services.postgresql = { enable = true; package = cfg.package; enableTCPIP = true; port = cfg.port; dataDir = cfg.dataDir; ensureDatabases = [ pgConfig.database.name ]; authentication = pkgs.lib.mkOverride 10 '' # Local connections use password local all all trust # Allow localhost TCP connections with password host all all 127.0.0.1/32 trust host all all ::1/128 trust ''; initialScript = pkgs.writeText "${pgConfig.database.name}-init" '' DO $$ BEGIN IF NOT EXISTS (SELECT FROM pg_user WHERE usename = '${pgConfig.database.user}') THEN CREATE USER ${pgConfig.database.user} WITH PASSWORD '${pgConfig.database.password}' SUPERUSER; END IF; END $$; CREATE DATABASE ${pgConfig.database.name}; GRANT ALL PRIVILEGES ON DATABASE ${pgConfig.database.name} TO ${pgConfig.database.user}; ''; settings = { # Default config max_connections = 100; shared_buffers = "128MB"; dynamic_shared_memory_type = "posix"; log_destination = "stderr"; logging_collector = true; log_directory = "log"; log_filename = "postgresql-%Y-%m-%d_%H%M%S.log"; log_min_messages = "info"; log_min_error_statement = "info"; log_connections = true; }; }; environment.systemPackages = [ cfg.package ]; environment.variables = { PGHOST = "localhost"; PGPORT = toString cfg.port; PGUSER = pgConfig.database.user; PGDATABASE = pgConfig.database.name; DATABASE_URL = "postgresql://${pgConfig.database.user}:${pgConfig.database.password}@localhost:${toString cfg.port}/${pgConfig.database.name}"; }; }; }
and I broke the config out to one file so I can use the settings in many different iterations of postgresql:
{ ... }: { database = { name = "cheeblr"; user = "postgres"; password = "postgres"; port = 5432; dataDir = "./postgresql"; settings = { max_connections = 100; shared_buffers = "128MB"; dynamic_shared_memory_type = "posix"; log_destination = "stderr"; logging_collector = true; log_directory = "log"; log_filename = "postgresql-%Y-%m-%d_%H%M%S.log"; log_min_messages = "info"; log_min_error_statement = "info"; log_connections = true; listen_addresses = "localhost"; }; }; }
and here are the utilities I built to work with the database:
{ pkgs , lib ? pkgs.lib , name ? "cheeblr" }: let pgConfig = import ./postgresql-config.nix { }; postgresql = pkgs.postgresql; bin = { pgctl = "${postgresql}/bin/pg_ctl"; psql = "${postgresql}/bin/psql"; initdb = "${postgresql}/bin/initdb"; createdb = "${postgresql}/bin/createdb"; pgIsReady = "${postgresql}/bin/pg_isready"; }; config = { dataDir = pgConfig.database.dataDir; port = pgConfig.database.port; user = pgConfig.database.user; password = pgConfig.database.password; }; mkPgConfig = '' listen_addresses = '${pgConfig.database.settings.listen_addresses}' port = ${toString config.port} unix_socket_directories = '$PGDATA' max_connections = ${toString pgConfig.database.settings.max_connections} shared_buffers = '${pgConfig.database.settings.shared_buffers}' dynamic_shared_memory_type = '${pgConfig.database.settings.dynamic_shared_memory_type}' log_destination = 'stderr' logging_collector = off ''; mkHbaConfig = '' local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust ''; envSetup = '' export PGPORT="''${PGPORT:-${toString config.port}}" export PGUSER="''${PGUSER:-${config.user}}" export PGDATABASE="''${PGDATABASE:-${pgConfig.database.name}}" export PGHOST="$PGDATA" ''; validateEnv = '' if [ -z "$PGDATA" ]; then echo "Error: PGDATA environment variable must be set" exit 1 fi ''; in { inherit config; setupScript = pkgs.writeShellScriptBin "pg-setup" '' ${envSetup} ${validateEnv} init_database() { echo "Creating PGDATA directory at: $PGDATA" rm -rf "$PGDATA" mkdir -p "$PGDATA" echo "Initializing database..." ${bin.initdb} -D "$PGDATA" \ --auth=trust \ --no-locale \ --encoding=UTF8 \ --username="${config.user}" # Write config files exactly as in working version cat > "$PGDATA/postgresql.conf" << EOF ${mkPgConfig} EOF cat > "$PGDATA/pg_hba.conf" << EOF ${mkHbaConfig} EOF } start_database() { echo "Starting PostgreSQL..." ${bin.pgctl} -D "$PGDATA" -l "$PGDATA/postgresql.log" start if [ $? -ne 0 ]; then echo "PostgreSQL failed to start. Here's the log:" cat "$PGDATA/postgresql.log" return 1 fi echo "Waiting for PostgreSQL to be ready..." RETRIES=0 while ! ${bin.pgIsReady} -h "$PGHOST" -p "$PGPORT" -q; do RETRIES=$((RETRIES+1)) if [ $RETRIES -eq 10 ]; then echo "PostgreSQL failed to become ready. Here's the log:" cat "$PGDATA/postgresql.log" return 1 fi sleep 1 echo "Still waiting... (attempt $RETRIES/10)" done } setup_database() { echo "Creating database..." ${bin.createdb} -h "$PGHOST" -p "$PGPORT" "$PGDATABASE" if [ $? -ne 0 ]; then echo "Failed to create database" return 1 fi # Use DO block for conditional user creation ${bin.psql} -h "$PGHOST" -p "$PGPORT" "$PGDATABASE" << EOF DO \$\$ BEGIN IF NOT EXISTS (SELECT FROM pg_user WHERE usename = '${config.user}') THEN CREATE USER ${config.user} WITH PASSWORD '${config.password}' SUPERUSER; END IF; END \$\$; GRANT ALL PRIVILEGES ON DATABASE ${pgConfig.database.name} TO ${config.user}; EOF } cleanup() { if [ -f "$PGDATA/postmaster.pid" ]; then echo "Stopping PostgreSQL..." ${bin.pgctl} -D "$PGDATA" stop -m fast fi } trap cleanup EXIT init_database && start_database && setup_database echo "Development environment ready:" echo " Socket directory: $PGHOST" echo " Port: $PGPORT" echo " Database URL: postgresql://${config.user}:${config.password}@localhost:$PGPORT/$PGDATABASE" echo "" echo "You can connect to the database using:" echo " ${bin.psql} -h $PGHOST -p $PGPORT $PGDATABASE" ''; pg-start = pkgs.writeShellScriptBin "pg-start" '' ${envSetup} ${validateEnv} echo "Starting PostgreSQL..." ${bin.pgctl} -D "$PGDATA" -l "$PGDATA/postgresql.log" start if [ $? -ne 0 ]; then echo "PostgreSQL failed to start. Here's the log:" cat "$PGDATA/postgresql.log" exit 1 fi echo "Waiting for PostgreSQL to be ready..." RETRIES=0 while ! ${bin.pgIsReady} -h "$PGHOST" -p "$PGPORT" -q; do RETRIES=$((RETRIES+1)) if [ $RETRIES -eq 10 ]; then echo "PostgreSQL failed to become ready. Here's the log:" cat "$PGDATA/postgresql.log" exit 1 fi sleep 1 echo "Still waiting... (attempt $RETRIES/10)" done ''; pg-connect = pkgs.writeShellScriptBin "pg-connect" '' ${envSetup} ${validateEnv} if [ -z "$PGPORT" ]; then echo "Port must be set" exit 1 fi if [ -z "$PGDATABASE" ]; then echo "Database name must be set" exit 1 fi ${bin.psql} -h $PGHOST -p $PGPORT $PGDATABASE ''; pg-stop = pkgs.writeShellScriptBin "pg-stop" '' ${envSetup} ${validateEnv} ${bin.pgctl} -D "$PGDATA" stop -m fast ''; }
and the current state of my flake:
{ description = "cheeblr"; inputs = { # IOG inputs iogx = { url = "github:input-output-hk/iogx"; inputs.hackage.follows = "hackage"; inputs.CHaP.follows = "CHaP"; inputs.nixpkgs.follows = "nixpkgs"; }; nixpkgs.url = "github:nixos/nixpkgs/nixos-unstable"; iohkNix = { url = "github:input-output-hk/iohk-nix"; inputs.nixpkgs.follows = "nixpkgs"; }; hackage = { url = "github:input-output-hk/hackage.nix"; flake = false; }; CHaP = { url = "github:IntersectMBO/cardano-haskell-packages?rev=35d5d7f7e7cfed87901623262ceea848239fa7f8"; flake = false; }; purescript-overlay = { url = "github:harryprayiv/purescript-overlay"; inputs.nixpkgs.follows = "nixpkgs"; }; flake-utils.url = "github:numtide/flake-utils"; flake-compat = { url = "github:edolstra/flake-compat"; flake = false; }; }; outputs = { self, nixpkgs, flake-utils, iohkNix, CHaP, iogx, purescript-overlay, ... }: { nixosModules = { postgresql = import ./nix/postgresql-service.nix; default = { ... }: { imports = [ self.nixosModules.postgresql ]; }; }; } // flake-utils.lib.eachSystem ["x86_64-linux" "x86_64-darwin" "aarch64-darwin"] (system: let name = "cheeblr"; lib = nixpkgs.lib; overlays = [ iohkNix.overlays.crypto purescript-overlay.overlays.default ]; pkgs = import nixpkgs { inherit system overlays; }; # Shell apps postgresModule = import ./nix/postgres-utils.nix { inherit pkgs name; }; vite = pkgs.writeShellApplication { name = "vite"; runtimeInputs = with pkgs; [ nodejs-slim ]; text = '' export CHEEBLR_BASE_PATH="${self}" npx vite --open ''; }; concurrent = pkgs.writeShellApplication { name = "concurrent"; runtimeInputs = with pkgs; [ concurrently ]; text = '' concurrently\ --color "auto"\ --prefix "[{command}]"\ --handle-input\ --restart-tries 10\ "$@" ''; }; spago-watch = pkgs.writeShellApplication { name = "spago-watch"; runtimeInputs = with pkgs; [ entr spago-unstable ]; text = ''find {src,test} | entr -s "spago $*" ''; }; code-workspace = pkgs.writeShellApplication { name = "code-workspace"; runtimeInputs = with pkgs; [ vscodium ]; text = '' codium cheeblr.code-workspace ''; }; dev = pkgs.writeShellApplication { name = "dev"; runtimeInputs = with pkgs; [ nodejs-slim spago-watch vite concurrent ]; text = '' concurrent "spago-watch build" vite ''; }; in { legacyPackages = pkgs; devShell = pkgs.mkShell { inherit name; nativeBuildInputs = with pkgs; [ pkg-config postgresql zlib openssl.dev libiconv openssl ]; buildInputs = with pkgs; [ # Front End tools esbuild nodejs_20 nixpkgs-fmt purs purs-tidy purs-backend-es purescript-language-server spago-unstable # Back End tools cabal-install ghc haskellPackages.fourmolu haskell-language-server hlint zlib pgcli pkg-config openssl.dev libiconv openssl # PostgreSQL tools postgresModule.setupScript postgresModule.pg-start postgresModule.pg-connect postgresModule.pg-stop pgadmin4-desktopmode # pgmanage # pgadmin4 # DevShell tools spago-watch vite dev code-workspace ] ++ (pkgs.lib.optionals (system == "aarch64-darwin") (with pkgs.darwin.apple_sdk.frameworks; [ Cocoa CoreServices ])); shellHook = '' # Set up PostgreSQL environment export PGDATA="$PWD/.postgres" export PGPORT="5432" export PGUSER="postgres" export PGPASSWORD="postgres" export PGDATABASE="${name}" # Run the setup script pg-setup ''; }; }); nixConfig = { extra-experimental-features = ["nix-command flakes" "ca-derivations"]; allow-import-from-derivation = "true"; extra-substituters = [ ... ]; }; }