### Clone and Run NpgsqlRest Example

Source: https://npgsqlrest.github.io/blog/sql-file-source-rest-api-from-plain-sql.html

Clone the NpgsqlRest documentation repository, install dependencies, navigate to the first example, and run the database setup and development server. Access the API at the provided URL.

```bash
git clone https://github.com/NpgsqlRest/npgsqlrest-docs.git
cd npgsqlrest-docs/examples && bun install
cd 1_my_first_function_sql_file
bun run db:up && bun run dev
# Visit http://127.0.0.1:8080
```

--------------------------------

### Running Npgsql.Rest Example Project

Source: https://npgsqlrest.github.io/blog/excel-export-table-format-postgresql-npgsqlrest.html

This bash script outlines the steps to clone the Npgsql.Rest documentation repository, install dependencies, navigate to an example, apply database migrations, and start the development server.

```bash
# Clone the repository
git clone https://github.com/NpgsqlRest/npgsqlrest-docs.git
cd npgsqlrest-docs/examples

# Install dependencies
bun install

# Navigate to the example
cd 14_table_format

# Apply database migrations
bun run db:up

# Start the server
bun run dev
```

--------------------------------

### Setup and Run NpgsqlRest Example

Source: https://npgsqlrest.github.io/blog/postgresql-bi-server-excel-csv-basic-auth

Steps to clone the NpgsqlRest documentation repository, navigate to the example directory, generate a self-signed SSL certificate, and run the NpgsqlRest server with a configuration file.

```bash
# Clone the repository
git clone https://github.com/NpgsqlRest/npgsqlrest-docs.git

# Navigate to example
cd npgsqlrest-docs/examples/5_csv_basic_auth

# Generate certificate
dotnet dev-certs https --export-path ./localhost.pfx --password dev123

# Run migrations and start
npgsqlrest --config config.json
```

--------------------------------

### Running the NpgsqlRest CRUD API Example

Source: https://npgsqlrest.github.io/blog/zero-to-crud-api-postgresql-tables-npgsqlrest.html

Commands to set up and run the NpgsqlRest CRUD API example project. This includes database migrations, frontend build, and starting the development server.

```bash
cd examples/11_crud_auto_api

# Create database tables
bun run db:up

# Build frontend
bun run build

# Start server
bun run dev
```

--------------------------------

### Run NpgsqlRest Example

Source: https://npgsqlrest.github.io/examples

Execute NpgsqlRest example code by first applying database migrations and then starting the development server. This command also rebuilds TypeScript and HTTP files.

```bash
cd 1_my_first_function

# Apply database migrations
bun run db:up

# Start the server (also rebuilds TypeScript and HTTP files)
bun run dev
```

--------------------------------

### Example Multi-Host Connection Configuration

Source: https://npgsqlrest.github.io/config/connection.html

A complete configuration example for a primary-replica PostgreSQL setup, including connection strings for both default and read-only connections, and target session attributes.

```json
{
  "ConnectionStrings": {
    "Default": "Host=primary.db.com,replica1.db.com,replica2.db.com;Database=mydb;Username=app;Password=secret",
    "ReadOnly": "Host=replica1.db.com,replica2.db.com,primary.db.com;Database=mydb;Username=app;Password=secret"
  },
  "ConnectionSettings": {
    "MultiHostConnectionTargets": {
      "Default": "PreferPrimary",
      "ByConnectionName": {
        "ReadOnly": "PreferStandby"
      }
    }
  }
}
```

--------------------------------

### Database Storage SQL Setup

Source: https://npgsqlrest.github.io/config/data-protection.html

Example SQL commands to set up the necessary table and functions for database storage of data protection keys.

```sql
create table data_protection_keys (
  name text not null primary key,
  data text not null
);

create function get_data_protection_keys()
returns setof text
security definer
language sql
begin atomic;
select data from data_protection_keys;
end;

create procedure store_data_protection_keys(
  _name text,
  _data text
)
security definer
language sql
begin atomic;
insert into data_protection_keys (name, data)
values (_name, _data)
on conflict (name) do update set data = excluded.data;
end;
```

--------------------------------

### SQL Setup for HTTP Request Example

Source: https://npgsqlrest.github.io/config/http-client.html

Provides the SQL schema and function definition for a complete example of making an HTTP request to the GitHub API, including response type definition, HTTP request comment, and function logic.

```sql
-- Create response type
create type github_api as (
    body text,
    status_code int,
    headers json,
    content_type text,
    success boolean,
    error_message text
);

-- Define HTTP request
comment on type github_api is 'GET https://api.github.com/users/{_username}
Accept: application/vnd.github.v3+json
User-Agent: NpgsqlRest
timeout 10s';

-- Create function
create function get_github_user(
    _username text,
    _response github_api
)
returns json
language plpgsql
as $$
begin
    if (_response).success then
        return (_response).body::json;
    else
        return json_build_object(
            'error', true,
            'status', (_response).status_code,
            'message', (_response).error_message
        );
    end if;
end;
$$;

comment on function get_github_user(text, github_api) is 'HTTP GET /github/user';
```

--------------------------------

### Simple Example with GET and Authorization

Source: https://npgsqlrest.github.io/guide/annotations.html

Exposes a function as a GET endpoint and requires authentication.

```APIDOC
## GET get_users

### Description
Returns all active users from the database.

### Method
GET

### Endpoint
/get_users

### Authorization
Requires authentication.
```

--------------------------------

### Install NpgsqlRest Dependencies

Source: https://npgsqlrest.github.io/examples

