Configuration for generating REST API endpoints from .sql files.

{
  "NpgsqlRest": {
    "SqlFileSource": {
      "Enabled": false,
      "FilePattern": "",
      "CommentsMode": "OnlyWithHttpTag",
      "CommentScope": "All",
      "ErrorMode": "Exit",
      "ResultPrefix": "result",
      "UnnamedSingleColumnSet": true,
      "NestedJsonForCompositeTypes": false
    }
  }
}

Enable or disable SQL file source endpoints. Default is false — you must explicitly enable this feature.

"SqlFileSource": {
  "Enabled": true
}

Glob pattern for locating SQL files. Supports the following wildcards:

When ** is present in the pattern, * stops matching / (standard glob semantics). When no ** is present, * matches / for backward compatibility.

// All .sql files in the sql/ directory (non-recursive)
"FilePattern": "sql/*.sql"

// All .sql files in sql/ and all subdirectories (recursive)
"FilePattern": "sql/**/*.sql"

// Any .sql file at any depth
"FilePattern": "**/*.sql"

An empty string disables the feature (even if Enabled is true).

Controls how comment annotations affect SQL file endpoint creation.

::: tip The default OnlyWithHttpTag means only SQL files with an explicit HTTP annotation (e.g., -- HTTP GET) become endpoints. Use ParseAll if you want every SQL file in the matched pattern to become an endpoint automatically. :::

Controls which comments in the SQL file are parsed as annotations.

With CommentScope: "Header", only comments before the first statement are parsed:

-- This IS parsed as an annotation
-- HTTP GET
-- @authorize admin

select * from users;

-- This is NOT parsed (after first statement)
-- @cached

With CommentScope: "All" (default), all comments are parsed regardless of position.

Controls behavior when a SQL file fails to parse or when PostgreSQL reports an error during the describe phase.

All SQL file errors are logged at Error level. In Exit mode, a Critical log explains the exit and how to switch to Skip mode.

A warning is logged when the configured file pattern matches no files.

Errors caught at startup include:

• Parse errors (malformed SQL, unclosed strings/quotes)
• Describe errors (PostgreSQL syntax errors, invalid table/column references)
• Parameter type conflicts in multi-command files

::: tip Use Exit (default) during development to catch SQL errors early. Use Skip in production to tolerate partial failures. :::

Prefix for result keys in multi-command JSON responses. Default keys are result1, result2, result3, etc.

// Default: result1, result2, ...
"ResultPrefix": "result"

// Custom: data1, data2, ...
"ResultPrefix": "data"

// Custom: query1, query2, ...
"ResultPrefix": "query"

Individual result keys can be overridden per-file using the @result annotation.

When true (default), single-column queries return flat arrays instead of arrays of objects. This matches the behavior of PostgreSQL functions returning setof single values.

-- sql/get_names.sql
select name from users;

With UnnamedSingleColumnSet: true (default):

["Alice", "Bob", "Charlie"]

With UnnamedSingleColumnSet: false:

[{"name": "Alice"}, {"name": "Bob"}, {"name": "Charlie"}]

This applies to both single-command endpoints and per-result in multi-command files.

Controls how composite type columns are serialized in SQL file endpoint responses.

Default (flat): Composite fields are spliced inline into the JSON row:

-- sql/get_user_with_address.sql
-- HTTP GET
-- @param $1 user_id
select id, address from users where id = $1;
-- where address is: create type address_type as (street text, city text, zip text)

{"id": 1, "street": "123 Main St", "city": "New York", "zip": "10001"}

With NestedJsonForCompositeTypes: true or @nested annotation: Composite wrapped under column name:

{"id": 1, "address": {"street": "123 Main St", "city": "New York", "zip": "10001"}}

Enable globally for all SQL file endpoints:

"SqlFileSource": {
  "Enabled": true,
  "FilePattern": "sql/**/*.sql",
  "NestedJsonForCompositeTypes": true
}

Or per-endpoint with the @nested annotation:

-- sql/get_user_with_address.sql
-- HTTP GET
-- @nested
-- @param $1 user_id
select id, address from users where id = $1;

NULL composites are serialized as null in nested mode, or as individual null fields in flat mode.

::: tip This setting is also available in Routine Options for function/procedure endpoints. Each endpoint source has its own independent setting. :::

Controls whether multi-command SQL file endpoints include the full SQL text in debug command logs.

Default (false): Only the file path and statement count are logged:

[DBG] -- POST http://127.0.0.1:8080/api/send-message
-- $1 text = 'hello'
SQL file: sql/send-message.sql (5 statements)

With LogCommandText: true: The full SQL body of all statements is logged.

{
  "NpgsqlRest": {
    "SqlFileSource": {
      "LogCommandText": true
    }
  }
}

Single-command SQL file endpoints always log the SQL text regardless of this setting. This only applies when LogCommands is true.

1. Enable the SQL file source in appsettings.json:

{
  "NpgsqlRest": {
    "SqlFileSource": {
      "Enabled": true,
      "FilePattern": "sql/**/*.sql"
    }
  }
}

2. Create a SQL file:

-- sql/get_users.sql
-- HTTP GET
-- @authorize
-- @param $1 active
select id, name, email from users where active = $1;

3. The endpoint is available at GET /api/get-users?active=true

• SQL File Endpoints Guide — the full task-oriented walkthrough
• Changelog v3.12.0 - Release notes for the SQL file source feature
• PARAM - Rename, retype, and configure parameters
• RESULT_NAME - Rename multi-command result keys
• NpgsqlRest Options - Core API generation settings