Package Exports

gl-life-data
gl-life-data/dist/src/index.js

This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (gl-life-data) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

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: 'user@example.com' });

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: 'user@example.com' }]

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: 'user@example.com', age: 30 };
const physical = mapper.logicalToPhysical('users', logical);
// { str_1: 'user@example.com', int_1: 30 }

const backToLogical = mapper.physicalToLogical('users', physical);
// { email: 'user@example.com', 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',
  'user@example.com',
  { 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