@exdig/db-core

A pluggable database abstraction layer with connection pooling, repository pattern, and audit hooks for any Node.js/TypeScript backend. Works with Express, NestJS, Fastify, or any Node.js framework.

Features

• 🔌 Pluggable DB Drivers - PostgreSQL by default, easily extensible
• 🔄 Connection Pooling - Separate read/write pools for optimal performance
• 🔁 Retry Strategy - Automatic retry for transient errors
• 📦 Repository Pattern - Generic CRUD operations with TypeScript generics
• 🗑️ Soft Delete - Built-in soft delete support
• 📝 Audit Hooks - Automatic population of created_at, updated_at, created_by, updated_by
• 📄 Pagination - Built-in pagination helpers
• 🔍 Query Builder - Type-safe query building utilities
• ⚠️ Error Handling - Comprehensive error mapping and handling
• 🏥 Health Checks - Connection health monitoring
• 🛑 Graceful Shutdown - Proper connection cleanup
• 🔧 Framework Agnostic - Works with Express, NestJS, Fastify, or plain Node.js

Installation

npm install @exdig/db-core

Quick Start

1. Environment Configuration

Create a .env file in your project root:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=your_database
DB_USER=your_user
DB_PASSWORD=your_password

DB_POOL_MIN=2
DB_POOL_MAX=10
DB_READ_POOL_MIN=2
DB_READ_POOL_MAX=10
DB_WRITE_POOL_MIN=2
DB_WRITE_POOL_MAX=10

DB_RETRY_ATTEMPTS=3
DB_RETRY_DELAY_MS=1000

2. Initialize Connection Manager

import { getConnectionManager } from '@exdig/db-core';

// Get singleton instance
const connectionManager = getConnectionManager();

// Initialize connection pools
await connectionManager.initialize();

// Health check
const isHealthy = await connectionManager.healthCheck();
console.log('Database healthy:', isHealthy);

3. Create a Repository

import { BaseRepository, ConnectionManager, BaseEntity } from '@exdig/db-core';

interface User extends BaseEntity {
  id: number;
  email: string;
  name: string;
  created_at: Date;
  updated_at: Date;
  created_by?: number;
  updated_by?: number;
  is_deleted: boolean;
}

class UserRepository extends BaseRepository<User> {
  constructor(connectionManager: ConnectionManager) {
    super(connectionManager, {
      tableName: 'users',
      primaryKey: 'id',
      softDelete: true,
      auditEnabled: true,
    });
  }

  // Add custom methods
  async findByEmail(email: string): Promise<User | null> {
    return this.findOne({ where: { email } });
  }
}

4. Use the Repository

const userRepo = new UserRepository(connectionManager);

// Create a user
const newUser = await userRepo.create({
  email: '[email protected]',
  name: 'John Doe',
}, {
  userId: 1, // Audit context
});

// Find by ID
const user = await userRepo.findById(1);

// Find with pagination
const result = await userRepo.findWithPagination({
  where: { name: 'John' },
  pagination: { page: 1, limit: 10 },
  sort: { field: 'created_at', direction: 'DESC' },
});

// Update
const updated = await userRepo.updateById(1, {
  name: 'Jane Doe',
}, {
  userId: 1,
});

// Soft delete
await userRepo.deleteById(1, { returning: true });

// Hard delete
await userRepo.deleteById(1, { hard: true });

API Reference

ConnectionManager

Methods

initialize(): Promise<void>

Initializes read and write connection pools.

getReadPool(): Pool

Returns the read pool for read operations.

getWritePool(): Pool

Returns the write pool for write operations.

queryRead<T>(text: string, params?: any[]): Promise<QueryResult<T>>

Executes a query on the read pool with retry logic.

queryWrite<T>(text: string, params?: any[]): Promise<QueryResult<T>>

Executes a query on the write pool with retry logic.

getClient(): Promise<PoolClient>

Gets a client from the write pool for transactions.

healthCheck(): Promise<boolean>

Checks the health of database connections.

shutdown(): Promise<void>

Gracefully shuts down all connection pools.

