gl-life-data

Flex Field System - Dynamic schema data access layer for SQL databases.

Build applications with runtime-configurable schemas. No migrations needed when adding fields.

Installation

npm install gl-life-data better-sqlite3 drizzle-orm nanoid

Quick Start (5 Minutes)

import { MetaDataService, FieldMapper, QueryBuilder } from 'gl-life-data';

// 1. Initialize service
const service = new MetaDataService('./app.db');

// 2. Define a field
await service.createMapping({
  tableName: 'users',
  logicalFieldName: 'email',
  dataType: 'string',
  isRequired: true,
  validationRules: { pattern: '^[^@]+@[^@]+\\.[^@]+$' }
});

// 3. Insert data (maps email → str_1 automatically)
const db = service.getDb();
const mapper = new FieldMapper(service);
const physicalData = mapper.logicalToPhysical('users', { email: '[email protected]' });

db.prepare('INSERT INTO flex_table (id, table_name, str_1) VALUES (?, ?, ?)')
  .run(nanoid(), 'users', physicalData.str_1);

// 4. Query data (maps str_1 → email automatically)
const qb = new QueryBuilder(service);
const result = qb.buildSelect('users', {
  where: [{ field: 'email', operator: 'LIKE', value: '%@example.com' }]
});

console.log(result.data); // [{ email: '[email protected]' }]

How It Works

graph LR
    A[Your App] -->|Logical Fields| B[FieldMapper]
    B -->|str_1, int_1, etc| C[flex_table]
    C -->|Physical Data| B
    B -->|Logical Fields| A

    D[MetaData Table] -.defines.-> B

Key Concept: Instead of altering tables, you define logical fields in metadata. The system maps them to physical columns automatically.

Complete Tutorial

Step 1: Create Your First Table

import { MetaDataService } from 'gl-life-data';

const service = new MetaDataService('./my-app.db');

// Define fields for a "products" table
await service.createMapping({
  tableName: 'products',
  logicalFieldName: 'name',
  dataType: 'string',
  isRequired: true,
  validationRules: { minLength: 3, maxLength: 100 }
});

await service.createMapping({
  tableName: 'products',
  logicalFieldName: 'price',
  dataType: 'decimal',
  isRequired: true,
  validationRules: { min: 0 }
});

await service.createMapping({
  tableName: 'products',
  logicalFieldName: 'inStock',
  dataType: 'boolean',
  isRequired: false
});

What Happened:

• name → mapped to str_1
• price → mapped to dec_1
• inStock → mapped to bool_1

Step 2: Insert Data (CREATE)

import { FieldMapper, FieldValidator } from 'gl-life-data';
import { nanoid } from 'nanoid';

const mappings = service.getTableMappings('products');
const db = service.getDb();

// Logical data (what you write)
const product = {
  name: 'Widget Pro',
  price: 49.99,
  inStock: true
};

// Validate
const nameMapping = mappings.find(m => m.logicalFieldName === 'name');
const validation = FieldValidator.validate(
  'name',
  product.name,
  nameMapping.validationRules,
  nameMapping.isRequired
);

if (!validation.isValid) {
  console.error(validation.errors);
  return;
}

// Map to physical fields
const mapper = new FieldMapper(service);
const physicalData = mapper.logicalToPhysical('products', product);
console.log(physicalData);
// { str_1: 'Widget Pro', dec_1: 49.99, bool_1: 1 }

// Insert
db.prepare(`
  INSERT INTO flex_table (id, table_name, str_1, dec_1, bool_1, created_at, updated_at)
  VALUES (?, ?, ?, ?, ?, datetime('now'), datetime('now'))
`).run(nanoid(), 'products', physicalData.str_1, physicalData.dec_1, physicalData.bool_1);

Step 3: Read Data (READ)

import { QueryBuilder } from 'gl-life-data';

const qb = new QueryBuilder(service);

// Query by logical field name
const { sql, params } = qb.buildSelect('products', {
  where: [{ field: 'inStock', operator: '=', value: true }],
  orderBy: [{ field: 'price', direction: 'ASC' }],
  limit: 10
});

const rows = db.prepare(sql).all(...params);
const mapper = new FieldMapper(service);
const result = rows.map(row => mapper.physicalToLogical('products', row));

console.log(result);
// [
//   { id: 'abc123', name: 'Widget Pro', price: 49.99, inStock: true },
//   { id: 'def456', name: 'Gadget', price: 29.99, inStock: true }
// ]

Query Flow:

