@bernierllc/database-adapter

Universal database connection and query abstraction layer with connection pooling for PostgreSQL, MySQL, SQLite, and MongoDB.

Features

• Multi-Database Support - Unified interface for PostgreSQL, SQLite, MySQL, and MongoDB
• Connection Pooling - Automatic connection pool management for PostgreSQL
• Type-Safe - Full TypeScript support with strict typing
• CRUD Operations - Convenience methods for common database operations
• Transaction Support - ACID transactions with automatic rollback on errors
• Health Monitoring - Built-in health checks and connection statistics
• Error Handling - Structured error types for different failure scenarios

Installation

npm install @bernierllc/database-adapter

Peer Dependencies

Install the database driver(s) you need:

# PostgreSQL
npm install pg

# MySQL
npm install mysql2

# SQLite
npm install better-sqlite3

# MongoDB
npm install mongodb

Usage

Basic Setup

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

// PostgreSQL
const db = new DatabaseManager({
  type: 'postgresql',
  host: 'localhost',
  port: 5432,
  database: 'myapp',
  user: 'postgres',
  password: 'password',
  pool: {
    min: 2,
    max: 10
  }
});

// SQLite
const db = new DatabaseManager({
  type: 'sqlite',
  database: './data/myapp.db'
});

await db.connect();

CRUD Operations

// Insert
const user = await db.insert('users', {
  name: 'John Doe',
  email: '[email protected]',
  age: 30
});
console.log(user.id); // Auto-generated ID

// Find by ID
const found = await db.findById('users', user.id);

// Find one by criteria
const user = await db.findOne('users', { email: '[email protected]' });

// Find many with options
const users = await db.findMany(
  'users',
  { age: { $gt: 25 } },
  {
    orderBy: 'created_at',
    orderDirection: 'DESC',
    limit: 10,
    offset: 0
  }
);

// Update
const updated = await db.update('users', user.id, {
  name: 'John Smith'
});

// Delete
const deleted = await db.delete('users', user.id);

Direct Queries

// Query multiple rows
const users = await db.query<User>(
  'SELECT * FROM users WHERE age > $1',
  [25]
);

// Query single row
const user = await db.queryOne<User>(
  'SELECT * FROM users WHERE email = $1',
  ['[email protected]']
);

// Execute (INSERT/UPDATE/DELETE)
const result = await db.execute(
  'UPDATE users SET last_login = $1 WHERE id = $2',
  [new Date(), userId]
);
console.log(result.affectedRows);

Transactions

await db.transaction(async (tx) => {
  // Insert user
  const user = await tx.queryOne(
    'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
    ['John Doe', '[email protected]']
  );

  // Insert profile
  await tx.execute(
    'INSERT INTO profiles (user_id, bio) VALUES ($1, $2)',
    [user.id, 'Software engineer']
  );

  // Both operations commit together, or rollback on any error
});

Health Monitoring

// Check database health
const health = await db.healthCheck();
console.log(health.status); // 'healthy' or 'unhealthy'
console.log(health.responseTime); // ms

// Get connection statistics
const stats = await db.getStats();
console.log(stats.totalConnections);
console.log(stats.activeConnections);
console.log(stats.totalQueries);
console.log(stats.avgQueryTime);

API Reference

DatabaseManager

Constructor

new DatabaseManager(config: DatabaseConfig)

DatabaseConfig Options:

interface DatabaseConfig {
  type: 'postgresql' | 'mysql' | 'sqlite' | 'mongodb';

  // Connection options (PostgreSQL/MySQL)
  host?: string;
  port?: number;
  database: string;
  user?: string;
  password?: string;

  // SQLite options
  filename?: string;

  // Connection pool (PostgreSQL)
  pool?: {
    min?: number;
    max?: number;
    idleTimeoutMillis?: number;
    connectionTimeoutMillis?: number;
  };

  // SSL (PostgreSQL)
  ssl?: {
    rejectUnauthorized?: boolean;
    ca?: string;
    cert?: string;
    key?: string;
  };
}

Methods

Connection Management

• connect(): Promise<void> - Connect to the database
• disconnect(): Promise<void> - Close all connections
• healthCheck(): Promise<HealthCheckResult> - Check database health
• getStats(): Promise<ConnectionStats> - Get connection statistics

