FAQ & Troubleshooting

General

What is NpgsqlRest?

NpgsqlRest is a self-contained executable that connects to PostgreSQL and automatically creates REST API endpoints from plain SQL script files, database functions, and procedures. No code generation step, no ORM — just SQL.

What PostgreSQL versions are supported?

PostgreSQL 13 and newer. Discovery uses standard pg_catalog views that are stable across PostgreSQL versions.

What .NET version is required?

None, for most users — the standalone executable and the Docker image are fully self-contained (AOT-compiled). The NuGet library targets .NET 10.

Is it safe from SQL injection?

Yes, by construction. Client-supplied values are always sent as PostgreSQL protocol parameters — never concatenated into SQL text. The SQL that runs is the SQL you wrote in the file, function, or procedure; a request can only choose parameter values, never SQL fragments. There is no query-building layer to inject into (unlike REST-to-SQL translators that construct queries from URLs).

How does NpgsqlRest compare to PostgREST or Supabase?

See the detailed comparison blog post for a full feature-by-feature breakdown. The short version: PostgREST turns your tables into an API you query from the client; NpgsqlRest turns your SQL (files, functions, procedures) into an API you design.

Can I use it inside an existing ASP.NET Core application?

Yes — the standalone executable is a wrapper around the NpgsqlRest NuGet middleware. In your own app: app.UseNpgsqlRest(new NpgsqlRestOptions(connectionString) { ... }). The plugins (SQL file source, TypeScript client, OpenAPI, MCP) are separate NuGet packages.

---

Installation & Setup

How do I install NpgsqlRest?

See the Installation Guide — standalone executable (Linux/macOS/Windows), an npm package (npm i npgsqlrest), a Docker image, or NuGet.

How do I run it in Docker?

sh

docker run --name npgsqlrest -p 8080:8080 \
  -v ./appsettings.json:/app/appsettings.json \
  vbilopav/npgsqlrest:latest

Remember that localhost inside the container is the container — point the connection string at host.docker.internal (or the compose service name) to reach your database.

How do I connect to my database?

Set the connection string in appsettings.json:

json

{
  "ConnectionStrings": {
    "Default": "Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=postgres"
  }
}

See Connection Settings for all options.

Can I use environment variables for configuration?

Yes. With ParseEnvironmentVariables in the Config section (enabled by default), use {ENV_VAR_NAME} placeholders in configuration values — optional by default, or {!ENV_VAR_NAME} for required (startup error when unset, since 3.17.0). An .env file is supported via the EnvFile option. Any setting can also be overridden on the command line: npgsqlrest --npgsqlrest:urlpathprefix=/v1.

---

Endpoints

My function doesn't appear as an endpoint

Check these causes, most common first:

1. No HTTP annotation — since 3.17.0 the client defaults to CommentsMode: "OnlyAnnotated": a routine becomes an endpoint only if its comment contains an HTTP annotation (or a plugin annotation like @mcp). Add one — comment on function my_func() is 'HTTP GET'; — or set CommentsMode: "ParseAll" to expose everything discovered.
2. Schema not included: by default only the public schema is scanned. Adjust SchemaSimilarTo in NpgsqlRest Options.
3. Insufficient privileges: the connection's database user needs EXECUTE on the function and USAGE on the schema.
4. A @disabled annotation on the routine.
5. Check the logs: run with the NpgsqlRest log level at Debug to see what was discovered and skipped.

My SQL file doesn't appear as an endpoint

Same CommentsMode rule as functions — the file needs an HTTP annotation by default. Two additional file-specific causes:

1. SkipPattern — files matching SqlFileSource.SkipPattern (default "*.test.sql") are excluded from endpoint discovery; they're test files for the test runner.
2. A describe error with ErrorMode: "Skip" — the file failed type-checking against the database and was skipped with a logged error. (ErrorMode: "Exit", the default, would have stopped startup and shown it.)

An endpoint exists but I get 404 — why?

A 404 for an existing path is almost always parameter matching: a request must supply values for all parameters without defaults, with matching names — otherwise no endpoint matches and the response is 404 (not 400). Check:

1. Parameter names are converted — p_user_id becomes pUserId with the default camelCase converter. The generated TypeScript client or HTTP file always shows the exact names.
2. Missing required parameter — give it a default (@param name default null in SQL files, DEFAULT in function signatures) to make it optional.
3. The path prefix — the full path includes UrlPathPrefix (default /api).
4. The HTTP method — select files/functions map to GET by default; mutations map to PUT/POST/DELETE.

