NocoBase Data Modeling

You are guiding the user to create data models in NocoBase. Follow this exact workflow.

Architecture: SQL + API Hybrid

NocoBase uses a hybrid approach — SQL for bulk column creation (fast), API for metadata management (interface/UI config).

Why not pure API? Creating fields one-by-one via API is slow and has quirks. SQL CREATE TABLE creates all columns in one shot, then syncFields imports them into NocoBase.

Recommended: Fast Path (2 Steps)

For maximum efficiency, use the batch tools:

Step 1: Create ALL tables in one SQL call

nb_execute_sql("CREATE TABLE IF NOT EXISTS nb_crm_customers (...); CREATE TABLE IF NOT EXISTS nb_crm_contacts (...); ...")

Put all tables in a single SQL statement. This is much faster than creating them one by one.

Step 2: Setup each collection with nb_setup_collection

nb_setup_collection("nb_crm_customers", "客户",
    '{"status":{"interface":"select","enum":[{"value":"潜在","label":"潜在","color":"default"},{"value":"已签约","label":"已签约","color":"green"}]},"phone":{"interface":"phone"},"email":{"interface":"email"},"description":{"interface":"textarea"}}',
    '[{"field":"contacts","type":"o2m","target":"nb_crm_contacts","foreign_key":"customer_id"},{"field":"opportunities","type":"o2m","target":"nb_crm_opportunities","foreign_key":"customer_id"}]')

This single call does: register → create system fields → sync → upgrade ALL field interfaces → create ALL relations. One call per table instead of 10+.

IMPORTANT: Process tables in dependency order — parent tables first (those referenced by FK), then child tables.

Manual Path (7 Steps Per Collection)

Use individual tools when you need fine-grained control:

Workflow (7 Steps Per Collection)

Step 1: Design — Analyze Requirements

• Read the user's design docs / requirements
• Identify all entities (tables), their fields, types, and relationships
• Plan the naming convention: nb_{module}_{entity} (e.g. nb_pm_projects)

Step 2: SQL DDL — Create Tables

Use nb_execute_sql to create tables with all columns:

CREATE TABLE IF NOT EXISTS nb_pm_projects (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(50),
    status VARCHAR(50) DEFAULT '草稿',
    priority VARCHAR(20) DEFAULT '中',
    description TEXT,
    start_date DATE,
    budget NUMERIC(12,2),
    sort INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

Rules:

• Always include id BIGSERIAL PRIMARY KEY
• Use VARCHAR for enum-like fields (will upgrade to select later)
• Use NUMERIC(p,s) for money/decimal
• Use DATE for date-only, TIMESTAMPTZ for datetime
• Use TEXT for long content
• Add sort INTEGER DEFAULT 0 if ordering is needed
• DO NOT create created_at, updated_at, created_by_id, updated_by_id columns in SQL — they are created via API in Step 4
• Multiple tables can be created in one SQL call

Step 3: Register Collection

Use nb_register_collection for each table:

nb_register_collection("nb_pm_projects", "Projects")
nb_register_collection("nb_pm_categories", "Categories", tree="adjacency-list")

• tree="adjacency-list" for parent-child hierarchies
• The table must already exist in DB

Step 4: Sync Fields

Use nb_sync_fields to import DB columns + create system fields:

nb_sync_fields(collection="nb_pm_projects")

This does two things:

1. Creates system fields via API (createdAt, updatedAt, createdBy, updatedBy)
2. Runs global syncFields to import all DB columns

CRITICAL: System fields (createdBy/updatedBy) MUST be created via API because they auto-generate FK columns. Creating them via SQL breaks the config.

Step 5: Upgrade Field Interfaces

Use nb_upgrade_field to change fields from default 'input' to correct types:

nb_upgrade_field("nb_pm_projects", "status", "select",
    enum='[{"value":"active","label":"Active"},{"value":"done","label":"Done"}]')
nb_upgrade_field("nb_pm_projects", "start_date", "date")
nb_upgrade_field("nb_pm_projects", "budget", "number", precision=2)
nb_upgrade_field("nb_pm_projects", "description", "textarea")

Common interfaces:

Step 6: Create Relations

Use nb_create_relation for associations:

nb_create_relation("nb_pm_tasks", "project", "m2o", "nb_pm_projects", "project_id", label="name")
nb_create_relation("nb_pm_projects", "tasks", "o2m", "nb_pm_tasks", "project_id")

Relation types:

• m2o (belongsTo): Task belongs to a Project. FK column on the source table.
• o2m (hasMany): Project has many Tasks. FK column on the target table.
• m2m (belongsToMany): Needs through, other_key params.
• o2o (hasOne): One-to-one.

Rule: The FK column (e.g. project_id) must exist in the DB table. Create it in Step 2.

Step 7: Seed Data (Optional)

Use nb_execute_sql to insert initial data:

INSERT INTO nb_pm_categories (name, code, sort) VALUES
('Development', 'DEV', 1),
('Design', 'DSN', 2),
('Marketing', 'MKT', 3);

Seed data tips:

• Insert parent records before children (FK constraints)
• Use explicit IDs when you need to reference them as FK values later
• For enum fields, values must exactly match the enum options defined in Step 5

Verification

After completing all steps:

1. nb_list_collections(filter="nb_pm_") — verify all tables registered
2. nb_list_fields("nb_pm_projects") — verify fields have correct interfaces
3. Check NocoBase UI to confirm tables appear in admin panel

Common Patterns

Enum field with colors

nb_upgrade_field("orders", "status", "select",
    enum='[{"value":"pending","label":"Pending","color":"gold"},{"value":"completed","label":"Completed","color":"green"},{"value":"cancelled","label":"Cancelled","color":"red"}]')

Enum JSON format: Each option is {"value":"x","label":"x","color":"colorName"}. Color names: red, green, blue, orange, gold, purple, cyan, grey, default. Value and label are usually the same for Chinese apps.

Multiple-select enum

nb_upgrade_field("products", "tags", "multipleSelect",
    enum='[{"value":"hot","label":"Hot","color":"red"},{"value":"new","label":"New","color":"blue"}]')

Field upgrade — only changes metadata

nb_upgrade_field only updates the field's NocoBase metadata (interface, uiSchema). It does NOT alter the database column. The DB column stays VARCHAR — NocoBase handles enum display in the UI layer.

Tree collection (categories)

CREATE TABLE nb_pm_categories (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id BIGINT REFERENCES nb_pm_categories(id),
    sort INTEGER DEFAULT 0
);

nb_register_collection("nb_pm_categories", "Categories", tree="adjacency-list")

Multiple tables in batch

Create all SQL tables first, register all, then sync once, then upgrade all. This is more efficient than processing one table at a time.

Re-upgrading fields (idempotent)

Running nb_upgrade_field on an already-upgraded field is safe — it will detect the current interface matches and skip. Use this when you need to fix enum options or add colors to existing select fields.