sql-http-proxy.example.yaml

# =============================================================================
# sql-http-proxy Configuration Example
# =============================================================================
# This file demonstrates all available configuration options.
# See: https://github.com/mpyw/sql-http-proxy
# =============================================================================

# -----------------------------------------------------------------------------
# Global Helpers
# -----------------------------------------------------------------------------
# JavaScript functions available in all JS contexts (pre, post, mock JS, filter, csv.value_parser)

global_helpers:
  js: |
    // Validation helpers
    function requireString(value, name) {
      if (typeof value !== 'string' || value.trim() === '') {
        throw { status: 400, body: { error: name + ' is required' } };
      }
      return value.trim();
    }

    function requireEmail(value) {
      const email = requireString(value, 'email');
      if (!email.includes('@')) {
        throw { status: 400, body: { error: 'invalid email format' } };
      }
      return email.toLowerCase();
    }

    function requireInt(value, name, min, max) {
      const n = parseInt(value);
      if (isNaN(n)) {
        throw { status: 400, body: { error: name + ' must be an integer' } };
      }
      if (min !== undefined && n < min) {
        throw { status: 400, body: { error: name + ' must be >= ' + min } };
      }
      if (max !== undefined && n > max) {
        throw { status: 400, body: { error: name + ' must be <= ' + max } };
      }
      return n;
    }

    // SQL helpers
    function escapeLike(s) {
      return s.replace(/\\/g, '\\\\').replace(/%/g, '\\%').replace(/_/g, '\\_');
    }

    // Date helpers
    function parseDate(s) {
      const d = new Date(s);
      if (isNaN(d.getTime())) return null;
      return d.toISOString();
    }

  # js_files:
  #   - ./helpers/validation.js
  #   - ./helpers/auth.js

# -----------------------------------------------------------------------------
# CSV Value Parser
# -----------------------------------------------------------------------------
# Custom parsing for CSV cell values (used in mock csv and csv_file)
# Has access to global_helpers functions

csv:
  value_parser: |
    // Auto-parse dates, numbers, booleans, and null
    if (value === '' || value === 'null' || value === 'NULL') return null;
    if (value === 'true') return true;
    if (value === 'false') return false;
    if (/^-?\d+$/.test(value)) return parseInt(value, 10);
    if (/^-?\d+\.\d+$/.test(value)) return parseFloat(value);
    // Use global helper for date parsing
    const date = parseDate(value);
    if (date) return date;
    return value;

# -----------------------------------------------------------------------------
# Database Connection
# -----------------------------------------------------------------------------
# Supports ${VAR}, $VAR, and ${VAR:-default} environment variable expansion
# Not required if all endpoints use mock

database:
  dsn: postgres://${DB_USER}:${DB_PASSWORD}@${DB_HOST:-localhost}:${DB_PORT:-5432}/mydb?sslmode=disable

  # Optional: SQL to execute on startup (useful for SQLite in-memory, schema setup)
  # init: |
  #   CREATE TABLE IF NOT EXISTS users (...);
  # Or with files:
  # init:
  #   sql_files:
  #     - ./migrations/001_schema.sql
  #     - ./migrations/002_seed.sql

# -----------------------------------------------------------------------------
# HTTP Configuration
# -----------------------------------------------------------------------------
# Configure HTTP server settings including CORS

http:
  # CORS can be:
  # - true: Permissive mode (Access-Control-Allow-Origin: *)
  # - Object with detailed configuration
  cors:
    allowed_origins:
      - https://example.com
      - https://app.example.com
    allow_credentials: true
    max_age: 86400  # 24 hours

# =============================================================================
# QUERIES (SELECT operations)
# =============================================================================

