@bernierllc/database-adapter-core

Abstract Database Interface for MECE Architecture - provides database-agnostic interfaces and base implementations for all database operations across BernierLLC tools.

Features

• Abstract Database Interfaces - Clean, database-agnostic interfaces for all database operations
• Base Implementations - Common functionality through abstract base classes
• Row Level Security (RLS) - Built-in support for multi-tenant security and user-based access control
• Query Builder Foundation - Type-safe SQL generation without string concatenation
• Migration System - Schema migration tracking and execution
• Transaction Support - ACID transaction interfaces
• Connection Pooling - Interface for connection pool management
• Error Handling - Structured error classes with sensitive data sanitization
• Type-Safe - Full TypeScript support with strict mode
• Zero Runtime Overhead - Pure interfaces and abstract classes

Installation

npm install @bernierllc/database-adapter-core

Usage

Creating a Database Adapter

Extend BaseDatabaseAdapter to create a concrete database implementation:

import {
  BaseDatabaseAdapter,
  IDatabaseConfig,
  IQueryResult,
  ITransaction,
  IDatabaseHealth,
  ConnectionError
} from '@bernierllc/database-adapter-core';

class PostgreSQLAdapter extends BaseDatabaseAdapter {
  private pool: pg.Pool;

  async connect(): Promise<void> {
    try {
      this.pool = new pg.Pool({
        host: this.config.host,
        port: this.config.port,
        database: this.config.database,
        user: this.config.username,
        password: this.config.password
      });

      await this.pool.connect();
      this.connected = true;
    } catch (error) {
      throw new ConnectionError(
        'Failed to connect to PostgreSQL',
        'CONNECTION_FAILED',
        error as Error,
        this.config.host,
        this.config.port
      );
    }
  }

  async disconnect(): Promise<void> {
    await this.pool.end();
    this.connected = false;
  }

  async query<T>(sql: string, params?: any[]): Promise<IQueryResult<T>> {
    this.ensureConnected();

    try {
      const result = await this.pool.query(sql, params);
      return {
        rows: result.rows as T[],
        rowCount: result.rowCount || 0,
        fields: result.fields.map(f => ({
          name: f.name,
          dataTypeID: f.dataTypeID,
          dataTypeName: f.dataTypeName,
          nullable: true
        })),
        command: result.command
      };
    } catch (error) {
      throw this.sanitizeError(error);
    }
  }

  async beginTransaction(): Promise<ITransaction> {
    this.ensureConnected();
    // Implementation details...
  }

  async healthCheck(): Promise<IDatabaseHealth> {
    const start = Date.now();
    await this.query('SELECT 1');
    const latencyMs = Date.now() - start;

    return {
      connected: this.connected,
      latencyMs,
      version: '15.0',
      activeConnections: this.pool.totalCount,
      maxConnections: this.pool.options.max
    };
  }
}

Using Row Level Security (RLS)

import { RLSContextManager, IRLSContext } from '@bernierllc/database-adapter-core';

const rlsManager = new RLSContextManager();

// Set RLS context for multi-tenant security
const context: IRLSContext = {
  userId: 'user-123',
  userRole: 'admin',
  tenantId: 'tenant-456',
  claims: { department: 'engineering' }
};

// Execute operations with RLS context
await rlsManager.withContext(context, async () => {
  // All database operations here will have RLS context applied
  const users = await adapter.query('SELECT * FROM users');
  // PostgreSQL RLS policies will automatically filter based on context
});

// Generate PostgreSQL SET statements for RLS
rlsManager.setContext(context);
const statements = rlsManager.generateRLSStatements();
// [
//   'SET LOCAL "app.user_id" = \'user-123\';',
//   'SET LOCAL "app.user_role" = \'admin\';',
//   'SET LOCAL "app.tenant_id" = \'tenant-456\';',
//   'SET LOCAL "app.claims" = \'{"department":"engineering"}\';'
// ]

Building Type-Safe Queries

import { BaseQueryBuilder } from '@bernierllc/database-adapter-core';

class PostgreSQLQueryBuilder extends BaseQueryBuilder<User> {
  insert(data: Partial<User>): this {
    // Implementation for INSERT
    return this;
  }

