description: Build and modify REST APIs with NpgsqlRest — exposing PostgreSQL as HTTP endpoints from two sources (database functions/procedures/tables/views, and plain .sql files), driven by SQL comment annotations (no C# needed). Use when working in an NpgsqlRest project: writing or changing endpoint SQL (functions or .sql files), comment annotations (HTTP routing, authorize, cached, proxy, HTTP Custom Types, SSE, MCP, upload), appsettings.json config, testing endpoints with SQL test files (`--test`), running in watch mode (`--watch`), or running/troubleshooting the `npgsqlrest` client.

Files matching `SkipPattern` (default `"*.test.sql"`) are excluded from endpoint discovery — they are test files for the [test runner](#testing-sql-test-files---test), which makes the co-located layout (`app.sql` next to `app.test.sql`) safe.

where created_at between :from_date and :to_date;

- **Parameters: named (`:name`) or positional (`$1, $2`)** — one style per file (mixing is a startup error). **Prefer named**: the placeholder IS the parameter name (camelCased for the API: `:from_date` → `fromDate`), a repeated name is ONE parameter (also across statements), and claim mappings (`@user_parameters` + `:_user_id`) hook up with zero annotations. `@param` then only handles type/default: `@param from_date default null`, `@param :from_date date` (Describe type hint), `@param from_date type is date` (retype without rename). Positional: `@param $N name [type] [default ...]` names/retypes/defaults. `@define_param name [type]` creates a *virtual* param (placeholders/claims; not bound to SQL). Tokenizer caveats for `:name`: `::casts`, `:=`, and `a[1:3]` never match; a variable slice bound needs a space (`a[1 : n]`).

**Test files only** (`*.test.sql`, run by `--test` — not endpoint annotations)

- Header: `-- @setup Step ...`, `-- @teardown Step ...`, `-- @connection Name`, `-- @tag a, b`. Inside HTTP blocks: `# @claim name=value`, `# @response name`. Includes: `\i file` / `\ir file`.

npgsqlrest ./config.json --test # run SQL test files, then exit (0 pass / 1 fail / 2 error / 3 config / 4 none)

npgsqlrest ./config.json --watch # dev server: restart on SQL file / config / database routine changes

npgsqlrest ./config.json --test --watch # dev test loop: re-run tests on changes

Tests are plain `.sql` files (glob: `TestRunner.FilePattern`, e.g. `./tests/**/*.test.sql` or co-located next to endpoints). Each file runs on its **own non-pooled connection**, in parallel; endpoints are invoked **in-process** (full pipeline: routing, auth, params, serialization) **on the test's own connection/transaction** — so a test can `begin`, insert fixtures, call the endpoint (it sees the uncommitted rows), assert, and `rollback`.

begin;

insert into users (email) values ('x@example.com');

select (select status from _response) = 200, 'authenticated caller gets 200';

select (select body::jsonb @> '[{"email": "x@example.com"}]' from _response), 'fixture listed';

rollback;

Essentials:

- **Assertions**: a SELECT whose first column is `boolean` (2nd column = assertion name), or a `do $$ ... assert ... $$` block. Everything else is arrange/act.

- **HTTP blocks**: block comment, first line `METHOD /full/path` (incl. `/api` prefix). Directives: `# @claim name=value` (repeatable; any claim = authenticated principal, none = anonymous), `# @response name` (custom capture table). Body after a blank line. Response lands in temp table `_response` (`_response_{n}` for 2+ blocks): `status int, body text, content_type text, headers jsonb, is_success boolean`.

- **Per-file header annotations** (leading `--` comments, TEST files only): `-- @setup StepName ...`, `-- @teardown StepName ...`, `-- @connection Name` (perfect isolation: run the file on its own database), `-- @tag smoke, slow`. Reuse scripts with psql-style `\i file` / `\ir file` includes (paste semantics).

- **Setup/Teardown/Steps** (`TestRunner` config): run-once steps in written order — `{ "Sql": ... }` / `{ "SqlFile": ... }` / `{ "Command": ... }`, each with optional `ConnectionName` and `Enabled` (false = ignored wherever referenced; the default config ships disabled examples to flip on). `Setup` runs BEFORE endpoint discovery → it can `create database app_test_{rnd5}` on an admin connection; `TestRunner.ConnectionName` points the whole run at it; teardown drops it (guaranteed — runs even on Ctrl+C/SIGTERM). `{rnd1}`..`{rnd10}` are per-run-stable random tokens (`{rndN_1}`.. for independent instances).

- **Selection**: `--testrunner:filter=login` (path substring/glob), `--testrunner:tag=smoke --testrunner:excludetag=slow`.

- **Endpoint coverage** reports after full runs (on by default; `CoverageThreshold: 100` fails the build naming untested endpoints). `JUnitOutput` for CI.

- **Debugging**: `DetailedReport: true` (richer report); `ResponseTempTable.DebugTable: "_responses_debug"` mirrors every response into a permanent table (survives rollback; query it after the run; not for CI); the `NpgsqlRestTest` log channel at `Verbose` shows every statement and HTTP call.

One flag (`--watch`, or config `Watch: { "Enabled": true }`), two flavors chosen by `--test`:

- **Server watch** (`--watch`): a supervisor restarts the server (~1s) on `.sql` source changes (SkipPattern-filtered), **configuration file** changes, and **database routine changes** — polling runs the routine discovery query hashed server-side (`Watch:DatabasePollingInterval`, default `2s`; detects create/replace/drop/comment on functions, grants, and type/table shape changes; never fires on unrelated objects). Code generation (TS client, HTTP files) re-runs every cycle. A broken SQL file logs its error and drops only its endpoint (`ErrorMode` forced to `Skip` while watching).

- **Test watch** (`--test --watch`): a changed test re-runs alone; a changed endpoint file or database routine rebuilds endpoints in-process (endpoint delta reported) and re-runs everything; teardown runs once, on exit.

For Docker Desktop bind mounts set `DOTNET_USE_POLLING_FILE_WATCHER=1` (file events only; database polling is unaffected).

- **SQL files:** annotations are in `--`/`/* */` comments; params are named `:name` (preferred — auto-named) or positional `$N` (name via `@param`) — **never mixed in one file** (startup error); a startup Describe error fails boot unless `ErrorMode: Skip`; use `@returns` for statements over not-yet-existing objects (temp tables); `DO` blocks can't take params or return values.

- **A `.sql` file named `*.test.sql` never becomes an endpoint** — `SqlFileSource.SkipPattern` excludes test files by default.

- **Test HTTP block paths must be the FULL path** including `UrlPathPrefix` (`/api/...`) — a bare `/get-users` is a 404 with a warning.

- **`ResponseTempTable.DebugTable` is a dev-only debugging aid** — never enable in CI; combine with `Keep: true` when the run drops its test database.