Install project dependencies using Bun, which includes downloading the NpgsqlRest binary and setting up necessary tools for the examples.

```bash
bun install
```

--------------------------------

### Minimal Configuration Example

Source: https://npgsqlrest.github.io/config/passkey-auth.html

Example of a minimal configuration enabling passkey authentication.

```APIDOC
## Complete Example

### Minimal Configuration
```json
{
  "Auth": {
    "CookieAuth": true,
    "PasskeyAuth": {
      "Enabled": true
    }
  }
}
```
```

--------------------------------

### Complete Configuration Example

Source: https://npgsqlrest.github.io/config/claims-mapping.html

Example of a complete NpgsqlRest configuration enabling user context and user parameters. This includes mapping claims to context keys and function parameters.

```json
{
  "NpgsqlRest": {
    "AuthenticationOptions": {
      "UseUserContext": true,
      "ContextKeyClaimsMapping": {
        "request.user_id": "user_id",
        "request.user_name": "user_name",
        "request.user_roles": "user_roles"
      },
      "IpAddressContextKey": "request.ip_address",
      "UseUserParameters": true,
      "ParameterNameClaimsMapping": {
        "_user_id": "user_id",
        "_user_name": "user_name",
        "_user_roles": "user_roles"
      },
      "ClaimsJsonParameterName": "_user_claims",
      "IpAddressParameterName": "_ip_address"
    }
  }
}
```

--------------------------------

### Example of Unknown Configuration Key Warning

Source: https://npgsqlrest.github.io/config/config-section.html

This is an example log message indicating an unknown configuration key was found during startup. The application will continue running if the mode is set to 'Warning'.

```log
[12:34:56 WRN] Unknown configuration key: NpgsqlRest:KebabCaselUrls
```

--------------------------------

### SQL File Endpoint Example

Source: https://npgsqlrest.github.io/

This example demonstrates how to define an HTTP GET endpoint using a SQL file. Annotations in the SQL comments specify authorization, caching, timeouts, and table formatting.

```APIDOC
## HTTP GET /users/

### Description
Retrieves a list of users, optionally filtered by department ID.

### Method
GET

### Endpoint
/users/

### Parameters
#### Query Parameters
- **$1** (int) - Optional - Department ID to filter users by.

### Request Example
GET /users/?$1=123

### Response
#### Success Response (200)
- **id** (int) - User ID.
- **name** (text) - User name.
- **email** (text) - User email.
- **role** (text) - User role.

#### Response Example
{
  "example": "[{"id": 1, "name": "John Doe", "email": "john.doe@example.com", "role": "admin"}]"
}

### Annotations
- @authorize admin, user
- @cached
- @cache_expires_in 30sec
- @timeout 5min
- @table_format = excel
- @excel_file_name = users.xlsx
```

--------------------------------

### Passkey Client-Side Script Example (TypeScript)

Source: https://npgsqlrest.github.io/blog/passkey-sql-auth.html

A starting point for a client-side script to interact with the browser's WebAuthn API for passkey registration and login. This example demonstrates the necessary calls to the WebAuthn API.

```typescript
import { getWebAuthn } from "@/lib/webauthn";

const { 
  register, 
  authenticate 
} = getWebAuthn();

// Example usage:
async function handleRegistration() {
  const user = { id: "some-user-id", name: "John Doe" };
  const options = await register.options(user);
  const credential = await navigator.credentials.create({ publicKey: options });
  await register.verify(credential, user);
}

async function handleLogin() {
  const options = await authenticate.options();
  const credential = await navigator.credentials.get({ publicKey: options });
  await authenticate.verify(credential);
}
```

--------------------------------

### Client-Side Passkey Implementation Example

Source: https://npgsqlrest.github.io/blog/passkey-sql-auth.html

A TypeScript example demonstrating how to use the `register` and `login` functions for passkey authentication.

```typescript
import { register, login } from './passkey';

// Registration
const result = await register({
    userName: 'alice',
    displayName: 'Alice',
    deviceName: 'MacBook Pro'
});

if (result.success) {
    console.log('Registered with credential:', result.credentialId);
}

// Login
const loginResult = await login({
    userName: 'alice'  // Optional for discoverable credentials
});

if (loginResult.success) {
    const user = JSON.parse(loginResult.response);
    console.log('Logged in as:', user.username);
}
```

--------------------------------

### Start NpgsqlRest with Configuration

Source: https://npgsqlrest.github.io/guide/quick-start.html

Execute the NpgsqlRest application without command-line arguments after creating the `appsettings.json` file. The output shows the application starting with the loaded configuration.

```bash
./npgsqlrest
```

```log
[12:55:09.738 DBG] ----> Starting with configuration(s): JsonConfigurationProvider for 'appsettings.json' (Optional), JsonConfigurationProvider for 'appsettings.Development.json' (Missing), CommandLineConfigurationProvider [NpgsqlRest]
[12:55:09.750 DBG] ----> Logging enabled: Console (minimum level: Verbose) [NpgsqlRest]
[12:55:09.750 DBG] Using Default as main connection string: Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=******;Application Name=example;Enlist=False;No Reset On Close=True [NpgsqlRest]
[12:55:09.750 DBG] Using connection retry options with strategy: RetrySequenceSeconds=1,3,6,12, ErrorCodes=08000,08003,08006,08001,08004,55P03,55006,53300,57P03,40001 [NpgsqlRest]
[12:55:09.753 DBG] Using EndpointSource PostgreSQL Source [NpgsqlRest]
[12:55:09.753 DBG] Routine caching is disabled. [NpgsqlRest]
[12:55:09.778 DBG] Using DataSource with schema 'public' for metadata queries. [NpgsqlRest]
[12:55:09.817 DBG] Function public.my_first_function mapped to GET /api/my-first-function has set HTTP by the comment annotation to GET /api/my-first-function [NpgsqlRest]
[12:55:09.818 DBG] Created endpoint GET /api/my-first-function [NpgsqlRest]
[12:55:09.821 INF] Started in 00:00:00.0850561, listening on http://localhost:8080, version 3.0.0.0 [NpgsqlRest]
```

