Items API (CRUD Operations)

This documentation is verified against the actual API test suite (test/item.test.js).

Overview

The Items API provides CRUD (Create, Read, Update, Delete) operations for any collection in your database. All endpoints follow the pattern /items/:collection where :collection is your table name.

Key Features:

Dynamic CRUD for any collection
Advanced filtering and querying
Relational data loading
Field selection
Pagination support
Permission-based access control
Bulk operations with transactional safety

Get Items

Retrieve items from a collection with optional filtering, sorting, and pagination.

Endpoint: GET /items/:collection

Authentication: Required (Bearer token)

URL Parameters

Parameter	Type	Description
collection	string	Name of the collection (table)

Query Parameters

Parameter	Type	Description
fields	array	Fields to return. Supports wildcards: `` (all fields), `relation.` (relation fields), `relation.**` (recursive). See Item Query Reference Guide
filter	object	Filter conditions (JSON stringified)
limit	number	Maximum number of items to return
page	number	Page number for pagination

Basic Example

curl "http://localhost:3000/items/posts" \
  -H "Authorization: Bearer YOUR_TOKEN"

With Field Selection

curl "http://localhost:3000/items/posts?fields=%5B%22id%22%2C%22title%22%2C%22content%22%5D" \
  -H "Authorization: Bearer YOUR_TOKEN"

With Pagination

curl "http://localhost:3000/items/posts?limit=10&page=1" \
  -H "Authorization: Bearer YOUR_TOKEN"

Response

Success (200 OK):

{
  "data": [
    {
      "id": 1,
      "title": "Test Post",
      "content": "This is a test post",
      "published": true,
      "authorId": "user-id"
    }
  ],
  "totalCount": 1
}

Advanced Queries

Relational Queries

Load related data using dot notation in the fields parameter.

Example: Get users with their role information

curl -G "http://localhost:3000/items/baasix_User" \
  --data-urlencode 'fields=["*", "userRoles.role.name"]' \
  -H "Authorization: Bearer YOUR_TOKEN"

Response:

{
  "data": [
    {
      "id": "user-id",
      "email": "test@example.com",
      "firstName": "Test",
      "lastName": "User",
      "userRoles": [
        {
          "role": {
            "name": "user"
          }
        }
      ]
    }
  ],
  "totalCount": 1
}

Filtering

Use the filter parameter with JSON-stringified conditions.

Filter Operators:

eq - Equal to
ne - Not equal to
gt - Greater than
gte - Greater than or equal
lt - Less than
lte - Less than or equal
in - In array
nin - Not in array
like - SQL LIKE pattern

Example: Filter users by role name and first name

curl -G "http://localhost:3000/items/baasix_User" \
  --data-urlencode 'filter={"AND":[{"userRoles.role.name":{"eq":"user"}},{"firstName":{"eq":"Test"}}]}' \
  --data-urlencode 'fields=["*", "userRoles.role.name"]' \
  --data-urlencode 'limit=10' \
  --data-urlencode 'page=1' \
  -H "Authorization: Bearer YOUR_TOKEN"

Filter Structure:

{
  "AND": [
    {
      "userRoles.role.name": {
        "eq": "user"
      }
    },
    {
      "firstName": {
        "eq": "Test"
      }
    }
  ]
}

Logical Operators:

AND - All conditions must match
OR - At least one condition must match

Create Item

Create a new item in a collection.

Endpoint: POST /items/:collection

Authentication: Required (Bearer token)

Request Body

Send the item data as JSON. Required fields depend on your schema definition.

{
  "title": "Test Post",
  "content": "This is a test post",
  "published": true,
  "authorId": "user-id"
}

Response

Success (201 Created):

{
  "data": {
    "id": 1,
    "title": "Test Post",
    "content": "This is a test post",
    "published": true,
    "authorId": "user-id",
    "createdAt": "2024-01-01T00:00:00.000Z"
  }
}

Error (403 Forbidden):

When user doesn't have create permission:

{
  "error": {
    "message": "You don't have permission to create items in this collection"
  }
}

Example

curl -X POST "http://localhost:3000/items/posts" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "title": "New Post",
    "content": "Post content here",
    "authorId": "user-123"
  }'

Update Item

Update an existing item by ID.

Endpoint: PATCH /items/:collection/:id

Authentication: Required (Bearer token)

URL Parameters

Parameter	Type	Description
collection	string	Name of the collection
id	string/number	ID of the item to update

Request Body

Send only the fields you want to update:

{
  "title": "Updated Title",
  "content": "Updated Content",
  "published": true
}

Response

Success (200 OK):

{
  "data": {
    "id": 1
  }
}