queries:
  # ---------------------------------------------------------------------------
  # Basic Queries
  # ---------------------------------------------------------------------------

  # Simple query - returns array of rows
  - type: many
    path: /users
    sql: SELECT * FROM users ORDER BY id

  # Single row query - returns object or 404
  - type: one
    path: /user
    sql: SELECT * FROM users WHERE id = :id

  # ---------------------------------------------------------------------------
  # Path Parameters
  # ---------------------------------------------------------------------------
  # Use {param} syntax for path parameters (chi router syntax).
  # Path params take priority over query params.

  - type: one
    path: /users/{id:*uuid_v7*}
    sql: SELECT * FROM users WHERE id = :id

  - type: many
    path: /users/{user_id:*uuid_v7*}/posts
    sql: SELECT * FROM posts WHERE user_id = :user_id

  # ---------------------------------------------------------------------------
  # Path Parameters with Regex Constraints
  # ---------------------------------------------------------------------------
  # Use {param:regex} to validate path parameters. Returns 404 if not matched.

  # Any UUID (strict lowercase format) - using shorthand *uuid*
  - type: one
    path: /documents/{id:*uuid*}
    sql: SELECT * FROM documents WHERE id = :id

  # UUIDv4 only (version=4, variant=1) - using shorthand *uuid_v4*
  # Validates: xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx (y = 8, 9, a, or b)
  - type: one
    path: /tokens/{id:*uuid_v4*}
    sql: SELECT * FROM tokens WHERE id = :id

  # UUIDv7 only (version=7, variant=1) - using shorthand *uuid_v7*
  # Validates: xxxxxxxx-xxxx-7xxx-yxxx-xxxxxxxxxxxx (y = 8, 9, a, or b)
  - type: one
    path: /events/{id:*uuid_v7*}
    sql: SELECT * FROM events WHERE id = :id

  # Numeric ID only
  - type: one
    path: /posts/{id:[0-9]+}
    sql: SELECT * FROM posts WHERE id = :id

  # Slug format (lowercase letters, numbers, hyphens)
  - type: one
    path: /articles/{slug:[a-z0-9-]+}
    sql: SELECT * FROM articles WHERE slug = :slug

  # ---------------------------------------------------------------------------
  # Query with Custom HTTP Method
  # ---------------------------------------------------------------------------
  # Queries default to GET, but can use POST/PUT/PATCH/DELETE for complex params

  - type: many
    method: POST  # Accept JSON body instead of query params
    path: /users/search
    sql: SELECT * FROM users WHERE department = :department
    accepts: json  # Only accept application/json

  # ---------------------------------------------------------------------------
  # Accepts Option
  # ---------------------------------------------------------------------------
  # Control which Content-Types are accepted for request body
  # Default: [json, form] (both application/json and application/x-www-form-urlencoded)

  - type: many
    method: POST
    path: /users/filter
    sql: SELECT * FROM users WHERE status = :status
    accepts: [json, form]  # Explicit: both JSON and form data

  - type: many
    method: POST
    path: /users/json-only
    sql: SELECT * FROM users WHERE role = :role
    accepts: json  # Only JSON (returns 415 for form data)

  # ---------------------------------------------------------------------------
  # Handle Not Found (type: one only)
  # ---------------------------------------------------------------------------
  # By default, type: one returns 404 when no row found
  # With handle_not_found: true, passes null to post-transform instead

  - type: one
    path: /user-or-default
    sql: SELECT * FROM users WHERE id = :id
    handle_not_found: true
    transform:
      post: |
        if (output === null) {
          return { found: false, id: input.id, message: 'User not found' };
        }
        return { found: true, ...output };

  # ---------------------------------------------------------------------------
  # Pre-Transform
  # ---------------------------------------------------------------------------
  # Validate/transform input parameters before SQL execution
  # Free variables: ctx (shared state), sql (modifiable)
  # Parameter: input (request params)
  # Must return: object (new params for SQL)

  - type: many
    path: /users/paginated
    sql: SELECT * FROM users ORDER BY id LIMIT :limit OFFSET :offset
    transform:
      pre: |
        // Uses global helper functions
        const limit = requireInt(input.limit || '10', 'limit', 1, 100);
        const offset = requireInt(input.offset || '0', 'offset', 0);

        // Store in ctx for post-transform
        ctx.pagination = { limit, offset };

        return { limit, offset };

  # ---------------------------------------------------------------------------
  # Dynamic SQL Modification
  # ---------------------------------------------------------------------------
  # The `sql` free variable can be modified in pre-transform

  - type: many
    path: /users/dynamic-search
    sql: SELECT * FROM users WHERE 1=1
    transform:
      pre: |
        const params = {};

        if (input.name) {
          sql += ' AND name LIKE :name';
          params.name = '%' + escapeLike(input.name) + '%';
        }

        if (input.status) {
          sql += ' AND status = :status';
          params.status = input.status;
        }

        if (input.sort === 'name') {
          sql += ' ORDER BY name';
        } else {
          sql += ' ORDER BY id';
        }

        return params;

  # ---------------------------------------------------------------------------
  # Post-Transform (type: one)
  # ---------------------------------------------------------------------------
  # Transform the result after SQL execution
  # Free variable: ctx
  # Parameters: input (original params), output (query result)

  - type: one
    path: /user/formatted
    sql: SELECT * FROM users WHERE id = :id
    transform:
      post: |
        return {
          id: output.id,
          displayName: output.first_name + ' ' + output.last_name,
          email: output.email,
          isAdmin: output.role === 'admin'
        };

  # ---------------------------------------------------------------------------
  # Post-Transform (type: many) - Each Mode
  # ---------------------------------------------------------------------------
  # Transform each row individually

  - type: many
    path: /users/each-transform
    sql: SELECT * FROM users
    transform:
      post:
        each: |
          return {
            id: output.id,
            fullName: output.first_name + ' ' + output.last_name,
            email: output.email.toLowerCase()
          };

  # ---------------------------------------------------------------------------
  # Post-Transform (type: many) - All Mode
  # ---------------------------------------------------------------------------
  # Transform the entire result array
  # Shorthand: `post: |` is equivalent to `post: { all: | }`

  - type: many
    path: /users/wrapped
    sql: SELECT * FROM users LIMIT :limit OFFSET :offset
    transform:
      pre: |
        ctx.limit = parseInt(input.limit) || 10;
        ctx.offset = parseInt(input.offset) || 0;
        return { limit: ctx.limit, offset: ctx.offset };
      post:
        all: |
          return {
            data: output,
            pagination: {
              limit: ctx.limit,
              offset: ctx.offset,
              count: output.length
            }
          };

  # ---------------------------------------------------------------------------
  # Post-Transform (type: many) - Each + All Combined
  # ---------------------------------------------------------------------------
  # First transform each row, then transform the entire array

  - type: many
    path: /users/full-transform
    sql: SELECT * FROM users LIMIT :limit
    transform:
      pre: |
        ctx.requestedLimit = parseInt(input.limit) || 10;
        return { limit: ctx.requestedLimit };
      post:
        each: |
          // Runs for each row
          return {
            ...output,
            email: output.email.toUpperCase()
          };
        all: |
          // Runs once on the array of transformed rows
          return {
            meta: { count: output.length, limit: ctx.requestedLimit },
            items: output
          };

  # ---------------------------------------------------------------------------
  # Error Handling in Transforms
  # ---------------------------------------------------------------------------
  # Throw an object with status and body to return custom HTTP error

  - type: one
    path: /admin/user
    sql: SELECT * FROM users WHERE id = :id
    transform:
      pre: |
        if (!input.token) {
          throw { status: 401, body: { error: 'unauthorized' } };
        }
        ctx.isAdmin = input.token === 'admin-secret';
        return { id: input.id };
      post: |
        if (!ctx.isAdmin && output.role === 'admin') {
          throw { status: 403, body: { error: 'access denied' } };
        }
        return output;

  # ===========================================================================
  # MOCK DATA SOURCES
  # ===========================================================================
  # Mock allows returning data without database connection
  # Useful for testing, prototyping, or static data endpoints
  # Mock is at the same level as sql - use one or the other, not both

  # ---------------------------------------------------------------------------
  # Mock: object_js (type: one)
  # ---------------------------------------------------------------------------
  # JavaScript returning a single object
  # Free variables: ctx, sql
  # Parameter: input (request params)

  - type: one
    path: /mock/user
    mock:
      object_js: |
        if (input.id === '404') {
          return null;  // Returns 404 for type: one
        }
        return {
          id: parseInt(input.id),
          name: 'Mock User ' + input.id,
          email: 'user' + input.id + '@example.com',
          created_at: new Date().toISOString()
        };

  # ---------------------------------------------------------------------------
  # Mock: array_js (type: many)
  # ---------------------------------------------------------------------------
  # JavaScript returning an array

  - type: many
    path: /mock/users
    mock:
      array_js: |
        const count = parseInt(input.count) || 3;
        const users = [];
        for (let i = 1; i <= count; i++) {
          users.push({
            id: i,
            name: 'User ' + i,
            email: 'user' + i + '@example.com'
          });
        }
        return users;

  # ---------------------------------------------------------------------------
  # Mock: csv (inline)
  # ---------------------------------------------------------------------------
  # CSV is parsed using csv.value_parser if configured

  - type: many
    path: /mock/products
    mock:
      csv: |
        id,name,price,in_stock,created_at
        1,Widget,19.99,true,2024-01-15
        2,Gadget,29.99,true,2024-02-20
        3,Gizmo,9.99,false,2024-03-10

  # ---------------------------------------------------------------------------
  # Mock: csv_file (file)
  # ---------------------------------------------------------------------------

  # - type: many
  #   path: /mock/products-from-file
  #   mock:
  #     csv_file: ./mocks/products.csv

  # ---------------------------------------------------------------------------
  # Mock: array (YAML array)
  # ---------------------------------------------------------------------------

  - type: many
    path: /mock/categories
    mock:
      array:
        - id: 1
          name: Electronics
          description: Electronic devices and accessories
        - id: 2
          name: Clothing
          description: Apparel and fashion items
        - id: 3
          name: Books
          description: Physical and digital books

  # ---------------------------------------------------------------------------
  # Mock: object (YAML object, type: one)
  # ---------------------------------------------------------------------------

  - type: one
    path: /mock/config
    mock:
      object:
        key: app_settings
        value: { theme: dark, language: en }
        updated_at: "2024-01-01T00:00:00Z"

  # ---------------------------------------------------------------------------
  # Mock: array_json_file (file)
  # ---------------------------------------------------------------------------

  # - type: many
  #   path: /mock/categories-from-file
  #   mock:
  #     array_json_file: ./mocks/categories.json

  # ---------------------------------------------------------------------------
  # Mock: jsonl (inline)
  # ---------------------------------------------------------------------------
  # JSON Lines format - one JSON object per line

  - type: many
    path: /mock/logs
    mock:
      jsonl: |
        {"id": 1, "level": "info", "message": "Server started"}
        {"id": 2, "level": "warn", "message": "High memory usage"}
        {"id": 3, "level": "error", "message": "Connection timeout"}

  # ---------------------------------------------------------------------------
  # Mock: jsonl_file (file)
  # ---------------------------------------------------------------------------

  # - type: many
  #   path: /mock/logs-from-file
  #   mock:
  #     jsonl_file: ./mocks/logs.jsonl

  # ---------------------------------------------------------------------------
  # Mock with filter (type: one using array source)
  # ---------------------------------------------------------------------------
  # filter is required when using array sources with type: one
  # Free variables: ctx
  # Parameters: row (current item), input (request params)
  # Returns: boolean (true to include)

  - type: one
    path: /mock/user-by-email
    mock:
      array:
        - { id: 1, name: Alice, email: alice@example.com }
        - { id: 2, name: Bob, email: bob@example.com }
        - { id: 3, name: Charlie, email: charlie@example.com }
      filter: return row.email === input.email

  # ---------------------------------------------------------------------------
  # Mock with filter (type: many)
  # ---------------------------------------------------------------------------
  # filter is optional for type: many

  - type: many
    path: /mock/users-by-role
    mock:
      array:
        - { id: 1, name: Alice, role: admin }
        - { id: 2, name: Bob, role: user }
        - { id: 3, name: Charlie, role: admin }
      filter: return !input.role || row.role === input.role

  # ---------------------------------------------------------------------------
  # Mock with Post-Transform
  # ---------------------------------------------------------------------------
  # Mock output can be further transformed with post

  - type: many
    path: /mock/users-transformed
    mock:
      csv: |
        id,first_name,last_name,email
        1,John,Doe,john@example.com
        2,Jane,Smith,jane@example.com
    transform:
      post:
        each: |
          return {
            id: output.id,
            fullName: output.first_name + ' ' + output.last_name,
            email: output.email,
            isMock: true
          };

  # ---------------------------------------------------------------------------
  # Response Delay
  # ---------------------------------------------------------------------------
  # Add artificial latency to responses (useful for testing loading states)

  - type: one
    path: /mock/slow-user
    delay: 500ms  # Wait 500ms before responding
    mock:
      object:
        id: 1
        name: Slow User

  - type: many
    path: /mock/slow-users
    delay: 1s  # Wait 1 second before responding
    mock:
      array:
        - { id: 1, name: Alice }
        - { id: 2, name: Bob }