--------------------------------

### Complete Example: GitHub User API

Source: https://npgsqlrest.github.io/config/http-client.html

A full example demonstrating the configuration and usage of the HTTP client to fetch GitHub user data.

```APIDOC
## Complete Example 
### Configuration 
```json
{
  "NpgsqlRest": {
    "HttpClientOptions": {
      "Enabled": true
    }
  }
}
```

### SQL Setup 
```sql
-- Create response type
create type github_api as (
    body text,
    status_code int,
    headers json,
    content_type text,
    success boolean,
    error_message text
);

-- Define HTTP request
comment on type github_api is 'GET https://api.github.com/users/{_username}
Accept: application/vnd.github.v3+json
User-Agent: NpgsqlRest
timeout 10s';

-- Create function
create function get_github_user(
    _username text,
    _response github_api
)
returns json
language plpgsql
as $$
begin
    if (_response).success then
        return (_response).body::json;
    else
        return json_build_object(
            'error', true,
            'status', (_response).status_code,
            'message', (_response).error_message
        );
    end if;
end;
$$;

comment on function get_github_user(text, github_api) is 'HTTP GET /github/user';
```

### Usage 
```
GET /github/user/octocat
```

Returns the GitHub user data or an error response.
```

--------------------------------

### Example HTTP Definitions

Source: https://npgsqlrest.github.io/config/http-client.html

Illustrates various ways to define HTTP requests, including simple GET, GET with headers, and POST with a request body.

```APIDOC
**Simple GET request:**
```sql
comment on type api_response is 'GET https://api.example.com/data';
```
```

```APIDOC
**GET with headers:**
```sql
comment on type api_response is 'GET https://api.example.com/data
Authorization: Bearer {_token}
Accept: application/json';
```
```

```APIDOC
**POST with body:**
```sql
comment on type api_response is 'POST https://api.example.com/users
Content-Type: application/json

{"name": "{_name}", "email": "{_email}"}';
```
```

--------------------------------

### Simple GET Request Definition

Source: https://npgsqlrest.github.io/config/http-client.html

Example of defining a simple GET request using the HTTP definition format within a SQL comment.

```sql
comment on type api_response is 'GET https://api.example.com/data';
```

--------------------------------

### Start NpgsqlRest with Connection String

Source: https://npgsqlrest.github.io/guide/quick-start.html

Start NpgsqlRest by providing the default connection string via a command-line argument. This command initiates the service and makes it listen on http://localhost:8080.

```bash
./npgsqlrest --connectionstrings:default="Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=postgres"
```

--------------------------------

### Configuration File Format Example

Source: https://npgsqlrest.github.io/guide/configuration.html

NpgsqlRest configuration files use standard JSON format and support comments. This example shows settings for application name, database connection, and NpgsqlRest options.

```json
{
  // Application identification
  "ApplicationName": "MyApi",

  // Database connection
  "ConnectionStrings": {
    "Default": "Host=localhost;Database=mydb;Username=user;Password=pass"
  },

  // NpgsqlRest options
  "NpgsqlRest": {
    "UrlPathPrefix": "/api",
    "RequiresAuthorization": false
  }
}
```

--------------------------------

### Complete Rate Limiter Configuration Example

Source: https://npgsqlrest.github.io/config/rate-limiter.html

Example configuration with multiple rate limiting policies applied. This includes Fixed Window, Sliding Window, Token Bucket, and Concurrency policies.

```json
{
  "RateLimiterOptions": {
    "Enabled": true,
    "StatusCode": 429,
    "StatusMessage": "Too many requests. Please try again later.",
    "DefaultPolicy": "bucket",
    "Policies": [
      {
        "Type": "FixedWindow",
        "Enabled": true,
        "Name": "fixed",
        "PermitLimit": 100,
        "WindowSeconds": 60,
        "QueueLimit": 10,
        "AutoReplenishment": true
      },
      {
        "Type": "SlidingWindow",
        "Enabled": true,
        "Name": "sliding",
        "PermitLimit": 100,
        "WindowSeconds": 60,
        "SegmentsPerWindow": 6,
        "QueueLimit": 10,
        "AutoReplenishment": true
      },
      {
        "Type": "TokenBucket",
        "Enabled": true,
        "Name": "bucket",
        "TokenLimit": 100,
        "TokensPerPeriod": 10,
        "ReplenishmentPeriodSeconds": 10,
        "QueueLimit": 10,
        "AutoReplenishment": true
      },
      {
        "Type": "Concurrency",
        "Enabled": true,
        "Name": "concurrency",
        "PermitLimit": 10,
        "QueueLimit": 5,
        "OldestFirst": true
      }
    ]
  }
}
```

--------------------------------

### Complete External Authentication Example with Google and GitHub

Source: https://npgsqlrest.github.io/config/external-auth.html

