Build Your First Complete CRUD API

In this tutorial, you'll build a complete product management API with Create, Read, Update, and Delete operations using flAPI. By the end, you'll have a fully functional API that validates input, returns created data, and wraps writes in transactions.

What You'll Build

A REST API for managing products with these endpoints:

POST /products/ - Create a new product
GET /products/ - List all products
GET /products/:id - Get a single product
PUT /products/:id - Update a product
DELETE /products/:id - Delete a product

Prerequisites

flAPI binary or Docker installed
Northwind SQLite database (included in flAPI examples)
curl or similar HTTP client

Step 1: Set Up Your Project

Create the directory structure:

mkdir -p my-crud-api/sqls
cd my-crud-api

flAPI loads endpoint YAML and SQL templates from the same directory configured by template.path (here, ./sqls).

Step 2: Create a flAPI Configuration File

Create flapi.yaml:

project-name: my-crud-api
project-description: Product CRUD API on the Northwind SQLite database

template:
  path: './sqls'

# SQLite connection to Northwind database
connections:
  northwind-sqlite:
    init: |
      INSTALL sqlite;
      LOAD sqlite;
      ATTACH IF NOT EXISTS './data/northwind.sqlite' AS nw (TYPE sqlite);

duckdb:
  access_mode: READ_WRITE

flAPI automatically picks up every *.yaml endpoint file it finds under template.path (here, ./sqls). The CRUD endpoint files in the next steps will all live under that directory.

Step 3: Create the READ Endpoints

3.1: List All Products

Create sqls/products-list.yaml:

url-path: /products/
method: GET
description: Get all products

request: []  # No parameters

template-source: products-list.sql
connection: [northwind-sqlite]

Create sqls/products-list.sql:

SELECT
  ProductID as id,
  ProductName as name,
  SupplierID as supplier_id,
  UnitPrice as unit_price,
  UnitsInStock as units_in_stock,
  Discontinued as discontinued
FROM Products
ORDER BY ProductID
LIMIT 100

Test it:

curl http://localhost:8080/products/

3.2: Get Single Product

Create sqls/products-get.yaml:

url-path: /products/:product_id
method: GET
description: Get a specific product by ID

request:
  - field-name: product_id
    field-in: path
    required: true
    description: Product ID to retrieve
    validators:
      - type: int
        min: 1

template-source: products-get.sql
connection: [northwind-sqlite]

Create sqls/products-get.sql:

SELECT
  ProductID as id,
  ProductName as name,
  SupplierID as supplier_id,
  UnitPrice as unit_price,
  UnitsInStock as units_in_stock,
  Discontinued as discontinued
FROM Products
WHERE ProductID = {{{ params.product_id }}}

Test it:

curl http://localhost:8080/products/1

Step 4: Create the CREATE Endpoint

Create sqls/products-create.yaml:

url-path: /products/
method: POST
description: Create a new product

# Protect writes with JWT (the read endpoints stay public).
auth:
  enabled: true
  type: bearer
  jwt-secret: '{{env.PRODUCTS_API_JWT_SECRET}}'
  jwt-issuer: my-auth-server

operation:
  type: write
  validate-before-write: true
  returns-data: true
  transaction: true

# Drop the cached GET /products/ payload after a successful write so the
# next read goes back to SQLite and re-materializes a fresh snapshot.
cache:
  invalidate-on-write: true

request:
  - field-name: product_name
    field-in: body
    required: true
    description: Product name (1-100 characters)
    validators:
      - type: string
        min: 1
        max: 100
        preventSqlInjection: true

  - field-name: supplier_id
    field-in: body
    required: true
    description: Supplier ID (must exist in database)
    validators:
      - type: int
        min: 1

  - field-name: unit_price
    field-in: body
    required: false
    description: Unit price in decimal format
    validators:
      - type: string
        regex: '^\d+(\.\d{1,2})?$'

  - field-name: units_in_stock
    field-in: body
    required: false
    description: Number of units in stock
    validators:
      - type: int
        min: 0

  - field-name: discontinued
    field-in: body
    required: false
    description: Whether product is discontinued (0 or 1)
    validators:
      - type: int
        min: 0
        max: 1
    default: "0"

template-source: products-create.sql
connection: [northwind-sqlite]

Create sqls/products-create.sql:

INSERT INTO Products (
    ProductName,
    SupplierID,
    UnitPrice,
    UnitsInStock,
    Discontinued
)
VALUES (
    '{{{ params.product_name }}}',
    {{{ params.supplier_id }}},
    {{{ params.unit_price }}},
    {{{ params.units_in_stock }}},
    COALESCE({{{ params.discontinued }}}, 0)
)
RETURNING
  ProductID as id,
  ProductName as name,
  SupplierID as supplier_id,
  UnitPrice as unit_price,
  UnitsInStock as units_in_stock,
  Discontinued as discontinued

