🛠️ Weekend Project: Databases

What You're Building

TaskForge is a small team task manager — think a stripped-down Trello or Jira. Users register and log in, create tasks, assign them to teammates, comment, and get notified. That sounds like a job for one database, and honestly, for a real MVP it would be. But the point of this weekend is to feel the seams between database paradigms by deliberately spreading the data across three stores that each shine at a different job.

This mirrors polyglot persistence — the real-world practice of choosing a data store per workload rather than forcing everything into one engine. Netflix, Uber, and Airbnb all run several database technologies in production for exactly this reason.

The Architecture & Why Three Stores

Each store has a clear job. Read this table until the "why" for each is obvious — every design decision in the milestones flows from it.

# docker-compose.yml
services:
  mongo:
    image: mongo:7
    ports: ["27017:27017"]
  postgres:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: taskforge
      POSTGRES_DB: taskforge
    ports: ["5432:5432"]
  redis:
    image: redis:7
    ports: ["6379:6379"]

Milestone 1 — Scaffold & Connect

Goal: a running Express server that successfully connects to all three databases on startup and logs three green checkmarks. If a connection fails, the process should exit loudly — silent failures are the enemy of a multi-store app.

Create the project

mkdir taskforge && cd taskforge
npm init -y
npm install express mongoose pg redis dotenv cors helmet jsonwebtoken bcrypt uuid
npm install --save-dev nodemon

mkdir -p src/{config,models,services,controllers,routes,middleware}
touch .env .gitignore

Use pg (node-postgres) for PostgreSQL. If you prefer MySQL, swap in mysql2 — the query shape is nearly identical; only the placeholder syntax differs ($1 for Postgres vs ? for MySQL).

Connection modules

The modern redis v4+ and mongoose v8 clients are promise-native — no more promisify wrappers like older tutorials show.

// src/config/db.js
import mongoose from 'mongoose';
import pg from 'pg';
import { createClient } from 'redis';

// --- MongoDB ---
export async function connectMongo() {
  await mongoose.connect(process.env.MONGO_URI);
  console.log('✅ MongoDB connected');
}

// --- PostgreSQL (connection pool) ---
export const pgPool = new pg.Pool({ connectionString: process.env.PG_URI });
export async function connectPostgres() {
  const client = await pgPool.connect();
  await client.query('SELECT 1');
  client.release();
  console.log('✅ PostgreSQL connected');
}

// --- Redis ---
export const redis = createClient({ url: process.env.REDIS_URI });
redis.on('error', (err) => console.error('Redis error:', err));
export async function connectRedis() {
  await redis.connect();
  console.log('✅ Redis connected');
}

// src/app.js
import 'dotenv/config';
import express from 'express';
import helmet from 'helmet';
import cors from 'cors';
import { connectMongo, connectPostgres, connectRedis } from './config/db.js';

const app = express();
app.use(helmet());
app.use(cors());
app.use(express.json());

app.get('/health', (req, res) => res.json({ status: 'ok' }));

async function start() {
  try {
    await Promise.all([connectMongo(), connectPostgres(), connectRedis()]);
    app.listen(3000, () => console.log('🚀 TaskForge on http://localhost:3000'));
  } catch (err) {
    console.error('❌ Startup failed:', err);
    process.exit(1);   // fail loud, fail fast
  }
}
start();

Milestone 2 — Data Models

Goal: define the shape of your data in each store. This is where the source-of-truth decisions from the architecture table become concrete.

MongoDB (Mongoose) — task detail & profiles

MongoDB owns the rich, variable parts: full task descriptions, tags, attachments, and profile preferences that differ per user.

// src/models/task.js
import mongoose from 'mongoose';

const taskSchema = new mongoose.Schema({
  taskId:      { type: String, required: true, unique: true, index: true }, // shared UUID
  title:       { type: String, required: true },
  description: String,
  status:      { type: String, enum: ['backlog','todo','in_progress','in_review','done'], default: 'todo' },
  priority:    { type: String, enum: ['low','medium','high','urgent'], default: 'medium' },
  assigneeId:  String,
  creatorId:   String,
  teamId:      String,
  dueDate:     Date,
  tags:        [String],
  attachments: [{ name: String, url: String, size: Number }],
  customFields: mongoose.Schema.Types.Mixed   // flexible per task type
}, { timestamps: true });

