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

Index Management

Create Index

Add an index to a collection for better query performance.

Endpoint: POST /schemas/:collection/indexes

Authentication: Required (Administrator only)

URL Parameters

Parameter	Type	Description
collection	string	Name of the collection

Request Body

{
  "name": "idx_users_email",
  "fields": ["email"],
  "unique": true,
  "type": "btree"
}

Field	Type	Required	Description
name	string	No	Index name (auto-generated if not provided)
fields	string[]	Yes	Fields to include in the index
unique	boolean	No	Whether the index enforces uniqueness
type	string	No	Index type: `btree`, `hash`, `gin`, `gist`

Response

Success (201 Created):

{
  "message": "Index added successfully"
}

Example

curl -X POST "http://localhost:3000/schemas/users/indexes" \
  -H "Authorization: Bearer ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "idx_users_email",
    "fields": ["email"],
    "unique": true
  }'

Delete Index

Remove an index from a collection.

Endpoint: DELETE /schemas/:collection/indexes/:indexName

Authentication: Required (Administrator only)

URL Parameters

Parameter	Type	Description
collection	string	Name of the collection
indexName	string	Name of the index

Response

Success (200 OK):

{
  "message": "Index removed successfully"
}

Example

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

Relationship Management

Create Relationship

Create a relationship between two collections.

Endpoint: POST /schemas/:sourceCollection/relationships

Authentication: Required (Administrator only)

URL Parameters

Parameter	Type	Description
sourceCollection	string	Name of the source collection

Request Body

{
  "name": "author",
  "type": "M2O",
  "target": "users",
  "alias": "posts",
  "onDelete": "CASCADE"
}

Field	Type	Required	Description
name	string	Yes	Name of the relationship field
type	string	Yes	Relationship type: `M2O`, `O2M`, `O2O`, `M2M`, `M2A`
target	string	Yes*	Target collection name (*not required for M2A)
alias	string	No	Alias for the reverse relationship on target
onDelete	string	No	Delete behavior: `CASCADE`, `RESTRICT`, `SET NULL`
onUpdate	string	No	Update behavior: `CASCADE`, `RESTRICT`, `SET NULL`
tables	array	No	Target tables for M2A (polymorphic) relationships
through	string	No	Custom junction table name for M2M/M2A relationships

Relationship Types

Type	Description
`M2O`	Many-to-One: Creates foreign key with auto-index
`O2M`	One-to-Many: Virtual reverse of M2O
`O2O`	One-to-One: Creates foreign key with auto-index
`M2M`	Many-to-Many: Creates junction table with auto-indexed FKs
`M2A`	Many-to-Any: Polymorphic junction table

Response

Success (201 Created):

{
  "message": "Relationship created successfully"
}

Example

curl -X POST "http://localhost:3000/schemas/posts/relationships" \
  -H "Authorization: Bearer ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "author",
    "type": "M2O",
    "target": "users",
    "alias": "posts",
    "onDelete": "CASCADE"
  }'

Update Relationship

Update an existing relationship.

Endpoint: PATCH /schemas/:sourceCollection/relationships/:fieldName

Authentication: Required (Administrator only)

URL Parameters

Parameter	Type	Description
sourceCollection	string	Name of the source collection
fieldName	string	Name of the relationship field

Request Body

Update properties of the relationship:

{
  "onDelete": "CASCADE",
  "tables": ["posts", "products"]
}

Note: For M2A relationships, you can update the tables array to add or remove target tables.

Response

Success (200 OK):

{
  "message": "Relationship updated successfully"
}

Example

curl -X PATCH "http://localhost:3000/schemas/posts/relationships/author" \
  -H "Authorization: Bearer ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "onDelete": "CASCADE"
  }'

Delete Relationship

Remove a relationship from a collection.

Endpoint: DELETE /schemas/:sourceCollection/relationships/:fieldName

Authentication: Required (Administrator only)

URL Parameters

Parameter	Type	Description
sourceCollection	string	Name of the source collection
fieldName	string	Name of the relationship field

Response

Success (200 OK):

{
  "message": "Relationship deleted successfully"
}

Example

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

Schema Export & Import

Export All Schemas

Export all schemas as a JSON file for backup or migration. The response is a downloadable file.

Endpoint: GET /schemas-export

Authentication: Required (Administrator only)

Response

Returns a JSON file download with the following structure:

{
  "version": "1.0",
  "timestamp": "2025-02-07T10:30:00.000Z",
  "schemas": [
    {
      "collectionName": "posts",
      "schema": {
        "name": "Post",
        "fields": { ... },
        "timestamps": true
      },
      "createdAt": "2025-01-01T00:00:00.000Z",
      "updatedAt": "2025-02-07T10:00:00.000Z"
    }
  ]
}

Example

curl "http://localhost:3000/schemas-export" \
  -H "Authorization: Bearer ADMIN_TOKEN" \
  -o schemas-backup.json

Preview Schema Import

Preview what changes will be made before importing schemas.

Endpoint: POST /schemas-preview-import

Authentication: Required (Administrator only)

Request

Upload a JSON file using multipart form data:

curl -X POST "http://localhost:3000/schemas-preview-import" \
  -H "Authorization: Bearer ADMIN_TOKEN" \
  -F "schema=@schemas-backup.json"

Response

Success (200 OK):

{
  "importVersion": "1.0",
  "importTimestamp": "2025-02-07T10:30:00.000Z",
  "changes": {
    "new": [{ "collectionName": "posts", "details": "New schema will be created" }],
    "modified": [{ "collectionName": "users", "differences": { "fields": { "added": ["phone"] } } }],
    "deleted": ["old_collection"],
    "unchanged": ["categories"]
  }
}

Import Schemas

Import schemas from a JSON file. Uses the same format as the export.

Endpoint: POST /schemas-import

Authentication: Required (Administrator only)

Request

Upload a JSON file using multipart form data:

curl -X POST "http://localhost:3000/schemas-import" \
  -H "Authorization: Bearer ADMIN_TOKEN" \
  -F "schema=@schemas-backup.json"

The JSON file should have the same structure as the export:

{
  "version": "1.0",
  "schemas": [
    {
      "collectionName": "posts",
      "schema": {
        "name": "Post",
        "fields": {
          "id": { "type": "UUID", "primaryKey": true },
          "title": { "type": "String", "allowNull": false }
        }
      }
    }
  ]
}

Response

Success (200 OK):

{
  "message": "Schema import completed",
  "changes": {
    "created": ["posts"],
    "updated": ["users"],
    "unchanged": ["categories"],
    "deleted": [],
    "errors": []
  }
}

Example

# Import schemas from backup file
curl -X POST "http://localhost:3000/schemas-import" \
  -H "Authorization: Bearer ADMIN_TOKEN" \
  -F "schema=@schemas-backup.json"

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