flAPI

# Single configuration file serves as BOTH REST endpoint AND MCP tool url-path: /customers/ # Makes this a REST endpoint mcp-tool: # Also makes this an MCP tool name: get_customers description: Retrieve customer information by ID result-mime-type: application/json request: - field-name: id field-in: query description: Customer ID required: false validators: - type: int min: 1 max: 1000000 preventSqlInjection: true template-source: customers.sql connection: [customers-parquet] rate-limit: enabled: true max: 100 interval: 60 auth: enabled: true type: basic users: - username: admin password: secret roles: [admin]

# MCP Resource example mcp-resource: name: customer_schema description: Customer database schema definition mime-type: application/json template-source: customer-schema.sql connection: [customers-parquet]

Once MCP is enabled, you can interact with tools using JSON-RPC 2.0:

# Check MCP server health curl 'http://localhost:8081/mcp/health' # Initialize MCP connection curl -X POST http://localhost:8081/mcp/jsonrpc \ -H "Content-Type: application/json" \ -d '{"jsonrpc": "2.0", "id": 1, "method": "initialize"}' # List available tools (discovered from unified configuration) curl -X POST http://localhost:8081/mcp/jsonrpc \ -H "Content-Type: application/json" \ -d '{"jsonrpc": "2.0", "id": 2, "method": "tools/list"}' # Call a tool (same SQL template used for both REST and MCP) curl -X POST http://localhost:8081/mcp/jsonrpc \ -H "Content-Type: application/json" \ -d '{"jsonrpc": "2.0", "id": 3, "method": "tools/call", "params": {"name": "get_customers", "arguments": {"id": "123"}}}'

Here's a simple example of how to create an API endpoint using flAPI:

flAPI uses the popularYAMLformat to configure the API endpoints. A basic configuration file looks like this:

project_name: example-flapi-project project_description: An example flAPI project demonstrating various configuration options template: path: './sqls' # The path where SQL templates and API endpoint configurations are stored environment-whitelist: # Optional: List of regular expressions for whitelisting envvars which are available in the templates - '^FLAPI_.' duckdb: # Configuration of the DuckDB embedded into flAPI db_path: ./flapi_cache.db # Optional: remove or comment out for in-memory database, we use this store also as cache access_mode: READ_WRITE # See the https://duckdb.org/docs/configuration/overview) for more details threads: 8 max_memory: 8GB default_order: DESC connections: # A YAML map of database connection configurations, a API endpoint needs to reference one of these connections bigquery-lakehouse: # SQL commands to initialize the connection (e.g., e.g. installing, loading and configuring the BQ a DuckDB extension) init: | INSTALL 'bigquery' FROM 'http://storage.googleapis.com/hafenkran'; LOAD 'bigquery'; properties: # A YAML map of connection-specific properties (accessible in templates via {{ context.conn.property_name }}) project_id: 'my-project-id' customers-parquet: properties: path: './data/customers.parquet' heartbeat: enabled: true # The eartbeat worker is a background thread which can can be used to periodically trigger endpionts worker-interval: 10 # The interval in seconds at which the heartbeat worker will trigger endpoints enforce-https: enabled: false # Whether to force HTTPS for the API connections, we strongly recommend to use a reverse proxy to do SSL termination # ssl-cert-file: './ssl/cert.pem' # ssl-key-file: './ssl/key.pem'

> curl -X GET -u admin:secret "http://localhost:8080/customers?id=123" | jq . { "next": "", "total_count": 1, "data": [ { "c_mktsegment": "BUILDING", "c_acctbal": 5897.82999999999992724, "c_phone": "15-817-151-1168", "c_address": "YsOnaaER8MkvK5cpf4VSlq", "c_nationkey": 5, "c_name": "Customer#000000123", "c_comment": "ependencies. regular, ironic requests are fluffily regu", "c_custkey": 123 } ] }

⁉️ DuckLake-backed caching (current implementation)

