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/{endpoints,sqls}
cd my-crud-api

Step 2: Create a flAPI Configuration File

Create flapi.yaml:

server:
  port: 8080

# SQLite connection to Northwind database
connections:
  - name: northwind-sqlite
    driver: sqlite
    path: ":memory:"  # Or path to your database file

endpoints:
  - file: endpoints/products-list.yaml
  - file: endpoints/products-get.yaml
  - file: endpoints/products-create.yaml
  - file: endpoints/products-update.yaml
  - file: endpoints/products-delete.yaml

Step 3: Create the READ Endpoints

3.1: List All Products

Create endpoints/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 endpoints/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 endpoints/products-create.yaml:

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

operation:
  type: write
  validate-before-write: true
  returns-data: true
  transaction: 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
    defaultValue: "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 endpoints/products-update.yaml:

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

operation:
  type: write
  validate-before-write: true
  returns-data: true
  transaction: 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 endpoints/products-delete.yaml:

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

operation:
  type: write
  validate-before-write: true
  returns-data: false
  transaction: 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]

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:

authentication:
  type: jwt
  secret: ${JWT_SECRET}
  required_claims:
    - roles: ["editor", "admin"]

Add Rate Limiting

Prevent abuse:

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

Row-Level Security

Restrict users to their own data:

UPDATE Products
SET name = :name
WHERE ProductID = :product_id
  AND owner_id = {{{ context.user.id }}}

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 System
• Security Best Practices