Skip to main content

Creating Your First API

This guide walks you through creating your first API endpoint with flAPI using a Parquet file as the data source. You will end up with a running GET /customers/ endpoint that supports optional filtering parameters.

Prerequisites​

Before starting, make sure you have:

  1. The flapi binary or Docker image available (see the Quickstart Guide).
  2. A customers.parquet file. The flAPI repository ships one under examples/data/customers.parquet (TPC-H customer columns: c_custkey, c_name, c_acctbal, c_mktsegment, etc.).

Project Layout​

Create the following directory layout:

my-first-api/
├── flapi.yaml
├── data/
│   └── customers.parquet
└── sqls/
    ├── customers.yaml      # Endpoint configuration
    └── customers.sql       # SQL template (Mustache)

Step 1: Create the Main Configuration​

Create flapi.yaml at the project root:

project-name: my-first-api
project-description: My first flAPI project

template:
  path: ./sqls
  environment-whitelist:
    - '^FLAPI_.*'

connections:
  customers-parquet:
    properties:
      path: ./data/customers.parquet

duckdb:
  access_mode: READ_WRITE

Key points:

  • project-name / project-description use hyphens (this is the canonical naming convention).
  • connections.<name>.properties exposes values to your SQL template as {{ conn.<key> }}.
  • DuckDB pass-through keys (access_mode, db_path, threads, max_memory, default_order) keep snake_case because they are forwarded verbatim to DuckDB.

Step 2: Define the Endpoint​

Create sqls/customers.yaml:

url-path: /customers/
method: GET

request:
  - field-name: id
    field-in: query
    description: Customer key (c_custkey)
    required: false
    validators:
      - type: int
        min: 1
        max: 1000000
        preventSqlInjection: true

  - field-name: segment
    field-in: query
    description: Market segment
    required: false
    validators:
      - type: enum
        allowedValues: [AUTOMOBILE, BUILDING, FURNITURE, HOUSEHOLD, MACHINERY]

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

with-pagination: true

Key points:

  • url-path, template-source, and connection are required.
  • method defaults to GET if omitted.
  • Each request field declares field-name, field-in (query, path, header, or body), and an optional list of validators. Built-in validator types are int, string, enum, email, uuid, date, and time.
  • with-pagination: true automatically accepts limit and offset query parameters.

Step 3: Write the SQL Template​

Create sqls/customers.sql. Templates use Mustache syntax — {{ params.foo }} for variables and {{#params.foo}} … {{/params.foo}} for conditional sections that render only when the parameter is present.

SELECT
  c_custkey   AS id,
  c_name      AS name,
  c_acctbal   AS balance,
  c_mktsegment AS segment
FROM '{{{ conn.path }}}'
WHERE 1=1
{{#params.id}}
  AND c_custkey = {{{ params.id }}}
{{/params.id}}
{{#params.segment}}
  AND c_mktsegment = '{{{ params.segment }}}'
{{/params.segment}}

Notes on the template syntax:

  • {{{ ... }}} (triple braces) emits the value unescaped, which is required for SQL fragments and file paths.
  • {{ conn.path }} reads the path property of the customers-parquet connection defined in flapi.yaml.
  • {{#params.id}} … {{/params.id}} renders the inner SQL only when the id query parameter is provided. Otherwise it is silently skipped.
  • Validators defined in customers.yaml run before the template is rendered, so by the time {{ params.id }} reaches the SQL it has already been type-checked.

Step 4: Run flAPI​

Start the server pointing at your config file:

./flapi -c flapi.yaml

flAPI listens on port 8080 by default (override with -p <port> or http-port: in flapi.yaml). You can also validate the configuration without starting the server:

./flapi -c flapi.yaml --validate-config

Step 5: Call the Endpoint​

In another terminal:

# All customers (first page)
curl "http://localhost:8080/customers/"

# Filter by customer key
curl "http://localhost:8080/customers/?id=42"

# Filter by market segment
curl "http://localhost:8080/customers/?segment=AUTOMOBILE"

# Combine filters with pagination
curl "http://localhost:8080/customers/?segment=BUILDING&limit=10&offset=0"

Next Steps​

🍪 Cookie Settings