Database Selection Dilemma: Beyond SQL vs NoSQL for Modern Software Engineering

export interface DatabaseRepository<T, ID> {
  findById(id: ID): Promise<T | null>;
  findMany(filter: Partial<T>, limit?: number): Promise<T[]>;
  create(entity: T): Promise<ID>;
  update(id: ID, changes: Partial<T>): Promise<boolean>;
  delete(id: ID): Promise<boolean>;
  executeInTransaction<T>(fn: () => Promise<T>): Promise<T>;
}

import { Pool, PoolClient } from 'pg';
import { MongoClient, ObjectId } from 'mongodb';

export class PostgresRepository<T extends { id: string }> implements DatabaseRepository<T, string> {
  private pool: Pool;

  constructor(connectionString: string) {
    this.pool = new Pool({
      connectionString,
      max: 20,
      idleTimeoutMillis: 30000,
      connectionTimeoutMillis: 2000,
    });
  }

  async findById(id: string): Promise<T | null> {
    const res = await this.pool.query(`SELECT * FROM entities WHERE id = $1`, [id]);
    return res.rows[0] || null;
  }

  async executeInTransaction<T>(fn: () => Promise<T>): Promise<T> {
    const client: PoolClient = await this.pool.connect()

; try { await client.query('BEGIN'); const result = await fn(); await client.query('COMMIT'); return result; } catch (error) { await client.query('ROLLBACK'); throw error; } finally { client.release(); } } }

export class MongoRepository<T extends { _id: ObjectId }> implements DatabaseRepository<T, string> { private collection: ReturnType<MongoClient['db']>['collection'];

constructor(uri: string, dbName: string, collectionName: string) { const client = new MongoClient(uri); const db = client.db(dbName); this.collection = db.collection<T>(collectionName); }

async findById(id: string): Promise<T | null> { return this.collection.findOne({ _id: new ObjectId(id) }); }

async executeInTransaction<T>(fn: () => Promise<T>): Promise<T> { const session = this.collection.client.startSession(); try { return await session.withTransaction(async () => fn(), { readConcern: { level: 'snapshot' }, writeConcern: { w: 'majority' }, }); } finally { await session.endSession(); } } }

### Step 4: Enforce Runtime Schema Validation
Even in "schema-less" stores, unvalidated mutations cause silent corruption. Integrate a validation layer that runs before persistence.

