@warriorteam/dynamic-table

NestJS SDK for Dynamic Table System with PostgreSQL + JSONB - Build Airtable/Notion-like applications easily.

Features

• 🚀 Dynamic Schema - Create tables and fields on-the-fly without migrations
• 📊 26+ Field Types - Text, Number, Date, Select, Relation, Formula, and more
• 🔍 Powerful Query Engine - Filter, sort, search with JSONB optimization
• 📐 Formula Support - Computed fields with SQL expressions
• 🔗 Relations - Link records between tables with Lookup and Rollup
• 📝 Audit History - Track all changes automatically
• ✅ Validation - Built-in validation for all field types
• 🎯 TypeScript - Full type safety with interfaces and enums

Requirements

• Node.js >= 18
• PostgreSQL >= 12
• NestJS >= 10
• TypeORM >= 0.3

Installation

npm install @warriorteam/dynamic-table

Database Setup

Run the initialization SQL script to create required tables and indexes:

-- Enable Extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- See src/sql/init.sql for full schema

Or use TypeORM synchronize (development only):

TypeOrmModule.forRoot({
  // ... config
  synchronize: true, // WARNING: Don't use in production
  entities: [DYNAMIC_TABLE_ENTITIES],
})

Quick Start

1. Import Module

// app.module.ts
import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { DynamicTableModule, DYNAMIC_TABLE_ENTITIES } from '@warriorteam/dynamic-table';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'postgres',
      host: 'localhost',
      port: 5432,
      database: 'mydb',
      username: 'user',
      password: 'pass',
      entities: [...DYNAMIC_TABLE_ENTITIES],
      synchronize: false,
    }),
    DynamicTableModule.forRoot(),
  ],
})
export class AppModule {}

2. Use Services

// product.service.ts
import { Injectable } from '@nestjs/common';
import {
  WorkspaceService,
  TableService,
  FieldService,
  RecordService,
  FieldType,
  FilterParams,
} from '@warriorteam/dynamic-table';

@Injectable()
export class ProductService {
  constructor(
    private workspaceService: WorkspaceService,
    private tableService: TableService,
    private fieldService: FieldService,
    private recordService: RecordService,
  ) {}

  async setup() {
    // Create workspace
    const workspace = await this.workspaceService.create({
      name: 'My Store',
      slug: 'my-store',
    });

    // Create table
    const table = await this.tableService.create({
      workspaceId: workspace.id,
      name: 'Products',
      slug: 'products',
    });

    // Create fields
    await this.fieldService.create({
      tableId: table.id,
      name: 'Product Name',
      keyName: 'name',
      type: FieldType.TEXT,
      isPrimary: true,
      isRequired: true,
    });

    await this.fieldService.create({
      tableId: table.id,
      name: 'Price',
      keyName: 'price',
      type: FieldType.CURRENCY,
      config: { currencyCode: 'VND', currencySymbol: '₫' },
    });

    await this.fieldService.create({
      tableId: table.id,
      name: 'Quantity',
      keyName: 'qty',
      type: FieldType.NUMBER,
    });

    // Create formula field
    await this.fieldService.create({
      tableId: table.id,
      name: 'Total',
      keyName: 'total',
      type: FieldType.FORMULA,
      config: {
        formulaExpression: '{price} * {qty}',
        outputType: 'number',
      },
    });

    return table;
  }

  async createProduct(tableId: string, data: any) {
    return this.recordService.create({
      tableId,
      data: {
        name: data.name,
        price: data.price,
        qty: data.qty,
      },
    });
  }

  async getProducts(tableId: string) {
    const filters: FilterParams[] = [
      { column: 'price', operator: 'gt', value: 0 },
    ];

    return this.recordService.findAll(tableId, {
      filters,
      sort: { fieldKey: 'name', order: 'ASC' },
      pagination: { page: 1, limit: 20 },
    });
  }

  async searchProducts(tableId: string, query: string) {
    return this.recordService.findAll(tableId, {
      search: { query, fields: ['name'] },
    });
  }
}

Field Types

