DEV Community

Cover image for A Unified, Type-Safe Database Adapter System for Multi-DB Applications
bilel salem
bilel salem

Posted on

A Unified, Type-Safe Database Adapter System for Multi-DB Applications

#database #backend #nestjs #designpatterns

Database Adapters System

A flexible, database-agnostic interface that allows your application to work with multiple database types through a unified API.

Table of Contents

Overview

The Database Adapters System provides a generic interface that abstracts database-specific operations, allowing you to:

  • Switch between databases without changing business logic
  • Support multiple databases in the same application
  • Maintain type safety across all database operations
  • Preserve backward compatibility with existing MongoDB code
  • Extend easily to support new database types

Architecture 

┌─────────────────────┐ │ Business Logic │ ├─────────────────────┤ │ IDatabaseService │ ← Generic Interface ├─────────────────────┤ │ DatabaseService │ ← Implementation ├─────────────────────┤ │ Database Adapters │ ├─────────────────────┤ │ MongoDB │ PostgreSQL│ ← Database-specific │ MySQL │ SQLite │ adapters └─────────────────────┘
Enter fullscreen mode Exit fullscreen mode

Core Components

  1. Generic Types (src/common/types/common.types.ts)
- `DatabaseOperator` - Standardized query operators - `DatabaseCondition` - Generic condition structure - `DatabaseQuery` - Complete query specification - `DatabaseAdapter` - Adapter interface contract
Enter fullscreen mode Exit fullscreen mode
  1. Database Service (src/common/database/services/database.service.ts)
- Unified API for all database operations - Adapter management and switching - Backward compatibility layer
Enter fullscreen mode Exit fullscreen mode
  1. Adapters (src/common/database/adapters/)
- `MongoDBAdapter` - MongoDB translation - `PostgreSQLAdapter` - PostgreSQL SQL generation - Extensible for additional databases
Enter fullscreen mode Exit fullscreen mode
  1. Factory (src/common/database/factories/database-adapter.factory.ts)
    • Centralized adapter creation and management
    • Runtime adapter switching support

Supported Databases

Database Status Adapter Class Notes
MongoDB ✅ Full MongoDBAdapter Default, backward compatible
PostgreSQL ✅ Full PostgreSQLAdapter SQL generation with parameters
MySQL 🚧 Planned MySQLAdapter Coming soon
SQLite 🚧 Planned SQLiteAdapter Coming soon
Redis 🚧 Planned RedisAdapter For caching/sessions

Quick Start

1. Basic Usage (MongoDB - Default) 