sequenceDiagram
    participant App
    participant QueryBuilder
    participant Database

    App->>QueryBuilder: where({ field: 'inStock', value: true })
    QueryBuilder->>QueryBuilder: Map 'inStock' → 'bool_1'
    QueryBuilder->>Database: SELECT * WHERE bool_1 = 1
    Database->>QueryBuilder: Physical rows
    QueryBuilder->>QueryBuilder: Map bool_1 → 'inStock'
    QueryBuilder->>App: Logical data

Step 4: Update Data (UPDATE)

// Find record by ID
const record = db.prepare(`
  SELECT * FROM flex_table WHERE id = ? AND table_name = ?
`).get('abc123', 'products');

// Convert to logical
const mapper = new FieldMapper(service);
const logicalData = mapper.physicalToLogical('products', record);
console.log(logicalData);
// { id: 'abc123', name: 'Widget Pro', price: 49.99, inStock: true }

// Update logical data
logicalData.price = 39.99;
logicalData.inStock = false;

// Map back to physical
const updates = mapper.logicalToPhysical('products', logicalData);

// Update database
db.prepare(`
  UPDATE flex_table
  SET dec_1 = ?, bool_1 = ?, updated_at = datetime('now')
  WHERE id = ?
`).run(updates.dec_1, updates.bool_1, 'abc123');

Step 5: Delete Data (DELETE)

db.prepare(`
  DELETE FROM flex_table WHERE id = ? AND table_name = ?
`).run('abc123', 'products');

Schema Evolution (No Migrations!)

Problem: You need to add a new field to an existing table.

Traditional Solution: Write a migration, alter table, deploy.

Flex Field Solution:

// Just create a new mapping - no migration needed!
await service.createMapping({
  tableName: 'products',
  logicalFieldName: 'sku',
  dataType: 'string',
  isRequired: false
});

// Immediately available for use
const product = {
  name: 'Widget Pro',
  price: 49.99,
  sku: 'WDG-001'  // New field!
};

Before:

MetaData Table:
| logicalFieldName | physicalFieldName | dataType |
|-----------------|-------------------|----------|
| name            | str_1             | string   |
| price           | dec_1             | decimal  |
| inStock         | bool_1            | boolean  |

After:

MetaData Table:
| logicalFieldName | physicalFieldName | dataType |
|-----------------|-------------------|----------|
| name            | str_1             | string   |
| price           | dec_1             | decimal  |
| inStock         | bool_1            | boolean  |
| sku             | str_2             | string   |  ← New!

graph TD
    A[Add Field Request] --> B{Field Allocator}
    B --> C{str_2 available?}
    C -->|Yes| D[Assign str_2]
    C -->|No| E[Use json_data overflow]
    D --> F[Update MetaData]
    E --> F
    F --> G[Field Immediately Available]

Physical Schema

The flex_table has pre-allocated typed columns:

