Module 6: Database Integration

NoSQL: MongoDB & Mongoose

MongoDB is a document-oriented database that persists data in BSON format (Binary JSON). Unlike relational rows with fixed columns, MongoDB documents are dynamic objects capable of housing nested subdocuments and arrays.

Relational Database               MongoDB Equivalent
──────────────────────────────────────────────────────────
Database                   ───▶   Database
Table                      ───▶   Collection
Row                        ───▶   Document (BSON)
Column                     ───▶   Field
Primary Key (id)           ───▶   _id (ObjectId: 12-byte hex)
JOIN                       ───▶   $lookup / Mongoose populate

MongoDB Document Structures & Indexing

1. Embedding vs. Referencing:
   • Embedding (Denormalization): Embed subdocuments when data is accessed together, has a 1-to-few relationship, and rarely grows indefinitely (e.g., street address on a user profile).
   • Referencing (Normalization): Store an array of ObjectId references when data has 1-to-many or many-to-many relationships, is updated independently, or risks breaching the 16MB document size limit (e.g., an author with 100,000 blog comments).
2. Indexing: Without indexes, MongoDB executes a Collection Scan (COLLSCAN), checking every document sequentially. An index creates a sorted B-Tree data structure enabling an Index Scan (IXSCAN).
   • Single Field Index: Efficient for direct equality or range lookups ({ email: 1 }).
   • Compound Index: Orders data across multiple keys ({ status: 1, createdAt: -1 }). Equality, Sort, Range (ESR rule) dictates property ordering.
   • Unique Index: Prevents duplicate keys at the storage engine level ({ sparse: true, unique: true }).
   • TTL Index: Automatically deletes documents after an elapsed timeframe (e.g., password reset tokens).

Object Data Modeling (ODM) with Mongoose

Mongoose enforces type casting, validation, query building, and lifecycle hooks on top of the MongoDB native driver.

Schemas, Models & Native Validations

JavaScript

import mongoose from 'mongoose';

const postSchema = new mongoose.Schema(
  {
    title: {
      type: String,
      required: [true, 'Post title is required'],
      trim: true,
      minlength: [5, 'Title must be at least 5 characters'],
      maxlength: [120, 'Title cannot exceed 120 characters']
    },
    slug: {
      type: String,
      unique: true,
      lowercase: true
    },
    content: {
      type: String,
      required: true
    },
    author: {
      type: mongoose.Schema.Types.ObjectId,
      ref: 'User', // Reference to the User model
      required: true,
      index: true
    },
    tags: [{ type: String, trim: true }],
    isPublished: {
      type: Boolean,
      default: false
    }
  },
  {
    timestamps: true, // Auto-generates createdAt and updatedAt
    toJSON: { virtuals: true },
    toObject: { virtuals: true }
  }
);

// Compound Index: Optimizes queries fetching published posts sorted by date
postSchema.index({ isPublished: 1, createdAt: -1 });

export const Post = mongoose.model('Post', postSchema);

Middleware (Hooks)

Mongoose hooks intercept execution at the document or query level.

• Document Middleware: this refers to the document instance. Supported actions: init, validate, save, deleteOne.
• Query Middleware: this refers to the Query object, not the document. Supported actions: find, findOne, findOneAndUpdate, updateMany.

JavaScript

// Pre-save hook: Generate URL slug before document persistence
postSchema.pre('save', function (next) {
  if (this.isModified('title')) {
    this.slug = this.title
      .toLowerCase()
      .replace(/[^\w ]+/g, '')
      .replace(/ +/g, '-');
  }
  next();
});

// Query Middleware: Automatically filter out soft-deleted items
postSchema.pre(/^find/, function (next) {
  // 'this' is the active query object
  this.where({ isDeleted: { $ne: true } });
  next();
});

Population

Population replaces specified paths in a document with real documents from another collection (similar to an application-level foreign key join).

JavaScript

// Retrieve post and populate author profile, omitting sensitive credentials
const post = await Post.findById(postId)
  .populate('author', 'name email avatar')
  .lean(); // .lean() skips hydrating Mongoose Documents, returning raw plain JS objects (4-5x faster)

Relational Databases: PostgreSQL & MySQL

Relational Database Management Systems (RDBMS) organize data into structured, tabular schemas governed by ACID transactions and referential integrity constraints.

       Table: users                           Table: orders
┌────┬───────────────┬──────────────┐   ┌────┬─────────┬────────┬────────────┐
│ id │ email (UQ)    │ name         │   │ id │ user_id │ total  │ status     │
├────┼───────────────┼──────────────┤   ├────┼─────────┼────────┼────────────┤
│ 1  │ alex@work.com │ Alex Rivera  │◀──┼─ 1 │    1    │ 129.50 │ DELIVERED  │
│ 2  │ sam@dev.io    │ Sam Taylor   │   │ 2  │    1    │  45.00 │ PROCESSING │
└────┴───────────────┴──────────────┘   └────┴─────────┴────────┴────────────┘
                                        Foreign Key: orders.user_id ──▶ users.id

Relational Schema Design & Foreign Keys

