Write Operations (CRUD)

flAPI supports full CRUD (Create, Read, Update, Delete) operations, so you can build complete data APIs that not only serve data but also accept modifications. This guide covers how to configure write endpoints with validation, transactions, and RETURNING-clause responses.

Overview

Write operations in flAPI let you:

• CREATE (POST) — insert new records with validation
• UPDATE (PUT / PATCH) — modify existing records (full or partial)
• DELETE (DELETE) — remove records safely
• RETURNING — get the affected rows back in the response

All write endpoints support:

• Pre-write validation (the same 7 validator types as read endpoints + SQL-injection scanning)
• ACID transactions (automatic rollback on error)
• RETURNING-clause data in the response
• Structured 400 error responses with field-level messages

How flAPI Picks Read vs Write

flAPI auto-detects the operation type from method::

method:	operation.type default
GET	Read
POST, PUT, PATCH, DELETE	Write

You can override the auto-detection with an explicit operation: block.

Basic Configuration

Write endpoints look just like read endpoints, with an additional operation: block.

url-path: /api/products/
method: POST

operation:
  type: Write              # Read or Write (case-insensitive)
  validate-before-write: true   # default true
  returns-data: true       # populate `data` from RETURNING
  transaction: true        # wrap in a transaction (default true)

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

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

operation.* key	Type	Default	Description
type	string	auto	Read or Write (case-insensitive). Auto-set from method:.
returns-data	bool	false	Populate data from a RETURNING clause.
transaction	bool	true	Wrap the query in a transaction.
validate-before-write	bool	true	Enforce request validation before any SQL runs.

Operation Types

CREATE (POST)

Insert new records.

url-path: /api/products/
method: POST

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

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

  - field-name: supplier_id
    field-in: body
    required: true
    validators:
      - type: int
        min: 1

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

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

SQL template (products-create.sql):

INSERT INTO nw.Products (
    ProductName,
    SupplierID,
    UnitPrice
)
VALUES (
    '{{{ params.product_name }}}',
    {{ params.supplier_id }},
    {{ params.unit_price }}
)
RETURNING ProductID, ProductName, UnitPrice

Request:

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

Response:

{
  "rows_affected": 1,
  "data": [
    { "ProductID": 42, "ProductName": "New Widget", "UnitPrice": 29.99 }
  ]
}

UPDATE (PUT / PATCH)

Modify existing records. For partial updates, leave the body fields optional and COALESCE in SQL.

url-path: /api/products/:product_id
method: PUT

operation:
  type: Write
  returns-data: true
  transaction: true
  validate-before-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})?$'

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

SQL template:

UPDATE nw.Products
SET
    ProductName = COALESCE('{{{ params.product_name }}}', ProductName),
    UnitPrice   = COALESCE({{ params.unit_price }}, UnitPrice)
WHERE ProductID = {{ params.product_id }}
RETURNING ProductID, ProductName, UnitPrice

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

DELETE

Remove records.

url-path: /api/products/:product_id
method: DELETE

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

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

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

SQL template:

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

Response (no RETURNING):

{
  "rows_affected": 1
}

Validation

Pre-write validation uses the same seven validator types as read endpoints (int, string, enum, email, uuid, date, time). See Validation for the complete reference.

- field-name: email
  field-in: body
  required: true
  validators:
    - type: email

By default every validator scans the value for SQL-injection patterns (preventSqlInjection: true). Disable it only for fields that legitimately contain SQL-looking text.

Transactions

operation:
  type: Write
  transaction: true   # default

Benefits:

• Atomicity — the whole template runs in one transaction; an error rolls everything back.
• Consistency — never partial writes.
• Isolation — concurrent writes don't interleave.
• Durability — committed changes persist.

RETURNING Clause

Use SQL RETURNING to send the affected rows back without a follow-up query. Combine it with operation.returns-data: true:

operation:
  type: Write
  returns-data: true

INSERT INTO products (name, price)
VALUES ('{{{ params.name }}}', {{ params.price }})
RETURNING id, name, price

{
  "rows_affected": 1,
  "data": [
    { "id": 42, "name": "Widget", "price": 29.99 }
  ]
}

Cache Hooks on Write

If the endpoint touches a cached table, two flags control what happens to that cache after the write:

cache:
  enabled: true
  table: products_cache
  schema: analytics
  schedule: 5m
  primary-key: [ProductID]
  cursor:
    column: UpdatedAt
    type: timestamp

  invalidate-on-write: true    # mark cache stale after this endpoint runs
  refresh-on-write: false      # immediately re-materialise the cache after write

Key	Default	Effect
cache.invalidate-on-write	false	Mark the cache stale; the next read triggers a refresh.
cache.refresh-on-write	false	Refresh the cache immediately after the write completes.

Response Shape

Successful write

{
  "rows_affected": 1,
  "data": [
    { "id": 42, "name": "Widget", "price": 29.99 }
  ]
}

data is only present when operation.returns-data: true and the SQL has a RETURNING clause.

Validation error (400)

{
  "errors": [
    { "field": "product_name", "message": "String is shorter than the minimum allowed length" },
    { "field": "supplier_id",  "message": "Integer is less than the minimum allowed value" }
  ]
}

Database error (500)

{
  "error": "Internal Server Error: FOREIGN KEY constraint failed"
}

Best Practices

1. Validate every body field

- field-name: email
  field-in: body
  required: true
  validators:
    - type: email

2. Use enums for status / category fields

- field-name: status
  field-in: body
  validators:
    - type: enum
      allowedValues: [pending, active, archived]

3. Keep transactions on

operation:
  transaction: true

4. Return data for confirmation

operation:
  returns-data: true

INSERT INTO products (...) VALUES (...)
RETURNING id, name, price

5. Validate path parameters too

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

6. Use COALESCE for partial updates

UPDATE products
SET
    name  = COALESCE('{{{ params.name }}}', name),
    price = COALESCE({{ params.price }}, price)
WHERE id = {{ params.id }}

Security Considerations

Authentication

Add auth to write endpoints. The schema mirrors read endpoints — see Authentication.

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

For basic auth with roles:

auth:
  enabled: true
  type: basic
  users:
    - username: editor
      password: '${EDITOR_PASSWORD}'
      roles: [editor, write]
    - username: admin
      password: '${ADMIN_PASSWORD}'
      roles: [admin, editor, write, read]

Rate Limiting

rate-limit:
  enabled: true
  max: 100
  interval: 60     # 100 requests per 60 seconds

Row-Level Restrictions

Use the auth.* context inside the SQL template to scope writes to the caller:

UPDATE products
SET name = '{{{ params.name }}}'
WHERE id = {{ params.id }}
  AND (
    '{{{ auth.roles }}}' LIKE '%admin%'
    OR owner_username = '{{{ auth.username }}}'
  )

The auth.* context exposes auth.username, auth.roles, auth.email, auth.type, and auth.authenticated.

Examples

See the CRUD API Tutorial for a complete working example with all four operations.

Next Steps

• Parameters — field-in: body and friends
• Validation — the seven validator types
• Authentication — basic / JWT / bearer / OIDC
• Response Format — read-endpoint envelope vs write-endpoint shape