Error (403 Forbidden):

When user doesn't have update permission:

{
  "error": {
    "message": "You don't have permission to update items in this collection"
  }
}

Example

curl -X PATCH "http://localhost:3000/items/posts/1" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "title": "Updated Post Title"
  }'

Delete Item

Delete an item by ID.

Endpoint: DELETE /items/:collection/:id

Authentication: Required (Bearer token)

URL Parameters

Parameter	Type	Description
collection	string	Name of the collection
id	string/number	ID of the item to delete

Response

Success (200 OK):

{
  "message": "Item deleted successfully"
}

Error (403 Forbidden):

When user doesn't have delete permission:

{
  "error": {
    "message": "You don't have permission to delete items from this collection"
  }
}

Example

curl -X DELETE "http://localhost:3000/items/posts/1" \
  -H "Authorization: Bearer YOUR_TOKEN"

Soft Deletes (Paranoid Mode)

If your schema has paranoid: true, items are soft-deleted (marked as deleted but not removed from database). They won't appear in normal queries but can be recovered.

Bulk Operations

Bulk operations allow you to create, update, or delete multiple items in a single request. All bulk operations are transactional - either all operations succeed or all are rolled back. This ensures data consistency and prevents partial updates.

Key Features

Atomic Transactions: All operations succeed or fail together
Deferred After Hooks: Hooks that may have side effects (emails, API calls) only execute after successful commit
Rollback Safety: If any operation fails, all previous operations in the batch are rolled back
No Orphaned Side Effects: Emails or third-party notifications are never sent for rolled-back operations

Bulk Create

Create multiple items in a single transaction.

Endpoint: POST /items/:collection/bulk

Authentication: Required (Bearer token)

Request Body: Array of items to create

[
  {
    "name": "Item 1",
    "email": "item1@example.com",
    "status": "active"
  },
  {
    "name": "Item 2",
    "email": "item2@example.com",
    "status": "pending"
  },
  {
    "name": "Item 3",
    "email": "item3@example.com",
    "status": "active"
  }
]

Success Response (201 Created):

{
  "data": [1, 2, 3]
}

The response contains an array of created item IDs.

Error Response (400 Bad Request):

{
  "error": {
    "message": "Request body must be an array"
  }
}

Example:

curl -X POST "http://localhost:3000/items/products/bulk" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '[
    {"name": "Product A", "price": 29.99, "category": "electronics"},
    {"name": "Product B", "price": 49.99, "category": "electronics"},
    {"name": "Product C", "price": 19.99, "category": "accessories"}
  ]'

Transaction Behavior:

If any item fails validation (e.g., missing required field), NO items are created:

# This request will fail and create NO items
curl -X POST "http://localhost:3000/items/products/bulk" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '[
    {"name": "Valid Item", "price": 29.99},
    {"name": null, "price": 49.99},
    {"name": "Another Valid", "price": 19.99}
  ]'

Bulk Update

Update multiple items in a single transaction.

Endpoint: PATCH /items/:collection/bulk

Authentication: Required (Bearer token)

Request Body: Array of objects with id and fields to update

[
  {
    "id": 1,
    "status": "completed",
    "priority": 10
  },
  {
    "id": 2,
    "status": "pending",
    "priority": 20
  },
  {
    "id": 3,
    "status": "archived",
    "priority": 30
  }
]

Success Response (200 OK):

{
  "data": [1, 2, 3]
}

The response contains an array of updated item IDs.

Error Response (400 Bad Request):

{
  "error": {
    "message": "Request body must be an array"
  }
}

Example:

curl -X PATCH "http://localhost:3000/items/orders/bulk" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '[
    {"id": 101, "status": "shipped", "trackingNumber": "ABC123"},
    {"id": 102, "status": "shipped", "trackingNumber": "DEF456"},
    {"id": 103, "status": "cancelled"}
  ]'

Transaction Behavior:

If any update fails (e.g., item not found), NO items are updated:

# This request will fail and update NO items
curl -X PATCH "http://localhost:3000/items/orders/bulk" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '[
    {"id": 101, "status": "updated"},
    {"id": 99999, "status": "will_fail"},
    {"id": 103, "status": "updated"}
  ]'

Note: Items without an id field are skipped (not an error):

[
  {"id": 1, "status": "updated"},
  {"status": "no_id_skipped"},
  {"id": 3, "status": "updated"}
]

Bulk Delete

Delete multiple items in a single transaction.

Endpoint: DELETE /items/:collection/bulk

Authentication: Required (Bearer token)

Request Body: Array of IDs to delete

[1, 2, 3, 4, 5]

Success Response (204 No Content):

No response body on success.