Test it:

curl -X POST http://localhost:8080/products/ \
  -H "Content-Type: application/json" \
  -d '{
    "product_name": "New Widget",
    "supplier_id": 1,
    "unit_price": "29.99",
    "units_in_stock": 100
  }'

Response:

{
  "returned_data": [
    {
      "id": 78,
      "name": "New Widget",
      "supplier_id": 1,
      "unit_price": 29.99,
      "units_in_stock": 100,
      "discontinued": 0
    }
  ],
  "rows_affected": 1
}

Step 5: Create the UPDATE Endpoint

Create sqls/products-update.yaml:

url-path: /products/:product_id
method: PUT
description: Update an existing product

auth:
  enabled: true
  type: bearer
  jwt-secret: '{{env.PRODUCTS_API_JWT_SECRET}}'
  jwt-issuer: my-auth-server

operation:
  type: write
  validate-before-write: true
  returns-data: true
  transaction: true

cache:
  invalidate-on-write: true

request:
  - field-name: product_id
    field-in: path
    required: true
    validators:
      - type: int
        min: 1

  - field-name: product_name
    field-in: body
    required: false
    validators:
      - type: string
        min: 1
        max: 100
        preventSqlInjection: true

  - field-name: unit_price
    field-in: body
    required: false
    validators:
      - type: string
        regex: '^\d+(\.\d{1,2})?$'

  - field-name: units_in_stock
    field-in: body
    required: false
    validators:
      - type: int
        min: 0

  - field-name: discontinued
    field-in: body
    required: false
    validators:
      - type: int
        min: 0
        max: 1

template-source: products-update.sql
connection: [northwind-sqlite]

Create sqls/products-update.sql:

UPDATE Products
SET
  ProductName = COALESCE('{{{ params.product_name }}}', ProductName),
  UnitPrice = COALESCE({{{ params.unit_price }}}, UnitPrice),
  UnitsInStock = COALESCE({{{ params.units_in_stock }}}, UnitsInStock),
  Discontinued = COALESCE({{{ params.discontinued }}}, Discontinued)
WHERE ProductID = {{{ params.product_id }}}
RETURNING
  ProductID as id,
  ProductName as name,
  SupplierID as supplier_id,
  UnitPrice as unit_price,
  UnitsInStock as units_in_stock,
  Discontinued as discontinued

Test it:

curl -X PUT http://localhost:8080/products/78 \
  -H "Content-Type: application/json" \
  -d '{
    "product_name": "Updated Widget",
    "unit_price": "34.99"
  }'

Step 6: Create the DELETE Endpoint

Create sqls/products-delete.yaml:

url-path: /products/:product_id
method: DELETE
description: Delete a product

auth:
  enabled: true
  type: bearer
  jwt-secret: '{{env.PRODUCTS_API_JWT_SECRET}}'
  jwt-issuer: my-auth-server

operation:
  type: write
  validate-before-write: true
  returns-data: false
  transaction: true

cache:
  invalidate-on-write: true

request:
  - field-name: product_id
    field-in: path
    required: true
    validators:
      - type: int
        min: 1
        preventSqlInjection: true

template-source: products-delete.sql
connection: [northwind-sqlite]

Why invalidate on write?

The three write endpoints all set cache.invalidate-on-write: true. After a successful POST, PUT, or DELETE, flAPI drops any cached payload for GET /products/ so the next read re-materializes from SQLite instead of serving the stale snapshot it captured before the mutation. If you'd rather proactively re-run the cache template right after the write instead of waiting for the next request, use cache.refresh-on-write: true instead.

A cached GET for high-traffic reads

If the listing endpoint gets pounded, switch it to a DuckLake-backed cache so hot reads hit DuckDB instead of SQLite. Open sqls/products-list.yaml and add a cache: block:

url-path: /products/
method: GET
description: Get all products

cache:
  enabled: true
  table: products_list_cache
  schema: analytics
  schedule: 5m
  primary-key: [id]

request: []

template-source: products-list.sql
connection: [northwind-sqlite]

Then update sqls/products-list.sql to read from the cache table that flAPI populates for you:

SELECT
  id,
  name,
  supplier_id,
  unit_price,
  units_in_stock,
  discontinued