BaseRepository

Constructor

constructor(
  connectionManager: ConnectionManager,
  options: RepositoryOptions
)

Methods

findById(id: string | number, includeDeleted?: boolean): Promise<T | null>

Finds a record by ID.

findByIdOrFail(id: string | number, includeDeleted?: boolean): Promise<T>

Finds a record by ID or throws NotFoundError.

find(options?: FindOptions<T>): Promise<T[]>

Finds records matching the given options.

findOne(options?: FindOptions<T>): Promise<T | null>

Finds a single record matching the given options.

findWithPagination(options?: FindOptions<T>): Promise<PaginationResult<T>>

Finds records with pagination.

create(data: Partial<T>, context?: AuditContext, client?: PoolClient): Promise<T>

Creates a new record.

createMany(dataArray: Partial<T>[], context?: AuditContext, client?: PoolClient): Promise<T[]>

Creates multiple records.

update(options: UpdateOptions, data: Partial<T>, context?: AuditContext, client?: PoolClient): Promise<T>

Updates records matching the criteria.

updateById(id: string | number, data: Partial<T>, context?: AuditContext, client?: PoolClient): Promise<T>

Updates a record by ID.

delete(options: DeleteOptions, context?: AuditContext, client?: PoolClient): Promise<T | void>

Deletes records (soft delete by default).

deleteById(id: string | number, options?: DeleteOptions, context?: AuditContext, client?: PoolClient): Promise<T | void>

Deletes a record by ID.

count(options?: FindOptions<T>): Promise<number>

Counts records matching the given options.

exists(id: string | number, includeDeleted?: boolean): Promise<boolean>

Checks if a record exists.

rawQuery<Result>(query: string, params?: any[], useWritePool?: boolean): Promise<Result[]>

Executes a raw SQL query.

Framework Integration Examples

Express.js / Plain Node.js

Here's how to use @exdig/db-core in an Express.js application or any plain Node.js backend:

// database.ts - Initialize connection
import { getConnectionManager, BaseRepository, BaseEntity } from '@exdig/db-core';

const connectionManager = getConnectionManager();
await connectionManager.initialize();

// user.repository.ts
interface User extends BaseEntity {
  id: number;
  email: string;
  name: string;
  created_at: Date;
  updated_at: Date;
  is_deleted: boolean;
}

class UserRepository extends BaseRepository<User> {
  constructor() {
    super(connectionManager, {
      tableName: 'users',
      primaryKey: 'id',
      softDelete: true,
      auditEnabled: true,
    });
  }

  async findByEmail(email: string): Promise<User | null> {
    return this.findOne({ where: { email } });
  }
}

export const userRepository = new UserRepository();

// user.service.ts
export class UserService {
  async createUser(email: string, name: string, userId: number) {
    return userRepository.create(
      { email, name },
      { userId } // Audit context
    );
  }

  async getUserById(id: number) {
    return userRepository.findByIdOrFail(id);
  }

  async getUsers(page: number = 1, limit: number = 10) {
    return userRepository.findWithPagination({
      pagination: { page, limit },
      sort: { field: 'created_at', direction: 'DESC' },
    });
  }

  async updateUser(id: number, data: Partial<User>, userId: number) {
    return userRepository.updateById(id, data, { userId });
  }

  async deleteUser(id: number) {
    return userRepository.deleteById(id, { returning: true });
  }
}

// app.ts - Express routes
import express from 'express';
import { UserService } from './user.service';

const app = express();
app.use(express.json());

const userService = new UserService();