Example configuration for integrating Google and GitHub OAuth. This includes general external authentication settings like ReturnToPath and LoginCommand, alongside specific credentials for each provider.

```json
{
  "Auth": {
    "CookieAuth": true,
    "CookieValidDays": 30,

    "External": {
      "Enabled": true,
      "ReturnToPath": "/dashboard",
      "LoginCommand": "select * from external_login($1, $2, $3, $4, $5)",

      "Google": {
        "Enabled": true,
        "ClientId": "{GOOGLE_CLIENT_ID}",
        "ClientSecret": "{GOOGLE_CLIENT_SECRET}"
      },

      "GitHub": {
        "Enabled": true,
        "ClientId": "{GITHUB_CLIENT_ID}",
        "ClientSecret": "{GITHUB_CLIENT_SECRET}"
      }
    }
  }
}
```

--------------------------------

### Complete Authentication Configuration Example

Source: https://npgsqlrest.github.io/config/authentication-options.html

A comprehensive example of NpgsqlRest authentication configuration, including default authentication type, status/scheme/hash column names, user context mapping, and login/logout paths.

```json
{
  "NpgsqlRest": {
    "AuthenticationOptions": {
      "DefaultAuthenticationType": "MyApp",
      "StatusColumnName": "status",
      "SchemeColumnName": "scheme",
      "HashColumnName": "hash",
      "PasswordParameterNameContains": "password",
      "DefaultUserIdClaimType": "user_id",
      "DefaultNameClaimType": "user_name",
      "DefaultRoleClaimType": "user_roles",
      "ObfuscateAuthParameterLogValues": true,
      "UseUserContext": true,
      "ContextKeyClaimsMapping": {
        "request.user_id": "user_id",
        "request.user_name": "user_name",
        "request.user_roles": "user_roles"
      },
      "IpAddressContextKey": "request.ip_address",
      "UseUserParameters": true,
      "ParameterNameClaimsMapping": {
        "_user_id": "user_id",
        "_user_name": "user_name",
        "_user_roles": "user_roles"
      },
      "ClaimsJsonParameterName": "_user_claims",
      "IpAddressParameterName": "_ip_address",
      "LoginPath": "/api/auth/login",
      "LogoutPath": "/api/auth/logout"
    }
  }
}
```

--------------------------------

### Define a SQL File Endpoint

Source: https://npgsqlrest.github.io/config/sql-file-source.html

Create a SQL file with comments defining the HTTP method and parameters to expose it as an API endpoint. This example shows a GET endpoint for retrieving users.

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

--------------------------------

### Production NpgsqlRest Configuration Example

Source: https://npgsqlrest.github.io/config/npgsqlrest.html

A comprehensive example of a production-ready NpgsqlRest configuration. This includes settings for connection management, schema inclusion/exclusion, URL formatting, authorization, logging, and request header handling.

```json
{
  "NpgsqlRest": {
    "ConnectionName": null,
    "UseMultipleConnections": true,
    "CommandTimeout": "30 seconds",
    "IncludeSchemas": ["api"],
    "ExcludeSchemas": ["internal"],
    "CommentsMode": "OnlyWithHttpTag",
    "UrlPathPrefix": "/api",
    "KebabCaseUrls": true,
    "CamelCaseNames": true,
    "RequiresAuthorization": true,
    "LogConnectionNoticeEvents": true,
    "LogConnectionNoticeEventsMode": "FirstStackFrameAndMessage",
    "LogCommands": false,
    "LogCommandParameters": false,
    "RequestHeadersMode": "Parameter",
    "RequestHeadersParameterName": "_headers",
    "ExecutionIdHeaderName": "X-NpgsqlRest-ID"
  }
}
```

--------------------------------

### Clone NpgsqlRest Docs Repository

Source: https://npgsqlrest.github.io/examples

Clone the NpgsqlRest documentation repository to access example code. This is the first step to setting up and running the provided examples.

```bash
git clone https://github.com/NpgsqlRest/npgsqlrest-docs.git
cd npgsqlrest-docs/examples
```

--------------------------------

### GET Request with Headers

Source: https://npgsqlrest.github.io/config/http-client.html

Demonstrates defining a GET request with custom headers, such as Authorization and Accept, within a SQL comment.

```sql
comment on type api_response is 'GET https://api.example.com/data
Authorization: Bearer {_token}
Accept: application/json';
```

--------------------------------

### Start NpgsqlRest with Debug Logging

Source: https://npgsqlrest.github.io/guide/quick-start.html

Start NpgsqlRest with verbose logging enabled using the `--log:minimallevels:npgsqlrest=debug` argument. This provides detailed output about the service's startup process, configuration, and endpoint creation, useful for debugging.

```bash
./npgsqlrest --connectionstrings:default="Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=postgres" --log:minimallevels:npgsqlrest=debug
```

--------------------------------

### Example JSON Response for GET Users

Source: https://npgsqlrest.github.io/blog/sql-rest-api.html

This is an example of the JSON response structure that the generated GET /api/get-users endpoint might return.

```json
[
  {
    "userId": 123,
    "username": "john_doe",
    "email": "john_doe@example.com",
    "active": true
  }, 
  {
    "userId": 124,
    "username": "jane_doe",
    "email": "jane_doe@example.com",
    "active": false
  }
]
```

--------------------------------

### Exploring and Generating Configuration

Source: https://npgsqlrest.github.io/guide/configuration.html

Shows how to use command-line arguments to generate a default configuration file, search for specific settings, and inspect the effective configuration with applied overrides.