Error Response (400 Bad Request):

{
  "error": {
    "message": "Request body must be an array of IDs"
  }
}

Example:

curl -X DELETE "http://localhost:3000/items/notifications/bulk" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '[1, 2, 3, 4, 5]'

Transaction Behavior:

If any delete fails (e.g., item not found), NO items are deleted:

# This request will fail and delete NO items
curl -X DELETE "http://localhost:3000/items/notifications/bulk" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '[1, 99999, 3]'

Bulk Operations and Hooks

Bulk operations integrate with the hooks system, but with an important difference:

Before Hooks: Execute for each item DURING the transaction (can modify data, can cause rollback)

After Hooks: Execute for all items AFTER the transaction commits successfully

This design ensures that:

Side effects (emails, webhooks, third-party API calls) only happen for persisted data
If the transaction rolls back, no after-hooks execute
Your system remains consistent even during failures

Example Flow:

1. Start Transaction
2. For each item:
   - Execute before-create hook
   - Insert item
3. Commit Transaction
4. For each created item:
   - Execute after-create hook (send email, etc.)

If step 2 fails for any item, the transaction rolls back and step 4 never executes.

Data Import

Import data from CSV or JSON files into any collection. Import operations are transactional - either all records are imported or none are (rollback on any error).

Key Features

Authentication Required: Only authenticated users can import data
Transactional: All-or-nothing import - if any row fails, the entire import is rolled back
Multi-tenant Support: Tenant-specific users automatically import to their tenant
Type Conversion: Automatic conversion of string values to appropriate field types
Nested Data: Support for JSON objects and arrays within fields
Relational Data: Support for foreign key references

Import CSV

Import data from a CSV file.

Endpoint: POST /items/:collection/import-csv

Authentication: Required (Bearer token)

Content-Type: multipart/form-data

Form Fields:

Field	Type	Required	Description
csvFile	file	Yes	The CSV file to import (max 50MB)
tenant	string	Conditional	Required for administrators importing into tenant-enabled collections in multi-tenant mode

CSV Format Requirements:

First row must contain column headers matching field names
Values are automatically converted to appropriate types
JSON objects/arrays should be properly formatted JSON strings
Empty lines are skipped

Example CSV File:

name,email,age,metadata,tags
John Doe,john@example.com,30,"{""department"":""IT""}","[""admin"",""user""]"
Jane Smith,jane@example.com,25,"{""department"":""HR""}","[""user""]"

Success Response (200 OK):

{
  "success": true,
  "message": "Successfully imported 2 items",
  "results": {
    "imported": 2,
    "failed": 0,
    "errors": []
  }
}

Error Response (400 Bad Request):

{
  "error": {
    "message": "Import failed. 1 rows had errors. Transaction rolled back.",
    "details": {
      "results": {
        "imported": 0,
        "failed": 1,
        "errors": [
          {
            "row": 2,
            "data": { "name": null, "email": "test@example.com" },
            "error": "name cannot be null"
          }
        ]
      }
    }
  }
}

Error Response (401 Unauthorized):

{
  "error": {
    "message": "Authentication required for import operations"
  }
}

Example:

curl -X POST "http://localhost:3000/items/users/import-csv" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -F "csvFile=@/path/to/users.csv"

Multi-tenant Example (Administrator):

curl -X POST "http://localhost:3000/items/products/import-csv" \
  -H "Authorization: Bearer ADMIN_TOKEN" \
  -F "csvFile=@/path/to/products.csv" \
  -F "tenant=tenant-uuid-here"

Import JSON

Import data from a JSON file.

Endpoint: POST /items/:collection/import-json

Authentication: Required (Bearer token)

Content-Type: multipart/form-data

Form Fields:

Field	Type	Required	Description
jsonFile	file	Yes	The JSON file to import (max 50MB)
tenant	string	Conditional	Required for administrators importing into tenant-enabled collections in multi-tenant mode

JSON Format Requirements:

Must be a valid JSON array of objects
Each object represents one item to create
Field names should match collection field names
Nested objects and arrays are supported

Example JSON File:

[
  {
    "name": "John Doe",
    "email": "john@example.com",
    "age": 30,
    "metadata": {
      "department": "IT",
      "skills": ["JavaScript", "Python"]
    },
    "tags": ["admin", "user"]
  },
  {
    "name": "Jane Smith",
    "email": "jane@example.com",
    "age": 25,
    "metadata": {
      "department": "HR"
    },
    "tags": ["user"]
  }
]

Success Response (200 OK):

{
  "success": true,
  "message": "Successfully imported 2 items",
  "results": {
    "imported": 2,
    "failed": 0,
    "errors": []
  }
}