ducklake: enabled: true alias: cache metadata-path: ./examples/data/cache.ducklake data-path: ./examples/data/cache.ducklake data-inlining-row-limit: 10 # Enable data inlining for small changes (optional) retention: max-snapshot-age: 14d compaction: enabled: false scheduler: enabled: true

url-path: /publicis template-source: publicis.sql connection: [bigquery-lakehouse] cache: enabled: true table: publicis_cache schema: analytics schedule: 5m retention: max_snapshot_age: 14d template_file: publicis/publicis_cache.sql

-- publicis/publicis_cache.sql CREATE OR REPLACE TABLE {{cache.catalog}}.{{cache.schema}}.{{cache.table}} AS SELECT p.country, p.product_category, p.campaign_type, p.channel, sum(p.clicks) AS clicks FROM bigquery_scan('{{{conn.project_id}}}.landing__publicis.kaercher_union_all') AS p GROUP BY 1, 2, 3, 4;

-- publicis.sql SELECT p.country, p.product_category, p.campaign_type, p.channel, p.clicks FROM {{cache.catalog}}.{{cache.schema}}.{{cache.table}} AS p WHERE 1=1

Data inlining (optional, for small changes)

DuckLake supports writing very small inserts directly into the metadata catalog instead of creating a Parquet file for every micro-batch. This is called "Data Inlining" and can significantly speed up small, frequent updates.

ducklake: enabled: true alias: cache metadata_path: ./examples/data/cache.ducklake data_path: ./examples/data/cache.ducklake data_inlining_row_limit: 10 # inline inserts up to 10 rows

- Larger inserts automatically fall back to normal Parquet file writes.
- Inlining applies to all caches (global setting), no per-endpoint toggle.

-- Flush all inlined data in the catalog CALL ducklake_flush_inlined_data('cache'); -- Flush only a specific schema CALL ducklake_flush_inlined_data('cache', schema_name => 'analytics'); -- Flush only a specific table (default schema "main") CALL ducklake_flush_inlined_data('cache', table_name => 'events_cache'); -- Flush a specific table in a specific schema CALL ducklake_flush_inlined_data('cache', schema_name => 'analytics', table_name => 'events_cache');

- This feature is provided by DuckLake and is currently marked experimental upstream. See the DuckLake docs for details:Data Inlining.

# Incremental append cache: enabled: true table: events_cache schema: analytics schedule: 10m cursor: column: created_at type: timestamp template-file: events/events_cache.sql # Incremental merge (upsert) cache: enabled: true table: customers_cache schema: analytics schedule: 15m primary-key: [id] cursor: column: updated_at type: timestamp template_file: customers/customers_cache.sql

Cache template variables available to your SQL:

-- events/events_cache.sql INSERT INTO {{cache.catalog}}.{{cache.schema}}.{{cache.table}} SELECT  FROM source_events WHERE {{#cache.previousSnapshotTimestamp}} event_time > TIMESTAMP '{{cache.previousSnapshotTimestamp}}' {{/cache.previousSnapshotTimestamp}}

-- customers/customers_cache.sql MERGE INTO {{cache.catalog}}.{{cache.schema}}.{{cache.table}} AS t USING ( SELECT  FROM source_customers WHERE {{#cache.previousSnapshotTimestamp}} updated_at > TIMESTAMP '{{cache.previousSnapshotTimestamp}}' {{/cache.previousSnapshotTimestamp}} ) AS s ON t.id = s.id WHEN MATCHED THEN UPDATE SET name = s.name, email = s.email, updated_at = s.updated_at WHEN NOT MATCHED THEN INSERT () VALUES (s.);

Audit, retention, compaction, and control APIs

curl -X POST "http://localhost:8080/api/v1/_config/endpoints/publicis/cache/refresh"

- Audit logs (endpoint-specific and global):

curl "http://localhost:8080/api/v1/_config/endpoints/publicis/cache/audit" curl "http://localhost:8080/api/v1/_config/cache/audit"