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.