Direct Queries

• query<T>(sql: string, params?: any[]): Promise<T[]> - Execute SELECT query
• queryOne<T>(sql: string, params?: any[]): Promise<T | null> - Execute SELECT returning single row
• execute(sql: string, params?: any[]): Promise<ExecuteResult> - Execute INSERT/UPDATE/DELETE

CRUD Operations

• findById<T>(table: string, id: any): Promise<T | null> - Find record by ID
• findOne<T>(table: string, where: Record<string, any>): Promise<T | null> - Find single record
• findMany<T>(table: string, where?: Record<string, any>, options?: QueryOptions): Promise<T[]> - Find multiple records
• insert<T>(table: string, data: Partial<T>): Promise<T> - Insert record
• update<T>(table: string, id: any, data: Partial<T>): Promise<T> - Update record
• delete(table: string, id: any): Promise<boolean> - Delete record

Transactions

• transaction<T>(fn: (tx: Transaction) => Promise<T>): Promise<T> - Execute transaction

Error Types

// Base error
class DatabaseError extends Error {
  code?: string;
  cause?: Error;
}

// Specific error types
class ConnectionError extends DatabaseError {}
class QueryError extends DatabaseError {}
class TransactionError extends DatabaseError {}

Type Definitions

interface ExecuteResult {
  affectedRows: number;
  insertId?: number | string;
  changedRows?: number;
}

interface HealthCheckResult {
  status: 'healthy' | 'unhealthy';
  responseTime: number;
  error?: string;
}

interface ConnectionStats {
  totalConnections: number;
  activeConnections: number;
  idleConnections?: number;
  waitingClients?: number;
  totalQueries: number;
  avgQueryTime: number;
}

interface QueryOptions {
  orderBy?: string;
  orderDirection?: 'ASC' | 'DESC';
  limit?: number;
  offset?: number;
}

Database-Specific Features

PostgreSQL

• Connection pooling with configurable pool size
• SSL/TLS support
• Prepared statements for security
• Full transaction support

SQLite

• WAL (Write-Ahead Logging) mode for better concurrency
• In-memory or file-based databases
• Atomic transactions
• Foreign key enforcement

MySQL

• Connection pooling
• Prepared statements
• SSL support
• Transaction support

MongoDB

• Connection pooling
• Document-based operations
• Aggregation pipeline support

Best Practices

Connection Management

// Create connection once at app startup
const db = new DatabaseManager(config);
await db.connect();

// Use throughout app lifecycle

// Close on shutdown
process.on('SIGTERM', async () => {
  await db.disconnect();
  process.exit(0);
});

Error Handling

try {
  const user = await db.findById('users', userId);
} catch (error) {
  if (error instanceof ConnectionError) {
    // Handle connection failure
    logger.error('Database connection failed', error);
  } else if (error instanceof QueryError) {
    // Handle query error
    logger.error('Query failed', error);
  } else {
    // Handle unknown error
    throw error;
  }
}

Type Safety

interface User {
  id?: number;
  name: string;
  email: string;
  created_at?: Date;
}

// Type-safe operations
const user = await db.findById<User>('users', 1);
const users = await db.query<User>('SELECT * FROM users');
const newUser = await db.insert<User>('users', {
  name: 'John',
  email: '[email protected]'
});

Performance Tips

1. Use Connection Pooling - For PostgreSQL/MySQL, configure appropriate pool sizes
2. Prepare Statements - The adapter automatically uses prepared statements
3. Batch Operations - Use transactions for multiple related operations
4. Index Your Queries - Create database indexes for frequently queried columns
5. Monitor Statistics - Use getStats() to identify performance issues

Integration Status

• Logger: Not integrated - Uses console logging. Integration with @bernierllc/logger recommended for production
• Docs-Suite: Ready - Markdown documentation available
• NeverHub integration: Not applicable - Core utility package with no service discovery requirements

See Also

• @bernierllc/logger - Structured logging for database operations
• @bernierllc/retry-policy - Add retry logic for transient database errors
• @bernierllc/cache-manager - Cache database query results

License

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

This package is proprietary software licensed for use only within the scope of the project it was delivered for.