NestJS Database Standards

Selection Framework

1. Data Structure Analysis (The "What")

• Structured & Highly Related: Users, Orders, Inventory, Financials.
  • Choice: PostgreSQL (Default).
  • Why: Strict schema validation, ACID transactions, complex generic queries (Joins).
• Unstructured / Polymorphic: Product Catalogs (lots of unique attributes), CMS Content, Raw JSON blobs.
  • Choice: MongoDB.
  • Why: Schema flexibility, fast development speed for flexible data models.
• Time-Series / Metrics: IoT Sensor Data, Stock Prices, Server Logs.
  • Choice: TimescaleDB (Postgres Extension).
  • Why: Compression, hypertable partitioning, rapid ingestion.

2. Access Pattern Analysis (The "How")

• Transactional (OLTP): "User buys items to cart".
  • Requirement: Strong Consistency (ACID). SQL is mandatory.
• Analytical (OLAP): "Dashboard showing sales trends".
  • Requirement: Aggregation speed. Columnar storage (ClickHouse) or Read Replicas.
• High Throughput Write: "1M events/sec".
  • Requirement: Append-only speed. Cassandra / DynamoDB (Leaderless replication).

3. Decision Matrix

Feature Needed	Primary Choice	Alternative
General Purpose App	PostgreSQL	MySQL
Flexible JSON Docs	MongoDB	PostgreSQL (JSONB)
Search Engine	ElasticSearch	PostgreSQL (Full Text)
Financial Transactions	PostgreSQL	(None)

Patterns

• Repository Pattern: Isolate database logic.
  • TypeORM: Inject @InjectRepository(Entity).
  • Prisma: Create a comprehensive PrismaService.
• Abstraction: Services should call Repositories, not raw SQL queries.

Configuration (TypeORM)

• Async Loading: Always use TypeOrmModule.forRootAsync to load secrets from ConfigService.
• Sync: Set synchronize: false in production; use migrations instead.

Scaling & Production

• Read Replicas: Configure separate replication connections (Master for Write, Slaves for Read) in TypeORM/Prisma to distribute load.
• Connection Multiplexing:
  • Problem: Scaling K8s pods to 100+ exhausts DB connection limits (100 pods * 10 connections = 1000 conns).
  • Solution: Use PgBouncer (Postgres) or ProxySQL (MySQL) in transaction mode. Do NOT rely solely on ORM pooling.
• Migrations:
  • NEVER run synchronize: true in production.
  • Execution: Run migrations via a dedicated "init container" or CD job step. Do NOT auto-run inside the main app process on startup (race conditions when scaling to multiple pods).
• Soft Deletes: Use @DeleteDateColumn (TypeORM) or middleware (Prisma) to preserve data integrity.

Architectures (Multi-Tenancy & Sharding)

• Column-Based (SaaS Standard): Single DB, tenant_id column.
  • Scale: High. Isolation: Low.
  • Code: Requires Row-Level Security (RLS) policies or strict Where scopes.
• Schema-Based: One DB, one Schema per Tenant.
  • Scale: Medium. Isolation: Medium. Good for B2B.
• Database-Based: One DB per Tenant.
  • Scale: Low (max ~500 tenants per cluster). Isolation: High.
  • Code: Requires "Connection Switching" middleware. Complex.
• Horizontal Sharding:
  • Logic: Shard massive tables by a key (e.g. user_id) across physical nodes to exceed single-node write limits.
  • Complexity: Extreme. Avoid until >10TB data. Use "Partitioning" first.
• Partioning (Postgres):
  • Strategy: Use native Table Partitioning (e.g., by range/date) for massive tables (Logs, Audit, Events).
  • App Logic: Ensure partition keys (e.g., created_at) are included in WHERE clauses to enable "Partition Pruning".

Migrations & Data Evolution

• Separation:
  • Schema Migrations (DDL): Structural changes (CREATE TABLE, ADD COLUMN). Fast. Run before app deploy.
  • Data Migrations (DML): transforming data (UPDATE users SET name = ...). Slow. Run as background jobs or separate scripts purely to avoid locking tables for too long.
• Zero-Downtime Field Migration (Expand-Contract Pattern):
  1. Expand: Add new column new_field (nullable). Deploy App v1 (Writes to both old and new).
  2. Migrate: Backfill data from old to new in batches (background script).
  3. Contract: Deploy App v2 (Reads/Writes only new). Drop old_field in next schema migration.
• Seeding:
  • Dev: Use factories (@faker-js/faker) to generate mock data.
  • Prod: Only seed static dictionaries (Roles, Countries) using "Upsert" logic to prevent duplicates.

Best Practices

1. Pagination: Mandatory. Use limit/offset or cursor-based pagination.
2. Indexing: Define indexes in code (decorators/schema) for frequently filtered columns (where, order by).
3. Transactions: Use QueryRunner (TypeORM) or $transaction (Prisma) for all multi-step mutations to ensure atomicity.