```typescript
import { z } from 'zod';

const EntitySchema = z.object({
  id: z.string().uuid(),
  status: z.enum(['active', 'archived', 'pending']),
  metadata: z.record(z.unknown()).optional(),
  createdAt: z.coerce.date(),
});

export function validateEntity(payload: unknown): z.infer<typeof EntitySchema> {
  return EntitySchema.parse(payload);
}

Architecture Decisions & Rationale

• Abstraction over inheritance: The repository interface isolates business logic from connection pooling, retry logic, and transaction semantics. This enables A/B testing storage engines without rewriting service layers.
• Explicit transaction boundaries: Both adapters wrap critical paths in explicit transaction scopes. This prevents partial writes and ensures consistency guarantees are visible in code, not hidden in framework magic.
• Connection pool sizing: Relational pools are capped to prevent thread exhaustion and lock contention. NoSQL drivers use built-in pooling with maxIdleTime to handle ephemeral cloud networking.
• Validation at the boundary: Zod schemas run before serialization, catching malformed payloads early. This reduces downstream indexing failures and replication skew.

Pitfall Guide

1. Treating "Schema-less" as "No Validation"

Mistake: Storing arbitrary JSON without runtime checks, leading to inconsistent field types, missing required attributes, and index corruption. Fix: Enforce structural contracts at the application boundary. Use JSON Schema, Zod, or class-validator. Validate before serialization and reject non-conforming payloads with explicit error codes.

2. Ignoring CAP Theorem Trade-offs in Distributed NoSQL

Mistake: Assuming eventual consistency is free. Under network partitions, reads may return stale data, causing business logic failures in inventory, billing, or session management. Fix: Tune consistency levels per query. Use strong reads for financial/state-critical paths. Document consistency expectations in API contracts. Implement idempotency keys to handle duplicate writes during partition recovery.

3. Over-Indexing in Document/Key-Value Stores

Mistake: Creating indexes for every query filter, causing write amplification, storage bloat, and compaction overhead in LSM-based engines. Fix: Index only high-cardinality query patterns. Monitor index usage metrics. Use composite indexes aligned with query sort order. Drop unused indexes quarterly.

4. Forcing Relational JOINs in Document Stores

Mistake: Attempting multi-collection joins in MongoDB or DynamoDB, resulting in application-level N+1 queries, high latency, and cache invalidation storms. Fix: Denormalize strategically. Embed frequently accessed related data. Use application-level joins only for analytical or non-critical paths. Maintain reference integrity via event-driven sync or change streams.

5. Neglecting Connection Pool Exhaustion

Mistake: Opening connections per request without pooling or timeout limits, causing file descriptor leaks and database crashes under traffic spikes. Fix: Configure max connections based on CPU cores and query duration. Set idle timeouts and connection limits. Implement circuit breakers and retry backoff. Monitor active connections in observability dashboards.

6. Assuming Distributed SQL Eliminates Latency

Mistake: Deploying CockroachDB or YugabyteDB without understanding placement rules and cross-region replication lag, expecting single-node performance. Fix: Define data placement policies. Keep hot partitions co-located with compute. Use follower reads for non-critical queries. Measure p99 latency across regions before committing to distributed topology.

7. Skipping Backup and Point-in-Time Recovery Testing

Mistake: Relying on automated snapshots without validating restore procedures, leading to data loss during corruption or ransomware events. Fix: Schedule logical and physical backups. Test restore workflows quarterly. Enable point-in-time recovery where supported. Store backups in cross-region immutable storage.

Production Bundle

Action Checklist

• Map workload topology: Define read/write ratio, consistency tolerance, and schema volatility before selection.
• Implement repository abstraction: Decouple business logic from storage mechanics using a unified interface.
• Enforce runtime validation: Apply schema contracts at the application boundary to prevent silent corruption.
• Configure connection pools: Set max connections, idle timeouts, and retry backoff based on infrastructure limits.
• Tune consistency per query: Use strong reads for state-critical paths, eventual for high-throughput ingestion.
• Index strategically: Create composite indexes aligned with query filters; drop unused indexes quarterly.
• Test backup/restore: Validate point-in-time recovery and cross-region replication monthly.
• Monitor p99 latency: Track query execution plans, connection utilization, and replication lag in production.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Financial transactions, audit logs, complex reporting	Relational (PostgreSQL/MySQL)	ACID compliance, deterministic query planning, mature tooling	Moderate infrastructure cost; lower operational risk
IoT telemetry, session stores, high-velocity ingestion	Document/Key-Value (MongoDB/DynamoDB)	Write scalability, flexible schema, horizontal partitioning	Higher storage cost; reduced dev cycle friction
Multi-region active-active with strong consistency	Distributed SQL (CockroachDB/YugabyteDB)	Serializable isolation with automatic sharding	Premium licensing; higher network latency overhead
Real-time analytics, time-series workloads	Columnar/Time-Series (ClickHouse/Timescale)	Optimized for aggregate queries, compression, retention policies	Lower compute cost; requires ETL pipeline maintenance

Configuration Template

# .env.production
DB_RELATIONAL_URL=postgresql://user:pass@db-host:5432/app_db?sslmode=require
DB_RELATIONAL_POOL_MAX=20
DB_RELATIONAL_IDLE_TIMEOUT=30000

DB_NOSQL_URI=mongodb://user:pass@nosql-host:27017/app_db?retryWrites=true&w=majority
DB_NOSQL_POOL_SIZE=50
DB_NOSQL_SERVER_SELECTION_TIMEOUT=5000

VALIDATION_STRICT_MODE=true
OBSERVABILITY_METRICS_PORT=9090

// src/config/database.ts
import { z } from 'zod';
import { PostgresRepository } from '../repositories/PostgresRepository';
import { MongoRepository } from '../repositories/MongoRepository';

const EnvSchema = z.object({
  DB_RELATIONAL_URL: z.string().url(),
  DB_RELATIONAL_POOL_MAX: z.coerce.number().min(5).max(50),
  DB_NOSQL_URI: z.string(),
  DB_NOSQL_POOL_SIZE: z.coerce.number().min(10).max(100),
});

export function initializeRepositories() {
  const env = EnvSchema.parse(process.env);
  
  const relational = new PostgresRepository(env.DB_RELATIONAL_URL);
  relational.pool.options.max = env.DB_RELATIONAL_POOL_MAX;
  
  const nosql = new MongoRepository(env.DB_NOSQL_URI, 'app_db', 'entities');
  
  return { relational, nosql };
}

Quick Start Guide

1. Install dependencies: npm install pg mongodb zod
2. Define your entity schema: Create a Zod or JSON Schema contract matching your domain model.
3. Initialize repositories: Import the configuration template, set environment variables, and instantiate PostgresRepository or MongoRepository.
4. Wire to service layer: Inject the repository interface into your business logic. Use executeInTransaction for state mutations.
5. Validate and deploy: Run load tests with autocannon or k6. Monitor p99 latency, connection pool usage, and index hit ratios. Adjust pool sizes and consistency levels based on telemetry.