```bash
# Generate a fully commented default configuration file
npgsqlrest --config > appsettings.json

# Search for settings related to a topic
npgsqlrest --config cors
npgsqlrest --config=timeout

# Inspect effective configuration with overrides applied (case-insensitive)
npgsqlrest appsettings.json --npgsqlrest:commandtimeout=30 --config
```

--------------------------------

### Public Endpoint Example

Source: https://npgsqlrest.github.io/annotations/allow-anonymous.html

This example demonstrates how to use the @allow_anonymous annotation to make a SQL function accessible via HTTP GET without requiring authentication.

```APIDOC
## Public Endpoint

### Description
Allows unauthenticated access to the endpoint, overriding the global `RequiresAuthorization` setting.

### Method
HTTP GET

### Endpoint
(Implicitly defined by the function signature and comment)

### Parameters
None

### Request Example
None

### Response
#### Success Response (200)
- **version** (string) - The version of the API.

#### Response Example
```json
{
  "version": "1.0"
}
```
```

--------------------------------

### Configuration Precedence Example

Source: https://npgsqlrest.github.io/guide/configuration.html

Demonstrates how appsettings.json, environment variables, and command-line arguments interact to determine the final configuration values. Environment variables and command-line arguments override settings in appsettings.json.

```json
{
  "ApplicationName": "DefaultApp",
  "NpgsqlRest": {
    "UrlPathPrefix": "/api",
    "RequiresAuthorization": true
  }
}
```

```bash
export NpgsqlRest__UrlPathPrefix="/api/v2"
```

```bash
npgsqlrest --NpgsqlRest:RequiresAuthorization=false
```

--------------------------------

### PostgREST: Deployment Options

Source: https://npgsqlrest.github.io/blog/npgsqlrest-vs-postgrest-supabase-comparison.html

PostgREST can be deployed by downloading a static binary or using Docker. The binary deployment requires a configuration file.

```bash
# Download and run
wget https://github.com/PostgREST/postgrest/releases/latest/download/postgrest-linux-static-x64.tar.xz
tar xf postgrest-linux-static-x64.tar.xz
./postgrest postgrest.conf

# Docker
docker run -p 3000:3000 postgrest/postgrest
```

--------------------------------

### API Endpoint for Excel Download

Source: https://npgsqlrest.github.io/blog/excel-export-table-format-postgresql-npgsqlrest.html

Example GET request to trigger a direct download of the data as a properly-typed .xlsx Excel file.

```http
GET /api/example-14/get-data?format=excel
```

--------------------------------

### API Endpoint for HTML View

Source: https://npgsqlrest.github.io/blog/excel-export-table-format-postgresql-npgsqlrest.html

Example GET request to access the data as an HTML table, suitable for browser previews and copy-pasting.

```http
GET /api/example-14/get-data?format=html
```

--------------------------------

### Create Database Schema and Test Data

Source: https://npgsqlrest.github.io/blog/custom-types-multiset-rest-api.html

Defines the schema for authors, books, and reviews tables, and populates them with test data. This setup is used for subsequent examples.

```sql
create table authors (
    author_id int not null generated always as identity primary key,
    first_name text,
    last_name text
);

create table books (
    book_id int not null generated always as identity primary key,
    title text not null,
    author_id int references authors(author_id)
);

create table reviews (
    review_id int not null generated always as identity primary key,
    book_id int references books(book_id),
    reviewer_name text,
    rating int check (rating between 1 and 5),
    review_text text,
    created_at timestamp default now()
);

-- Test data for authors
insert into authors (first_name, last_name) values
    ('George', 'Orwell'),
    ('Jane', 'Austen'),
    ('Ernest', 'Hemingway'),
    ('Virginia', 'Woolf'),
    ('Franz', 'Kafka');

-- Test data for books
insert into books (title, author_id) values
    ('1984', 1),
    ('Animal Farm', 1),
    ('Pride and Prejudice', 2),
    ('Sense and Sensibility', 2),
    ('The Old Man and the Sea', 3),
    ('A Farewell to Arms', 3),
    ('Mrs Dalloway', 4),
    ('To the Lighthouse', 4),
    ('The Metamorphosis', 5),
    ('The Trial', 5);

-- Test data for reviews
insert into reviews (book_id, reviewer_name, rating, review_text) values
    (1, 'Alice Johnson', 5, 'A chilling and prophetic masterpiece.'),
    (1, 'Bob Smith', 4, 'Thought-provoking but bleak.'),
    (1, 'Carol White', 5, 'Essential reading for everyone.'),
    (2, 'David Brown', 5, 'Brilliant political allegory.'),
    (2, 'Eve Davis', 4, 'Simple yet profound.'),
    (3, 'Frank Miller', 5, 'The perfect romance novel.'),
    (3, 'Grace Lee', 5, 'Witty and timeless.'),
    (4, 'Henry Wilson', 4, 'Austen at her finest.'),
    (5, 'Ivy Chen', 5, 'Beautiful and moving.'),
    (5, 'Jack Taylor', 4, 'A short but powerful read.'),
    (6, 'Karen Adams', 4, 'Hemingway''s prose shines.'),
    (7, 'Leo Garcia', 5, 'Stream of consciousness done right.'),
    (8, 'Mia Robinson', 4, 'Poetic and haunting.'),
    (9, 'Noah Martinez', 5, 'Surreal and unforgettable.'),
    (9, 'Olivia Clark', 3, 'Disturbing but brilliant.'),
    (10, 'Paul Wright', 4, 'Kafka at his most absurd.');

```

