Chapter 04: Real Services, Repeatable Checks

Objective

Start PostgreSQL through devenv and test a transaction without leaving persistent test data.

Your tutor should stop at each gate and wait for your evidence before continuing.

Concept

Unit tests exercise Python behavior; an integration test exercises a real dependency. This lab adds a separate database contract: insert two tasks, complete one, and count one remaining. It does not yet connect the Python functions to a database. Keeping that boundary explicit prevents a green database check from being mistaken for application persistence coverage.

A declared service still has mutable state. PostgreSQL’s data directory survives ordinary development restarts. Initial database declarations run during initialization, not as a migration engine for an existing database. Tests must tolerate repetition and avoid depending on yesterday’s rows.

Concept check

Q1. Why can two runs with identical Nix inputs see different database contents?

Q2. Does the database test prove that new_task writes to PostgreSQL?

Gate: Declare the service

Copy postgres.nix from the archive, then add an import inside the outer braces of devenv.nix:

imports = [ ./postgres.nix ];

The module is:

{ pkgs, lib, config, ... }:
{
  services.postgres = {
    enable = true;
    package = pkgs.postgresql_17;
    listen_addresses = "";
    initialDatabases = [{ name = "agent_lab"; }];
  };

  # An absolute socket directory supplied by devenv, rather than localhost TCP.
  env.PGHOST = config.services.postgres.settings.unix_socket_directories;
  env.PGDATABASE = "agent_lab";

  enterTest = lib.mkAfter ''
    bash scripts/check-db.sh
  '';
}

An empty listen_addresses disables TCP listeners. Clients use the Unix socket declared by devenv. This is a local teaching database with local authentication, not a production database configuration.

Gate: Verify the service

In one terminal, run devenv up. In a second terminal in the same project:

devenv shell psql -X -d agent_lab -c 'SELECT version();'

Pass: PostgreSQL answers through the project socket. Inspect devenv shell env only locally if needed; never paste full environments into chat because they may contain credentials.

Stop the foreground process manager with Ctrl-C before the next gate. Two managers trying to own one database directory can collide.

Gate: Run a transaction check

Read scripts/check-db.sh:

#!/usr/bin/env bash
set -euo pipefail
ready=0
for attempt in {1..30}; do
  if pg_isready -q; then ready=1; break; fi
  sleep 1
done
if [ "$ready" != 1 ]; then echo 'Postgres not ready after 30 seconds' >&2; exit 1; fi

# Temporary table and rollback keep this repeatable in a persistent dev database.
result=$(psql -X -qAt -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
CREATE TEMP TABLE lab_tasks (title text NOT NULL, done boolean NOT NULL DEFAULT false);
INSERT INTO lab_tasks (title) VALUES ('Read Nix'), ('Ship');
UPDATE lab_tasks SET done = true WHERE title = 'Read Nix';
SELECT count(*) FROM lab_tasks WHERE NOT done;
ROLLBACK;
SQL
)
test "$result" = 1
echo 'Database contract passed: one pending task; transaction rolled back.'

Now run devenv test twice. devenv owns the service lifecycle for the test. Our script waits for readiness, and ON_ERROR_STOP=1 makes SQL errors fail the command. The temporary table and rollback prevent leaked rows.

Pass: both runs pass with exactly one pending task. Change the expected count to 2, observe a failure, and restore it.

Gate: Plan a real persistence extension

Describe the extra tests needed to connect Python to PostgreSQL: schema migration, insert/read round-trip, transaction rollback on invalid input, and independent fixtures. Do not count the current SQL smoke test as those tests.

Known failure modes

• A stale service can own the socket or data directory. Stop it through the process manager; do not delete a database to resolve a port conflict.
• Initialization changes do not rewrite existing schemas. Add migrations or create an explicitly disposable test database.
• A long checkout path may exceed Unix socket path limits; use a shorter lab path.
• A ready server is not a verified query. Readiness and assertions are separate gates.

Check your understanding

Q3. Why use ON_ERROR_STOP=1?

Q4. What keeps this SQL test repeatable?

References

• PostgreSQL service options
• psql reference

← Chapter 03 · Next: Chapter 05 →