Changelog v3.19.0

Version 3.19.0 (2026-07-03)

Full Changelog

This release introduces the SQL test runner (npgsqlrest --test) — write tests for your endpoints as plain .sql files, invoke endpoints in-process from inside a test, and assert on both the HTTP response and the database state, all within the test's own transaction. Also included: watch mode (--watch — re-run tests or restart the server on SQL and configuration changes), named parameters in SQL files (:name), a skip glob for the SQL file source, and the ability to mute an individual logger.

---

1. SQL test runner (--test)

code

npgsqlrest ./config.json --test

The runner discovers .sql test files, executes each on its own isolated connection, and reports per-assertion results to the console (and optionally JUnit XML for CI). A test file is ordinary SQL — arrange data, call an endpoint, assert on the result:

sql

-- tests/get_users_excludes_caller.test.sql
begin;

insert into app.users (id, email, name) values (100, 'x@example.com', 'Fixture');

/*
GET /api/get-users
# @claim user_id=1
*/
select status = 200,
       'authenticated caller gets 200'
from _response;
select body::jsonb @> '[{"email": "x@example.com"}]',
       'the fixture user is listed'
from _response;

rollback;

code

NpgsqlRest test runner — 9 file(s)
PASS  tests/get_users_excludes_caller.test.sql  (2 assertions, 52ms)
...
19 passed, 0 failed, 0 error(s)  —  19 assertions in 9 files

How it works

In --test mode the client builds the full endpoint middleware exactly as in normal operation (endpoints from database routines and/or SQL files, authentication, custom parameters — everything), but instead of starting the web server it runs the test files and exits with a result code. Endpoint calls made from a test are in-process: no network, no running server — the complete endpoint pipeline (routing, authorization, parameter binding, execution, serialization) runs against a synthetic HTTP context.

The critical property is connection affinity: the in-process endpoint call runs on the test's own connection, inside the test's own transaction. A test can begin, insert fixture rows, call an endpoint that sees those uncommitted rows, assert on the response, and rollback — leaving no trace. Each test file gets its own non-pooled physical connection (fresh session: no temp-table, GUC, or prepared-statement carryover), and files run in parallel (MaxParallelism, default = processor count). If a file never rolls back, closing its physical connection aborts the open transaction — that is the safety net.

Test-mode invariants, applied automatically:

