Skip to main content

SQL Templates

SQL templates define the queries that power your API endpoints. flAPI uses Mustache templating syntax to create dynamic, parameterized queries.

Quick Example​

-- sqls/customers.sql
SELECT
  customer_id,
  name,
  email,
  segment
FROM customers
WHERE 1=1
{{#params.segment}}
  AND segment = '{{{ params.segment }}}'
{{/params.segment}}
{{#params.id}}
  AND customer_id = {{ params.id }}
{{/params.id}}
ORDER BY created_at DESC
LIMIT 100

Mustache Syntax​

flAPI uses Mustache for template logic:

  • {{ params.X }} on a typed field (validator type int/double/boolean/date/time/uuid/enum/email/string): the renderer emits a DuckDB ? placeholder and binds the value via duckdb_bind_*. SQL injection is structurally impossible at this site. Use for equality lookups.
  • {{{ variable }}}: Renders the raw value (no escaping). Use when the value participates in the SQL text — LIKE '%...%', identifier insertion, or composite expressions the prepared-statement rewriter cannot decompose.
  • {{#variable}}...{{/variable}}: Conditional block (rendered if variable is present and truthy). References inside a section stay on the Mustache path even if the field is typed.
  • {{^variable}}...{{/variable}}: Inverted block (rendered if variable is absent or falsy).

See SQL Templating Guide → Variable Insertion for the full layered injection-defense story (validators → prepared bind → regex fallback).

Available Context​

In your SQL templates, you have access to:

  • params.*: Validated request parameters (query, path, header, body)
  • conn.*: Properties of the first connection listed in connection:
  • auth.*: Authenticated user info — auth.username, auth.roles, auth.email, auth.type, auth.authenticated
  • cache.*: Cache metadata (used in cache-refresh templates)
  • env.*: Whitelisted environment variables

Next Steps​

For comprehensive SQL templating documentation, see:

🍪 Cookie Settings