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

Get Single Item

Retrieve a specific item from a collection by its ID.

Endpoint: GET /items/:collection/:id

Authentication: Required (Bearer token)

URL Parameters

Parameter	Type	Description
`collection`	string	The name of the collection
`id`	string	The unique item identifier

Query Parameters

Parameter	Type	Description	Example
`fields`	string	Select specific fields/relations	`?fields=["*","author.name"]`

Response

Success (200 OK):

{
  "data": {
    "id": "post-123",
    "title": "My Blog Post",
    "content": "Post content here...",
    "published": true,
    "createdAt": "2024-01-15T10:30:00.000Z",
    "author": {
      "name": "John Doe"
    }
  }
}

Error (404 Not Found):

{
  "error": {
    "message": "Item not found"
  }
}

Example

# Get a single post with author relation
curl "http://localhost:3000/items/posts/post-123?fields=%5B%22*%22%2C%22author.name%22%5D" \
  -H "Authorization: Bearer YOUR_TOKEN"

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.

Force Delete (Hard Delete)

To permanently delete an item on a paranoid schema, use the force query parameter:

# Force permanent deletion
curl -X DELETE "http://localhost:3000/items/posts/1?force=true" \
  -H "Authorization: Bearer YOUR_TOKEN"

Restore Soft-Deleted Items

To restore a soft-deleted item, use the restore endpoint:

# Restore a soft-deleted item
curl -X POST "http://localhost:3000/items/posts/1/restore" \
  -H "Authorization: Bearer YOUR_TOKEN"

Query Soft-Deleted Items

By default, soft-deleted items are excluded from queries. Use paranoid=false to include them:

# Include soft-deleted items in results
curl "http://localhost:3000/items/posts?paranoid=false" \
  -H "Authorization: Bearer YOUR_TOKEN"

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