| Type | Description | Config Options | |------|-------------|----------------| | TEXT | Single line text | - | | LONG_TEXT | Multi-line text | enableRichText, maxLength | | EMAIL | Email with validation | - | | PHONE | Phone number | defaultCountryCode | | URL | URL with validation | urlType | | NUMBER | Numeric value | precision | | CURRENCY | Money value | currencyCode, currencySymbol, precision | | PERCENT | Percentage | percentFormat, precision | | RATING | Star rating | maxRating, ratingIcon | | AUTONUMBER | Auto-increment | prefix, startNumber, digitCount | | SELECT | Single select | options | | MULTI_SELECT | Multiple select | options | | BOOLEAN | Checkbox | - | | DATE | Date only | dateFormat | | DATETIME | Date and time | dateFormat, timeFormat, timezone | | DURATION | Time duration | durationFormat | | RELATION | Link to another table | targetTableId, allowMultiple | | LOOKUP | Value from linked record | relationFieldId, lookupFieldId | | ROLLUP | Aggregate from linked records | rollupRelationFieldId, rollupFieldId, rollupFunction | | FORMULA | Calculated field | formulaExpression, outputType | | CREATED_TIME | Auto: creation time | - | | MODIFIED_TIME | Auto: last modified | - | | CREATED_BY | Auto: creator | - | | MODIFIED_BY | Auto: last modifier | - | | ATTACHMENT | File attachments | allowedFileTypes, maxFileSize, maxFiles | | USER | User reference | allowMultipleUsers, notifyOnAssign |

Filter Operators

| Operator | Description | Example | |----------|-------------|---------| | eq | Equal | { column: 'status', operator: 'eq', value: 'active' } | | neq | Not equal | { column: 'status', operator: 'neq', value: 'deleted' } | | gt | Greater than | { column: 'price', operator: 'gt', value: 100 } | | gte | Greater than or equal | { column: 'qty', operator: 'gte', value: 1 } | | lt | Less than | { column: 'price', operator: 'lt', value: 1000 } | | lte | Less than or equal | { column: 'qty', operator: 'lte', value: 100 } | | contains | Contains substring | { column: 'name', operator: 'contains', value: 'phone' } | | not_contains | Does not contain | { column: 'name', operator: 'not_contains', value: 'test' } | | starts_with | Starts with | { column: 'code', operator: 'starts_with', value: 'PRD' } | | ends_with | Ends with | { column: 'email', operator: 'ends_with', value: '@gmail.com' } | | in | In array | { column: 'status', operator: 'in', value: ['active', 'pending'] } | | not_in | Not in array | { column: 'status', operator: 'not_in', value: ['deleted'] } | | is_empty | Is null or empty | { column: 'notes', operator: 'is_empty', value: null } | | is_not_empty | Is not null/empty | { column: 'name', operator: 'is_not_empty', value: null } | | is_before | Date before | { column: 'dueDate', operator: 'is_before', value: '2024-01-01' } | | is_after | Date after | { column: 'createdAt', operator: 'is_after', value: '2024-01-01' } | | is_within | Within time range | { column: 'createdAt', operator: 'is_within', value: { days: 7 } } |

Formula Examples

// Basic arithmetic
'{price} * {qty}'

// With multiple fields
'{price} * {qty} * (1 - {discount} / 100)'

// The formula is converted to SQL:
// COALESCE((record.data->>'price')::numeric, 0) * COALESCE((record.data->>'qty')::numeric, 0)

API Reference

WorkspaceService

• create(dto) - Create workspace
• findAll() - List all workspaces
• findById(id) - Get workspace by ID
• findBySlug(slug) - Get workspace by slug
• update(id, dto) - Update workspace
• delete(id) - Delete workspace

TableService

• create(dto) - Create table
• findByWorkspace(workspaceId) - List tables in workspace
• findById(id) - Get table by ID
• findBySlug(workspaceId, slug) - Get table by slug
• update(id, dto) - Update table
• delete(id) - Delete table

FieldService

• create(dto) - Create field
• findByTable(tableId) - List fields in table
• findById(id) - Get field by ID
• update(id, dto) - Update field
• delete(id) - Delete field
• reorder(tableId, fieldIds) - Reorder fields

RecordService

• create(dto) - Create record
• batchCreate(dto) - Create multiple records
• findAll(tableId, options) - Query records with filters
• findById(id) - Get record by ID
• update(id, dto) - Update record
• patch(id, data) - Partial update
• delete(id) - Delete record
• batchDelete(ids) - Delete multiple records
• getHistory(recordId) - Get change history

ValidationService

• validate(data, fields) - Validate record data
• validateField(value, field) - Validate single field

FormulaService

• parseToSQL(expression) - Convert formula to SQL
• validate(expression, fields) - Validate formula
• extractFieldReferences(expression) - Get referenced fields

License

MIT © WarriorTeam