Why are parameter and column names camelCased? How do I turn that off?

The default NameConverter converts snake_case PostgreSQL names to camelCase JSON/URL names. Set "CamelCaseNames": false in the NpgsqlRest section to keep names exactly as they are in the database.

My query returns one row — why do I get an array?

Endpoints return arrays by default. Annotate with @single to return the first row as a single JSON object, or combine with @nested for composite shapes. A single-column result set returns a flat value array (["a","b"]) — that's the UnnamedSingleColumnSet default in SQL File Source.

How do I return plain text, HTML, or CSV instead of JSON?

Use @raw — the column values are written to the response verbatim, with @separator and @new_line for delimiter control, plus a Content-Type response header:

sql

-- HTTP GET
-- @raw
-- @separator ,
-- @new_line \n
-- Content-Type: text/csv
select id, name, price from products;

There is also a table format output mode for ready-made HTML tables and Excel exports.

How do I customize the endpoint URL path?

Use the @path annotation — -- @path /custom/path in a SQL file, or the same line in a function comment. Versioning works the same way: @path /v2/orders.

How do I restrict access to an endpoint?

Use @authorize, optionally with roles: -- @authorize admin, manager. Anything without @authorize is public unless you flip the global RequiresAuthorization option — then everything requires auth and @allow_anonymous opts out per endpoint.

Can I expose tables and views directly, without writing any SQL?

That's deliberately not the default model — NpgsqlRest wants you to design the API surface. The closest thing is a one-line SQL file per operation (select * from my_view; is a complete endpoint file). If you want fully automatic table CRUD, the NpgsqlRest.CrudSource NuGet plugin exists for library users, but plain SQL files are the recommended path.

---

Parameters

Named or positional parameters in SQL files — which should I use?

Named (:name, since 3.19.0) for almost everything — the placeholder is the parameter name, so no @param naming annotations are needed, and the same name used repeatedly (even across statements) is one parameter:

sql

-- HTTP GET
select id, title from reports
where created_at between :from_date and :to_date;

GET /api/get-reports?fromDate=...&toDate=... — done. Positional ($1, $2) remains fully supported; one style per file. See SQL File Endpoints — Parameters.

How do I make a parameter optional?

Give it a default. Functions: the native DEFAULT clause. SQL files: the @param annotation — -- @param status default 'active' or -- @param label default null. A parameter without a default is required, and a request missing it gets a 404 (no matching endpoint).

How do I get the authenticated user's ID into a query?

Enable claim-to-parameter mapping with @user_parameters and use a parameter whose name matches a claim mapping (default: _user_id → the user-id claim). With named parameters this needs nothing else:

sql

-- HTTP GET
-- @authorize
-- @user_parameters
select id, total, status
from orders
where user_id = :_user_id;

The value comes from the authenticated principal — the client cannot send or override it. (With positional parameters, add -- @param $1 _user_id to give $1 the mapped name.)

Error: "could not determine data type of parameter"

PostgreSQL couldn't infer the parameter's type from context (classic case: select set_config('key', :value, true)). Give it a type hint: -- @param :value text (or -- @param $1 value text positionally), or add an inline cast in the SQL (:value::text).

---

Authentication

What authentication methods are supported?

Cookie-based auth, JWT Bearer tokens, Microsoft Bearer tokens, HTTP Basic Auth, Passkeys/WebAuthn (FIDO2), and external OAuth providers (Google, GitHub, LinkedIn, Microsoft, Facebook). All can be enabled simultaneously. See Authentication config.

How do I set up JWT authentication?

json

{
  "Auth": {
    "JwtAuth": true,
    "JwtSecret": "your-secret-key-at-least-32-characters-long",
    "JwtExpire": "60 minutes"
  }
}

See the Multiple Auth Schemes blog post for a complete walkthrough including login endpoints and RBAC.

---

Testing

How do I test my endpoints?

With the built-in SQL test runner (since 3.19.0): write tests as plain .sql files and run npgsqlrest ./config.json --test. A test inserts fixtures, invokes a real endpoint in-process (full pipeline: routing, auth, parameter binding, serialization), asserts on the captured response with ordinary SQL, and rolls back — endpoints see the test's uncommitted data because they run on the test's own connection and transaction.

sql

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

/*
GET /api/get-users
# @claim user_id=1
*/
select status = 200, 'authenticated caller gets 200' from _response;

rollback;

Can tests run against a temporary database instead of my real one?