• Normalization:
  • 1NF: Atomicity of data (no arrays as scalar values).
  • 2NF: Elimination of partial dependency (all non-key attributes rely on the whole primary key).
  • 3NF: Elimination of transitive dependency (non-key attributes depend only on the primary key).
• Foreign Keys (FK): Maintain relational integrity across tables.
  • ON DELETE CASCADE: Deleting a parent row automatically purges associated children.
  • ON DELETE RESTRICT / NO ACTION: Rejects parent deletion if child references exist.
  • ON DELETE SET NULL: Sets child FK columns to NULL on parent removal.

Transactions & ACID Guarantees

Transactions guarantee that multiple SQL operations succeed or fail as an indivisible unit:

• Atomicity: All operations succeed, or the entire batch rolls back.
• Consistency: Constraints (uniques, checks, FKs) are verified before committing.
• Isolation: Uncommitted state changes remain invisible to concurrent operations (controlled by isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable).
• Durability: Committed transactions persist through sudden power failures or crashes (Write-Ahead Logging / WAL).

Modern ORMs & Query Builders

While TypeORM uses traditional OOP decorators with Active Record / Data Mapper patterns, modern Node.js environments favor Prisma (type-safe, generated client) and Drizzle ORM (zero-overhead, SQL-like query builder).

Option 1: Prisma ORM

Prisma uses a custom declarative schema file (schema.prisma), which generates an end-to-end type-safe TypeScript/JavaScript client.

Code snippet

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

enum OrderStatus {
  PENDING
  PAID
  CANCELLED
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  orders    Order[]
  createdAt DateTime @default(now())

  @@map("users")
}

model Order {
  id        Int         @id @default(autoincrement())
  total     Decimal     @db.Decimal(10, 2)
  status    OrderStatus @default(PENDING)
  userId    Int
  user      User        @relation(fields: [userId], references: [id], onDelete: Cascade)
  createdAt DateTime    @default(now())

  @@index([userId])
  @@map("orders")
}

JavaScript

// Querying & ACID Transactions via Prisma Client
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

// Interactive Transaction: Transfer Funds
async function transferFunds(fromUserId, toUserId, amount) {
  return await prisma.$transaction(async (tx) => {
    // 1. Decrement sender balance
    const sender = await tx.account.update({
      where: { userId: fromUserId },
      data: { balance: { decrement: amount } }
    });

    if (sender.balance < 0) {
      throw new Error('Insufficient funds. Transaction rolled back.');
    }

    // 2. Increment receiver balance
    const receiver = await tx.account.update({
      where: { userId: toUserId },
      data: { balance: { increment: amount } }
    });

    return { sender, receiver };
  });
}

Option 2: Drizzle ORM

Drizzle executes close to pure SQL with zero abstraction overhead. Schemas are defined in native JavaScript/TypeScript.

JavaScript

// schema.js
import { pgTable, serial, text, integer, timestamp, numeric, pgEnum } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const statusEnum = pgEnum('status', ['PENDING', 'PAID', 'CANCELLED']);

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name')
});

export const orders = pgTable('orders', {
  id: serial('id').primaryKey(),
  total: numeric('total', { precision: 10, scale: 2 }).notNull(),
  status: statusEnum('status').default('PENDING'),
  userId: integer('user_id').references(() => users.id, { onDelete: 'cascade' }),
  createdAt: timestamp('created_at').defaultNow()
});

// Relational Definitions
export const usersRelations = relations(users, ({ many }) => ({
  orders: many(orders)
}));

JavaScript

// Queries with Drizzle
import { db } from './db.js';
import { users, orders } from './schema.js';
import { eq, desc } from 'drizzle-orm';

// Performant Left Join
const userWithOrders = await db
  .select({
    userId: users.id,
    userEmail: users.email,
    orderId: orders.id,
    orderTotal: orders.total
  })
  .from(users)
  .leftJoin(orders, eq(users.id, orders.userId))
  .where(eq(users.id, 1))
  .orderBy(desc(orders.createdAt));

In-Memory Caching: Redis

Redis (Remote Dictionary Server) is an in-memory, single-threaded, key-value data structure store used to lower database load and maintain ephemeral state.

Client ──▶ Express ──▶ Cache Hit (1-2ms)  ──▶ Return Cached Response
                 │
                 └───▶ Cache Miss ──▶ DB Query (50-200ms) ──▶ Store in Redis (TTL)

Bash

npm install ioredis

Redis Client Setup

JavaScript

import Redis from 'ioredis';

export const redis = new Redis({
  host: process.env.REDIS_HOST || '127.0.0.1',
  port: Number(process.env.REDIS_PORT) || 6379,
  password: process.env.REDIS_PASSWORD || undefined,
  retryStrategy(times) {
    const delay = Math.min(times * 50, 2000);
    return delay;
  }
});

redis.on('connect', () => console.log('Connected to Redis'));
redis.on('error', (err) => console.error('Redis connection error:', err));

1. Key-Value Caching (Cache-Aside Pattern)