--------------------------------

### Minimal Configuration

Source: https://npgsqlrest.github.io/config/passkey-auth.html

Enable passkey authentication with a simple configuration setting.

```APIDOC
## Minimal Configuration

Minimal configuration to enable passkey authentication:

```json
{
  "Auth": {
    "PasskeyAuth": {
      "Enabled": true
    }
  }
}
```
```

--------------------------------

### SQL File Endpoint Definition

Source: https://npgsqlrest.github.io/blog/the-power-of-simplicity.html

Define a REST endpoint by writing a SQL query in a .sql file with an HTTP method annotation. This example shows a GET endpoint for fetching users by department.

```sql
-- sql/get-users.sql
-- HTTP GET
-- @param $1 department
SELECT id, name, email FROM users WHERE department = $1;
```

--------------------------------

### Create NpgsqlRest Configuration File

Source: https://npgsqlrest.github.io/guide/quick-start.html

Create an `appsettings.json` file to define default connection strings, logging levels, and SQL file source settings for NpgsqlRest. This avoids the need for command-line arguments during startup.

```json
{
  // Default connection string to the PostgreSQL database
  "ConnectionStrings": {
    "Default": "Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=postgres"
  },

  // Logging configuration, use "Debug" level for NpgsqlRest namespace
  "Log": {
    "MinimalLevels": {
      "NpgsqlRest": "Debug"
    }
  },

  // Enable SQL file endpoints (scan sql/ directory recursively)
  "NpgsqlRest": {
    "SqlFileSource": {
      "Enabled": true,
      "FilePattern": "sql/**/*.sql"
    }
  }
}
```

--------------------------------

### Declare API Endpoint with SQL Annotations

Source: https://npgsqlrest.github.io/

Define HTTP methods, authorization, caching, timeouts, and file formats directly within SQL comments. This example shows how to configure a GET request for users with specific caching and authorization rules.

```sql
/*
HTTP GET /users/
@authorize admin, user
@cached
@cache_expires_in 30sec
@timeout 5min
@table_format = excel
@excel_file_name = users.xlsx
*/
select id, name, email, role
from users
where $1 is null or department_id = $1;
```

--------------------------------

### Supabase Edge Function Example

Source: https://npgsqlrest.github.io/blog/npgsqlrest-vs-postgrest-supabase-comparison.html

This TypeScript code demonstrates a Supabase Edge Function that fetches order data from PostgreSQL and then calls an external PDF rendering service. It requires manual database connection setup and external service calls.

```typescript
//supabase/functions/generate-pdf/index.ts
import { serve } from "https://deno.land/std/http/server.ts"

serve(async (req) => {
  const { orderId } = await req.json()

  // Must manually connect to database
  const supabase = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
  )
  const { data } = await supabase
    .from('orders')
    .select('*, customer(*), items(*)')
    .eq('id', orderId)
    .single()

  // Must manually call external service
  const pdf = await fetch('https://pdf-renderer.internal/render', {
    method: 'POST',
    body: JSON.stringify(data),
  })

  return new Response(await pdf.arrayBuffer(), {
    headers: { 'Content-Type': 'application/pdf' },
  })
})
```

--------------------------------

### Example: Burst Traffic Thread Pool Configuration

Source: https://npgsqlrest.github.io/blog/performance-scalability-high-availability-npgsqlrest.html

Configure thread pool settings to handle normal load efficiently while accommodating significant bursts. This setup ensures responsiveness during quiet periods and rapid scaling during unexpected load spikes.

```json
{
  "ThreadPool": {
    "MinWorkerThreads": 100,
    "MinCompletionPortThreads": 100,
    "MaxWorkerThreads": 600,
    "MaxCompletionPortThreads": 600
  }
}
```

--------------------------------

### Example .env File Format

Source: https://npgsqlrest.github.io/config/config-section.html

Illustrates the format for a .env file, supporting KEY=VALUE pairs, comments, and quoted values. Variables loaded from this file can be used in configuration.

```dotenv
# Database connection settings
PGHOST=localhost
PGPORT=5432
PGDATABASE=example_db
PGUSER=postgres
PGPASSWORD=postgres
```

--------------------------------

### Define Custom URL Paths for Table Endpoints

Source: https://npgsqlrest.github.io/blog/zero-to-crud-api-postgresql-tables-npgsqlrest.html

Customize the URL paths for generated CRUD endpoints by specifying them in SQL comments using the HTTP directive. This example shows how to set custom paths for GET and POST requests to the 'users' table.

```sql
-- Custom URL path
comment on table users is '
HTTP GET /v1/users
HTTP POST /v1/users
';
```

--------------------------------

### Usage Example for HTTP Client

Source: https://npgsqlrest.github.io/config/http-client.html

Demonstrates how to call the PostgreSQL function that makes an HTTP request, specifying the endpoint and parameters.

```code
GET /github/user/octocat
```

--------------------------------

### Define PostgreSQL Function with API Annotations

Source: https://npgsqlrest.github.io/

Create a PostgreSQL function to serve as an API endpoint, with annotations for HTTP methods, authorization, caching, retries, and rate limiting defined in the function's comment. This example configures a GET request for users with advanced settings.

```sql
create or replace function api.get_users(
  _department_id int
)
returns table (id int, name text, email text, role text)
language sql
begin atomic;
select id, name, email, role
from users
where
  _department_id is null
  or department_id = _department_id;
end;

comment on function api.get_users(int) is '
HTTP GET /users/
@authorize admin, user
@cached
@cache_expires_in 30sec
@timeout 10sec
@retry_strategy aggressive
@rate_limiter_policy authenticated_limit
@tsclient_module = users
';
```