Yes — that's the recommended CI setup. Setup steps create (and Teardown drops) a uniquely named database (app_test_{rnd5}), migrations run as a step, and TestRunner.ConnectionName points the whole run at it. Template databases give per-test clones for perfect isolation. See the scenario catalog in the Testing Guide.

My test fixtures need half the database inserted first — is there a better way?

Yes, and it's pure PostgreSQL: declare your foreign keys deferrable, then start the test with set constraints all deferred;. Deferred constraints are checked at COMMIT — and a test that ends in rollback never commits, so you can insert only the rows the test is about, in any order, referencing rows that don't exist. No fixture factories, no dependency-ordered setup. See the technique in the Testing Guide.

Is there a watch mode?

Two, with one flag. npgsqlrest ... --test --watch re-runs tests on changes — a changed test re-runs alone in milliseconds; a changed endpoint file or database routine rebuilds the endpoints in-process and re-runs everything, reporting exactly which endpoints appeared or dropped; Ctrl+C still tears the test database down. npgsqlrest ... --watch (without --test) watches the running server — it restarts on SQL file, configuration, and database routine changes, regenerating code (TypeScript client, HTTP files) on every cycle, so create or replace a function in psql and the endpoint is live seconds later. See Watch Mode configuration.

---

Performance

How fast is it?

Independent-methodology benchmarks measure thousands of requests per second on a single host — see the 2025 benchmark post for numbers against PostgREST, PostGraphile, and Hasura, including the methodology. The executables are AOT-compiled native binaries; there is no JIT warmup and no reflection at runtime.

How do I enable caching?

Annotate with @cached (+ @cache_expires_in 5 minutes). The backend (Memory, Redis, or HybridCache) is configured in Cache Options; per-user and per-parameter cache keys are supported.

How do I enable response compression?

json

{ "ResponseCompression": { "Enabled": true } }

See Response Compression.

How do I set up rate limiting?

Define policies in Rate Limiter config and apply them per endpoint with @rate_limiter_policy.

---

Debugging & Logging

How do I see which endpoints are created and what options they have?

Set the NpgsqlRest log level to Debug — every endpoint logs as it is created, including which annotations were applied:

json

{ "Log": { "MinimalLevels": { "NpgsqlRest": "Debug" } } }

Or list them without starting the server: npgsqlrest --endpoints.

How do I log the SQL each endpoint executes at runtime?

Two settings: "LogCommands": true in the NpgsqlRest section opts in, and the NpgsqlRest channel must be at Verbose (commands log at trace level):

json

{
  "NpgsqlRest": { "LogCommands": true },
  "Log": { "MinimalLevels": { "NpgsqlRest": "Verbose" } }
}

Add "LogCommandParameters": true to include parameter values (mind the sensitive-data implications in production — @security_sensitive obfuscates a specific endpoint). See the Logging Guide for the full picture.

How do I see the metadata queries NpgsqlRest runs at startup?

Set the NpgsqlRest log level to Verbose — includes everything from Debug plus the raw pg_catalog discovery queries and the SQL-file describe phase. Useful when a function or file isn't being discovered and you need to see the underlying query and its filters.

How do I completely silence a logger?

Since 3.19.0 any Log:MinimalLevels entry accepts "Off" (aliases "None", "Silent"):

json

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

Each named logger is independent — handy for muting the application channels while watching the test runner's NpgsqlRestTest channel.

---

Troubleshooting

Startup warning: "Unknown configuration key"

Almost always a typo in appsettings.json — keys are validated at startup. Run npgsqlrest --config to print the complete annotated configuration, or use the published JSON schema for editor autocompletion.

Error: "permission denied for schema"

The database user lacks USAGE on the schema:

sql

grant usage on schema my_schema to my_user;
grant execute on all functions in schema my_schema to my_user;

This is also a feature: run the server as a least-privilege role and endpoints can only do what that role can do.

Timeout errors (504 Gateway Timeout)

Adjust the command timeout per endpoint with @command_timeout 2 minutes, or globally in configuration.

Encrypted data is unreadable after restart

Data Protection keys default to in-memory on Linux — configure persistent storage:

json

{ "DataProtection": { "Storage": "FileSystem", "FileSystemPath": "/var/lib/npgsqlrest/keys" } }

See Data Protection config.

Leftover *_abcde test databases

The test runner drops its {rnd}-named databases on every exit path it can intercept — including Ctrl+C, SIGTERM, and hard startup errors. Leftovers mean a run was killed with SIGKILL (nothing can intercept that) or ran with Keep: true. Drop them manually:

sql

select format('drop database %I with (force);', datname)
from pg_database where datname like 'app_test_%' \gexec