CREATE TABLE flex_table (
  id TEXT PRIMARY KEY,
  table_name TEXT NOT NULL,

  -- String fields (250 slots)
  str_1 TEXT, str_2 TEXT, ..., str_250 TEXT,

  -- Integer fields (100 slots)
  int_1 INTEGER, int_2 INTEGER, ..., int_100 INTEGER,

  -- Decimal fields (50 slots)
  dec_1 REAL, dec_2 REAL, ..., dec_50 REAL,

  -- Boolean fields (50 slots)
  bool_1 INTEGER, bool_2 INTEGER, ..., bool_50 INTEGER,

  -- Date fields (50 slots)
  date_1 TEXT, date_2 TEXT, ..., date_50 TEXT,

  -- JSON overflow (unlimited fields)
  json_1 TEXT, json_2 TEXT, ..., json_50 TEXT,

  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Overflow Behavior: When all 250 string fields are used, the 251st string field automatically goes to json_1.

API Reference

MetaDataService

class MetaDataService {
  constructor(dbPath: string)

  // Create a new field mapping
  createMapping(input: CreateMappingInput): Promise<MetadataSelect>

  // Update existing mapping
  updateMapping(input: UpdateMappingInput): Promise<MetadataSelect>

  // Delete mapping
  deleteMapping(id: string): Promise<boolean>

  // Get mapping by ID
  getMapping(id: string): MetadataSelect | null

  // Get mapping by logical field name
  getMappingByLogicalField(tableName: string, logicalFieldName: string): MetadataSelect | null

  // Get all mappings for a table
  getTableMappings(tableName: string): MetadataSelect[]

  // Get all table names
  getAllTables(): string[]

  // Get database connection
  getDb(): Database

  close(): void
}

FieldMapper

class FieldMapper {
  constructor(metaDataService: MetaDataService)

  // Convert logical data to physical column names
  logicalToPhysical(
    tableName: string,
    logicalData: Record<string, any>
  ): Record<string, any>

  // Convert physical data to logical field names
  physicalToLogical(
    tableName: string,
    physicalData: Record<string, any>
  ): Record<string, any>

  // Check if a field is mapped
  isMapped(tableName: string, logicalFieldName: string): boolean

  // Get physical field name for a logical field
  getPhysicalFieldName(tableName: string, logicalFieldName: string): string | null

  // Get logical field name for a physical column
  getLogicalFieldName(tableName: string, physicalColumnName: string): string | null

  // Get all mapped fields for a table
  getMappedFields(tableName: string): Array<{ logical: string; physical: string }>
}

Example:

const mapper = new FieldMapper(service);

const logical = { email: '[email protected]', age: 30 };
const physical = mapper.logicalToPhysical('users', logical);
// { str_1: '[email protected]', int_1: 30 }

const backToLogical = mapper.physicalToLogical('users', physical);
// { email: '[email protected]', age: 30 }

QueryBuilder

class QueryBuilder {
  constructor(metaDataService: MetaDataService)

  // Build SELECT query
  buildSelect(tableName: string, options?: QueryOptions): QueryResult

  // Build COUNT query for pagination
  buildCount(tableName: string, options?: QueryOptions): QueryResult

  // Build UPDATE query
  buildUpdate(
    tableName: string,
    updates: Record<string, any>,
    where: WhereCondition[]
  ): QueryResult

  // Build DELETE query
  buildDelete(tableName: string, where: WhereCondition[]): QueryResult
}

interface QueryOptions {
  where?: WhereCondition[];
  orderBy?: OrderBy[];
  limit?: number;
  offset?: number;
}

interface QueryResult {
  sql: string;
  params: any[];
}

WhereCondition:

interface WhereCondition {
  field: string;           // Logical field name
  operator: '=' | '!=' | '>' | '>=' | '<' | '<=' | 'LIKE' | 'IN' | 'IS NULL' | 'IS NOT NULL';
  value?: any;
}

Example:

const qb = new QueryBuilder(service);
const { sql, params } = qb.buildSelect('users', {
  where: [
    { field: 'age', operator: '>=', value: 18 },
    { field: 'status', operator: '=', value: 'active' }
  ],
  orderBy: [{ field: 'createdAt', direction: 'DESC' }],
  limit: 50
});

const rows = db.prepare(sql).all(...params);

FieldValidator

class FieldValidator {
  // Validate a single field
  static validate(
    fieldName: string,
    value: any,
    rules: ValidationRules | null,
    isRequired: boolean
  ): ValidationResult

  // Validate multiple fields
  static validateFields(
    fields: Array<{
      name: string;
      value: any;
      rules: ValidationRules | null;
      isRequired: boolean;
    }>
  ): ValidationResult
}

ValidationRules:

interface ValidationRules {
  minLength?: number;      // String minimum length
  maxLength?: number;      // String maximum length
  pattern?: string;        // Regex pattern
  min?: number;           // Numeric minimum
  max?: number;           // Numeric maximum
  unique?: boolean;       // Uniqueness constraint
  foreignKey?: string;    // Foreign key reference
}

Example:

const result = FieldValidator.validate(
  'email',
  '[email protected]',
  { pattern: '^[^@]+@[^@]+\\.[^@]+$', maxLength: 255 },
  true
);

if (!result.isValid) {
  console.error(result.errors);
  // [{ field: 'email', rule: 'pattern', message: '...' }]
}

Data Types

Supported data types:

type DataType = 'string' | 'integer' | 'decimal' | 'boolean' | 'date' | 'json';

Type Mapping:

• string → TEXT (str_1 to str_250)
• integer → INTEGER (int_1 to int_100)
• decimal → REAL (dec_1 to dec_50)
• boolean → INTEGER (bool_1 to bool_50, stored as 0/1)
• date → TEXT (date_1 to date_50, ISO 8601 format)
• json → TEXT (json_1 to json_50, JSON string)

Migration Utilities

When you need to migrate existing data:

import {
  MigrationRunner,
  MoveFieldMigration,
  TypeConversionMigration,
  BackfillDefaultsMigration
} from 'gl-life-data';

const runner = new MigrationRunner('./app.db');

// Move data from one physical field to another
await runner.run(new MoveFieldMigration({
  tableName: 'users',
  sourceField: 'str_1',
  targetField: 'str_2'
}));

// Convert data types
await runner.run(new TypeConversionMigration({
  tableName: 'products',
  fieldName: 'str_10',
  fromType: 'string',
  toType: 'integer',
  converter: (value) => parseInt(value)
}));

// Backfill missing values
await runner.run(new BackfillDefaultsMigration({
  tableName: 'users',
  fieldName: 'str_5',
  defaultValue: 'N/A'
}));

TypeScript Support

Full type definitions included:

import type {
  MetadataSelect,
  MetadataInsert,
  CreateMappingInput,
  UpdateMappingInput,
  ValidationRules,
  ValidationResult,
  ValidationError,
  DataType,
  FieldAllocation,
  WhereCondition,
  OrderBy,
  QueryOptions,
  QueryResult,
  MigrationResult,
  MigrationOptions
} from 'gl-life-data';

Use Cases

1. Multi-Tenant SaaS

Each tenant defines custom fields without affecting other tenants.

// Tenant A adds "department" field
await service.createMapping({
  tableName: 'tenant_a_users',
  logicalFieldName: 'department',
  dataType: 'string'
});

// Tenant B adds different fields
await service.createMapping({
  tableName: 'tenant_b_users',
  logicalFieldName: 'employeeId',
  dataType: 'string'
});

2. Rapid Prototyping

Add fields as requirements emerge without writing migrations.

// Day 1: Basic user fields
await service.createMapping({ tableName: 'users', logicalFieldName: 'name', dataType: 'string' });

// Day 3: Need phone number
await service.createMapping({ tableName: 'users', logicalFieldName: 'phone', dataType: 'string' });

// Day 7: Need age verification
await service.createMapping({ tableName: 'users', logicalFieldName: 'age', dataType: 'integer' });

3. User-Configurable Forms

Users define their own form fields at runtime.

// Admin creates custom fields via UI
const fields = [
  { name: 'companySize', type: 'integer' },
  { name: 'industry', type: 'string' },
  { name: 'website', type: 'string' }
];

for (const field of fields) {
  await service.createMapping({
    tableName: 'custom_forms',
    logicalFieldName: field.name,
    dataType: field.type
  });
}

Complete Example

import { MetaDataService, FieldMapper, QueryBuilder, FieldValidator } from 'gl-life-data';
import { nanoid } from 'nanoid';

// Step 1: Initialize
const service = new MetaDataService('./blog.db');

// Step 2: Define schema
await service.createMapping({
  tableName: 'posts',
  logicalFieldName: 'title',
  dataType: 'string',
  isRequired: true,
  validationRules: { minLength: 5, maxLength: 200 }
});

await service.createMapping({
  tableName: 'posts',
  logicalFieldName: 'content',
  dataType: 'string',
  isRequired: true
});

await service.createMapping({
  tableName: 'posts',
  logicalFieldName: 'published',
  dataType: 'boolean',
  isRequired: false
});

// Step 3: Create post
const db = service.getDb();
const mapper = new FieldMapper(service);
const mappings = service.getTableMappings('posts');

const post = {
  title: 'Getting Started with Flex Fields',
  content: 'This is a revolutionary way to handle dynamic schemas...',
  published: true
};

// Validate
const titleMapping = mappings.find(m => m.logicalFieldName === 'title');
const validation = FieldValidator.validate('title', post.title, titleMapping.validationRules, true);

if (validation.isValid) {
  const physicalData = mapper.logicalToPhysical('posts', post);

  db.prepare(`
    INSERT INTO flex_table (id, table_name, str_1, str_2, bool_1, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, datetime('now'), datetime('now'))
  `).run(nanoid(), 'posts', physicalData.str_1, physicalData.str_2, physicalData.bool_1);
}

// Step 4: Query posts
const qb = new QueryBuilder(service);
const { sql, params } = qb.buildSelect('posts', {
  where: [{ field: 'published', operator: '=', value: true }],
  orderBy: [{ field: 'createdAt', direction: 'DESC' }],
  limit: 10
});

const rows = db.prepare(sql).all(...params);
const result = rows.map(row => mapper.physicalToLogical('posts', row));

console.log(result);
// [{ id: '...', title: 'Getting Started...', content: '...', published: true }]

service.close();

Guides

For in-depth information, see the following guides included in this package:

• FLEX-FIELD-SYSTEM-GUIDE.md - Complete architecture and design patterns
• AI-AGENT-DEVELOPMENT-GUIDE.md - Building AI agents with this library

Requirements

• Node.js >= 18.0.0
• better-sqlite3 >= 12.0.0
• drizzle-orm >= 0.45.0
• nanoid >= 5.0.0

License

Apache-2.0