--------------------------------

### Customize Startup Message with Placeholders

Source: https://npgsqlrest.github.io/config/top-level.html

Customize the startup message using available placeholders like {time}, {urls}, {version}, {environment}, and {application}. This allows for dynamic and informative startup logs.

```json
{
  "StartupMessage": "Started in {time}, listening on {urls}, version {version}, env: {environment}"
}
```

--------------------------------

### Example CSV Output

Source: https://npgsqlrest.github.io/blog/postgresql-bi-server-excel-csv-basic-auth

This is an example of the CSV output generated by the `sales_report` function with the specified annotations.

```csv
"exported_by","order_id","customer_name","product","quantity","unit_price","total","order_date"
"admin",1,"Acme Corp","Widget Pro",50,29.99,1499.50,"2024-01-15"
"admin",2,"TechStart Inc","Widget Basic",100,19.99,1999.00,"2024-01-16"
```

--------------------------------

### Example Static Files Configuration

Source: https://npgsqlrest.github.io/config/static-files.html

A comprehensive JSON configuration for serving static files, including enabling content parsing, defining protected admin and dashboard paths, and specifying unauthorized redirect behavior.

```json
{
  "StaticFiles": {
    "Enabled": true,
    "RootPath": "wwwroot",
    "AuthorizePaths": [
      "/admin/*",
      "/dashboard/*"
    ],
    "UnauthorizedRedirectPath": "/login.html",
    "UnauthorizedReturnToQueryParameter": "return_to",
    "ParseContentOptions": {
      "Enabled": true,
      "AvailableClaims": ["name", "email", "role"],
      "CacheParsedFile": true,
      "FilePaths": ["*.html", "*.htm"],
      "AntiforgeryFieldName": "antiForgeryFieldName",
      "AntiforgeryToken": "antiForgeryToken"
    }
  }
}
```

--------------------------------

### Nested JSON Response Example 2

Source: https://npgsqlrest.github.io/blog/custom-types-multiset-rest-api.html

Example of a nested JSON response for '/api/example-12/get-authors-with-details-type' when custom types are nested.

```json
[
  {
    "author": {
      "authorId": 1,
      "firstName": "George",
      "lastName": "Orwell"
    },
    "booksInfo": {
      "books": 2,
      "activeReviews": 5,
      "avgRating": 4.6000000000000000
    }
  },
  {
    "author": {
      "authorId": 2,
      "firstName": "Jane",
      "lastName": "Austen"
    },
    "booksInfo": {
      "books": 2,
      "activeReviews": 3,
      "avgRating": 4.6666666666666667
    }
  },
  ...
]
```

--------------------------------

### Nested JSON Response Example 1

Source: https://npgsqlrest.github.io/blog/custom-types-multiset-rest-api.html

Example of a nested JSON response when the 'NestedJsonForCompositeTypes' feature is enabled for '/api/example-12/get-authors-with-details'.

```json
[
  {
    "author": {
      "authorId": 1,
      "firstName": "George",
      "lastName": "Orwell"
    },
    "books": 2
  },
  {
    "author": {
      "authorId": 2,
      "firstName": "Jane",
      "lastName": "Austen"
    },
    "books": 2
  },
  ...
]
```

--------------------------------

### NpgsqlRest: Deployment Options

Source: https://npgsqlrest.github.io/blog/npgsqlrest-vs-postgrest-supabase-comparison.html

NpgsqlRest offers flexible deployment through a single binary, Docker, or NPM. The binary option is highlighted for its simplicity and zero dependencies.

```bash
# Option 1: Direct download (30MB)
wget https://github.com/NpgsqlRest/NpgsqlRest/releases/latest/download/npgsqlrest-linux64
chmod +x npgsqlrest-linux64
./npgsqlrest-linux64 --connection "Host=localhost;Database=mydb;Username=api"

# Option 2: Docker
docker run -p 8080:8080 vbilopav/npgsqlrest:latest \
  --connection "Host=host.docker.internal;Database=mydb;Username=api"

# Option 3: NPM
npm install -g npgsqlrest
npx npgsqlrest --connection "..."
```

--------------------------------

### Frontend Application Example

Source: https://npgsqlrest.github.io/blog/zero-to-crud-api-postgresql-tables-npgsqlrest.html

This example shows how to use the generated TypeScript client in a frontend application to perform CRUD operations on contacts.

```APIDOC
## loadContacts

### Description
Loads and renders all contacts.

### Method
(Internal function, not directly callable via API)

### Endpoint
N/A

### Usage
```typescript
// Call this function to load contacts
await loadContacts();
```
```

```APIDOC
## createContact

### Description
Creates a new contact.

### Method
(Internal function, not directly callable via API)

### Endpoint
N/A

### Usage
```typescript
// Call this function to create a contact
await createContact("Jane Doe", "jane.doe@example.com", "987-654-3210");
```
```

```APIDOC
## updateContact

### Description
Updates an existing contact.

### Method
(Internal function, not directly callable via API)

### Endpoint
N/A

### Usage
```typescript
// Call this function to update a contact
await updateContact(1, "Jane Doe", "jane.doe.updated@example.com", "987-654-3210");
```
```

```APIDOC
## deleteContact

### Description
Deletes a contact.

### Method
(Internal function, not directly callable via API)

### Endpoint
N/A

### Usage
```typescript
// Call this function to delete a contact
await deleteContact(1);
```
```

