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 access is configurable via SCHEMAS_PUBLIC env variable
Schemas define table structure with fields, types, and constraints
Database tables are automatically created/modified based on schema definitions

Access Control

Schema read access is controlled by the SCHEMAS_PUBLIC environment variable:

SCHEMAS_PUBLIC	Behavior
`true` (default)	All authenticated users can read schemas
`false`	Only users with `read` permission on `baasix_SchemaDefinition` can access

Configuration

# .env file on server

# Allow all authenticated users (default, good for development)
SCHEMAS_PUBLIC=true

# Restrict to authorized users only (recommended for production)
SCHEMAS_PUBLIC=false

Why Restrict Schema Access?

In production environments, you may want to restrict schema access because:

Security: Schema details reveal your data model structure
Privacy: Field names and types can expose business logic
Compliance: Some regulations require limiting metadata exposure

Granting Schema Access

When SCHEMAS_PUBLIC=false, grant read access to specific roles:

# Using Baasix API
curl -X POST "http://localhost:3000/permissions" \
  -H "Authorization: Bearer ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "role_Id": "YOUR_ROLE_ID",
    "collection": "baasix_SchemaDefinition", 
    "action": "read",
    "fields": ["*"]
  }'

Get All Schemas

Retrieve a list of all schemas in the system.

Endpoint: GET /schemas

Authentication: Required (access depends on SCHEMAS_PUBLIC setting)

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 (access depends on SCHEMAS_PUBLIC setting)

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)
validate	object	No	Validation rules (see validation section)

Supported Field Types

SUID - Short UUID (compact unique identifier)
String - Text field (with optional length)
Integer - Whole number
BigInt - Large whole number
Boolean - True/false value
DateTime - Date and time
Date - Date only
Time - Time only
Decimal - Precise decimal number (with precision/scale)
Double - Double precision floating point
Float - Single precision floating point
Real - Real number
Text - Long text field (unlimited)
JSON / JSONB - JSON data (JSONB for indexed queries)
UUID - Universal unique identifier
Enum - Enumerated values
Array - Array of values
Geometry / Geography - PostGIS spatial types

Default Value Types

Type	Description	Example
`UUIDV4`	Random UUID v4	`{"type": "UUIDV4"}`
`SUID`	Short unique ID	`{"type": "SUID"}`
`NOW`	Current timestamp	`{"type": "NOW"}`
`AUTOINCREMENT`	Auto-increment integer	`{"type": "AUTOINCREMENT"}`
`SQL`	Custom SQL expression	`{"type": "SQL", "value": "..."}`
Static	Any static value	`"active"`, `false`, `0`

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

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

// Generate UUID
{ "type": "UUIDV4" }

// Current timestamp
{ "type": "NOW" }

// Custom SQL expression
{ "type": "SQL", "value": "(SELECT MAX(sort) + 1 FROM items)" }

// Static value
"some value"

Field Validation Rules

Rule	Type	Description
`min`	number	Minimum value for numeric fields
`max`	number	Maximum value for numeric fields
`isInt`	boolean	Validate as integer
`notEmpty`	boolean	String must not be empty
`isEmail`	boolean	Validate email format
`isUrl`	boolean	Validate URL format
`len`	[min, max]	String length range
`is` / `matches`	regex	Pattern matching

{
  "email": {
    "type": "String",
    "validate": { "isEmail": true, "notEmpty": true }
  },
  "age": {
    "type": "Integer",
    "validate": { "min": 0, "max": 120 }
  },
  "username": {
    "type": "String",
    "validate": { "len": [3, 50], "notEmpty": true }
  }
}

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