Universal database connection and query abstraction layer with connection pooling
npm install @bernierllc/database-adapterUniversal database connection and query abstraction layer with connection pooling for PostgreSQL, MySQL, SQLite, and MongoDB.
- 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
``bash`
npm install @bernierllc/database-adapter
Install the database driver(s) you need:
`bashPostgreSQL
npm install pg
Usage
$3
typescript
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();
`$3
typescript
// Insert
const user = await db.insert('users', {
name: 'John Doe',
email: 'john@example.com',
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: 'john@example.com' });
// 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);
`$3
typescript
// Query multiple rows
const users = await db.query(
'SELECT * FROM users WHERE age > $1',
[25]
);// Query single row
const user = await db.queryOne(
'SELECT * FROM users WHERE email = $1',
['john@example.com']
);
// 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);
`$3
typescript
await db.transaction(async (tx) => {
// Insert user
const user = await tx.queryOne(
'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
['John Doe', 'john@example.com']
); // 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
});
`$3
typescript
// 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
$3
#### Constructor
typescript
new DatabaseManager(config: DatabaseConfig)
`DatabaseConfig Options:
typescript
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 to the database
- disconnect(): Promise - Close all connections
- healthCheck(): Promise - Check database health
- getStats(): Promise - Get connection statistics##### Direct Queries
-
query - Execute SELECT query
- queryOne - Execute SELECT returning single row
- execute(sql: string, params?: any[]): Promise - Execute INSERT/UPDATE/DELETE##### CRUD Operations
-
findById - Find record by ID
- findOne - Find single record
- findMany - Find multiple records
- insert - Insert record
- update - Update record
- delete(table: string, id: any): Promise - Delete record##### Transactions
-
transaction - Execute transaction$3
`typescript
// 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 {}
`$3
typescript
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
$3
- Connection pooling with configurable pool size
- SSL/TLS support
- Prepared statements for security
- Full transaction support
$3
- WAL (Write-Ahead Logging) mode for better concurrency
- In-memory or file-based databases
- Atomic transactions
- Foreign key enforcement
$3
- Connection pooling
- Prepared statements
- SSL support
- Transaction support
$3
- Connection pooling
- Document-based operations
- Aggregation pipeline support
Best Practices
$3
typescript
// 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);
});
`$3
typescript
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;
}
}
`$3
typescript
interface User {
id?: number;
name: string;
email: string;
created_at?: Date;
}// Type-safe operations
const user = await db.findById('users', 1);
const users = await db.query('SELECT * FROM users');
const newUser = await db.insert('users', {
name: 'John',
email: 'john@example.com'
});
`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
- 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
- @bernierllc/logger - Structured logging for database operations
- @bernierllc/retry-policy - Add retry logic for transient database errors
- @bernierllc/cache-manager - Cache database query results
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.