export const Task = mongoose.model('Task', taskSchema);

PostgreSQL — teams, permissions & the audit trail

Relationships and guaranteed writes live here. Note the foreign keys — the relational store enforces integrity that MongoDB would leave to application code.

-- src/config/schema.sql
CREATE TABLE IF NOT EXISTS users (
    user_id       UUID PRIMARY KEY,
    email         VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at    TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS teams (
    team_id    UUID PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,
    created_by UUID NOT NULL REFERENCES users(user_id)
);

CREATE TABLE IF NOT EXISTS team_memberships (
    team_id UUID NOT NULL REFERENCES teams(team_id),
    user_id UUID NOT NULL REFERENCES users(user_id),
    role    VARCHAR(10) NOT NULL DEFAULT 'member'
              CHECK (role IN ('admin','member','guest')),
    PRIMARY KEY (team_id, user_id)
);

-- Lightweight task reference; full detail lives in MongoDB
CREATE TABLE IF NOT EXISTS tasks (
    task_id     UUID PRIMARY KEY,
    title       VARCHAR(255) NOT NULL,
    team_id     UUID NOT NULL REFERENCES teams(team_id),
    assignee_id UUID REFERENCES users(user_id),
    status      VARCHAR(15) NOT NULL DEFAULT 'todo',
    created_by  UUID NOT NULL REFERENCES users(user_id),
    created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS audit_logs (
    log_id      BIGSERIAL PRIMARY KEY,
    entity_type VARCHAR(20) NOT NULL,
    entity_id   UUID NOT NULL,
    action      VARCHAR(20) NOT NULL,
    actor_id    UUID NOT NULL REFERENCES users(user_id),
    changes     JSONB,
    created_at  TIMESTAMPTZ DEFAULT now()
);

Redis — key conventions

Redis has no schema, but you still design its key namespace. Consistent, colon-delimited keys keep the store readable and let you use pattern scans.

Milestone 3 — Create a Task Across Stores

Goal: the heart of the project. One createTask call must write to Postgres and MongoDB and Redis, and it must not leave data half-written if something fails midway.

Here's the write flow. The relational transaction is the anchor: we only commit it after MongoDB succeeds, so the two authoritative stores agree.

// src/services/taskService.js
import { v4 as uuid } from 'uuid';
import { Task } from '../models/task.js';
import { pgPool, redis } from '../config/db.js';

export async function createTask(data, userId) {
  const client = await pgPool.connect();     // dedicated connection for the transaction
  const taskId = uuid();
  let mongoSaved = false;

  try {
    await client.query('BEGIN');

    // 1. Relational reference row
    await client.query(
      `INSERT INTO tasks (task_id, title, team_id, assignee_id, created_by)
       VALUES ($1, $2, $3, $4, $5)`,
      [taskId, data.title, data.teamId, data.assigneeId ?? null, userId]
    );

    // 2. Rich document in MongoDB (same shared UUID)
    await Task.create({
      taskId, title: data.title, description: data.description,
      priority: data.priority, assigneeId: data.assigneeId,
      creatorId: userId, teamId: data.teamId, dueDate: data.dueDate,
      tags: data.tags ?? [], customFields: data.customFields ?? {}
    });
    mongoSaved = true;

    // 3. Audit trail (still inside the SQL transaction)
    await client.query(
      `INSERT INTO audit_logs (entity_type, entity_id, action, actor_id, changes)
       VALUES ('task', $1, 'create', $2, $3)`,
      [taskId, userId, { title: data.title, teamId: data.teamId }]
    );

    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');           // undo Postgres
    if (mongoSaved) await Task.deleteOne({ taskId }); // compensate MongoDB
    throw err;
  } finally {
    client.release();
  }

  // 4. Best-effort side effects (safe to retry, never block the response)
  await redis.set(`task:${taskId}`,
    JSON.stringify({ taskId, title: data.title, status: 'todo' }),
    { EX: 300 });
  if (data.assigneeId) {
    await redis.lPush('notifications:queue',
      JSON.stringify({ userId: data.assigneeId, type: 'task_assigned', taskId }));
    await redis.incr(`user:notif:count:${data.assigneeId}`);
  }

  return { taskId, ...data };
}

Reading with cache-aside

export async function getTask(taskId) {
  const cached = await redis.get(`task:${taskId}`);
  if (cached) return JSON.parse(cached);          // cache hit

  const task = await Task.findOne({ taskId }).lean(); // miss → source of truth
  if (!task) throw new Error('Task not found');

  await redis.set(`task:${taskId}`, JSON.stringify(task), { EX: 300 }); // repopulate
  return task;
}

Milestone 4 — Cache, Sessions & Polish

Goal: round out the app with Redis-backed sessions, cache invalidation on writes, and the auth middleware that ties it together.

Sessions in Redis

On login, verify credentials against Postgres, then store a session in Redis keyed by a random id. The session id (not a raw JWT) is what the client sends back.

// src/services/authService.js (excerpt)
import bcrypt from 'bcrypt';
import { v4 as uuid } from 'uuid';
import { pgPool, redis } from '../config/db.js';

export async function login(email, password) {
  const { rows } = await pgPool.query(
    'SELECT user_id, password_hash FROM users WHERE email = $1', [email]);
  const user = rows[0];
  if (!user || !(await bcrypt.compare(password, user.password_hash))) {
    throw new Error('Invalid credentials');
  }

  const sessionId = uuid();
  await redis.set(`session:${sessionId}`,
    JSON.stringify({ userId: user.user_id, email }),
    { EX: 3600 });                       // 1-hour TTL = automatic logout
  return { sessionId };
}

// src/middleware/auth.js
import { redis } from '../config/db.js';

export async function requireAuth(req, res, next) {
  const sessionId = req.headers['x-session-id'];
  if (!sessionId) return res.status(401).json({ error: 'Auth required' });

  const raw = await redis.get(`session:${sessionId}`);
  if (!raw) return res.status(401).json({ error: 'Invalid or expired session' });

  req.user = JSON.parse(raw);
  next();
}

Invalidate on write

Whenever a task changes, delete its cache key so the next read repopulates from the source of truth. Stale caches are the classic multi-store bug — invalidate eagerly.

export async function updateTask(taskId, patch) {
  const updated = await Task.findOneAndUpdate({ taskId }, { $set: patch }, { new: true }).lean();
  await redis.del(`task:${taskId}`);   // ← the crucial line
  return updated;
}

Build Checklist

Track your weekend against this. Tick each item as you finish; the milestones above map directly to these groups.

What Good Looks Like

Anyone can make three databases connect. A strong submission shows judgment. Use this rubric to grade yourself honestly.

Stretch Goals

Finished early, or coming back to level up? Pick one — each deepens a different skill.

• Search across stores: add a full-text task search using MongoDB's $text index, filtered by team membership pulled from Postgres.
• Real-time notifications: replace the polled notification list with Redis Pub/Sub pushing over WebSockets.
• Analytics dashboard: write a Postgres aggregate query (tasks per status per team) and cache the result in Redis with a 1-minute TTL.
• Simplify on purpose: re-implement TaskForge with only PostgreSQL (using a jsonb column for flexible fields). Compare the line count and note what you lost and gained — this is the most instructive stretch of all.

Quick Quiz

Summary & What's Next

📚 Further Reading

• MongoDB Manual
• PostgreSQL Documentation
• Redis Documentation
• Martin Fowler — Polyglot Persistence
• Designing Data-Intensive Applications — Martin Kleppmann

🚀 What's Next?

You've now built a real multi-store backend and felt where the databases end and your code begins. In the next module we zoom back out to the framework that glued it all together: how Express actually structures a backend, from routing to middleware to error handling.