Schema API

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

Overview

The Schema API allows you to dynamically create, read, update, and delete database schemas (tables) at runtime. This provides a flexible way to manage your data models without database migrations.

Key Points:

Schema creation/modification/deletion requires administrator role
Reading schemas is available to all authenticated users
Schemas define table structure with fields, types, and constraints
Database tables are automatically created/modified based on schema definitions

Get All Schemas

Retrieve a list of all schemas in the system.

Endpoint: GET /schemas

Authentication: Required (any authenticated user)

Response

Success (200 OK):

{
  "data": [
    {
      "collectionName": "TestModel",
      "schema": {
        "name": "TestModel",
        "fields": {
          "id": {
            "type": "SUID",
            "primaryKey": true,
            "defaultValue": { "type": "SUID" }
          },
          "name": { "type": "String" },
          "email": { "type": "String", "unique": true }
        }
      }
    }
  ]
}

Example

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

Get Specific Schema

Retrieve a single schema by collection name.

Endpoint: GET /schemas/:collectionName

Authentication: Required (any authenticated user)

URL Parameters

Parameter	Type	Description
collectionName	string	Name of the collection

Response

Success (200 OK):

{
  "data": {
    "collectionName": "TestModel",
    "schema": {
      "name": "TestModel",
      "fields": {
        "id": {
          "type": "SUID",
          "primaryKey": true,
          "defaultValue": { "type": "SUID" }
        },
        "name": { "type": "String" },
        "email": { "type": "String", "unique": true }
      }
    }
  }
}

Example

curl "http://localhost:3000/schemas/TestModel" \
  -H "Authorization: Bearer YOUR_TOKEN"

Create Schema

Create a new schema (table) in the database.

Endpoint: POST /schemas

Authentication: Required (Administrator only)

Request Body

{
  "collectionName": "TestModel",
  "schema": {
    "name": "TestModel",
    "fields": {
      "id": {
        "type": "SUID",
        "primaryKey": true,
        "defaultValue": { "type": "SUID" }
      },
      "name": { "type": "String" },
      "email": { "type": "String", "unique": true }
    }
  }
}

Field	Type	Required	Description
collectionName	string	Yes	Name of the collection (table name)
schema	object	Yes	Schema definition object
schema.name	string	Yes	Name of the model
schema.fields	object	Yes	Field definitions

Field Definition

Each field in schema.fields can have:

Property	Type	Required	Description
type	string	Yes	Data type (see supported types below)
primaryKey	boolean	No	Is this the primary key?
defaultValue	object/any	No	Default value or default value generator
unique	boolean	No	Must values be unique?
allowNull	boolean	No	Allow null values? (default: true)

Supported Field Types

SUID - Short UUID (compact unique identifier)
String - Text field
Integer - Whole number
Boolean - True/false value
DateTime - Date and time
Double - Decimal number
Float - Floating point number
Text - Long text field
JSON - JSON data
And many more...

Default Value Types

// Auto-increment integer
{ "type": "AUTOINCREMENT" }

// Generate SUID
{ "type": "SUID" }

// Static value
"some value"

Schema Options

Additional schema-level options:

{
  "collectionName": "posts",
  "schema": {
    "name": "Post",
    "fields": { /* ... */ },
    "usertrack": true,    // Track created_by, updated_by
    "paranoid": true      // Soft delete (adds deletedAt field)
  }
}

Response

Success (201 Created):

{
  "message": "Schema created successfully"
}

Error (403 Forbidden):

When non-admin tries to create schema:

{
  "error": {
    "message": "Access denied. Administrators only."
  }
}

Complete Example

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
        },
        "createdAt": {
          "type": "DateTime",
          "default": "now()"
        }
      },
      "usertrack": true,
      "paranoid": true
    }
  }'

Update Schema

Modify an existing schema by adding or changing fields.

Endpoint: PATCH /schemas/:collectionName

Authentication: Required (Administrator only)

URL Parameters

Parameter	Type	Description
collectionName	string	Name of the collection to update

Request Body

Send the complete updated schema definition:

{
  "schema": {
    "name": "TestModel",
    "fields": {
      "id": {
        "type": "SUID",
        "primaryKey": true,
        "defaultValue": { "type": "SUID" }
      },
      "name": { "type": "String" },
      "email": { "type": "String", "unique": true },
      "newField": { "type": "String" }
    }
  }
}

Response

Success (200 OK):

{
  "message": "Schema updated successfully"
}

Error (403 Forbidden):

When non-admin tries to update schema:

{
  "error": {
    "message": "Access denied. Administrators only."
  }
}

Example

curl -X PATCH "http://localhost:3000/schemas/TestModel" \
  -H "Authorization: Bearer ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "schema": {
      "name": "TestModel",
      "fields": {
        "id": {
          "type": "SUID",
          "primaryKey": true,
          "defaultValue": { "type": "SUID" }
        },
        "name": { "type": "String" },
        "email": { "type": "String", "unique": true },
        "phone": { "type": "String" }
      }
    }
  }'

Delete Schema

Delete a schema and its associated database table.

Endpoint: DELETE /schemas/:collectionName

Authentication: Required (Administrator only)

URL Parameters

Parameter	Type	Description
collectionName	string	Name of the collection to delete

Response

Success (200 OK):

{
  "message": "Schema deleted successfully"
}

Error (403 Forbidden):

When non-admin tries to delete schema:

{
  "error": {
    "message": "Access denied. Administrators only."
  }
}

Warning: Deleting a schema will permanently delete the database table and all its data. This action cannot be undone.

Example

curl -X DELETE "http://localhost:3000/schemas/TestModel" \
  -H "Authorization: Bearer ADMIN_TOKEN"

Complete Workflow Example

1. Create a Blog 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
        }
      },
      "usertrack": true,
      "paranoid": true
    }
  }'

2. View the Schema

curl "http://localhost:3000/schemas/posts" \
  -H "Authorization: Bearer ADMIN_TOKEN"

3. Add a New Field

curl -X PATCH "http://localhost:3000/schemas/posts" \
  -H "Authorization: Bearer ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "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
        },
        "tags": {
          "type": "JSON"
        }
      },
      "usertrack": true,
      "paranoid": true
    }
  }'

4. Start Using the Collection

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,
    "tags": ["tech", "tutorial"]
  }'

Important Notes

Administrator Access

Only users with the administrator role can:

Create schemas (POST /schemas)
Update schemas (PATCH /schemas/:collectionName)
Delete schemas (DELETE /schemas/:collectionName)

All authenticated users can:

List schemas (GET /schemas)
View specific schemas (GET /schemas/:collectionName)

Schema Naming

Use singular collection names (e.g., "post" not "posts")
Collection names become table names in the database
Use camelCase or snake_case consistently

Field Constraints

The following constraints are enforced at the database level:

primaryKey - Unique identifier
unique - No duplicate values
allowNull - Whether field can be null
defaultValue - Default value when not provided

Schema System

Schema Reference Guide - Complete field type reference (Range, Array, PostGIS types)
Database Schema Guide - Relationships, patterns, and best practices

Data Operations

Item Routes - CRUD operations for collections
Item Query Reference Guide - Query parameters and filters
Complete Filter Reference - All filter operators

Access Control

Authentication Routes - User authentication
Permission Routes - Field-level access control

Automation

Hooks System - Respond to schema changes with hooks
Hooks and Endpoints Guide - Hook patterns and examples

Integration

Integration Guide - Client-side schema operations
API Routes Reference - Complete endpoint listing

On this page