npm package: @gl-life/gl-life-database
Description: Enhanced gl-life-data wrapper with CloudForge features - Type-safe database abstraction layer with multi-tenancy, caching, migrations, and Cloudflare Workers support
Author: GL.Life
Latest version: 1.2.0

# @gl-life/gl-life-database

Enhanced gl-life-data wrapper with CloudForge features - Type-safe database abstraction layer with multi-tenancy, caching, migrations, and Cloudflare Workers support.

## About

`@gl-life/gl-life-database` is a comprehensive database abstraction layer that extends gl-life-data with enterprise features for multi-tenant SaaS applications. Built with TypeScript and designed for type safety, it supports both cloud-native deployments (Cloudflare Workers) and enterprise bring-your-own-database (BYOD) scenarios with SQL Server, MySQL, PostgreSQL, Oracle, and more. Perfect for pro users who want to leverage their existing infrastructure while gaining powerful features like Flex tables, multi-tenancy, and intelligent caching.

### Key Features

- 🗄️ **Type-Safe Query Builder** - Fluent API with full TypeScript support extending gl-life-data
- 🔀 **Flex Table System** - Dynamic schema without ALTER TABLE, metadata-driven field mapping
- 🏢 **Enterprise Database Support** - Full support for SQL Server, MySQL, PostgreSQL, Oracle, and more
- 👥 **Multi-Tenancy** - Built-in tenant isolation with Row-Level Security (RLS)
- ⚡ **Flexible Caching** - Memory cache, Redis, or Cloudflare KV backends
- 🔄 **Migrations** - Version-controlled database migrations with rollback support
- 📡 **Data Streaming** - Memory-efficient streaming for large datasets with backpressure control
- ☁️ **Cloud + On-Premise** - Deploy on Cloudflare Workers OR bring your own infrastructure
- 🔒 **SQL Injection Protection** - Parameterized queries with automatic sanitization
- 🧪 **Well-Tested** - >95% test coverage with comprehensive security tests
- 📊 **Performance Optimized** - Query building overhead <1ms (P95)

### Part of GoodLife Sargam