Error Response (400 Bad Request):

{
  "error": {
    "message": "Import failed. 1 rows had errors. Transaction rolled back.",
    "details": {
      "results": {
        "imported": 0,
        "failed": 1,
        "errors": [
          {
            "row": 1,
            "data": { "name": null, "email": "test@example.com" },
            "error": "name cannot be null"
          }
        ]
      }
    }
  }
}

Error Response (401 Unauthorized):

{
  "error": {
    "message": "Authentication required for import operations"
  }
}

Example:

curl -X POST "http://localhost:3000/items/users/import-json" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -F "jsonFile=@/path/to/users.json"

Multi-tenant Example (Administrator):

curl -X POST "http://localhost:3000/items/products/import-json" \
  -H "Authorization: Bearer ADMIN_TOKEN" \
  -F "jsonFile=@/path/to/products.json" \
  -F "tenant=tenant-uuid-here"

Import with Relational Data

Both CSV and JSON imports support foreign key references to existing records.

Example: Import posts with existing author and category references

[
  {
    "title": "My First Post",
    "content": "Hello World",
    "authorId": "existing-user-uuid",
    "categoryId": 1
  }
]

Note: The referenced records must already exist. Nested creation (automatically creating related records) is planned for a future enhancement.

Multi-tenant Import Rules

In multi-tenant mode, import behavior depends on the user's role type and the collection:

User Type	Collection Type	Behavior
Tenant-specific user	Any	Uses tenant from user's session (automatic)
Administrator	Without tenant_Id field	No tenant handling needed
Administrator	With tenant_Id field	Must provide `tenant` in request body

Tenant-specific users cannot override their tenant - data is always imported to their assigned tenant.

Administrators must explicitly specify which tenant to import data into when working with tenant-enabled collections.

Permissions

All Items API operations require appropriate permissions. Permissions are checked at:

Collection level - Does user have access to this collection?
Action level - Can user perform this action (read/create/update/delete)?
Field level - Can user access/modify these specific fields?

Permission Errors

403 Forbidden:

{
  "error": {
    "message": "You don't have permission to [action] items in this collection"
  }
}

Setting Permissions

Administrators can grant permissions:

curl -X POST "http://localhost:3000/permissions" \
  -H "Authorization: Bearer ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "role_Id": "user-role-id",
    "collection": "posts",
    "action": "read",
    "fields": ["*"]
  }'

Actions:

read - View items
create - Create new items
update - Modify existing items
delete - Remove items

Field Access:

["*"] - All fields
["title", "content"] - Specific fields only

Complete Example Workflow

1. Create a schema

curl -X POST "http://localhost:3000/schemas" \
  -H "Authorization: Bearer ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "collectionName": "posts",
    "schema": {
      "name": "Post",
      "fields": {
        "id": { "type": "Integer", "primaryKey": true, "defaultValue": { "type": "AUTOINCREMENT" } },
        "title": { "type": "String", "allowNull": false },
        "content": { "type": "String", "allowNull": false },
        "published": { "type": "Boolean", "default": false },
        "authorId": { "type": "String", "allowNull": false }
      },
      "paranoid": true
    }
  }'

2. Grant permissions

curl -X POST "http://localhost:3000/permissions" \
  -H "Authorization: Bearer ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "role_Id": "user-role-id",
    "collection": "posts",
    "action": "read",
    "fields": ["*"]
  }'

3. Create an item

curl -X POST "http://localhost:3000/items/posts" \
  -H "Authorization: Bearer USER_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "title": "My First Post",
    "content": "Hello World",
    "authorId": "user-123",
    "published": true
  }'

4. Query items

curl -G "http://localhost:3000/items/posts" \
  --data-urlencode 'filter={"published":{"eq":true}}' \
  -H "Authorization: Bearer USER_TOKEN"

Core References

Schema Routes - Create and manage collections
Schema Reference Guide - Field types and schema definitions
Database Schema Guide - Relationships and schema patterns

Query System

Item Query Reference Guide - Query parameters and operators
Complete Filter Reference - All filter operators (array, range, geospatial)
Advanced Query Guide - Complex filtering, aggregation, and joins

Access Control

Permission Routes - Configure field-level access control
Authentication Routes - User authentication and sessions
Multi-tenant Guide - Tenant-scoped data isolation

Extensions & Automation

Hooks System - Customize item operations with hooks
Hooks and Endpoints Guide - Hook patterns and service access
Reports Routes - Analytics and aggregation on items

Integration

Integration Guide - Client-side CRUD implementation
Error Handling Guide - Handle item operation errors
API Routes Reference - Complete endpoint listing

On this page