The Cache-Aside strategy queries the cache first. If a key is absent, it queries the persistent database, writes the result to Redis with a Time-To-Live (TTL) expiration, and returns the response.

JavaScript

export async function getProductById(productId) {
  const cacheKey = `cache:products:${productId}`;

  // 1. Check Redis Cache
  const cachedData = await redis.get(cacheKey);
  if (cachedData) {
    return JSON.parse(cachedData); // Cache Hit
  }

  // 2. Cache Miss: Query Database (Postgres / Mongo)
  const product = await db.product.findUnique({ where: { id: productId } });
  if (!product) return null;

  // 3. Populate Redis with a 1-hour expiration (3600 seconds)
  // EX sets TTL in seconds; prevents stale data from persisting indefinitely
  await redis.set(cacheKey, JSON.stringify(product), 'EX', 3600);

  return product;
}

// Cache Invalidation: Clear cache immediately on data mutation
export async function updateProduct(productId, updates) {
  const updatedProduct = await db.product.update({
    where: { id: productId },
    data: updates
  });

  // Invalidate stale cache
  await redis.del(`cache:products:${productId}`);
  return updatedProduct;
}

2. Session Storage

Centralizing user sessions inside Redis decouples state from specific Node.js instances, allowing the backend application to scale horizontally across multi-core clusters or multiple servers without losing session state.

JavaScript

import session from 'express-session';
import { RedisStore } from 'connect-redis';

// Configure session middleware backed by Redis store
export function setupSessionMiddleware(app) {
  const store = new RedisStore({
    client: redis,
    prefix: 'sess:' // Redis keys will look like "sess:session_id"
  });

  app.use(
    session({
      store,
      secret: process.env.SESSION_SECRET || 'insecure-secret-key',
      resave: false,
      saveUninitialized: false,
      cookie: {
        secure: process.env.NODE_ENV === 'production', // Transmit over HTTPS only
        httpOnly: true,                                // Block client-side XSS access
        maxAge: 1000 * 60 * 60 * 24                   // 24 hours
      }
    })
  );
}

3. Rate Limiting (Sliding Window Algorithm)

Rate limiters protect API resources against brute-force attacks and abuse. While basic rate limiters use fixed buckets (which are vulnerable to traffic spikes at window boundaries), Redis Sorted Sets (ZSET) enable an accurate, high-performance sliding-window counter.

Sorted Set: ratelimit:user_42
Score / Value: [ 1773082910000, 1773082910000 ] (Timestamps in ms)
Operation:
1. ZREMRANGEBYSCORE: Remove elements older than (now - windowSize)
2. ZADD: Add current timestamp
3. ZCARD: Count total items in the remaining window
4. If count > limit ──▶ Reject (429)

JavaScript

// Express Middleware: Sliding Window Rate Limiter via Redis Sorted Sets
export function rateLimiter({ windowMs, maxRequests }) {
  return async (req, res, next) => {
    const ip = req.ip || req.headers['x-forwarded-for'] || 'unknown';
    const key = `ratelimit:${ip}`;
    const now = Date.now();
    const windowStart = now - windowMs;

    try {
      // Execute operations atomically using a Redis pipeline
      const pipeline = redis.pipeline();

      // 1. Purge requests that fall outside the active sliding window
      pipeline.zremrangebyscore(key, 0, windowStart);

      // 2. Add current request timestamp
      pipeline.zadd(key, now, `${now}:${Math.random()}`);

      // 3. Retrieve total count of requests within the active window
      pipeline.zcard(key);

      // 4. Set expiry to auto-clean keys after window expires
      pipeline.pexpire(key, windowMs);

      const results = await pipeline.exec();
      // results[2] contains output of zcard: [err, count]
      const totalRequests = results[2][1];

      res.setHeader('X-RateLimit-Limit', maxRequests);
      res.setHeader('X-RateLimit-Remaining', Math.max(0, maxRequests - totalRequests));

      if (totalRequests > maxRequests) {
        return res.status(429).json({
          status: 'fail',
          message: 'Too many requests. Please slow down.'
        });
      }

      next();
    } catch (err) {
      console.error('Rate limiter failure:', err);
      // Fail open: Allow request through if Redis fails to prevent taking down the API
      next();
    }
  };
}

Database Architecture Decision Matrix

Dimension	MongoDB	PostgreSQL / MySQL	Redis
Primary Paradigm	Document Store (NoSQL)	Relational (RDBMS)	In-Memory Key-Value Store
Data Schema	Dynamic / Polymorphic	Rigid / Normal Form	Primitives, Hashes, Sets, Lists
Ideal Use Cases	Catalogs, event streams, content management	Financial ledgers, multi-table orders, analytics	Caching, session store, leaderboards, locks
Scaling Model	Native sharding (horizontal)	Read replicas + vertical sizing	Cluster sharding, Sentinel replication
Query Mechanism	Aggregation pipelines, Mongoose	Structured Query Language (SQL)	Atomic commands (GET, ZADD, HSET)
Latency	Medium (5-25ms)	Medium (5-30ms)	Sub-millisecond (<1-2ms)