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: Multiple queries
SELECT * FROM customers WHERE country = 'US';
SELECT * FROM customers WHERE country = 'DE';
SELECT * FROM customers WHERE country = 'FR' AND segment = 'ENTERPRISE';
-- ... hundreds of combinations

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

Basic Syntax

Variable Insertion

Use {{{ }}} (triple braces) for safe variable insertion:

-- Template
SELECT * FROM customers
WHERE customer_id = {{{params.id}}}

-- With params: {"id": 12345}
SELECT * FROM customers
WHERE customer_id = 12345

Important: Triple braces {{{ }}} prevent HTML escaping and are safe for SQL when combined with flAPI's parameter validation.

Conditional Blocks

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

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

When params are provided:

{"status": "completed", "date": "2024-01-01"}

Expands to:

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

When params are empty:

{}

Expands to:

SELECT * FROM orders
WHERE 1=1

Negation (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

Request Parameters (`params`)

Access query/path/header parameters:

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

Connection Properties (`conn`)

Access connection configuration:

-- Connection defined as:
-- connections:
--   my-data:
--     properties:
--       path: './data/customers.parquet'

SELECT * FROM '{{{conn.path}}}'
WHERE active = true

User Context (`context.user`)

Access authenticated user information for row-level security:

SELECT * FROM sales_data
WHERE 1=1
{{#context.user.roles.admin}}
  -- Admins see everything
{{/context.user.roles.admin}}
{{^context.user.roles.admin}}
  -- Regular users see only their region
  AND region = '{{{context.user.region}}}'
  AND department = '{{{context.user.department}}}'
{{/context.user.roles.admin}}

Cache Properties (`cache`)

When building cache, access cache configuration:

-- In cache template
CREATE OR REPLACE TABLE {{cache.catalog}}.{{cache.schema}}.{{cache.table}} AS
SELECT * FROM source_data;

Environment Variables (`env`)

Access whitelisted environment variables:

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

Advanced Patterns

Dynamic Column Selection

SELECT
  customer_id,
  name,
  {{#context.user.roles.admin}}
  email,
  phone,
  {{/context.user.roles.admin}}
  created_at
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}}

Dynamic Sorting

SELECT * FROM products
ORDER BY
{{#params.sort_by}}
  {{{params.sort_by}}}
{{/params.sort_by}}
{{^params.sort_by}}
  created_at
{{/params.sort_by}}
{{#params.sort_desc}}
  DESC
{{/params.sort_desc}}

Array Iteration

SELECT * FROM products
WHERE category IN (
{{#params.categories}}
  '{{{.}}}'{{^-last}},{{/-last}}
{{/params.categories}}
)

Security Considerations

SQL Injection Prevention

flAPI prevents SQL injection through parameter validation:

# Endpoint configuration
request:
  - field-name: status
    field-in: query
    validators:
      - type: string
        enum: ['pending', 'completed', 'cancelled']

Only validated values are injected into templates.

Whitelist Pattern

Always use validation to whitelist acceptable values:

# ✅ Good: Validated enum
validators:
  - type: string
    enum: ['name', 'email', 'created_at']

# ❌ Bad: No validation
# User could inject arbitrary SQL

Safe Operators

Use parameterized patterns:

-- ✅ Good: Safe parameter injection
WHERE customer_id = {{{params.id}}}

-- ❌ Bad: String concatenation vulnerability
WHERE customer_id = {{{params.id}}} OR 1=1

Real-World Examples

Example 1: Filterable Customer API

Endpoint (sqls/customers.yaml):

url-path: /customers/
request:
  - field-name: segment
    field-in: query
    required: false
    validators:
      - type: string
        enum: ['AUTOMOTIVE', 'BUILDING', 'FURNITURE']
  - field-name: min_balance
    field-in: query
    required: false
    validators:
      - type: number
        min: 0
template-source: customers.sql

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

Usage:

# All customers
curl http://localhost:8080/customers/

# Automotive segment only
curl http://localhost:8080/customers/?segment=AUTOMOTIVE

# High-balance customers
curl http://localhost:8080/customers/?min_balance=5000

# Combined filters
curl http://localhost:8080/customers/?segment=AUTOMOTIVE&min_balance=5000

Example 2: Row-Level Security

Template:

SELECT
  order_id,
  customer_name,
  total,
  status
FROM orders
WHERE 1=1
{{#context.user.roles.admin}}
  -- Admins see all orders
{{/context.user.roles.admin}}
{{^context.user.roles.admin}}
  -- Sales reps see only their region
  AND region = '{{{context.user.region}}}'
  AND sales_rep_id = {{{context.user.id}}}
{{/context.user.roles.admin}}
{{#params.status}}
  AND status = '{{{params.status}}}'
{{/params.status}}

Example 3: Cache Materialization

Cache Template (sqls/analytics_cache.sql):

-- Runs on schedule to populate cache
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,
  AVG(revenue) as avg_revenue
FROM bigquery_scan('project.dataset.orders')
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1, 2, 3

API Template (sqls/analytics.sql):

-- Serves API requests from cache
SELECT *
FROM {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
WHERE 1=1
{{#params.country}}
  AND country = '{{{params.country}}}'
{{/params.country}}
{{#params.category}}
  AND product_category = '{{{params.category}}}'
{{/params.category}}
ORDER BY date DESC, total_revenue DESC

Testing Templates

Use the flapii CLI to test template expansion:

# Test without parameters
$ flapii templates expand /customers/

# Test with parameters
$ flapii templates expand /customers/ \
  --params '{"segment": "AUTOMOTIVE", "min_balance": 5000}'

Expanded SQL:
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
SELECT
  c_custkey as id,
  c_name as name,
  c_mktsegment as segment,
  c_acctbal as balance
FROM './data/customers.parquet'
WHERE 1=1
  AND c_mktsegment = 'AUTOMOTIVE'
  AND c_acctbal >= 5000
ORDER BY c_acctbal DESC
LIMIT 100
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Best Practices

1. Always Start with `WHERE 1=1`

Makes conditional filters cleaner:

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

-- ❌ Bad: Complex first-condition logic
WHERE
{{#params.filter1}}
  field1 = '{{{params.filter1}}}'
{{/params.filter1}}
{{^params.filter1}}
  1=1
{{/params.filter1}}

2. Use Validation

Always validate parameters:

validators:
  - type: string
    pattern: '^[A-Z]{2}$'  # Two-letter country code

3. Comment Your Templates

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

4. Keep Templates Focused

One template per endpoint. Don't try to handle too many use cases in one template.

5. Test Edge Cases

# Empty params
flapii templates expand /endpoint/

# All params
flapii templates expand /endpoint/ --params '{...}'

# Partial params
flapii templates expand /endpoint/ --params '{"param1": "value"}'

Next Steps

Quickstart Guide: Build your first templated API
How It Works: Understand the complete flAPI process
Endpoint Configuration: Learn endpoint setup and configuration
Parameters: Define and access request parameters
Validation: Validate parameters safely
YAML Syntax: Advanced YAML features and includes
CLI Tools: Test and debug templates locally
Examples: See templates in real-world examples

How Template Processing Works​

Why Templates?​

Basic Syntax​

Variable Insertion​

Conditional Blocks​

Negation (Unless)​

Context Variables​

Request Parameters (params)​

Connection Properties (conn)​

User Context (context.user)​

Cache Properties (cache)​

Environment Variables (env)​

Advanced Patterns​

Dynamic Column Selection​

Conditional Joins​

Dynamic Sorting​

Array Iteration​

Security Considerations​

SQL Injection Prevention​

Whitelist Pattern​

Safe Operators​

Real-World Examples​

Example 1: Filterable Customer API​

Example 2: Row-Level Security​

Example 3: Cache Materialization​

Testing Templates​

Best Practices​

1. Always Start with WHERE 1=1​

2. Use Validation​

3. Comment Your Templates​

4. Keep Templates Focused​

5. Test Edge Cases​

Next Steps​