Advanced CRUD

Add pagination, filtering, sorting, and batch operations to your endpoints.

Advanced CRUD

Once you have basic CRUD endpoints, add pagination, filtering, and sorting to make them production-ready. This guide builds on the patterns from Build a REST API.

Steps

Add pagination

Update your list endpoint to support ?page=1&limit=20:

1. Set Variable — Name: page, Value: query.page | toNumber | default(1)
    2. Set Variable — Name: limit, Value: query.limit | toNumber | default(20) | clamp(1, 100)
    3. Set Variable — Name: offset, Value: (vars.page - 1) * vars.limit
    4. Count — Table: products, Assign To: total
    5. Database Select — Table: products, Limit: vars.limit, Offset: vars.offset, Order By: created_at DESC, Assign To: products
    6. Respond — Body: { data: vars.products, pagination: { page: vars.page, limit: vars.limit, total: vars.total, pages: (vars.total / vars.limit) | ceil } }

Add filtering

Build a dynamic Where clause from query parameters:

1. Set Variable — Name: where, Value: {}
    2. If/Else — Condition: query.category != null
       - True: Set Variable — Value: vars.where | set('category', query.category)
    3. If/Else — Condition: query.minPrice != null
       - True: Set Variable — Value: vars.where | set('price', { $gte: query.minPrice | toNumber })

Pass vars.where as the Where parameter in your Database Select step. Clients filter with ?category=electronics&minPrice=50.

Add sorting

Accept ?sort=price&order=asc:

1. Set Variable — Name: allowedSorts, Value: ['created_at', 'price', 'name']
    2. If/Else — Condition: vars.allowedSorts | includes(query.sort | default('created_at'))
       - False: Respond — Status: 400, Body: { error: 'Unsupported sort field' }
    3. Set Variable — Name: sortField, Value: query.sort | default('created_at')
    4. Set Variable — Name: sortOrder, Value: (query.order == 'asc') ? 'ASC' : 'DESC'
    5. Set Variable — Name: orderBy, Value: vars.sortField + ' ' + vars.sortOrder

Use vars.orderBy in the Order By property of your Database Select step.

Batch updates

Create PATCH /api/products/batch for updating multiple records:

1. Loop — Iterable: input.updates, Item Variable: update
       - Database Update — Table: products, Data: vars.update.data, Where: { id: vars.update.id }
    2. Respond — Status: 200, Body: { updated: input.updates | length }

Keep sorting safe

Always validate sort columns against a whitelist. Do not pass arbitrary query parameter values into an Order By field.

Build a REST API
/guides/build-rest-api
Start with the basic CRUD setup
Add User Authentication
/guides/user-auth
Protect these endpoints