NestJS SDK for Dynamic Table System with PostgreSQL + JSONB - Build Airtable/Notion-like applications easily
npm install @warriorteam/dynamic-tablebash
npm install @warriorteam/dynamic-table
`
Database Setup
Run the initialization SQL script to create required tables and indexes:
`sql
-- 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):
`typescript
TypeOrmModule.forRoot({
// ... config
synchronize: true, // WARNING: Don't use in production
entities: [DYNAMIC_TABLE_ENTITIES],
})
`
Quick Start
$3
`typescript
// 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 {}
`
$3
`typescript
// 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
`typescript
// 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
$3
- 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
$3
- 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
$3
- 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
$3
- 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
$3
- validate(data, fields) - Validate record data
- validateField(value, field) - Validate single field
$3
- parseToSQL(expression) - Convert formula to SQL
- validate(expression, fields) - Validate formula
- extractFieldReferences(expression)` - Get referenced fields