  update(data: Partial<User>): this {
    // Implementation for UPDATE
    return this;
  }

  delete(): this {
    // Implementation for DELETE
    return this;
  }

  build(): { sql: string; params: any[] } {
    // Build PostgreSQL-specific SQL
    let sql = `SELECT ${this._select.join(', ')} FROM ${this._from}`;
    if (this._where.length > 0) {
      sql += ` WHERE ${this._where.join(' AND ')}`;
    }
    return { sql, params: this._params };
  }
}

// Usage
const query = new PostgreSQLQueryBuilder()
  .select('id', 'name', 'email')
  .from('users')
  .where('age > $1', [18])
  .where('status = $2', ['active'])
  .orderBy('name', 'ASC')
  .limit(10)
  .offset(20);

const result = await query.execute(adapter);

Managing Migrations

import {
  BaseMigrationManager,
  IMigration,
  IDatabaseAdapter
} from '@bernierllc/database-adapter-core';

const migrations: IMigration[] = [
  {
    id: '001',
    name: 'create_users_table',
    async up(adapter: IDatabaseAdapter) {
      await adapter.query(`
        CREATE TABLE users (
          id SERIAL PRIMARY KEY,
          name VARCHAR(255) NOT NULL,
          email VARCHAR(255) UNIQUE NOT NULL,
          created_at TIMESTAMPTZ DEFAULT NOW()
        )
      `);
    },
    async down(adapter: IDatabaseAdapter) {
      await adapter.query('DROP TABLE users');
    }
  },
  {
    id: '002',
    name: 'add_users_status',
    async up(adapter: IDatabaseAdapter) {
      await adapter.query(`
        ALTER TABLE users ADD COLUMN status VARCHAR(50) DEFAULT 'active'
      `);
    },
    async down(adapter: IDatabaseAdapter) {
      await adapter.query('ALTER TABLE users DROP COLUMN status');
    }
  }
];

const migrationManager = new BaseMigrationManager(adapter, migrations);

// Run pending migrations
await migrationManager.up();

// Get migration status
const status = await migrationManager.status();
console.log(status);
// [
//   { id: '001', name: 'create_users_table', status: 'applied', appliedAt: Date },
//   { id: '002', name: 'add_users_status', status: 'applied', appliedAt: Date }
// ]

// Rollback last migration
await migrationManager.down();

Error Handling

import {
  DatabaseError,
  ConnectionError,
  QueryError
} from '@bernierllc/database-adapter-core';

try {
  await adapter.connect();
} catch (error) {
  if (error instanceof ConnectionError) {
    console.error(`Connection failed: ${error.host}:${error.port}`);
    console.error(`Error code: ${error.code}`);
  }
}

try {
  await adapter.query('SELECT * FROM users WHERE id = $1', [userId]);
} catch (error) {
  if (error instanceof QueryError) {
    console.error(`Query failed: ${error.query}`);
    console.error(`Parameters: ${error.params}`);
  }
}

API Reference

Interfaces

IDatabaseAdapter

Main adapter interface that all concrete adapters must implement.

Methods:

• connect(): Promise<void> - Connect to database
• disconnect(): Promise<void> - Disconnect from database
• isConnected(): boolean - Check connection status
• query<T>(sql: string, params?: any[]): Promise<IQueryResult<T>> - Execute query
• beginTransaction(): Promise<ITransaction> - Begin transaction
• healthCheck(): Promise<IDatabaseHealth> - Check database health

ITransaction

Transaction interface for ACID operations.

Methods:

• query<T>(sql: string, params?: any[]): Promise<IQueryResult<T>> - Execute query in transaction
• commit(): Promise<void> - Commit transaction
• rollback(): Promise<void> - Rollback transaction
• isActive(): boolean - Check if transaction is active

IQueryResult<T>

Query result with metadata.

Properties:

• rows: T[] - Result rows
• rowCount: number - Number of rows returned
• fields: IFieldInfo[] - Field metadata
• command: string - SQL command (SELECT, INSERT, UPDATE, DELETE)

IDatabaseConfig

Database connection configuration.

Properties:

• type: 'postgresql' | 'mysql' | 'sqlite' | 'mssql' - Database type
• host?: string - Host address
• port?: number - Port number
• database: string - Database name
• username?: string - Username
• password?: string - Password
• ssl?: boolean | object - SSL configuration
• pooling?: object - Connection pooling settings