# =============================================================================
# MUTATIONS (INSERT/UPDATE/DELETE operations)
# =============================================================================

mutations:
  # ---------------------------------------------------------------------------
  # Basic Mutations
  # ---------------------------------------------------------------------------

  # INSERT with RETURNING - returns created row
  - type: one
    method: POST
    path: /user
    sql: |
      INSERT INTO users (first_name, last_name, email)
      VALUES (:first_name, :last_name, :email)
      RETURNING *

  # UPDATE with RETURNING - returns updated row
  - type: one
    method: PUT
    path: /user
    sql: |
      UPDATE users
      SET first_name = :first_name, last_name = :last_name, email = :email
      WHERE id = :id
      RETURNING *

  # PATCH - partial update
  - type: one
    method: PATCH
    path: /user
    sql: |
      UPDATE users SET email = :email WHERE id = :id
      RETURNING *

  # DELETE without RETURNING - returns 204 No Content
  - type: none
    method: DELETE
    path: /user
    sql: DELETE FROM users WHERE id = :id

  # ---------------------------------------------------------------------------
  # Bulk Operations (type: many)
  # ---------------------------------------------------------------------------

  - type: many
    method: POST
    path: /users/bulk-status
    sql: |
      UPDATE users SET status = :status
      WHERE department = :department
      RETURNING *

  # ---------------------------------------------------------------------------
  # Mutation with Validation
  # ---------------------------------------------------------------------------

  - type: one
    method: POST
    path: /user/validated
    sql: |
      INSERT INTO users (first_name, last_name, email)
      VALUES (:first_name, :last_name, :email)
      RETURNING *
    transform:
      pre: |
        // Uses global helper functions
        const email = requireEmail(input.email);
        const firstName = requireString(input.first_name, 'first_name');
        const lastName = requireString(input.last_name, 'last_name');

        return {
          first_name: firstName,
          last_name: lastName,
          email: email
        };
      post: |
        return { ...output, created: true };

  # ---------------------------------------------------------------------------
  # Mutation with Accepts Option
  # ---------------------------------------------------------------------------

  - type: one
    method: POST
    path: /user/form
    sql: |
      INSERT INTO users (name, email) VALUES (:name, :email)
      RETURNING *
    accepts: form  # Only accept application/x-www-form-urlencoded

  # ---------------------------------------------------------------------------
  # Mock Mutation
  # ---------------------------------------------------------------------------

  - type: one
    method: POST
    path: /mock/user
    mock:
      object_js: |
        return {
          id: Math.floor(Math.random() * 10000),
          name: input.name,
          email: input.email,
          created_at: new Date().toISOString()
        };

  # ---------------------------------------------------------------------------
  # Mock Mutation with type: none
  # ---------------------------------------------------------------------------
  # Use mock: true for type: none when no database operation is needed

  - type: none
    method: DELETE
    path: /mock/user
    mock: true  # No database operation, just validate and return 204
    transform:
      pre: |
        // Validate the request
        if (!input.id) {
          throw { status: 400, body: { error: 'id is required' } };
        }
        return { id: input.id };