app.post('/users', async (req, res) => {
  try {
    const user = await userService.createUser(
      req.body.email,
      req.body.name,
      req.user?.id || 1 // From your auth middleware
    );
    res.json(user);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.get('/users', async (req, res) => {
  try {
    const page = parseInt(req.query.page as string) || 1;
    const limit = parseInt(req.query.limit as string) || 10;
    const result = await userService.getUsers(page, limit);
    res.json(result);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.get('/users/:id', async (req, res) => {
  try {
    const user = await userService.getUserById(parseInt(req.params.id));
    res.json(user);
  } catch (error) {
    res.status(404).json({ error: error.message });
  }
});

app.put('/users/:id', async (req, res) => {
  try {
    const user = await userService.updateUser(
      parseInt(req.params.id),
      req.body,
      req.user?.id || 1
    );
    res.json(user);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.delete('/users/:id', async (req, res) => {
  try {
    await userService.deleteUser(parseInt(req.params.id));
    res.json({ message: 'User deleted' });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Graceful shutdown
process.on('SIGTERM', async () => {
  await connectionManager.shutdown();
  process.exit(0);
});

app.listen(3000, () => {
  console.log('Server running on port 3000');
});

NestJS Integration (Optional)

1. Create a Database Module

// database.module.ts
import { Module, Global, OnModuleDestroy, Inject } from '@nestjs/common';
import { getConnectionManager, ConnectionManager } from '@exdig/db-core';

@Global()
@Module({
  providers: [
    {
      provide: 'DATABASE_CONNECTION',
      useFactory: async () => {
        const connectionManager = getConnectionManager();
        await connectionManager.initialize();
        return connectionManager;
      },
    },
  ],
  exports: ['DATABASE_CONNECTION'],
})
export class DatabaseModule implements OnModuleDestroy {
  constructor(@Inject('DATABASE_CONNECTION') private connectionManager: ConnectionManager) {}

  async onModuleDestroy() {
    await this.connectionManager.shutdown();
  }
}

2. Create Repository Providers

// user.repository.ts
import { Injectable, Inject } from '@nestjs/common';
import { BaseRepository, ConnectionManager, BaseEntity } from '@exdig/db-core';

export interface User extends BaseEntity {
  id: number;
  email: string;
  name: string;
  created_at: Date;
  updated_at: Date;
  is_deleted: boolean;
}

@Injectable()
export class UserRepository extends BaseRepository<User> {
  constructor(@Inject('DATABASE_CONNECTION') connectionManager: ConnectionManager) {
    super(connectionManager, {
      tableName: 'users',
      primaryKey: 'id',
      softDelete: true,
      auditEnabled: true,
    });
  }

  async findByEmail(email: string): Promise<User | null> {
    return this.findOne({ where: { email } });
  }
}

3. Create Service Layer

// user.service.ts
import { Injectable } from '@nestjs/common';
import { UserRepository } from './user.repository';

@Injectable()
export class UserService {
  constructor(private readonly userRepo: UserRepository) {}

  async createUser(email: string, name: string, userId: number) {
    return this.userRepo.create(
      { email, name },
      { userId } // Audit context
    );
  }

  async getUserById(id: number) {
    return this.userRepo.findByIdOrFail(id);
  }

  async getUsers(page: number = 1, limit: number = 10) {
    return this.userRepo.findWithPagination({
      pagination: { page, limit },
      sort: { field: 'created_at', direction: 'DESC' },
    });
  }

  async updateUser(id: number, data: Partial<User>, userId: number) {
    return this.userRepo.updateById(id, data, { userId });
  }

  async deleteUser(id: number) {
    return this.userRepo.deleteById(id, { returning: true });
  }
}

4. Register in App Module

// app.module.ts
import { Module } from '@nestjs/common';
import { DatabaseModule } from './database/database.module';
import { UserRepository } from './users/user.repository';
import { UserService } from './users/user.service';
import { UsersController } from './users/users.controller';

@Module({
  imports: [DatabaseModule],
  controllers: [UsersController],
  providers: [UserRepository, UserService],
})
export class AppModule {}

5. Use in Controller

// users.controller.ts
import { Controller, Get, Post, Put, Delete, Body, Param, Query, Req } from '@nestjs/common';
import { UserService } from './user.service';

@Controller('users')
export class UsersController {
  constructor(private readonly userService: UserService) {}

  @Post()
  async create(@Body() createUserDto: { email: string; name: string }, @Req() req: any) {
    return this.userService.createUser(
      createUserDto.email,
      createUserDto.name,
      req.user.id // From your auth guard
    );
  }

  @Get()
  async findAll(@Query('page') page: number, @Query('limit') limit: number) {
    return this.userService.getUsers(page, limit);
  }

  @Get(':id')
  async findOne(@Param('id') id: number) {
    return this.userService.getUserById(id);
  }

  @Put(':id')
  async update(@Param('id') id: number, @Body() updateDto: any, @Req() req: any) {
    return this.userService.updateUser(id, updateDto, req.user.id);
  }

  @Delete(':id')
  async remove(@Param('id') id: number) {
    return this.userService.deleteUser(id);
  }
}

Advanced Usage

Custom Audit Hooks

import { AuditHook, AuditContext, BaseEntity } from '@exdig/db-core';

class CustomAuditHook implements AuditHook {
  beforeCreate(entity: Partial<BaseEntity>, context?: AuditContext) {
    return {
      ...entity,
      created_at: new Date(),
      created_by: context?.userId,
      // Custom logic
    };
  }
}

const userRepo = new UserRepository(connectionManager);
userRepo.setAuditHook(new CustomAuditHook());

Transactions

const client = await connectionManager.getClient();

try {
  await client.query('BEGIN');

  const user1 = await userRepo.create({ email: '[email protected]' }, undefined, client);
  const user2 = await userRepo.create({ email: '[email protected]' }, undefined, client);

  await client.query('COMMIT');
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  client.release();
}

Complex Queries

// Using filter options
const users = await userRepo.find({
  where: [
    { field: 'email', operator: 'LIKE', value: '%@example.com' },
    { field: 'created_at', operator: '>=', value: new Date('2024-01-01') },
  ],
  sort: [
    { field: 'created_at', direction: 'DESC' },
    { field: 'name', direction: 'ASC' },
  ],
  pagination: { page: 1, limit: 20 },
});

// Using object-based where (simple equality)
const activeUsers = await userRepo.find({
  where: { is_active: true, status: 'verified' },
});

Error Handling

import {
  NotFoundError,
  UniqueConstraintError,
  isUniqueConstraintError,
  isRetryableError,
} from '@exdig/db-core';

try {
  await userRepo.create({ email: '[email protected]' });
} catch (error) {
  if (isUniqueConstraintError(error)) {
    console.log('Email already exists');
  } else if (isRetryableError(error)) {
    // Retry logic handled automatically
    console.log('Transient error occurred');
  } else {
    throw error;
  }
}

Custom Database Driver

import { IDatabaseDriver, DatabaseConfig } from '@exdig/db-core';
import { Pool, PoolClient, QueryResult } from 'pg';
import { Pool as MySQLPool } from 'mysql2/promise';

class MySQLDriver implements IDatabaseDriver {
  createPool(config: DatabaseConfig): MySQLPool {
    // Implement MySQL pool creation
  }

  async createClient(config: DatabaseConfig): Promise<PoolClient> {
    // Implement MySQL client creation
  }

  async query<T>(pool: MySQLPool, text: string, params?: any[]): Promise<QueryResult<T>> {
    // Implement MySQL query execution
  }

  async queryClient<T>(client: PoolClient, text: string, params?: any[]): Promise<QueryResult<T>> {
    // Implement MySQL client query execution
  }
}

const connectionManager = getConnectionManager(new MySQLDriver());

Error Types

• DatabaseError - Base database error
• ConnectionError - Connection-related errors
• QueryError - Query execution errors
• UniqueConstraintError - Unique constraint violations
• ForeignKeyConstraintError - Foreign key constraint violations
• NotFoundError - Record not found
• RetryableError - Transient errors that can be retried
• ValidationError - Data validation errors

TypeScript Support

This package is fully written in TypeScript and provides comprehensive type definitions. All methods are type-safe with generics.

Graceful Shutdown

// In your application shutdown handler
process.on('SIGTERM', async () => {
  await connectionManager.shutdown();
  process.exit(0);
});

process.on('SIGINT', async () => {
  await connectionManager.shutdown();
  process.exit(0);
});

License

MIT

Contributing

Contributions are welcome! Please open an issue or submit a pull request.