Skip to main content

SQL Templating

flAPI uses Mustache templating to create dynamic, parameterized SQL queries. This approach, inspired by dbt, is immediately familiar to data analysts and data scientists while providing powerful flexibility.

How Template Processing Works​

Why Templates?​

Instead of writing separate SQL for every possible query combination, write one template that adapts:

-- Without templates: many queries
SELECT * FROM customers WHERE segment = 'AUTOMOBILE';
SELECT * FROM customers WHERE segment = 'BUILDING';
SELECT * FROM customers WHERE segment = 'FURNITURE' AND id = 42;
-- ... hundreds of combinations

-- With templates: one query
SELECT * FROM customers
WHERE 1=1
{{#params.segment}}
  AND segment = '{{{ params.segment }}}'
{{/params.segment}}
{{#params.id}}
  AND id = {{ params.id }}
{{/params.id}}

Basic Syntax​

Variable Insertion​

Use {{ }} (double braces) for typed equality lookups (the value is bound as a DuckDB prepared-statement parameter) and {{{ }}} (triple braces) for substring patterns, identifier insertion, and other places where the value participates in the SQL text:

-- Typed scalar (validator type: int/double/bool/date/time/uuid/enum/email/string)
-- The renderer emits a `?` placeholder and binds the value via duckdb_bind_*.
-- SQL injection at this site is structurally impossible.
WHERE customer_id = {{ params.id }}

-- Substring / LIKE / non-equality — value flows through Mustache as text,
-- single quotes required around it
WHERE name LIKE '%{{{ params.name }}}%'

When params.id = 12345 and params.name = "Acme", the prepared form renders as:

WHERE customer_id = ?     -- bound: int64(12345)
WHERE name LIKE '%Acme%'

flAPI's SQL-injection defense is layered:

  1. RequestValidator rejects malformed inputs (typed fields, range/regex/enum/format checks). Integer, date, and time parsing is strict — 1; DROP TABLE no longer slips through as 1, and 2024-03-15' OR 1=1 no longer slips through as 2024-03-15.
  2. DuckDB prepared-statement bind is the hard boundary for typed double-brace references on every code path — GET, POST, PUT, PATCH, Arrow-streaming endpoints, and multi-statement INSERT…;SELECT…RETURNING templates. The value travels as a primitive, not text, and cannot smuggle SQL.
  3. Keyword regex fallback still rejects obvious injection patterns for fields where the prepared path can't apply (triple-brace sites and untyped fields). It's demoted to a debug-level log only for numeric/temporal bindable fields where the regex is a known false-positive source.

If a typed parameter cannot be converted to its SQL type (e.g. id=abc for an int field), flAPI returns HTTP 400 with a JSON error body — bind-conversion failures are client input errors, not server errors.

End-to-end injection corpora ship in the repo:

Both confirm every classic injection pattern (UNION, OR 1=1, comment-evasion, xkcd 327) either returns zero rows or is rejected at the validator/bind boundary — none execute as SQL.

Conditional Blocks​

Use {{#variable}}...{{/variable}} for conditional inclusion:

SELECT * FROM orders
WHERE 1=1
{{#params.status}}
  AND status = '{{{ params.status }}}'
{{/params.status}}
{{#params.start_date}}
  AND order_date >= '{{{ params.start_date }}}'
{{/params.start_date}}

When params are provided ({"status": "completed", "start_date": "2024-01-01"}):

SELECT * FROM orders
WHERE 1=1
  AND status = 'completed'
  AND order_date >= '2024-01-01'

When params are empty ({}):

SELECT * FROM orders
WHERE 1=1

Inverted Sections (Unless)​

Use {{^variable}}...{{/variable}} for "if not" conditions:

SELECT * FROM products
WHERE 1=1
{{^params.include_discontinued}}
  AND active = true
{{/params.include_discontinued}}

Context Variables​

flAPI exposes the following Mustache contexts to every SQL template (defined in src/sql_template_processor.cpp):

Request Parameters (params.*)​

Access validated query, path, header and body parameters:

SELECT * FROM customers
WHERE 1=1
{{#params.segment}}
  AND market_segment = '{{{ params.segment }}}'
{{/params.segment}}
{{#params.id}}
  AND id = {{ params.id }}
{{/params.id}}

Path parameters declared with :name in url-path are available the same way (params.id for /customers/:id).

Connection Properties (conn.*)​

Access properties of the first connection listed under connection: in the endpoint YAML:

connections:
  customers-parquet:
    properties:
      path: './data/customers.parquet'
SELECT * FROM '{{{ conn.path }}}'
WHERE active = true

Authentication Context (auth.*)​

When an authenticated request hits the endpoint, the following fields are populated:

FieldDescription
auth.usernameThe authenticated principal's username
auth.rolesThe principal's roles (space-separated string suitable for matching)
auth.emailThe principal's email (when available, e.g., OIDC)
auth.typeAuthentication scheme: basic, jwt, bearer, or oidc
auth.authenticated"true" when the request was authenticated

Use these for row-level security or audit logging:

SELECT *
FROM sales_data
WHERE 1=1
{{#auth.authenticated}}
  AND sales_rep = '{{{ auth.username }}}'
{{/auth.authenticated}}

To branch on a specific role, perform the check inside the SQL itself (Mustache iterates auth.roles as a string, not a set):

SELECT *
FROM orders
WHERE 1=1
  AND (
    -- Admin sees everything; otherwise restrict to own region
    '{{{ auth.roles }}}' LIKE '%admin%'
    OR sales_rep = '{{{ auth.username }}}'
  )

Cache Properties (cache.*)​

In cache-refresh templates only, these fields are available:

FieldDescription
cache.catalogDuckLake catalog name
cache.schemaCache schema
cache.tableCache table
cache.scheduleRefresh schedule string
cache.previousSnapshotId / cache.previousSnapshotTimestampPrevious snapshot pointers
cache.cursorColumn / cache.cursorType / cache.primaryKeysIncremental refresh helpers

Environment Variables (env.*)​

Whitelisted environment variables (see template.environment-whitelist in flapi.yaml):

SELECT *
FROM logs
WHERE region = '{{{ env.AWS_REGION }}}'

Advanced Patterns​

Dynamic Column Selection​

SELECT
  customer_id,
  name
  {{#params.include_pii}}
  , email
  , phone
  {{/params.include_pii}}
FROM customers

Conditional Joins​

SELECT
  o.order_id,
  o.total
  {{#params.include_customer}}
  , c.customer_name
  {{/params.include_customer}}
FROM orders o
{{#params.include_customer}}
LEFT JOIN customers c ON o.customer_id = c.id
{{/params.include_customer}}

Default Values via Inverted Sections​

SELECT * FROM products
ORDER BY {{#params.sort_by}}{{{ params.sort_by }}}{{/params.sort_by}}{{^params.sort_by}}created_at{{/params.sort_by}}
LIMIT {{#params.limit}}{{ params.limit }}{{/params.limit}}{{^params.limit}}100{{/params.limit}}

Security Considerations​

SQL Injection Prevention​

flAPI prevents SQL injection through parameter validation (src/request_validator.cpp):

request:
  - field-name: status
    field-in: query
    validators:
      - type: enum
        allowedValues: ['pending', 'completed', 'cancelled']

Every parameter is type-checked, range-checked, and (by default) scanned for SQL-injection patterns before it ever reaches the template engine.

Whitelist Pattern​

Always restrict free-form text inputs with a validator:

# Good: enum whitelist
validators:
  - type: enum
    allowedValues: ['name', 'email', 'created_at']

# Good: strict regex
validators:
  - type: string
    regex: '^[A-Za-z_]+$'
    preventSqlInjection: true

Safe Variable Forms​

-- Integers/identifiers
WHERE customer_id = {{ params.id }}

-- Strings (always single-quoted around triple-brace)
WHERE name = '{{{ params.name }}}'

Real-World Examples​

Example 1: Filterable Customer API​

Endpoint (sqls/customers.yaml):

url-path: /customers/
method: GET

request:
  - field-name: segment
    field-in: query
    required: false
    validators:
      - type: enum
        allowedValues: [AUTOMOBILE, BUILDING, FURNITURE, HOUSEHOLD, MACHINERY]
  - field-name: min_balance
    field-in: query
    required: false
    validators:
      - type: int
        min: 0
        max: 1000000

template-source: customers.sql
connection:
  - customers-parquet

Template (sqls/customers.sql):

SELECT
  c_custkey      AS id,
  c_name         AS name,
  c_mktsegment   AS segment,
  c_acctbal      AS balance
FROM '{{{ conn.path }}}'
WHERE 1=1
{{#params.segment}}
  AND c_mktsegment = '{{{ params.segment }}}'
{{/params.segment}}
{{#params.min_balance}}
  AND c_acctbal >= {{ params.min_balance }}
{{/params.min_balance}}
ORDER BY c_acctbal DESC
LIMIT 100

Example 2: Row-Level Security via auth.*​

SELECT
  order_id,
  customer_name,
  total,
  status
FROM orders
WHERE 1=1
  AND (
    '{{{ auth.roles }}}' LIKE '%admin%'
    OR sales_rep = '{{{ auth.username }}}'
  )
{{#params.status}}
  AND status = '{{{ params.status }}}'
{{/params.status}}

Example 3: Cache Materialization​

Cache Template (sqls/analytics_cache.sql):

CREATE OR REPLACE TABLE {{cache.catalog}}.{{cache.schema}}.{{cache.table}} AS
SELECT
  DATE(order_date)  AS date,
  product_category,
  country,
  COUNT(*)          AS order_count,
  SUM(revenue)      AS total_revenue
FROM bigquery_scan('project.dataset.orders')
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1, 2, 3

Best Practices​

1. Always Start with WHERE 1=1​

Makes conditional filters trivial to add or remove:

WHERE 1=1
{{#params.filter1}}
  AND field1 = '{{{ params.filter1 }}}'
{{/params.filter1}}

2. Validate Every Parameter​

validators:
  - type: string
    regex: '^[A-Z]{2}$'
    preventSqlInjection: true

3. Match Brace Form to Type​

  • Integer/numeric parameters: {{ params.x }} (double).
  • String/date/email/uuid parameters: '{{{ params.x }}}' (triple, quoted).

4. Comment Your Templates​

-- Customer API template
-- Supports filtering by segment and minimum balance
SELECT ...

5. Keep Templates Focused​

One template per endpoint. Don't try to encode many use cases in a single file.

Next Steps​

🍪 Cookie Settings