import { DatabaseService } from 'src/common/database/services/database.service'; @Injectable() export class UserService { constructor(private readonly databaseService: DatabaseService) {} async findActiveUsers() { // Create a simple filter - works with any database const filter = this.databaseService.filterEqual('status', 'active'); return filter; // MongoDB: { status: { $eq: 'active' } } } }
Enter fullscreen mode Exit fullscreen mode

2. Switching to PostgreSQL 

import { DatabaseService } from 'src/common/database/services/database.service'; import { DatabaseAdapterFactory } from 'src/common/database/factories/database-adapter.factory'; @Injectable() export class UserService { constructor( private readonly databaseService: DatabaseService, private readonly adapterFactory: DatabaseAdapterFactory ) { // Switch to PostgreSQL const pgAdapter = this.adapterFactory.createAdapter('postgresql'); this.databaseService.setAdapter(pgAdapter); } async findActiveUsers() { const filter = this.databaseService.filterEqual('status', 'active'); return filter; // PostgreSQL: { whereClause: 'WHERE status = $1', parameters: ['active'] } } }
Enter fullscreen mode Exit fullscreen mode

Usage Examples

Building Complex Queries 

import { DatabaseOperator } from 'src/common/types/common.types'; // Create individual conditions const conditions = [ this.databaseService.createCondition( 'age', DatabaseOperator.GREATER_THAN, 18 ), this.databaseService.createCondition( 'email', DatabaseOperator.CONTAINS, '@company.com' ), this.databaseService.createCondition('status', DatabaseOperator.IN, [ 'active', 'premium', ]), ]; // Build complex query const query = this.databaseService.buildQuery(conditions, 'AND', { limit: 10, offset: 0, sort: [{ field: 'createdAt', direction: 'DESC' }], }); // Execute with current adapter const result = this.databaseService.executeQuery(query);
Enter fullscreen mode Exit fullscreen mode

Common Filter Operations 

// Text search const nameFilter = this.databaseService.filterContain('name', 'John', { caseSensitive: false, fullWord: false, }); // Date range const dateFilter = this.databaseService.filterDateBetween( 'createdAt', 'updatedAt', new Date('2023-01-01'), new Date('2023-12-31') ); // Array operations const categoryFilter = this.databaseService.filterIn('category', [ 'tech', 'business', ]); const excludeFilter = this.databaseService.filterNin('status', [ 'banned', 'deleted', ]); // Existence checks const profileFilter = this.databaseService.filterExists('profile', true); // Numeric comparisons const scoreFilter = this.databaseService.filterGreaterThan('score', 100);
Enter fullscreen mode Exit fullscreen mode

Runtime Adapter Switching 

@Injectable() export class DatabaseManager { constructor( private readonly databaseService: DatabaseService, private readonly adapterFactory: DatabaseAdapterFactory ) {} async switchDatabase(dbType: string) { if (this.adapterFactory.isSupported(dbType)) { const adapter = this.adapterFactory.createAdapter(dbType); this.databaseService.setAdapter(adapter); console.log(`Switched to ${dbType} adapter`); } else { throw new Error(`Unsupported database type: ${dbType}`); } } getSupportedDatabases(): string[] { return this.adapterFactory.getSupportedTypes(); } }
Enter fullscreen mode Exit fullscreen mode

Creating Custom Adapters

1. Implement the DatabaseAdapter Interface 

import { Injectable } from '@nestjs/common'; import { DatabaseAdapter, DatabaseQuery, DatabaseCondition, DatabaseSort, DatabaseOperator, } from 'src/common/types/common.types'; @Injectable() export class MySQLAdapter implements DatabaseAdapter { readonly name = 'mysql'; translateQuery(query: DatabaseQuery): { sql: string; parameters: any[] } { // Implement MySQL-specific query translation const conditions = query.conditions.map(condition => this.translateCondition(condition) ); const whereClause = conditions.length > 0 ? `WHERE ${conditions.map(c => c.condition).join(` ${query.logic || 'AND'} `)}` : ''; const parameters = conditions.flatMap(c => c.parameters); return { sql: whereClause, parameters, }; } translateCondition(condition: DatabaseCondition): { condition: string; parameters: any[]; } { const { field, operator, value } = condition; switch (operator) { case DatabaseOperator.EQUAL: return { condition: `${field} = ?`, parameters: [value] }; case DatabaseOperator.GREATER_THAN: return { condition: `${field} > ?`, parameters: [value] }; case DatabaseOperator.CONTAINS: return { condition: `${field} LIKE ?`, parameters: [`%${value}%`], }; // Add more operators as needed default: throw new Error(`Unsupported operator: ${operator}`); } } translateSort(sort: DatabaseSort[]): string { if (sort.length === 0) return ''; const orderClauses = sort.map(s => `${s.field} ${s.direction}`); return `ORDER BY ${orderClauses.join(', ')}`; } }
Enter fullscreen mode Exit fullscreen mode

2. Register the Adapter 

// In your module or service import { MySQLAdapter } from './adapters/mysql.adapter'; @Injectable() export class DatabaseSetupService { constructor(private readonly adapterFactory: DatabaseAdapterFactory) { // Register custom adapter this.adapterFactory.registerAdapter('mysql', () => new MySQLAdapter()); } }
Enter fullscreen mode Exit fullscreen mode

3. Use the Custom Adapter 

const mysqlAdapter = this.adapterFactory.createAdapter('mysql'); this.databaseService.setAdapter(mysqlAdapter); // Now all operations use MySQL adapter const filter = this.databaseService.filterEqual('id', 123); // Output: { sql: 'WHERE id = ?', parameters: [123] }
Enter fullscreen mode Exit fullscreen mode

Migration Guide

From MongoDB-Specific to Generic

Before (MongoDB-specific) 

// Old way - MongoDB only const mongoFilter = { status: { $eq: 'active' }, age: { $gt: 18 }, email: { $regex: /.*@company\.com/i }, };
Enter fullscreen mode Exit fullscreen mode

After (Database-agnostic) 

// New way - works with any database const conditions = [ this.databaseService.createCondition( 'status', DatabaseOperator.EQUAL, 'active' ), this.databaseService.createCondition( 'age', DatabaseOperator.GREATER_THAN, 18 ), this.databaseService.createCondition( 'email', DatabaseOperator.CONTAINS, '@company.com' ), ]; const query = this.databaseService.buildQuery(conditions, 'AND'); const filter = this.databaseService.executeQuery(query);
Enter fullscreen mode Exit fullscreen mode

Backward Compatibility

All existing MongoDB code continues to work:

// These still work unchanged const legacyFilter = this.databaseService.filterEqual('status', 'active'); const legacySearch = this.databaseService.filterContain('name', 'John'); const legacyDate = this.databaseService.filterDateBetween( 'start', 'end', date1, date2 );
Enter fullscreen mode Exit fullscreen mode

API Reference

DatabaseOperator Enum 

enum DatabaseOperator { EQUAL = 'eq', NOT_EQUAL = 'ne', GREATER_THAN = 'gt', GREATER_THAN_OR_EQUAL = 'gte', LESS_THAN = 'lt', LESS_THAN_OR_EQUAL = 'lte', IN = 'in', NOT_IN = 'nin', CONTAINS = 'contains', STARTS_WITH = 'startsWith', ENDS_WITH = 'endsWith', REGEX = 'regex', EXISTS = 'exists', IS_NULL = 'isNull', BETWEEN = 'between', }
Enter fullscreen mode Exit fullscreen mode

IDatabaseService Interface

Generic Query Methods

  • createCondition<T>(field, operator, value, options?) - Create a condition
  • buildQuery(conditions, logic?, options?) - Build complex query
  • executeQuery(query) - Execute query with current adapter

Convenience Methods

  • filterEqual<T>(field, value) - Equal filter
  • filterNotEqual<T>(field, value) - Not equal filter
  • filterContain(field, value, options?) - Text contains filter
  • filterIn<T>(field, values) - IN filter
  • filterNin<T>(field, values) - NOT IN filter
  • filterDateBetween(start, end, startDate, endDate) - Date range filter
  • filterGreaterThan<T>(field, value) - Greater than filter
  • filterLessThan<T>(field, value) - Less than filter
  • filterExists(field, exists) - Existence filter

Adapter Management

  • setAdapter(adapter) - Set database adapter
  • getAdapter() - Get current adapter
  • getSupportedDatabases() - List supported databases

DatabaseAdapterFactory

  • createAdapter(type) - Create adapter by type
  • getSupportedTypes() - List all supported types
  • isSupported(type) - Check if type is supported
  • registerAdapter(type, factory) - Register custom adapter

Best Practices

1. Adapter Selection Strategy 

// Environment-based adapter selection const dbType = process.env.DATABASE_TYPE || 'mongodb'; const adapter = this.adapterFactory.createAdapter(dbType); this.databaseService.setAdapter(adapter);
Enter fullscreen mode Exit fullscreen mode

2. Error Handling 

try { const adapter = this.adapterFactory.createAdapter(dbType); this.databaseService.setAdapter(adapter); } catch (error) { console.error(`Failed to initialize ${dbType} adapter:`, error.message); // Fallback to default const defaultAdapter = this.adapterFactory.createAdapter('mongodb'); this.databaseService.setAdapter(defaultAdapter); }
Enter fullscreen mode Exit fullscreen mode

3. Type Safety 

// Use specific types for better type safety interface UserFilter { status: 'active' | 'inactive'; age: number; email: string; } const userCondition = this.databaseService.createCondition< UserFilter['status'] >('status', DatabaseOperator.EQUAL, 'active');
Enter fullscreen mode Exit fullscreen mode

4. Performance Considerations 

// Cache adapters for frequently used databases const adapterCache = new Map<string, DatabaseAdapter>(); function getCachedAdapter(type: string): DatabaseAdapter { if (!adapterCache.has(type)) { adapterCache.set(type, this.adapterFactory.createAdapter(type)); } return adapterCache.get(type)!; }
Enter fullscreen mode Exit fullscreen mode

5. Testing Different Adapters 

describe('Database Operations', () => { const testCases = ['mongodb', 'postgresql']; testCases.forEach(dbType => { describe(`with ${dbType}`, () => { beforeEach(() => { const adapter = adapterFactory.createAdapter(dbType); databaseService.setAdapter(adapter); }); it('should filter active users', () => { const filter = databaseService.filterEqual('status', 'active'); expect(filter).toBeDefined(); // Add adapter-specific assertions }); }); }); });
Enter fullscreen mode Exit fullscreen mode

Open Source & Contributing

This Database Adapter System is being released as an open-source project on GitHub to encourage collaboration and community-driven improvement.

🔗 GitHub Repository: [Coming Soon / https://github.com/bilelsalemdev/universal-db-adapter/tree/main]

Everyone is welcome to contribute — whether it's:

  • Writing unit and integration tests
  • Completing or improving existing adapters (e.g., MySQL, SQLite, Redis)
  • Suggesting enhancements or new features
  • Reporting issues or edge cases
  • Improving documentation and usage examples

If you're interested in helping shape a flexible, database-agnostic abstraction layer for modern applications, feel free to fork the repo, submit PRs, or start a discussion.

Let’s build it together! 💪

Top comments (0)