Skip to main content

Write Operations (CRUD)

flAPI supports full CRUD (Create, Read, Update, Delete) operations, enabling you to 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 guaranteed data integrity.

Overview​

Write operations in flAPI allow you to:

  • CREATE (POST) - Insert new records with validation
  • UPDATE (PUT/PATCH) - Modify existing records with partial updates
  • DELETE (DELETE) - Remove records safely with transactions
  • RETURNING - Get back created/updated data immediately

All write operations support:

  • Pre-write validation (type checking, regex, min/max, SQL injection prevention)
  • ACID transactions (automatic rollback on error)
  • Immediate data return via RETURNING clause
  • Structured error responses with field-level feedback

Basic Configuration​

Write endpoints are configured just like read endpoints, with an additional operation block:

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

# Operation configuration
operation:
  type: write           # 'read' (default) or 'write'
  validate-before-write: true  # Validate inputs before touching database
  returns-data: true    # Return created/updated records
  transaction: true     # Wrap in transaction for atomicity

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

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

Operation Types​

CREATE (POST)​

Insert new records into your database.

Configuration:

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

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

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

UPDATE (PUT/PATCH)​

Modify existing records. All fields are typically optional for partial updates.

Configuration:

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

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})?$'

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

SQL Template (products-update.sql):

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

Request:

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 safely with transaction protection.

Configuration:

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

operation:
  type: write
  returns-data: false   # DELETE typically doesn't return data
  transaction: true
  validate-before-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]

SQL Template (products-delete.sql):

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

Response:

{
  "rows_affected": 1
}

Validation System​

Pre-write validation ensures data integrity before any database operation.

Validator Types​

String Validation​

- field-name: product_name
  validators:
    - type: string
      min: 1              # Minimum length
      max: 100            # Maximum length
      preventSqlInjection: true  # Block SQL injection patterns

Integer Validation​

- field-name: supplier_id
  validators:
    - type: int
      min: 1              # Minimum value
      max: 999            # Maximum value (optional)

Regex Pattern Validation​

- field-name: unit_price
  validators:
    - type: string
      regex: '^\d+(\.\d{1,2})?$'  # Valid decimal format

SQL Injection Prevention​

The preventSqlInjection flag automatically blocks common SQL injection patterns:

  • Quote characters (', ")
  • SQL keywords (SELECT, DROP, INSERT, etc.)
  • Comment syntax (--, /*, */)
  • Dangerous characters (; , \0)

Required vs Optional Fields​

request:
  - field-name: product_name
    required: true        # Must be provided
    validators: [...]

  - field-name: description
    required: false       # Optional (can be omitted)
    validators: [...]

Transactions​

All write operations can be wrapped in database transactions for ACID compliance.

operation:
  type: write
  transaction: true     # ACID transaction

Benefits:

  • Atomicity: Either the entire operation succeeds or rolls back completely
  • Consistency: Database is always in a valid state
  • Isolation: Concurrent writes don't interfere
  • Durability: Committed writes survive failures

RETURNING Clause​

Return created or updated data immediately without a separate query:

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

Returns:

{
  "returned_data": [
    {
      "id": 42,
      "name": "Widget",
      "price": 29.99
    }
  ]
}

Response Structure​

Successful Write​

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

Validation Error​

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

Multiple Validation Errors​

{
  "errors": [
    {
      "field": "product_name",
      "message": "String too short: minimum 1 character"
    },
    {
      "field": "supplier_id",
      "message": "Must be an integer >= 1"
    }
  ]
}

Database Error​

{
  "error": {
    "message": "Foreign key constraint failed: supplier_id 999 does not exist"
  }
}

Best Practices​

1. Always Validate Critical Fields​

- field-name: email
  required: true
  validators:
    - type: string
      regex: '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
      preventSqlInjection: true

2. Prevent SQL Injection​

- field-name: description
  validators:
    - type: string
      preventSqlInjection: true

3. Use Transactions for Multi-Row Operations​

operation:
  type: write
  transaction: true   # Ensures atomicity

4. Return Data for Confirmation​

operation:
  type: write
  returns-data: true  # Show client what was created

5. Validate Path Parameters​

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

6. Handle Partial Updates Gracefully​

Use COALESCE in SQL to only update provided fields:

UPDATE products
SET
    name = COALESCE(:name, name),
    price = COALESCE(:price, price)
WHERE id = :id

Security Considerations​

Authentication​

Add JWT authentication to write endpoints:

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

Rate Limiting​

Protect against abuse:

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

Row-Level Security​

Restrict who can modify which records:

UPDATE products
SET name = :name
WHERE id = :id
{{#context.user.roles.admin}}
  -- Admins can update any product
{{/context.user.roles.admin}}
{{^context.user.roles.admin}}
  AND owner_id = {{{ context.user.id }}}
{{/context.user.roles.admin}}

Examples​

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

🍪 Cookie Settings