--------------------------------

### API Gateway Pattern Example

Source: https://npgsqlrest.github.io/blog/reverse-proxy-postgresql-ai-service-npgsqlrest.html

Demonstrates how to use NpgsqlRest as an API Gateway to route requests to different internal microservices using SQL functions and the `@proxy` comment.

```APIDOC
## API Gateway Pattern

Route requests to different microservices:

-- Users service
create function users_api()
returns void
language sql
begin atomic;
select;
end;
comment on function users_api is '
HTTP GET /api/users
@proxy https://users-service.internal:8080';

-- Orders service
create function orders_api()
returns void
language sql
begin atomic;
select;
end;
comment on function orders_api is '
HTTP GET /api/orders
@proxy https://orders-service.internal:8080';
```

--------------------------------

### Generate Default Configuration File

Source: https://npgsqlrest.github.io/guide/configuration.html

Run `npgsqlrest --config` to output the default configuration. Redirect the output to `appsettings.json` to create a default configuration file.

```bash
npgsqlrest --config
```

```bash
npgsqlrest --config > appsettings.json
```

--------------------------------

### SQL Script for GET Users Endpoint

Source: https://npgsqlrest.github.io/blog/sql-rest-api.html

This SQL script, when placed in the configured directory and containing an 'HTTP' comment, will generate a GET /api/get-users endpoint.

```sql
-- HTTP
select user_id, username, email, active 
from example.users;
```

--------------------------------

### Minimal Passkey Authentication Configuration

Source: https://npgsqlrest.github.io/config/passkey-auth.html

Enable passkey authentication with minimal configuration. This example shows how to enable passkey authentication alongside cookie authentication.

```json
{
  "Auth": {
    "CookieAuth": true,
    "PasskeyAuth": {
      "Enabled": true
    }
  }
}
```

--------------------------------

### Single SQL Statement for GET Endpoint

Source: https://npgsqlrest.github.io/guide/sql-files.html

A single SQL SELECT statement in a file creates a standard GET endpoint. Column names are converted to camelCase by default.

```sql
-- sql/get-users.sql
-- HTTP GET
select user_id, username, email, active from example_2.users;
```

--------------------------------

### Complete NpgsqlRest Routine Options configuration

Source: https://npgsqlrest.github.io/config/routine-options.html

A comprehensive example showing various routine options, including custom separators, language inclusion/exclusion, and JSON nesting settings.

```json
{
  "NpgsqlRest": {
    "RoutineOptions": {
      "CustomTypeParameterSeparator": "_",
      "IncludeLanguages": ["plpgsql", "sql"],
      "ExcludeLanguages": null,
      "NestedJsonForCompositeTypes": false,
      "ResolveNestedCompositeTypes": true
    }
  }
}
```

--------------------------------

### Complete Production Connection Configuration

Source: https://npgsqlrest.github.io/config/connection.html

A comprehensive configuration example for a production environment, including multiple connection strings, detailed retry options, and NpgsqlRest multi-connection settings.

```json
{
  "ConnectionStrings": {
    "Default": "Host={PGHOST};Port={PGPORT};Database={PGDATABASE};Username={PGUSER};Password={PGPASSWORD};SSL Mode=Require;Pooling=true;Maximum Pool Size=100",
    "ReadReplica": "Host={PGHOST_REPLICA};Port={PGPORT};Database={PGDATABASE};Username={PGUSER};Password={PGPASSWORD};SSL Mode=Require;Pooling=true;Maximum Pool Size=50"
  },
  "ConnectionSettings": {
    "SetApplicationNameInConnection": true,
    "UseJsonApplicationName": false,
    "TestConnectionStrings": true,
    "RetryOptions": {
      "Enabled": true,
      "RetrySequenceSeconds": [0.5, 1, 2, 5, 10],
      "ErrorCodes": ["08000", "08003", "08006", "08001", "08004", "55P03", "55006", "53300", "57P03", "40001"]
    }
  },
  "NpgsqlRest": {
    "ConnectionName": null,
    "UseMultipleConnections": true
  }
}
```

--------------------------------

### Enabling HTTPS

Source: https://npgsqlrest.github.io/config/server.html

Example of enabling HTTPS by setting 'Ssl.Enabled' to true. Ensure certificates are configured in the Kestrel section.

```json
{
  "Ssl": {
    "Enabled": true,
    "UseHttpsRedirection": true,
    "UseHsts": true
  }
}
```

--------------------------------

### Create Protected Schema and Users Table

Source: https://npgsqlrest.github.io/blog/database-level-security-postgresql-authentication.html

Sets up the 'example_3' schema, enables the 'pgcrypto' extension for password hashing, and creates a 'users' table to store user credentials including password hashes.

```sql
-- V1__example_3_schema.sql (versioned migration - runs only once)

-- Create the protected schema
drop schema if exists example_3 cascade;
create schema example_3;

-- Enable pgcrypto for password hashing
create extension if not exists pgcrypto with schema example_3;

-- Users table with password hashes
create table example_3.users (
    user_id int primary key generated always as identity,
    username text not null,
    email text not null,
    password_hash text[] not null  -- Array of hashes (explained below)
);
```

--------------------------------

### Inspect Effective Configuration with Overrides

Source: https://npgsqlrest.github.io/guide/configuration.html

Include configuration files and command-line overrides before the `--config` switch to inspect the effective configuration.

```bash
npgsqlrest appsettings.json --npgsqlrest:commandtimeout=30 --config
```