FROM {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
ORDER BY id
LIMIT 100

Pair this with the invalidate-on-write flags on the write endpoints above and you get the best of both worlds: writes feel instantly consistent because the next GET rebuilds the cache, while idle traffic stays millisecond-fast.

Create sqls/products-delete.sql:

DELETE FROM Products
WHERE ProductID = {{{ params.product_id }}}

Test it:

curl -X DELETE http://localhost:8080/products/78

Response:

{
  "rows_affected": 1
}

Step 7: Run the Server

Start your flAPI server:

flapi -c flapi.yaml

Or with Docker:

docker run -p 8080:8080 \
  -v $(pwd):/config \
  ghcr.io/datazoode/flapi -c /config/flapi.yaml

Step 8: Complete API Walkthrough

Create a Product

curl -X POST http://localhost:8080/products/ \
  -H "Content-Type: application/json" \
  -d '{
    "product_name": "Excellent Widget",
    "supplier_id": 2,
    "unit_price": "49.99",
    "units_in_stock": 500
  }'

Read All Products

curl http://localhost:8080/products/

Read Specific Product

curl http://localhost:8080/products/78

Update Product

curl -X PUT http://localhost:8080/products/78 \
  -H "Content-Type: application/json" \
  -d '{
    "units_in_stock": 750,
    "unit_price": "54.99"
  }'

Delete Product

curl -X DELETE http://localhost:8080/products/78

Validation in Action

Try invalid inputs to see validation errors:

# Invalid: product_name too short
curl -X POST http://localhost:8080/products/ \
  -H "Content-Type: application/json" \
  -d '{
    "product_name": "",
    "supplier_id": 1
  }'

# Response:
{
  "error": {
    "field": "product_name",
    "message": "String must be at least 1 character"
  }
}

# Invalid: price format
curl -X POST http://localhost:8080/products/ \
  -H "Content-Type: application/json" \
  -d '{
    "product_name": "Widget",
    "supplier_id": 1,
    "unit_price": "not-a-number"
  }'

# Response:
{
  "error": {
    "field": "unit_price",
    "message": "Invalid format: expected decimal number (e.g., 19.99)"
  }
}

Next Steps

Add Security

Protect write endpoints with authentication. Each endpoint takes an auth block whose type is one of basic, bearer (also used for JWT bearer tokens — set jwt-secret to enable JWT validation), or oidc:

auth:
  enabled: true
  type: bearer
  jwt-secret: '{{env.JWT_SECRET}}'
  jwt-issuer: my-auth-server

Add Rate Limiting

Prevent abuse:

rate-limit:
  max: 100
  interval: 60  # Max 100 requests per minute

Row-Level Security

Restrict admins to all rows and other users to their own data. The authenticated username comes from the auth.username context variable, and auth.roles is a comma-joined string you can match with LIKE:

UPDATE Products
SET ProductName = '{{{ params.product_name }}}'
WHERE ProductID = {{{ params.product_id }}}
  AND (
    '{{{ auth.roles }}}' LIKE '%admin%'
    OR owner_username = '{{{ auth.username }}}'
  )

Expand Validation

Add more complex validators for your domain:

- field-name: discount_percentage
  validators:
    - type: int
      min: 0
      max: 100

Learn More

Write Operations Reference
Validation
Security Best Practices
Authentication schemes — JWT, basic, bearer, and OIDC details
Caching strategy — how DuckLake snapshots and invalidation fit together

What You'll Build​

Prerequisites​

Step 1: Set Up Your Project​

Step 2: Create a flAPI Configuration File​

Step 3: Create the READ Endpoints​

3.1: List All Products​

3.2: Get Single Product​

Step 4: Create the CREATE Endpoint​

Step 5: Create the UPDATE Endpoint​

Step 6: Create the DELETE Endpoint​

Why invalidate on write?​

A cached GET for high-traffic reads​

Step 7: Run the Server​

Step 8: Complete API Walkthrough​

Create a Product​

Read All Products​

Read Specific Product​

Update Product​

Delete Product​

Validation in Action​

Next Steps​

Add Security​

Add Rate Limiting​

Row-Level Security​

Expand Validation​

Learn More​

What You'll Build

Prerequisites

Step 1: Set Up Your Project

Step 2: Create a flAPI Configuration File

Step 3: Create the READ Endpoints

3.1: List All Products

3.2: Get Single Product

Step 4: Create the CREATE Endpoint

Step 5: Create the UPDATE Endpoint

Step 6: Create the DELETE Endpoint

Why invalidate on write?

A cached GET for high-traffic reads

Step 7: Run the Server

Step 8: Complete API Walkthrough

Create a Product

Read All Products

Read Specific Product

Update Product

Delete Product

Validation in Action

Next Steps

Add Security

Add Rate Limiting

Row-Level Security

Expand Validation

Learn More