• WrapInTransaction is forced off (the test file owns transaction control — the runner never injects BEGIN/COMMIT/ROLLBACK).
• Response caching is disabled (a test never sees another test's cached response).
• Code generation (HTTP files, TypeScript client, OpenAPI) is skipped — a test run never rewrites generated artifacts.

Test file anatomy

A test file is a sequence of SQL statements and HTTP blocks, executed strictly in order. Files are executed statement by statement (like psql): each statement runs in autocommit unless the file opens its own transaction. Semicolon splitting understands line/block comments, string literals with '' escapes, and dollar-quoted bodies — a do $$ … $$; block stays whole.

Assertions — a reported test is one of:

• A boolean-returning SELECT. If the first column is boolean, the statement is an assertion: the first row's value must be true (false or null fails; zero rows passes vacuously; only the first row is examined). The optional second column is the assertion's name/message, shown in the report and used as the JUnit test-case name:
  sql

  select count(*) = 3, 'exactly three users are seeded' from app.users;

• A do block. Passes unless it raises — assert inside a DO block raises SQLSTATE P0004, reported as a failure with the assert message. One DO block = one reported test (multiple asserts inside it are opaque to the runner):
  sql

  do $$ begin
      assert app.normalize_email(' X@Y.z ') = 'x@y.z', 'should trim and lowercase';
  end $$;

Any other statement is arrange/act — not counted as a test; it only surfaces if it errors. Any SQL error (other than an assert) is reported as an error with its SQLSTATE, message, statement text, and file:line.

Failing behavior is fail-fast per file: after the first failed/errored assertion the rest of the file does not run (a failed DO-block assert aborts the transaction anyway). Assertions that passed before the failure are still credited.

HTTP blocks — invoking endpoints

An HTTP request is embedded in a block comment whose first non-comment line is a request line — a single-request subset of the standard .http file syntax:

sql

/*
POST /api/create-user
Content-Type: application/json
# @claim user_id=42
# @claim roles=admin
# @response created

{"name": "Grace Hopper", "email": "grace@example.com"}
*/

Syntax rules:

• Request line: [HTTP] METHOD /path[?query] [HTTP/x] — the method is one of GET, POST, PUT, DELETE (the methods NpgsqlRest endpoints support); the path must start with / and must equal the endpoint's full path including UrlPathPrefix (default /api); the leading HTTP keyword and a trailing HTTP-version token are optional. A block comment whose first line is not a valid request line is an ordinary SQL comment — ignored.
• Headers: Name: Value lines after the request line. Content-Type is picked up for the request body.
• Directives (lines starting with # @ or // @, placed after the request line, before the body):
  • # @claim name=value — adds a claim to the acting principal. Repeatable, including the same claim type twice (e.g. two roles claims). Any # @claim makes the request authenticated; no # @claim means anonymous (an @authorize endpoint returns 401). Role checks and claim-to-parameter mappings (@user_parameters, ParameterNameClaimsMapping) work exactly as in production — tests exercise the real authorization path.
  • # @response name — capture this block's response into a temp table with the given name instead of the default.
• Body: everything after the first blank line, verbatim. (Limitation: a literal */ inside the body ends the SQL comment early.)
• Plain # or // lines are comments; unknown @ directives are ignored.

An HTTP block is an act step, not an assertion — it captures the response and produces no test result of its own. The assertions are the SQL statements that follow it. One request per block; use multiple blocks for multiple calls.

Endpoint kinds that cannot work meaningfully in-process are rejected with a clear error: SSE, upload, login/logout (inject the principal with # @claim instead), and outbound proxy/HTTP-type endpoints (tests must not call external services). A request whose path matches no endpoint still runs (a test may assert the 404 deliberately) but logs a warning — the most common cause is a path typo or a missing /api prefix.

The response temp table

Each HTTP block's response is captured into its own temp table on the test's connection, created fresh (no IF NOT EXISTS — a duplicate name, e.g. a repeated # @response name, fails the test loudly). Default columns:

Column	Type	Content
status	int	HTTP status code
body	text	response body (cast to ::jsonb to assert on JSON)
content_type	text	response content type
headers	jsonb	response headers
is_success	boolean	true for 2xx

Naming: a file with one HTTP block uses ResponseTempTable.Name (default _response); a file with two or more uses ResponseTempTable.MultiNamePattern (default _response_{n}) where {n} is the block's 1-based position — _response_1, _response_2, … A block with # @response name uses that name instead (it still counts in the numbering of the others). Column names are configurable; setting one to null/empty omits that column.

sql

select body::jsonb ->> 'email' = 'grace@example.com',
       'email is normalized to lowercase'
from _response;

Debugging captured responses — temp tables vanish with the test's rollback and connection, so you cannot inspect them afterwards (and re-issuing the request from an .http file cannot reproduce a response that depended on the test's uncommitted fixtures). Set ResponseTempTable.DebugTable (e.g. "_responses_debug"; default null = off) and every captured response is also mirrored into a permanent table — written on a separate autocommit connection, immune to rollbacks, recreated at the start of every run so it always holds the last run. One table covers everything: each HTTP block adds one row, with test_file, block (that block's response-table name — _response, a _response_{n} ordinal, or the # @response name), method, path, status, body, content_type, headers, is_success, and captured_at — so after a run you can open a query editor and dig into any response with jsonb operators. The temp-table semantics are unchanged; enabling it prints a loud warning (debugging aid — do not enable in CI). In the fresh-test-database workflow combine it with Keep: true, or teardown drops the database and the mirror with it.

Reusing scripts: \i and \ir includes

Shared SQL — fixture inserts, utility scripts — can be spliced into any test with psql's include syntax on its own line:

sql

begin;

\ir fixtures/extra_users.sql    -- path relative to THIS file (psql \ir semantics)
\i  ./shared/reset_counters.sql -- path relative to the cwd, like every other configured path

/* GET /api/get-users */
select jsonb_array_length(body::jsonb) = 5, 'seed + fixture users listed' from _response;

rollback;

An include behaves as if you pasted the file's content at that spot: SQL statements, assertions (counted as tests, attributed to the included file), HTTP blocks (they participate in _response_{n} numbering exactly as if pasted), and even header annotations — -- @setup/-- @teardown/-- @connection in an included file's leading comments count when the include sits in the host file's header region. That last one enables a shared profile idiom: put an annotation set in one file and attach it with a single include line (see example 21's shared/isolated_database.sql — one \ir line gives a test its own cloned database). Everything runs on the test's connection, inside the test's transaction — a fixture included this way rolls back with the test, so it is invisible to every other test and leaves no residue.

Two footnotes where "pasted" is refined rather than literal:

• An include must stand between complete statements — it cannot sit inside an unfinished statement or contribute a fragment of one (for reusable SQL fragments, use what PostgreSQL already provides: functions and views).
• Error attribution is better than a paste: a failure inside an included file is reported with the included file's name and line (the same thing psql does), not a line number in an imaginary merged file.

Includes nest (cycle-safe, depth-capped) and work in Setup/Teardown SqlFile steps too. A path may be single-quoted (\ir 'my fixtures/data.sql') and a trailing ; is forgiven; a non-include backslash line is passed through to PostgreSQL untouched (no other psql meta-commands are supported).

Setup and Teardown, and named steps

Run-once steps around the whole test session. Setup runs before endpoint discovery (so it can create/migrate the very schema the endpoints are built from); Teardown always runs at the end — even when tests fail or Setup itself fails (best-effort; Keep: true skips it to let you inspect state). Steps execute in the exact order written; a step is one of:

• { "Sql": "..." } — inline SQL,
• { "SqlFile": "path" } — a SQL file (executed statement by statement, like test files; \i/\ir includes work),
• { "Command": "...", "WorkingDirectory": "..." } — a shell command (e.g. docker compose up -d, an external migration tool). Non-zero exit fails Setup.

Sql/SqlFile steps run on the test connection by default, or on any named ConnectionStrings entry via a per-step "ConnectionName" — which enables maintenance operations like create database without the runner ever issuing DDL on its own.

Teardown is guaranteed beyond the happy path: from Setup onward the runner intercepts SIGINT (Ctrl+C) and SIGTERM (e.g. docker stop) and runs Teardown synchronously in the signal handler — before the process can be torn down by an impatient parent (bun run/npm run forward Ctrl+C and may kill their children immediately; waiting for the run loop to unwind would lose that race). A second Ctrl+C force-quits. A process-exit hook additionally covers hard exits — e.g. a broken endpoint SQL file under SqlFileSource.ErrorMode: Exit calls Environment.Exit(1), which previously leaked the just-created test database; the exit code is unchanged, but Teardown now runs first. All paths funnel into a run-once Teardown. (A parent that SIGKILLs instantly remains unsurvivable — that is what a leading drop database if exists … on a static name, or a periodic sweep, is for.)

Steps can be defined once in the Steps registry (name → step, like ConnectionStrings or CacheOptions.Profiles) and referenced by name; Setup/Teardown arrays accept names and inline objects mixed. Referencing an unknown name is a configuration error (exit 3).

Every step also has an Enabled flag (default true): a disabled step is simply ignored wherever it is referenced — skipped with a debug log line, never an error. The default configuration ships disabled example steps covering the typical scenarios (create/drop a {rnd}-named test database on an admin connection, apply a schema file, run a migration tool, start/stop a Docker PostgreSQL) — they show every step property in place, so instead of typing a step from scratch you copy one, adjust the names, and flip Enabled to true:

json

{
  "TestRunner": {
    "Steps": {
      "CreateTestDatabase":  { "Enabled": false, "ConnectionName": "Admin", "Sql": "create database app_test_{rnd5}" },
      "DropTestDatabase":    { "Enabled": false, "ConnectionName": "Admin", "Sql": "drop database if exists app_test_{rnd5} with (force)" },
      "ApplySchema":         { "Enabled": false, "SqlFile": "./migrations/schema.sql" },
      "RunMigrationTool":    { "Enabled": false, "Command": "echo replace with your migration tool command", "WorkingDirectory": "." },
      "StartDockerPostgres": { "Enabled": false, "Command": "docker run -d --name npgsqlrest-test-pg -e POSTGRES_PASSWORD=postgres -p 54329:5432 postgres" },
      "StopDockerPostgres":  { "Enabled": false, "Command": "docker rm -f npgsqlrest-test-pg" }
    }
  }
}

jsonc

{
  "TestRunner": {
    "Steps": {
      "CreateDatabase":  { "Sql": "create database app_test_{rnd5}", "ConnectionName": "Admin" },
      "ApplyMigrations": { "SqlFile": "./migrations/schema.sql" },
      "DropDatabase":    { "Sql": "drop database if exists app_test_{rnd5} with (force)", "ConnectionName": "Admin" }
    },
    "Setup":    ["CreateDatabase", "ApplyMigrations"],
    "Teardown": ["DropDatabase"]
  }
}

Per-file setup, teardown, and connection (header annotations)

An individual test file can attach named steps — and pick its own connection — with leading -- comment annotations (the same annotation idiom endpoint .sql files use), placed before the first statement:

sql

-- @setup CreateIsolatedDb
-- @teardown DropIsolatedDb
-- @connection Isolated

• -- @setup Name [Name …] — runs the named steps before this file (own connections, committed work — e.g. clone a database this file will use). An unknown step name fails the file with an error.
• -- @teardown Name [Name …] — runs after this file, always (best-effort, even when the test fails or times out), after the file's connection is closed — so a drop database … with (force) teardown works. An unknown step name logs a warning.
• -- @connection Name — runs this file, including its in-process endpoint calls, on a named ConnectionStrings entry instead of the test connection. An unknown name fails the file with an error.
• -- @tag Name [Name …] — declares the file's tags, filtered with the Tag/ExcludeTag options (see the filtering section below).

@setup and @teardown are repeatable, and one line may carry several names, separated by whitespace or commas (the NpgsqlRest annotation idiom — -- @setup A B, -- @setup A, B, and two -- @setup lines are all equivalent). Names accumulate and execute in exactly the order written — the same contract as the global Setup/Teardown arrays; teardown is not reversed, so write the step you want last, last. Setup is fail-fast (the first failing or unknown step stops the chain, the file body never runs, teardown still runs); each teardown step is best-effort (a failure logs a warning and the remaining steps still run).

Annotations can also come from an include in the header region — includes behave as if pasted, so a shared annotation "profile" file attaches with one line: \ir shared/isolated_database.sql (see the includes section above).

Since every word after @setup/@teardown is read as a step name, don't describe these annotations in a file's header comments using their literal syntax (-- @setup CreateDb creates the db… would try to run steps named creates, the, db…). Other -- comment lines in the header are ignored as usual.

Together these give per-test database isolation: a file's setup clones a migrated template (create database … template … — a near-instant file-level copy), @connection points the file at the clone, and teardown drops it. That is the escalation path for state that transaction rollback cannot isolate — most prominently sequences, which advance even when the transaction rolls back, so generated ids are only deterministic in a fresh clone. Per-file steps commit to shared state, so steps that mutate the shared test database should be idempotent or run under MaxParallelism: 1; in-transaction fixture reuse belongs to \ir instead.

A dedicated test database

TestRunner.ConnectionName points the whole test session — endpoint type-checking (SQL-file Describe), endpoint execution, and the tests — at a named ConnectionStrings entry instead of the app's main connection. That database need not exist at startup: it is never opened before Setup, so the first Setup step can create it.

jsonc

{
  "ConnectionStrings": {
    "Default": "Host={PGHOST};Database=appdb;...",                     // the real app DB — untouched by tests
    "Admin":   "Host={PGHOST};Database=postgres;...",                  // maintenance (needs CREATEDB)
    "Test":    "Host={PGHOST};Database=app_test_{rnd6};..."            // the throwaway test DB
  },
  "TestRunner": {
    "ConnectionName": "Test",
    "FilePattern": "./tests/**/*.test.sql",
    "Steps": {
      "CreateDatabase":  { "Sql": "create database app_test_{rnd6}", "ConnectionName": "Admin" },
      "ApplyMigrations": { "SqlFile": "./migrations/schema.sql" },     // runs on "Test"
      "DropDatabase":    { "Sql": "drop database if exists app_test_{rnd6} with (force)", "ConnectionName": "Admin" }
    },
    "Setup":    ["CreateDatabase", "ApplyMigrations"],
    "Teardown": ["DropDatabase"]
  }
}

{rnd1}…{rnd10} are random lowercase tokens (length = the digit), generated once per run and substituted everywhere {ENV} placeholders work — connection strings, Setup/Teardown SQL, and Commands. The same token yields the same value across the whole config, so the connection string, the create, and the drop all name the same database; concurrent suites on a shared server can't collide. When several distinct tokens of the same length are needed, the indexed instances {rndN_1}…{rndN_9} are each independent — {rnd3}, {rnd3_1} and {rnd3_2} are three different 3-character tokens, each stable for the run. (Trade-off: a hard crash that skips Teardown orphans that run's database. A static name with a leading drop database if exists … with (force); in Setup is the self-healing alternative.)

The same Setup/Teardown machinery covers the neighboring workflows with no additional features: clone a prepared template (create database … template app_template — near-instant; migrate the template once in Setup and clone it for the run and for -- @setup-annotated per-test databases), start a Docker Postgres (Command steps: docker run → wait for pg_isready → migrate; docker rm -f in Teardown), or run an external migrator (EF Core, Django, Flyway) as a Command.

Reporting

Results are per assertion (like pgTAP/xUnit — each boolean SELECT / DO block is one test), grouped per file:

code

PASS  tests/login_succeeds.test.sql  (3 assertions, 50ms)
FAIL  tests/get_users.test.sql  (49ms)
        ✗ the caller is excluded — 2 of 3 users listed  [tests/get_users.test.sql:17]
        select jsonb_array_length(body::jsonb) = 2, …

18 passed, 1 failed, 0 error(s)  —  19 assertions in 9 files

Failures show the assertion name, file:line, and the failing statement. DetailedReport: true additionally lists passed assertions (✓), full failing SQL, and captured raise notice output for passing tests (notices always show under failing tests). This shapes the console report only — it is distinct from raising the NpgsqlRestTest log level, which controls diagnostics (see Logging below). A file with no recognizable assertions is flagged rather than silently counted.

The report's colors are matched to Serilog's Code console theme, so the test report and the log lines around it read as one output: the FAIL/ERROR labels render as the byte-identical chip the theme uses for its ERR/FTL level (red text on a dark-grey block), PASS uses the same chip grammar in the mirror green, the rest of each line stays in the terminal's normal text color, and all failure text uses the theme's error red — never the 16-color red that renders orange in some terminals. Colors are disabled automatically when output is redirected (piped/CI logs stay plain).

JUnit XML (JUnitOutput: "path.xml"): one <testcase> per assertion (name = the assertion message, classname = the file), <failure>/<error> with message and file:line, captured notices in <system-out>, files without assertions marked <skipped> — works with any CI dashboard.

Exit codes: 0 all passed · 1 at least one failure · 2 at least one error (SQL error, timeout, unsupported endpoint, an interrupted run) · 3 setup/configuration error · 4 no test files found (AllowEmpty: true turns this into 0).

Logging

The runner logs through its own channel — NpgsqlRestTest (configurable via TestRunner.LoggerName) — leveled independently under Log:MinimalLevels (defaults to Information when absent, i.e. quiet):

• Verbose — every SQL statement and every HTTP invocation (GET /api/x → 200, captured into "_response"),
• Debug — discovery, per-file parse results, per-file outcomes, Setup/Teardown steps, degree of parallelism,
• Warning — a request that matches no endpoint, failed teardown steps,
• raise notice/warning from the database — logged by their severity, tagged with the test file that emitted them.

jsonc

{
  "Log": { "MinimalLevels": { "NpgsqlRest": "Off", "NpgsqlRestClient": "Off", "NpgsqlRestTest": "Verbose" } }
}

Configuration reference (TestRunner section)

jsonc

{
  "TestRunner": {
    "FilePattern": "",                    // glob selecting test files (same engine as SqlFileSource); empty disables
    "Filter": "",                         // narrow the discovered set: substring, or glob when it contains wildcards
    "Tag": "",                            // run only files carrying at least one of these tags (-- @tag name ...)
    "ExcludeTag": "",                     // skip files carrying any of these tags (wins over Tag)
    "ConnectionName": "",                 // ConnectionStrings entry to test against; empty = the main connection
    "MaxParallelism": 0,                  // concurrent test files; 0 = processor count
    "FailFast": false,                    // stop scheduling new files after the first failure (in-flight finish)
    "PerTestTimeout": "30s",              // per-file timeout: "30s", "5m", "1h", plain seconds, "hh:mm:ss"; 0 disables
    "JUnitOutput": null,                  // optional path for a JUnit XML report
    "Keep": false,                        // skip Teardown (inspect state after a failed run)
    "DetailedReport": false,              // detailed console report: passed ✓ lines, full failing SQL, notices for passing tests
    "AllowEmpty": false,                  // exit 0 instead of 4 when no tests are found
    "Coverage": null,                     // coverage summary: null (default) = on for full runs, quiet when narrowed; true/false = always/never
    "CoverageThreshold": null,            // 0-100: always report + fail an otherwise-passing run (exit 2) below it
    "LoggerName": "NpgsqlRestTest",       // the runner's log channel (leveled via Log:MinimalLevels)
    "ResponseTempTable": {
      "Name": "_response",                // table name when a file has ONE HTTP block
      "MultiNamePattern": "_response_{n}",// name pattern for 2+ blocks; {n} = 1-based block position
      "DebugTable": null,                 // debugging aid: ALSO mirror every response into this PERMANENT table (survives rollback; last run; not for CI)
      "Columns": {                        // response → column mapping; null/empty omits the column
        "Status": "status", "Body": "body", "ContentType": "content_type",
        "Headers": "headers", "IsSuccess": "is_success"
      }
    },
    "Steps": { },                         // named, reusable steps (name → step) for Setup/Teardown and -- @setup/-- @teardown;
                                          // each has "Enabled" (false = ignored wherever referenced); ships disabled examples
    "Setup": [],                          // run-once, BEFORE endpoint discovery, in written order (step names or inline objects)
    "Teardown": []                        // run-once, ALWAYS, in written order (Keep skips; same entries as Setup)
  }
}

A practical convention is to keep the TestRunner block (and quiet log levels) in a separate test-config.json layered on only for test runs: npgsqlrest ./config.json ./test-config.json --test.

Iterating on one test: Filter narrows the run to matching files, and like every option it can be set from the command line:

code

npgsqlrest ./config.json ./test-config.json --test --testrunner:filter=login

A value without wildcards is a case-insensitive substring match against each file's cwd-relative path (login runs every *login* file); a value with wildcards uses the same glob engine as FilePattern (**/get_users_shows*). Setup and Teardown still run — the filtered subset executes in the complete environment — and a filter that matches nothing exits with code 4 (AllowEmpty applies).

Tags group tests orthogonally to the directory layout. A file declares them with a header annotation, and runs are narrowed with Tag (include — the file must carry at least one) and ExcludeTag (skip — wins over include); both accept comma- or whitespace-separated lists, case-insensitive, and compose with Filter:

sql

-- @tag smoke, regression

code

npgsqlrest ... --test --testrunner:tag=smoke --testrunner:excludetag=slow

Since includes behave as if pasted, tags travel through a shared profile too: a profile file carrying -- @tag isolation, slow next to its -- @setup/-- @connection annotations tags every test that attaches it — e.g. all clone-isolated tests are automatically slow, so the everyday dev loop is just --testrunner:excludetag=slow, with zero per-file bookkeeping.

Endpoint coverage is something only an integrated runner can offer: the runner knows the entire API surface it built and records every endpoint the tests actually invoked, so after the run it reports the API-level analogue of code coverage — including the exact endpoints no test touches. It is on by default for full runs (it costs one line); a run narrowed by Filter/Tag stays quiet — a deliberately partial run would just nag — unless Coverage: true forces it, and Coverage: false silences it entirely:

code

19 passed, 0 failed, 0 error(s)  —  19 assertions in 9 files

endpoint coverage: 1/2 (50%)
        untested: GET /api/get-users

Endpoint kinds the runner rejects (SSE, upload, login/logout, outbound proxy) are excluded from the ratio and counted separately, so the number is honest. CoverageThreshold (0–100) turns it into a CI gate — it always reports, regardless of the Coverage setting or run narrowing: an otherwise-passing run below the threshold exits 2 — set it to 100 and forgetting to write a test for a new endpoint fails the build, naming the endpoint. "Covered" means invoked at least once by a test — execution, not assertion depth (the same semantics as code coverage).

Watch mode (--watch, or Watch:Enabled in configuration) keeps the process alive and re-runs on change — and because the endpoint middleware is built once at startup, re-runs are near-instant:

code

npgsqlrest ./config.json ./test-config.json --test --watch

Setup runs once, then everything runs once, then the test tree — and, when the SQL file source is enabled, the endpoint source tree — is watched recursively for *.sql changes (debounced). Changes are classified per file:

• a changed test file re-runs alone (the Filter still applies);
• a changed endpoint file (matching SqlFileSource.FilePattern) triggers an in-process endpoint rebuild — the sources are re-read and re-described against the test database, and the endpoint registry is swapped atomically — followed by a full rerun. After each rebuild the runner prints the endpoint delta (+ POST /api/new, - GET /api/x (endpoint dropped — check its SQL file for errors)), so breaking an endpoint file mid-session is visible immediately: the endpoint drops out, its tests fail with 404 warnings, and fixing the file brings it right back — no restart. (To make this safe, watch mode forces SqlFileSource.ErrorMode from Exit to Skip — a broken file must not kill the watch session; non-watch --test keeps Exit for CI. A rebuild that fails entirely keeps the previous endpoints live.)
• any other changed .sql under the test tree — an included fixture or profile, whose dependents are unknown — re-runs everything.

Teardown runs once, on exit — synchronously inside the SIGINT/SIGTERM handler (see the Setup and Teardown section), so the test database is dropped even when the watch process is stopped through a wrapper like bun run; a second Ctrl+C force-quits. Interactive/dev-only: a graceful stop exits 0 regardless of test outcomes — watch is not for CI gating. Database-routine sources have no files to watch — restart to pick up catalog changes.

Project layout

Two equally supported conventions — the difference is just the globs:

• Co-located: sql/get_users.sql + sql/get_users.test.sql. Pair the endpoint glob with the new SqlFileSource.SkipPattern (below) so test files are never exposed as endpoints.
• Separate tree: endpoints in sql/, tests in tests/ (named by scenario). The globs never overlap, so no SkipPattern is needed.

---

2. SqlFileSource.SkipPattern — exclude files from endpoint discovery

New option SkipPattern on the SQL file source (config key NpgsqlRest:SqlFileSource:SkipPattern, default "*.test.sql"). Files whose full path matches this glob are excluded from endpoint discovery: a .sql file becomes an endpoint only when it matches FilePattern and does not match SkipPattern.

This is what makes the co-located test layout safe: without it, a test file's /* GET /x */ block would be read as an HTTP annotation and exposed as an endpoint. The pattern uses the same glob engine and semantics as FilePattern (*.ext matches by suffix). Set it to an empty string ("") to disable the exclusion.

Behavior change: the default is "*.test.sql", so files matching that suffix are no longer exposed as endpoints out of the box. If you previously relied on serving *.test.sql files, set SkipPattern to "" to restore the old behavior.

3. Named parameters in SQL files: :name

SQL file endpoints can now use named placeholders instead of the positional $1, $2, …:

sql

/*
HTTP POST
@allow_anonymous
@single
*/
select u.id, u.email, u.full_name as name, r.name as role
from users u
join roles r on r.id = u.role_id
where u.email = :email
  and u.password_hash = crypt(:password, u.password_hash);

The placeholder is the parameter name: :email becomes the API parameter email (through the same NameConverter routine parameters use, so :user_id → userId with the default camelCase converter). The @param $1 email text-style annotations that existed only to name positional parameters are simply unnecessary — the file above needs none. Under the hood the SQL is rewritten to native $N before it is described and executed; PostgreSQL never sees the :name form, so type inference, Describe, and runtime behavior are identical to positional files.

What you get:

• Repetition collapses: the same name used multiple times — including across statements in a multi-command file — is one parameter (where :user_id = author_id or :user_id = editor_id takes a single userId value).
• Claim mappings hook up by placeholder name: select :_user_id under @authorize + @user_parameters binds the mapped claim with zero @param annotations.
• Annotations match by name where you still need them: @param email default null (defaults), @param :email citext (a Describe type hint), and the new retype-without-rename form @param email type is citext — renaming a parameter whose name came from its own placeholder would be nonsense, so type is changes only the type. All positional @param $N … forms keep working unchanged.
• The tokenizer knows SQL: strings ('…', "…", dollar-quoted bodies) and comments are untouched; ::int casts, := named-argument calls, and numeric slice bounds (a[1:3]) never match. A placeholder requires an identifier character immediately after the colon — the one caveat is an array slice with a variable bound, which must be written with a space (a[1 : n]).

One style per file: mixing $N and :name in the same file makes the ordinal assignment ambiguous and is rejected (logged; the file is skipped under ErrorMode: Skip, or exits under Exit).

Why not ? (JDBC style)? Considered and rejected: ?, ?|, ?&, and @? are PostgreSQL's own jsonb/geometric operators — where data ? 'admin' is legal, common SQL that no rewriter can reliably tell apart from a parameter. This is the same reason the PostgreSQL JDBC driver requires ?? escapes. Anonymous-positional already exists as $N.

4. Watch mode: --watch

The --watch flag — shorthand for the Watch:Enabled configuration setting — runs in one of two modes, depending on whether --test is present:

Command	Mode	Watches	On change
npgsqlrest ... --test --watch	Test watch	test files, included fixtures/profiles, the endpoint SQL files (when the SQL file source is enabled), and the database catalog	changed test re-runs alone; endpoint or database change rebuilds endpoints in-process and re-runs everything
npgsqlrest ... --watch	Server watch	the SQL file source tree, the configuration files, and the database catalog	the server restarts (~1s)

Test watch is described in the test runner section above. Server watch needs something to watch — an enabled SQL file source, database polling (on by default, below), or both; with neither, --watch without --test exits with an error.

Watching the routine source — database polling

Routine-source endpoints (functions and procedures) have no files to watch — so watch mode polls the database instead, and it does it with perfect fidelity: the poll runs the same routine discovery query the endpoint source uses, with the same configured filters (schema/name/language includes and excludes), hashed server-side into a single value on a dedicated non-pooled connection (default every 2s). If the hash changes, the discovered endpoints changed — by definition. That covers create/create or replace/drop/alter of functions and procedures (including GRANT/REVOKE), COMMENT ON — i.e. annotation changes, and changes to the composite types and tables used as parameter or return types (alter table users add column reshapes a returns setof users endpoint even though no function changed). Just as importantly, anything the discovery query does not read — an unrelated table, temp objects, data changes — can never cause a spurious restart. Any detected change triggers the same path a file change does: server watch restarts the server, test watch rebuilds endpoints in-process and re-runs the tests (— change detected (database) —); the test runner re-baselines after every rerun so self-inflicted changes never re-trigger.

This makes a routines-only project fully watchable: run npgsqlrest ./config.json --watch, then create or replace a function in psql — the endpoint is live about two seconds later, annotations included.

The whole feature lives in one top-level configuration section (the --watch flag is the shorthand for Watch:Enabled):

json

{
  "Watch": {
    "Enabled": false,
    "DatabasePollingInterval": "2s"
  }
}

DatabasePollingInterval accepts "2s", "500ms", "1m", plain seconds, or "hh:mm:ss"; 0 disables polling. Both settings apply to both watch flavors.

Server watch

sh

npgsqlrest ./config.json --watch

Run the server under a watcher: edit a SQL file and the running API restarts with the change applied (~1s) — add an endpoint and it's immediately callable, break one and the error is on screen while the rest of the API keeps serving, and any configured code generation (TypeScript client, HTTP files, OpenAPI) regenerates on every restart, so the frontend's types follow your SQL as you type.

How it works. The process becomes a small supervisor that spawns itself as a child server (marked by an environment variable) and watches the SqlFileSource tree plus the configuration files themselves. On a debounced change it stops the child gracefully and starts a fresh one — the child runs the completely normal server pipeline, so dev is byte-for-byte production behavior (the same model as dotnet watch). One relaxation: in the watch child, SqlFileSource.ErrorMode is forced from Exit to Skip, so a broken file logs its error and drops only its own endpoint instead of taking the server down.

Behavior:

Event	Result
.sql change under the source tree	restart (files matching SkipPattern — test files — are ignored)
configuration file change	restart with the new configuration
database routine change (detected by polling, above)	restart — — database change detected — restarting —
broken SQL file	restart; the error is logged, that endpoint drops, everything else serves
child crashes/exits on its own	supervisor prints server exited (code N) — waiting for file changes and revives on the next save (no crash-looping)
Ctrl+C / SIGTERM (docker stop)	child stopped gracefully, both processes exit, port freed
supervisor killed hard (SIGKILL)	the child detects the vanished parent and exits by itself — no orphan holding the port

Graceful child stop uses SIGTERM on Linux/macOS; on Windows the child is hard-killed (nothing needs teardown in a dev server). For environments where file events don't cross the filesystem boundary — Docker Desktop bind mounts, network shares — set the ecosystem-standard DOTNET_USE_POLLING_FILE_WATCHER=1 to switch to a 1-second polling scan (applies to both watch modes).

Works in every distribution: the AOT executables (the supervisor respawns Environment.ProcessPath), framework-dependent dotnet NpgsqlRestClient.dll (the dotnet host is re-invoked with the dll), and both Docker image flavors (the supervisor handles PID-1 signal and child-reaping duties).

5. Mute an individual logger with "Off" in Log:MinimalLevels

Each entry under Log:MinimalLevels now accepts "Off" (aliases "None" and "Silent", case-insensitive) to fully silence that logger. Previously the only accepted values were the Serilog levels Verbose…Fatal, and there was no way to turn a logger off completely.

• "Off" / "None" / "Silent" → the logger emits nothing (implemented as a minimum level above Fatal, since Serilog's LogEventLevel has no native "off").
• null, an omitted key, or an unrecognized value → unchanged: the logger keeps its built-in default level.

Each named logger is controlled independently — e.g. mute the application loggers entirely while watching the test runner:

json

{
  "Log": {
    "MinimalLevels": {
      "NpgsqlRest": "Off",
      "NpgsqlRestClient": "Off",
      "NpgsqlRestTest": "Verbose"
    }
  }
}

Notes

• The test runner's core hooks are additive and inert outside --test: an ambient-connection accessor on the endpoint pipeline (null by default) and response headers on the internal invocation result. Normal server operation is unchanged.
• Test files run statement by statement (the client operates Npgsql with SQL rewriting disabled — one statement per command), which is also psql's default execution model; explicit begin/commit/rollback in a file work as ordinary statements. Setup/Teardown Sql/SqlFile steps execute the same way — which is why create database works as a plain step.
• All new options are wired through the client configuration: appsettings.json, the JSON-schema descriptions, and the --config template.
• Working examples: examples/19_testing_basic (co-located layout, multi-step scenario files), examples/20_testing_newdb (separate tests/ tree, one test per file, fresh test database per run via named steps, deferrable-constraint fixtures, authorization + user parameters, a tag taxonomy — smoke/auth/fixtures/login — on every file), and examples/21_testing_isolation (template-clone workflow; two parallel per-test isolated databases — named apart with the indexed {rnd5_1}/{rnd5_2} tokens — proving deterministic sequence ids; a shared annotation profile attached via \ir that also carries the isolation, slow tags; a Command step mixed with named-step references in Setup).

Tests

Full test suite green (2394), including 63 unit tests for the test-file, HTTP-block, header-annotation, and include parsers plus the filter and tag matchers (NpgsqlRestTests/TestRunnerTests/ParserTests/), and 29 for named SQL-file parameters — 22 rewriter unit tests (casts, :=, slices, strings, dollar-quotes, jsonb-path strings, case-insensitive repetition, cross-statement sharing, mixing detection, named type hints) plus 7 end-to-end endpoint tests (auto-naming through the camelCase converter, a repeated placeholder bound from one value, required-parameter matching, name-matched defaults, type is retype, claim mapping by placeholder name, mixed-style rejection, and a multi-command file sharing :id across statements as one API parameter), plus a database-fingerprint test proving the watch poller's hash tracks the routine discovery result exactly (function create/replace/comment/drop and used-type changes fire; temp objects and unrelated tables never do). Watch mode verified live end-to-end in both flavors: file edit/break/fix cycles, config-change restarts, crash recovery, orphan prevention under SIGKILL, graceful SIGTERM teardown, polling-watcher mode, and database-driven changes (a function created in psql serving ~2s later; alter table reshaping a returns setof endpoint; unrelated tables causing zero restarts). All three documentation examples verified end-to-end against live PostgreSQL — including example 21's template-clone workflow (template migrated once; the shared run database and two parallel per-test isolated databases cloned from it concurrently; deterministic sequence ids asserted independently in both clones; everything dropped on teardown), watch mode (single-file rerun on a test change, full rerun on a fixture change, teardown on SIGINT/SIGTERM), path and tag filtering (including tags carried through a profile include), and the coverage report with a failing threshold gate. The new configuration keys are covered by the configuration round-trip tests (the --config template output matches appsettings.json).