Skip to content
NestJS Database logo

NestJS Database

Data access patterns, Scaling, Migrations, and ORM selection.

ngxtm/devkit0installs8stars

SKILL.md

Full skill instructions

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 NeededPrimary ChoiceAlternative
General Purpose AppPostgreSQLMySQL
Flexible JSON DocsMongoDBPostgreSQL (JSONB)
Search EngineElasticSearchPostgreSQL (Full Text)
Financial TransactionsPostgreSQL(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.

More skills from ngxtm

Implement Command Query Responsibility Segregation for scalable architectures. Use when separating read and write models, optimizing query performance, or building event-sourced systems.

8 0
View
GraalVM Native logo
ngxtm/devkit

GraalVM Native

Native image compilation, reflection configuration, and build optimization.

8 0
View
algolia-search logo
ngxtm/devkit

algolia-search

Expert patterns for Algolia search implementation, indexing strategies, React InstantSearch, and relevance tuning Use when: adding search to, algolia, instantsearch, search api, search functionality.

8 0
View
billing-automation logo
ngxtm/devkit

billing-automation

Build automated billing systems for recurring payments, invoicing, subscription lifecycle, and dunning management. Use when implementing subscription billing, automating invoicing, or managing recu...

8 0
View

Optimize Bazel builds for large-scale monorepos. Use when configuring Bazel, implementing remote execution, or optimizing build performance for enterprise codebases.

8 0
View

Automate Basecamp project management, to-dos, messages, people, and to-do list organization via Rube MCP (Composio). Always search tools first for current schemas.

8 0
View

Expert guide for Unreal Engine 5.x C++ development, covering UObject hygiene, performance patterns, and best practices.

8 0
View

Automate Outlook Calendar tasks via Rube MCP (Composio): create events, manage attendees, find meeting times, and handle invitations. Always search tools first for current schemas.

8 0
View
angular-migration logo
ngxtm/devkit

angular-migration

Migrate from AngularJS to Angular using hybrid mode, incremental component rewriting, and dependency injection updates. Use when upgrading AngularJS applications, planning framework migrations, or ...

8 0
View
ai-product logo
ngxtm/devkit

ai-product

Every product will be AI-powered. The question is whether you'll build it right or ship a demo that falls apart in production. This skill covers LLM integration patterns, RAG architecture, prompt ...

8 0
View

Popular AI tools

Kaiber logo
Video

Kaiber

Generate, edit, and beat-sync AI video with leading models in one workspace.

Paid
View
Vimcal logo
Productivity

Vimcal

The world's fastest calendar for remote work

Free
View

Transform Your Design with AI Designer by ImgCreator.ai

Freemium
View
Akool AI logo
Content & writing

Akool AI

Revolutionizing Video Production with AI-Powered Creativity

Paid
View

Extend an image past the frame and let AI fill the new aspect ratio.

Freemium
View
StarByFace logo
Security

StarByFace

Discover your celebrity doppelgänger with StarByFace!

Free
View
C

ChainClarity explains 700+ crypto whitepapers in plain English, with layered summaries, comparisons, research tools, alerts, and a $4.99 Pro plan.

Freemium
View
Opus Clip logo
Coding & apps

Opus Clip

Opus.ai: Revolutionize Your Web Experience

Free
View