This package is part of the **GoodLife Sargam** ecosystem - a comprehensive suite of 80 packages for building modern cloud applications. Visit [gl.life](https://gl.life) for more information.

## Installation

```bash
npm install @gl-life/gl-life-database
```

### Dependencies

The package has minimal dependencies:

- `@gl-life/gl-life-core` ^1.0.1 - Foundation utilities (Result, Option, Logger)
- `gl-life-data` latest - Base database package
- `zod` ^4.2.1 - Runtime type validation

## Quick Start

### Basic Usage

```typescript
import {
  ConnectionManager,
  TypeSafeQueryBuilder,
  Logger
} from '@gl-life/gl-life-database';

// Create a connection manager
const logger = new Logger({ level: 'info' });
const connectionManager = new ConnectionManager(logger);

// Connect to database
const connection = await connectionManager.connect({
  type: 'sqlite',
  filename: './myapp.db'
});

// Build and execute a query
const builder = new TypeSafeQueryBuilder('users', null, logger);
const sql = builder
  .select(['id', 'name', 'email'])
  .where('status', '=', 'active')
  .orderBy('created_at', 'DESC')
  .limit(10)
  .toSQL();

const result = await connection.execute(sql);
```

### Multi-Tenant Application

```typescript
import {
  TenantContext,
  QueryWrapper,
  Logger
} from '@gl-life/gl-life-database';

// Set up tenant context
const logger = new Logger({ level: 'info' });
const tenantContext = new TenantContext(logger);

// Set current tenant
tenantContext.setTenant({
  tenantId: 'tenant-123',
  isAdmin: false
});

// Queries automatically include tenant_id filtering
const wrapper = new QueryWrapper(tenantContext, logger);
const users = await wrapper.query('users', {
  where: { status: 'active' }
});
// Automatically adds: WHERE tenant_id = 'tenant-123' AND status = 'active'
```

### Caching

```typescript
import {
  MemoryCache,
  CacheConfig,
  Logger
} from '@gl-life/gl-life-database';

const config: CacheConfig = {
  enabled: true,
  backend: 'MEMORY',
  maxItems: 1000,
  defaultTTL: 300,
  evictionPolicy: 'LRU',
  invalidationStrategy: 'TIME_BASED'
};

const logger = new Logger({ level: 'info' });
const cache = new MemoryCache(config, logger);

// Cache query results
await cache.set('user:123', { id: 123, name: 'Alice' }, 60);
const user = await cache.get('user:123');

// Pattern-based invalidation
await cache.invalidatePattern('user:*');
```

### Database Migrations

```typescript
import {
  MigrationPlanner,
  MigrationConfig,
  Logger
} from '@gl-life/gl-life-database';

const config: MigrationConfig = {
  enabled: true,
  migrationTable: 'migrations',
  autoRun: false
};

const logger = new Logger({ level: 'info' });
const planner = new MigrationPlanner(config, logger);

// Load migrations from directory
const migrations = [
  {
    version: '001',
    name: 'create_users_table',
    up: 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)',
    down: 'DROP TABLE users'
  }
];

await planner.loadMigrations(migrations);

// Plan migration execution
const plan = planner.planMigration('001');
// Execute: await executor.executeMigration(plan);
```

### Cloudflare D1 Integration

```typescript
import { D1Adapter, Logger } from '@gl-life/gl-life-database';

// In Cloudflare Worker
export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const logger = new Logger({ level: 'info' });
    const adapter = new D1Adapter(env.DB, logger);

    const result = await adapter.execute(
      'SELECT * FROM users WHERE id = ?',
      [123]
    );

    return new Response(JSON.stringify(result));
  }
};
```

### Enterprise Database Support (BYOD - Bring Your Own Database)

Pro users can connect to their existing enterprise databases instead of using Cloudflare Workers. Full support for:

#### SQL Server (Microsoft)

```typescript
import { ConnectionManager, Logger } from '@gl-life/gl-life-database';

const logger = new Logger({ level: 'info' });
const manager = new ConnectionManager(logger);

const connection = await manager.connect({
  type: 'sqlserver',
  host: 'your-sql-server.database.windows.net',
  port: 1433,
  database: 'production_db',
  username: 'admin',
  password: process.env.DB_PASSWORD,
  options: {
    encrypt: true,                    // Azure SQL requires encryption
    trustServerCertificate: false,
    connectionTimeout: 30000
  }
});

// All features work identically: Flex tables, multi-tenancy, caching, migrations
const builder = new TypeSafeQueryBuilder('users', metaDataService, logger);
// ... same as any other database
```

#### PostgreSQL

```typescript
const connection = await manager.connect({
  type: 'postgres',
  host: 'your-postgres-instance.com',
  port: 5432,
  database: 'production_db',
  username: 'dbuser',
  password: process.env.DB_PASSWORD,
  options: {
    ssl: { rejectUnauthorized: false },
    poolSize: 20,
    idleTimeoutMillis: 30000
  }
});
```

#### MySQL / MariaDB

```typescript
const connection = await manager.connect({
  type: 'mysql',
  host: 'mysql-cluster.internal.com',
  port: 3306,
  database: 'production_db',
  username: 'dbuser',
  password: process.env.DB_PASSWORD,
  options: {
    connectionLimit: 10,
    charset: 'utf8mb4',
    timezone: 'Z'
  }
});
```

#### Oracle Database

```typescript
const connection = await manager.connect({
  type: 'oracle',
  host: 'oracle-enterprise.company.com',
  port: 1521,
  database: 'ORCL',              // Service name
  username: 'system',
  password: process.env.DB_PASSWORD,
  options: {
    poolMax: 10,
    poolMin: 2,
    poolIncrement: 1
  }
});
```

#### SQLite (Development/Testing)

```typescript
const connection = await manager.connect({
  type: 'sqlite',
  filename: './dev.db'            // Or ':memory:' for in-memory
});
```

**Key Benefits for Enterprise Users:**
- ✅ **Use Existing Infrastructure** - No migration required, connect to your current databases
- ✅ **All Features Work** - Flex tables, multi-tenancy, caching, migrations work on any database
- ✅ **Security Compliant** - Keep data on-premise, meet regulatory requirements
- ✅ **Cost Control** - Use your existing database licenses and hardware
- ✅ **Hybrid Deployments** - Mix cloud (D1) and on-premise databases in same application
- ✅ **Connection Pooling** - Built-in connection management for all enterprise databases

### Flex Table System

The Flex Table System is a powerful feature from gl-life-data that provides dynamic schema capabilities without ALTER TABLE operations. Perfect for multi-tenant SaaS applications where each tenant needs custom fields.

```typescript
import { TypeSafeQueryBuilder, Logger } from '@gl-life/gl-life-database';
import { MetaDataService } from 'gl-life-data';

// Initialize metadata service
const metaDataService = new MetaDataService('./database.db');

// Create metadata mapping: logical field → physical column
await metaDataService.createMapping({
  tableName: 'products',
  logicalFieldName: 'productName',
  dataType: 'string',          // Maps to string_field_1
  isRequired: true,
  description: 'Product name'
});

await metaDataService.createMapping({
  tableName: 'products',
  logicalFieldName: 'price',
  dataType: 'decimal',         // Maps to decimal_field_1
  isRequired: true
});

// Query using logical field names
const builder = new TypeSafeQueryBuilder('products', metaDataService, logger);
const sql = builder
  .select(['productName', 'price'])  // Uses logical names
  .where('price', '>', 100)
  .toSQL();
// Generates: SELECT string_field_1, decimal_field_1 FROM flex_table WHERE decimal_field_1 > ?
```

**Key Benefits:**
- **Universal Schema**: All tables use same physical structure (string_field_1, int_field_1, etc.)
- **No ALTER TABLE**: Add fields by creating metadata entries
- **Type Safety**: 10 fields per data type (string, int, decimal, date, boolean, text, datetime, float, bigint, uuid)
- **Multi-Tenant Ready**: Different tenants can have different custom fields
- **Query Transparency**: Use logical field names, metadata handles physical mapping

**Flex Table Schema:**
```sql
CREATE TABLE flex_table (
  id TEXT PRIMARY KEY,
  tenant_id TEXT,
  -- 10 string fields
  string_field_1 TEXT,
  string_field_2 TEXT,
  ...
  string_field_10 TEXT,
  -- 10 int fields
  int_field_1 INTEGER,
  ...
  int_field_10 INTEGER,
  -- Plus: decimal, date, boolean, text, datetime, float, bigint, uuid fields
  json_data TEXT,           -- Overflow for extra fields
  created_at TEXT,
  updated_at TEXT
);
```

See [examples/06-flex-tables.ts](./examples/06-flex-tables.ts) and [examples/07-flex-with-multi-tenancy.ts](./examples/07-flex-with-multi-tenancy.ts) for complete workflows.

### Data Streaming

Process large datasets efficiently without loading everything into memory. Perfect for exporting data, batch processing, or handling millions of rows.

```typescript
import { DatabaseConnectionManager, Logger } from '@gl-life/gl-life-database';

const logger = new Logger({ level: 'info' });
const manager = new DatabaseConnectionManager({ name: 'app_db' }, logger);
await manager.initialize();

const connection = await manager.acquire();

// Stream row-by-row (minimal memory usage)
for await (const user of connection.unwrap().stream(
  'SELECT * FROM users WHERE active = ?',
  [true],
  { batchSize: 1000 }
)) {
  await processUser(user);
}

// Stream in batches for bulk operations
for await (const batch of connection.unwrap().streamBatch(
  'SELECT * FROM users',
  [],
  { batchSize: 5000, maxMemoryMB: 100 }
)) {
  await bulkExport(batch); // Process 5000 rows at a time
}

// Stream with transformation
for await (const email of connection.unwrap().streamWithTransform(
  'SELECT * FROM users WHERE age > ?',
  [18],
  (user) => user.email.toLowerCase(),
  { batchSize: 500 }
)) {
  await sendEmail(email);
}

// Advanced control with StreamReader
const reader = connection.unwrap().createStreamReader({
  batchSize: 10000,
  maxMemoryMB: 200,
  backpressure: true
});

await reader.open('SELECT * FROM orders ORDER BY created_at', []);

while (reader.hasMore()) {
  const batch = await reader.fetchBatch();
  if (batch.isOk()) {
    console.log(`Memory: ${reader.getMemoryUsage()} MB`);
    console.log(`Throughput: ${reader.stats.rowsPerSecond} rows/sec`);
    await processBatch(batch.unwrap());
  }
}

await reader.close();
```

**Key Benefits:**
- ✅ **Memory Efficient** - Only current batch in memory, not entire dataset
- ✅ **Backpressure Control** - Automatic pause/resume prevents memory overflow
- ✅ **Real-time Stats** - Monitor throughput, memory usage, progress
- ✅ **Composable Pipelines** - Chain filter, map, aggregate processors
- ✅ **Error Handling** - Automatic cleanup on errors or interruption

See [examples/08-streaming.ts](./examples/08-streaming.ts) for 8 comprehensive examples including CSV export, data pipelines, and performance comparisons.

## Usage

### Query Builder

The type-safe query builder provides a fluent API for constructing SQL queries:

```typescript
import { TypeSafeQueryBuilder, Logger } from '@gl-life/gl-life-database';

const logger = new Logger({ level: 'info' });
const builder = new TypeSafeQueryBuilder('orders', null, logger);

// Complex query with joins
const sql = builder
  .select(['orders.id', 'orders.total', 'customers.name'])
  .join('customers', 'customers.id', '=', 'orders.customer_id')
  .where('orders.status', '=', 'completed')
  .where('orders.total', '>', 100)
  .orderBy('orders.created_at', 'DESC')
  .limit(20)
  .offset(0)
  .toSQL();

// Outputs: SELECT orders.id, orders.total, customers.name
// FROM orders
// JOIN customers ON customers.id = orders.customer_id
// WHERE orders.status = ? AND orders.total > ?
// ORDER BY orders.created_at DESC
// LIMIT 20 OFFSET 0
```

### Transactions

Execute multiple operations atomically:

```typescript
import {
  TransactionManager,
  TransactionConfig,
  Logger
} from '@gl-life/gl-life-database';

const config: TransactionConfig = {
  isolationLevel: 'READ_COMMITTED',
  timeout: 30000,
  retryAttempts: 3
};

const logger = new Logger({ level: 'info' });
const txManager = new TransactionManager(connection, config, logger);

await txManager.execute(async (tx) => {
  await tx.execute('INSERT INTO accounts (balance) VALUES (?)', [1000]);
  await tx.execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', [100, 1]);
  await tx.execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', [100, 2]);
  // Automatically commits on success, rolls back on error
});
```

### Tenant Isolation

Ensure data isolation in multi-tenant applications:

```typescript
import {
  TenantContext,
  QueryWrapper,
  Logger
} from '@gl-life/gl-life-database';

const logger = new Logger({ level: 'info' });
const tenantContext = new TenantContext(logger);

// Middleware to set tenant from request
function setTenantFromRequest(request: Request) {
  const tenantId = request.headers.get('X-Tenant-ID');
  if (!tenantId) {
    throw new Error('Missing tenant ID');
  }

  tenantContext.setTenant({
    tenantId,
    isAdmin: false
  });
}

// All queries automatically filtered by tenant
const wrapper = new QueryWrapper(tenantContext, logger);
const data = await wrapper.query('products', {});
// Automatically adds: WHERE tenant_id = ?

// Admin queries can bypass tenant filter
tenantContext.setTenant({
  tenantId: 'admin',
  isAdmin: true
});
const allData = await wrapper.query('products', {}, { bypassTenantFilter: true });
```

### Cache Invalidation Strategies

```typescript
import {
  MemoryCache,
  CacheConfig,
  InvalidationStrategy,
  Logger
} from '@gl-life/gl-life-database';

const logger = new Logger({ level: 'info' });

// Time-based invalidation
const timeCache = new MemoryCache({
  enabled: true,
  backend: 'MEMORY',
  maxItems: 1000,
  defaultTTL: 300,
  evictionPolicy: 'LRU',
  invalidationStrategy: 'TIME_BASED'
}, logger);

// Event-based invalidation
const eventCache = new MemoryCache({
  enabled: true,
  backend: 'MEMORY',
  maxItems: 1000,
  defaultTTL: 0, // No TTL, manual invalidation
  evictionPolicy: 'LRU',
  invalidationStrategy: 'EVENT_BASED'
}, logger);

// Invalidate on data changes
await eventCache.set('user:123', userData);
// ... later after update
await eventCache.delete('user:123');
// or invalidate all users
await eventCache.invalidatePattern('user:*');
```

## API Overview

### Core Classes

- **ConnectionManager** - Manages database connections and connection pooling
- **TypeSafeQueryBuilder** - Fluent API for building type-safe SQL queries
- **TransactionManager** - Handles database transactions with isolation levels
- **TenantContext** - Manages multi-tenant context and isolation
- **QueryWrapper** - Wraps queries with automatic tenant filtering
- **MemoryCache** - In-memory LRU cache for query results
- **KVCache** - Cloudflare Workers KV-backed cache
- **MigrationPlanner** - Plans and validates database migrations
- **D1Adapter** - Cloudflare D1 database adapter
- **DurableObjectStorage** - Durable Objects storage adapter

### Type System

All operations return `Result<T, E>` types from `@gl-life/gl-life-core` for functional error handling:

```typescript
import { Result, Option } from '@gl-life/gl-life-database';

const result: Result<User[], DatabaseError> = await queryUsers();

if (result.isOk()) {
  const users = result.unwrap();
  console.log('Found users:', users);
} else {
  const error = result.unwrapErr();
  console.error('Query failed:', error.message);
}
```

### Configuration

All components accept configuration objects with sensible defaults:

```typescript
interface CacheConfig {
  enabled: boolean;
  backend: 'MEMORY' | 'KV' | 'HYBRID';
  maxItems?: number;
  defaultTTL?: number;
  evictionPolicy?: 'LRU' | 'LFU' | 'FIFO';
  invalidationStrategy?: 'TIME_BASED' | 'EVENT_BASED' | 'HYBRID';
}

interface TransactionConfig {
  isolationLevel: 'READ_UNCOMMITTED' | 'READ_COMMITTED' | 'REPEATABLE_READ' | 'SERIALIZABLE';
  timeout: number;
  retryAttempts: number;
}

interface MigrationConfig {
  enabled: boolean;
  migrationTable: string;
  autoRun: boolean;
}
```

## Security

This library implements comprehensive security measures:

- ✅ **SQL Injection Protection** - All queries use parameterized statements
- ✅ **Tenant Isolation** - Automatic tenant_id filtering prevents data leaks
- ✅ **Type Safety** - TypeScript compile-time checks + Zod runtime validation
- ✅ **Audit Logging** - All operations are logged for security auditing
- ✅ **Zero Vulnerabilities** - Regular security audits with `npm audit`

See [SECURITY.md](./SECURITY.md) for detailed security audit results and best practices.

## Performance

Query building performance benchmarks (P95):

- Simple SELECT: <1ms
- Complex JOIN: <1ms
- GROUP BY with HAVING: <1ms
- Multiple WHERE conditions: <1ms

Cache performance benchmarks (P95):

- Cache GET (hit): <1ms
- Cache SET: <1ms
- Pattern invalidation (100 keys): <10ms

See [PERFORMANCE.md](./PERFORMANCE.md) for detailed benchmark results.

## Testing

Run the test suite:

```bash
# Run all tests
npm test

# Run tests in watch mode
npm run test:watch

# Generate coverage report
npm run test:coverage

# Run performance benchmarks
npm run bench

# Run specific benchmark
npm run bench:query
```

Current test coverage: **95.9%**

- Lines: 95.9%
- Functions: 96.2%
- Branches: 95.1%
- Statements: 95.9%

## Development

```bash
# Install dependencies
npm install

# Build the package
npm run build

# Run tests
npm test

# Run benchmarks
npm run bench
```

## Contributing

This package is part of the GoodLife Sargam ecosystem. For contribution guidelines, please visit [gl.life](https://gl.life).

## Support

For questions, issues, or feature requests:

- Email: [packages@gl.life](mailto:packages@gl.life)
- Website: [gl.life](https://gl.life)

## Documentation

- [API Documentation](./API.md) - Detailed API reference for AI agents
- [Security Audit](./SECURITY.md) - Security audit report and best practices
- [Performance Benchmarks](./PERFORMANCE.md) - Performance baselines and optimization guide

## License

Apache-2.0 © GoodLife

---

**Part of the GoodLife Sargam ecosystem**

For more information about the GoodLife Sargam ecosystem and our suite of 80 packages for building modern cloud applications, visit [gl.life](https://gl.life).