From Fragile to Fortress: A Real-World Database Adapter Deep Dive
At 2 AM, our payment service went down because someone upgraded PostgreSQL, and our app couldn't reconnect. 15 minutes to fix. Weeks to calculate the damage.
Three months later, our 12-minute test suite was making developers skip tests entirely. Bugs made it to staging.
Both problems had the same root cause: tight coupling to our database.
This tutorial shows you how to build a database adapter pattern that decouples your business logic from database implementation.
In the finished setup, your application:
Runs the same business logic across PostgreSQL, SQLite, or MongoDB Tests in milliseconds using in-memory mocks (no Docker) Recovers automatically from connection failures Switches databases with a one-line config change
Prerequisites
Node.js 18+
Basic TypeScript knowledge
Understanding of async/await and Promises
Familiarity with SQL databases
Code editor (VS Code, Cursor, etc.)
The full source code is available in this GitHub repository.
The Problem: Tight Coupling
Most basic applications directly import their database library. Here's what that typically looks like:
import knex from 'knex';
const db = knex({ client: 'postgresql', connection: DATABASE_URL });
async function getOrders() {
return db('orders').where('status', 'pending').select('*');
}
This approach locks you into PostgreSQL and Knex's API. Your business logic now depends on specific database implementation details...
The Solution: Abstraction Through Adapters
: Promise<any>;
close(): Promise<void>;
}
This interface becomes the contract between your application and any database implementation you choose."
Business logic needs to run queries and close connections. Nothing about Knex, connection pools, or retry logic.
Simple Adapter (Start Here)
The base adapter implements the interface and provides a structure for concrete implementations. Start with the simplest version possible.
abstract class DbAdapter<T> implements Database {
protected instance: T | null = null;
async initialize(): Promise<void> {
this.instance = this.createInstance();
await this.testConnection();
}
protected abstract createInstance(): T;
protected abstract testConnection(): Promise<void>;
abstract query(sql: string, params?: any[]): Promise<any>;
abstract close(): Promise<void>;
}
This establishes the pattern: concrete adapters focus on database-specific code, while shared infrastructure lives in the base class.
Now add resilience:
Once the basic pattern works, layer in resilience. This version adds exponential backoff.
abstract class DbAdapter<T> implements Database {
protected instance: T | null = null;
protected maxRetries = 5;
async initialize(): Promise<void> {
for (let attempt = 0; attempt < this.maxRetries; attempt++) {
try {
this.instance = this.createInstance();
await this.testConnection();
return; // Success!
} catch (error) {
if (attempt === this.maxRetries - 1) throw error;
const delay = 1000 * Math.pow(2, attempt); // 1s, 2s, 4s, 8s, 16s
await new Promise(resolve => setTimeout(resolve, delay));
}
}
}
protected abstract createInstance(): T;
protected abstract testConnection(): Promise<void>;
}
Write this retry logic once in the base class, and every adapter inherits it automatically. A database restart that would have crashed your app now triggers a 10-second retry sequence..
Write it once, inherit it everywhere.
Concrete Implementation
Now create a specific adapter for your database. Here's a Knex adapter that connects to PostgreSQL
class KnexAdapter extends DbAdapter<Knex> {
constructor(private config: Knex.Config) {
super();
}
protected createInstance(): Knex {
return knex(this.config);
}
protected async testConnection(): Promise<void> {
await this.instance!.raw('SELECT 1');
}
async query(sql: string, params: any[] = []): Promise<any> {
if (!this.instance) throw new Error('Database not initialized');
return this.instance.raw(sql, params);
}
async close(): Promise<void> {
await this.instance?.destroy();
}
}
The adapter focuses solely on Knex-specific code... Adding support for a different database means writing another 30-line adapter, not rewriting your entire application.
Environment Flexibility: Same Code, Different Databases
The beauty of this pattern emerges when you need different databases in different environments. Here's how the configuration might look:
// config/database.ts
export async function createDatabase() {
const manager = new DatabaseManager();
if (process.env.NODE_ENV === 'test') {
return manager.createDatabase(MockAdapter);
}
return manager.createDatabase(KnexAdapter);
}
// service.ts - NEVER changes
async function getOrders() {
const db = await dbManager.getDatabase();
return db.query('SELECT * FROM orders');
}
Development: Local PostgreSQL
CI/CD: In-memory mock (fast tests)
Production: PostgreSQL cluster
Your business logic calls getOrders() the same way regardless of environment... Same code, zero conditionals, different databases.
Testing Without Real Databases
The adapter pattern's biggest payoff comes during testing. Instead of spinning up Docker containers, create an in-memory mock adapter:
class MockAdapter extends DbAdapter<Map<string, any[]>> {
private data = new Map<string, any[]>();
// Seed test data
seed(table: string, rows: any[]) {
this.data.set(table, rows);
}
protected createInstance() {
return this.data;
}
protected async testConnection() {
// Always succeeds
}
async query(sql: string): Promise<any> {
if (sql.includes('SELECT') && sql.includes('orders')) {
return this.data.get('orders') || [];
}
return [];
}
async close() {
this.data.clear();
}
}
// Tests
describe('OrderService', () => {
beforeEach(() => {
const mock = new MockAdapter();
mock.seed('orders', [
{ id: 1, status: 'pending' },
{ id: 2, status: 'completed' }
]);
dbManager.createDatabase(() => mock);
});
it('gets pending orders', async () => {
const orders = await orderService.getPending();
expect(orders[0].status).toBe('pending');
});
});
This test runs in milliseconds because there's no database, no Docker, no network calls. The test validates your business logic, not your database configuration.
Testing strategy:
Business logic → MockAdapter (fast, run constantly)
Adapter behavior → Real database (slow, run in CI)
Why Not Just Use an ORM?
"Doesn't TypeORM/Prisma already abstract databases?"
Yes, but ORMs abstract the query language, not the connection layer. You still can't:
Easily swap ORMs mid-project
Test without a database (unless you mock the entire ORM)
Handle connection resilience your way
Use different databases per environment cleanly
The adapter pattern works WITH ORMs:
If you're already using TypeORM, you can wrap it in an adapter to get the benefits of both worlds:
class TypeORMAdapter extends DbAdapter<DataSource> {
protected createInstance(): DataSource {
return new DataSource(this.config);
}
async query(sql: string, params?: any[]): Promise<any> {
return this.instance!.query(sql, params);
}
}
Now you get TypeORM's entity system and migrations, plus the adapter pattern's retry logic, testing flexibility, and environment management.
Switching Databases: The Reality
Within the same type (PostgreSQL → MySQL):
✓ Write 30 lines of adapter code
✓ Change one config line
✓ Minimal business logic changes
Between types (SQL → NoSQL):
// Old (Postgres):
await db.query('SELECT * FROM orders WHERE status = ?', ['pending']);
// New (Mongo):
await db.query('orders', { status: 'pending' }); // Different signature!
You'll need to update your query layer. But you're still saving 80% of the work:
Connection/initialization logic: shared
Retry/resilience logic: shared
State management: shared
Testing infrastructure: shared
The adapter makes switching databases possible, not effortless.
Common Mistakes (And How to Avoid Them)
Mistake #1: Not Initializing Before Server Start
If your server starts accepting requests before the database is ready, the first few requests will fail:
// ❌ Wrong
app.listen(3000);
await dbManager.createDatabase(KnexAdapter);
// ✅ Right
await dbManager.createDatabase(KnexAdapter);
app.listen(3000);
Always initialize your database connection before your server starts listening. This ensures the database is ready when the first request arrives.
Mistake #2: Mixing Adapter and Direct Access
Accessing the underlying database instance directly bypasses all the adapter's benefits:
// ❌ Wrong - defeats the purpose
const db = await dbManager.getDatabase();
const knexInstance = db.instance; // Accessing internals!
// ✅ Right
const db = await dbManager.getDatabase();
await db.query('SELECT * FROM orders');
Stay at the abstraction layer. If you find yourself needing direct access, extend the adapter interface instead.
Mistake #3: Forgetting to Close Connections
Open database connections consume resources. Always clean up during application shutdown:
// ✅ Always close in shutdown handler
process.on('SIGTERM', async () => {
const db = await dbManager.getDatabase();
await db.close();
process.exit(0);
});
This ensures graceful shutdown and prevents connection leaks. Without this, your database might hit connection limits over time.
Mistake #4: Testing Everything With Real Databases
Real databases make tests slow and brittle. Use mocks for business logic tests:
// ❌ Slow - 12 minute test suite
describe('OrderService', () => {
beforeEach(() => setupRealPostgres());
// Every test hits real database
});
// ✅ Fast - millisecond test suite
describe('OrderService', () => {
beforeEach(() => dbManager.createDatabase(MockAdapter));
// Test business logic, not database
});
Reserve real database tests for integration tests that run in CI. Your unit tests should be fast enough to run on every save.
What This Pattern Doesn't Solve
This adapter handles:
✓ Database abstraction
✓ Environment flexibility
✓ Fast testing
✓ Connection resilience
Doesn't handle:
✗ Transaction management → Part 2 (next post)
✗ Connection pooling → Use PgBouncer (infrastructure)
✗ Query optimization → Different concern
✗ Schema migrations → Use migration tools
Each concern gets its own layer. This is your foundation.
What's Next: Transactions
We've solved abstraction and resilience. But there's a bigger problem lurking in multi-step operations:
async function transferMoney(from: string, to: string, amount: number) {
const db = await dbManager.getDatabase();
await db.query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [amount, from]);
// Connection fails here
await db.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [amount, to]);
}
If the connection fails between these two queries, money gets deducted from one account but never added to the other. The adapter handles reconnection, but it doesn't handle consistency. That's where transactional decorators come in.
Part 2: Transactional Decorators
ACID guarantees for multi-step operations
Automatic rollback on failures
Building on this adapter foundation
Each layer builds on the previous one.
Conclusion
Your database is an implementation detail. Business logic shouldn't care if it's PostgreSQL, MongoDB, or a mock.
The adapter pattern gives you:
Freedom to change databases (30 minutes vs. 30 days)
Fast tests without infrastructure (milliseconds vs. minutes)
Environment independence (same code everywhere)
Automatic recovery (connection failures handled gracefully)
Your first step: Clone the repo and run npm test. See how fast tests can be. Then adapt it to your own project.
Found this useful? Star the repo and share with your team. Questions? Open an issue on GitHub or drop them in the comments.
The best abstractions make you forget they exist. When your business logic just works—across environments, databases, and test scenarios—that's when you got it right.
This is Part 1 of the Database Reliability series. Next: Transactional Decorators for atomic operations.