IRLSContext

Row Level Security context.

Properties:

• userId: string - User ID
• userRole: string - User role
• tenantId?: string - Tenant ID (optional)
• claims?: Record<string, any> - Additional claims (optional)

Base Classes

BaseDatabaseAdapter

Abstract base class providing common adapter functionality.

Methods:

• isConnected(): boolean - Returns connection status
• ensureConnected(): void - Throws error if not connected
• sanitizeError(error: unknown): Error - Removes sensitive data from errors

BaseQueryBuilder<T>

Abstract query builder with fluent API.

Methods:

• select(...columns: string[]): this
• from(table: string): this
• where(condition: string, params?: readonly unknown[]): this
• whereIn(column: string, values: readonly unknown[]): this
• join(table: string, condition: string): this
• leftJoin(table: string, condition: string): this
• orderBy(column: string, direction?: 'ASC' | 'DESC'): this
• limit(count: number): this
• offset(count: number): this
• execute(adapter: IDatabaseAdapter): Promise<IQueryResult<T>>

BaseMigrationManager

Migration manager for schema changes.

Methods:

• up(): Promise<void> - Run pending migrations
• down(): Promise<void> - Rollback last migration
• status(): Promise<IMigrationStatus[]> - Get migration status

Utilities

RLSContextManager

Row Level Security context manager.

Methods:

• setContext(context: IRLSContext): void - Set RLS context
• getContext(): IRLSContext | null - Get current context
• clearContext(): void - Clear context
• withContext<T>(context: IRLSContext, fn: () => Promise<T>): Promise<T> - Execute with context
• generateRLSStatements(): string[] - Generate PostgreSQL SET statements

Error Classes

• DatabaseError - Base database error
• ConnectionError - Connection-specific errors
• QueryError - Query-specific errors

Integration Status

Logger Integration

Status: Optional - Can be integrated for error logging and query tracing

Adapters can optionally use @bernierllc/logger for:

• Connection lifecycle logging
• Query execution tracing
• Error logging with context

Docs-Suite Integration

Status: Ready

This package includes complete JSDoc/TypeDoc comments and can be integrated with documentation generators:

• All public APIs documented
• Interface documentation complete
• Examples included

NeverHub Integration

Status: Not Applicable

This is a pure interface package with no runtime behavior requiring service discovery or event communication.

Dependencies

• @bernierllc/logger - Structured logging (optional usage)
• @bernierllc/crypto-utils - For secure token generation in adapters

Testing

This package has comprehensive test coverage:

• 90.27% statement coverage
• 90.47% branch coverage
• 100% function coverage
• 90% line coverage
• 65 passing tests

Run tests:

npm test              # Watch mode
npm run test:run      # Single run
npm run test:coverage # With coverage report

Building

npm run build   # Compile TypeScript
npm run lint    # Run ESLint
npm run clean   # Remove build artifacts

Related Packages

Depends On

• @bernierllc/logger - Structured logging
• @bernierllc/crypto-utils - Cryptographic utilities

Used By

• @bernierllc/database-adapter-postgresql - PostgreSQL implementation (coming soon)
• @bernierllc/content-storage-adapter - Content CRUD operations (coming soon)
• All packages requiring database access

Part Of

• content-management-suite - Database foundation layer

Design Philosophy

This package follows MECE (Mutually Exclusive, Collectively Exhaustive) architecture principles:

• Abstract Interfaces - No concrete database implementations
• Single Responsibility - Pure interface definitions and base utilities
• Zero Coupling - Implementations are separate packages
• Adapter Pattern - Clean abstraction layer for all database operations

By keeping interfaces separate from implementations, we enable:

• Pluggable Backends - Switch databases without changing application code
• Testability - Mock adapters easily for testing
• Flexibility - Support multiple databases simultaneously
• Maintainability - Changes to one adapter don't affect others

License

Copyright (c) 2025 Bernier LLC. All rights reserved.

This file is licensed to the client under a limited-use license. The client may use and modify this code only within the scope of the project it was delivered for. Redistribution or use in other products or commercial offerings is not permitted without written consent from Bernier LLC.