Back to Articles
18 min read

Building a Scalable Data Layer in Express.js: SQL, NoSQL, and Caching Strategies

Moving beyond simple connection strings, this guide establishes professional patterns for database integration. We explore the architectural differences between ORMs and ODMs, implement connection pooling for high-throughput scenarios, and ensure data integrity through robust transaction handling and migration strategies across MongoDB, PostgreSQL, and Redis.

express

Database Integration

MongoDB with Mongoose

Mongoose is an ODM (Object Document Mapper) that provides schema-based modeling for MongoDB, offering validation, type casting, and query building with a clean API that makes working with MongoDB documents feel like working with JavaScript objects.

const mongoose = require('mongoose');

// Connection
mongoose.connect('mongodb://localhost:27017/myapp');

// Schema & Model
const userSchema = new mongoose.Schema({
  name: { type: String, required: true },
  email: { type: String, unique: true, lowercase: true },
  age: { type: Number, min: 0, max: 120 },
  posts: [{ type: mongoose.Schema.Types.ObjectId, ref: 'Post' }],
  createdAt: { type: Date, default: Date.now }
});

// Middleware (hooks)
userSchema.pre('save', function(next) {
  console.log('Saving user:', this.name);
  next();
});

// Instance methods
userSchema.methods.getProfile = function() {
  return `${this.name} (${this.email})`;
};

// Static methods
userSchema.statics.findByEmail = function(email) {
  return this.findOne({ email });
};

const User = mongoose.model('User', userSchema);

// Usage in Express
app.post('/users', async (req, res) => {
  const user = await User.create(req.body);
  res.status(201).json(user);
});

app.get('/users/:id', async (req, res) => {
  const user = await User.findById(req.params.id).populate('posts');
  res.json(user);
});

PostgreSQL with Sequelize

Sequelize is a promise-based ORM for PostgreSQL (and other SQL databases) that provides model definitions, associations, migrations, and powerful query capabilities with support for raw SQL when needed.

const { Sequelize, DataTypes, Op } = require('sequelize');

// Connection
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'postgres',
  logging: false,
  pool: { max: 10, min: 0, idle: 10000 }
});

// Model Definition
const User = sequelize.define('User', {
  id: {
    type: DataTypes.UUID,
    defaultValue: DataTypes.UUIDV4,
    primaryKey: true
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: { len: [2, 100] }
  },
  email: {
    type: DataTypes.STRING,
    unique: true,
    validate: { isEmail: true }
  },
  status: {
    type: DataTypes.ENUM('active', 'inactive'),
    defaultValue: 'active'
  }
}, {
  tableName: 'users',
  timestamps: true,  // createdAt, updatedAt
  paranoid: true     // soft deletes (deletedAt)
});

const Post = sequelize.define('Post', {
  title: DataTypes.STRING,
  content: DataTypes.TEXT
});

// Associations
User.hasMany(Post, { foreignKey: 'authorId', as: 'posts' });
Post.belongsTo(User, { foreignKey: 'authorId', as: 'author' });

// Express Usage
app.get('/users', async (req, res) => {
  const users = await User.findAll({
    where: { status: 'active' },
    include: [{ model: Post, as: 'posts' }],
    order: [['createdAt', 'DESC']],
    limit: 10
  });
  res.json(users);
});

MySQL Integration

MySQL can be integrated using either the mysql2 driver for raw queries or Sequelize ORM, with mysql2 offering promise support, prepared statements, and connection pooling for high-performance applications.

const mysql = require('mysql2/promise');

// Create connection pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// Express middleware to attach db
app.use((req, res, next) => {
  req.db = pool;
  next();
});

// Usage with prepared statements (SQL injection safe)
app.get('/users/:id', async (req, res) => {
  const [rows] = await req.db.execute(
    'SELECT * FROM users WHERE id = ?',
    [req.params.id]
  );
  res.json(rows[0]);
});

app.post('/users', async (req, res) => {
  const { name, email } = req.body;
  const [result] = await req.db.execute(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    [name, email]
  );
  res.status(201).json({ id: result.insertId, name, email });
});

// Complex query with JOIN
app.get('/users-with-orders', async (req, res) => {
  const [rows] = await req.db.query(`
    SELECT u.*, COUNT(o.id) as orderCount
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id
  `);
  res.json(rows);
});

Redis Integration

Redis is an in-memory data store used with Express for caching, session storage, rate limiting, and real-time features, providing sub-millisecond response times for frequently accessed data.

const Redis = require('ioredis');

const redis = new Redis({
  host: 'localhost',
  port: 6379,
  password: 'optional',
  retryDelayOnFailover: 100
});

// Caching middleware
const cache = (duration) => async (req, res, next) => {
  const key = `cache:${req.originalUrl}`;
  const cached = await redis.get(key);
  
  if (cached) {
    return res.json(JSON.parse(cached));
  }
  
  // Override res.json to cache response
  const originalJson = res.json.bind(res);
  res.json = async (data) => {
    await redis.setex(key, duration, JSON.stringify(data));
    return originalJson(data);
  };
  
  next();
};

// Usage
app.get('/products', cache(300), async (req, res) => {
  const products = await db.query('SELECT * FROM products');
  res.json(products);  // Automatically cached for 5 minutes
});

// Session storage with Redis
const session = require('express-session');
const RedisStore = require('connect-redis').default;

app.use(session({
  store: new RedisStore({ client: redis }),
  secret: 'your-secret',
  resave: false,
  saveUninitialized: false,
  cookie: { secure: true, maxAge: 86400000 }
}));

// Pub/Sub for real-time features
const subscriber = new Redis();
subscriber.subscribe('notifications');
subscriber.on('message', (channel, message) => {
  io.emit('notification', JSON.parse(message));
});
┌─────────────────────────────────────────────────────────────┐
│                    REDIS USE CASES                          │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  ┌─────────────┐    ┌─────────────┐    ┌─────────────┐     │
│  │   CACHING   │    │  SESSIONS   │    │ RATE LIMIT  │     │
│  │             │    │             │    │             │     │
│  │ GET/SET/EX  │    │   HSET/     │    │   INCR/     │     │
│  │             │    │   HGET      │    │   EXPIRE    │     │
│  └─────────────┘    └─────────────┘    └─────────────┘     │
│                                                             │
│  ┌─────────────┐    ┌─────────────┐    ┌─────────────┐     │
│  │   PUB/SUB   │    │   QUEUES    │    │ LEADERBOARD │     │
│  │             │    │             │    │             │     │
│  │ PUBLISH/    │    │  LPUSH/     │    │   ZADD/     │     │
│  │ SUBSCRIBE   │    │  BRPOP      │    │   ZRANGE    │     │
│  └─────────────┘    └─────────────┘    └─────────────┘     │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Connection Pooling

Connection pooling maintains a cache of database connections that can be reused, avoiding the overhead of creating new connections for each request and significantly improving application performance under load.

// PostgreSQL with pg-pool
const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  database: 'myapp',
  user: 'user',
  password: 'password',
  port: 5432,
  // Pool configuration
  max: 20,                  // Maximum connections
  min: 5,                   // Minimum connections
  idleTimeoutMillis: 30000, // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Fail if can't connect in 2s
  maxUses: 7500            // Close connection after N uses
});

// Monitor pool events
pool.on('connect', () => console.log('New client connected'));
pool.on('error', (err) => console.error('Pool error:', err));

// Express integration
app.use(async (req, res, next) => {
  req.db = pool;  // Attach pool, not individual connection
  next();
});

// Automatic connection management
app.get('/data', async (req, res) => {
  // Pool automatically acquires and releases connection
  const { rows } = await req.db.query('SELECT * FROM data');
  res.json(rows);
});

// Manual connection for transactions
app.post('/transfer', async (req, res) => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // ... transaction queries
    await client.query('COMMIT');
    res.json({ success: true });
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();  // Return to pool
  }
});
┌─────────────────────────────────────────────────────────────┐
│                   CONNECTION POOLING                         │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│   Express App                        Database               │
│   ┌─────────┐                       ┌─────────┐            │
│   │ Request │──┐                    │         │            │
│   └─────────┘  │   ┌───────────┐    │         │            │
│   ┌─────────┐  │   │   POOL    │    │   DB    │            │
│   │ Request │──┼──▶│ ┌──┬──┬──┐│───▶│         │            │
│   └─────────┘  │   │ │C1│C2│C3││    │         │            │
│   ┌─────────┐  │   │ └──┴──┴──┘│    │         │            │
│   │ Request │──┘   └───────────┘    └─────────┘            │
│   └─────────┘                                               │
│                                                             │
│   Without Pool: 1000 req = 1000 connections (BAD)          │
│   With Pool:    1000 req = 20 connections (GOOD)           │
│                                                             │
└─────────────────────────────────────────────────────────────┘

ORM/ODM Patterns

ORM (Object-Relational Mapping) and ODM (Object-Document Mapping) patterns abstract database operations into object-oriented code, using patterns like Active Record, Data Mapper, Repository, and Unit of Work to separate business logic from data access.

// PATTERN 1: Active Record (Sequelize style)
// Model knows how to persist itself
class User extends Model {
  static async findActive() {
    return this.findAll({ where: { status: 'active' } });
  }
}
const user = await User.create({ name: 'John' });
await user.save();

// PATTERN 2: Repository Pattern
// Separates domain logic from data access
class UserRepository {
  constructor(model) {
    this.model = model;
  }
  
  async findById(id) {
    return this.model.findByPk(id);
  }
  
  async findByEmail(email) {
    return this.model.findOne({ where: { email } });
  }
  
  async create(data) {
    return this.model.create(data);
  }
  
  async updateStatus(id, status) {
    return this.model.update({ status }, { where: { id } });
  }
}

// Usage in Express
const userRepo = new UserRepository(User);

app.get('/users/:id', async (req, res) => {
  const user = await userRepo.findById(req.params.id);
  res.json(user);
});

// PATTERN 3: Data Mapper (TypeORM style)
// Entities are plain objects, mapper handles persistence
@Entity()
class User {
  @PrimaryGeneratedColumn()
  id: number;
  
  @Column()
  name: string;
}

const userRepository = dataSource.getRepository(User);
const user = userRepository.create({ name: 'John' });
await userRepository.save(user);
┌─────────────────────────────────────────────────────────────┐
│                    ORM/ODM PATTERNS                          │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  ACTIVE RECORD              DATA MAPPER                     │
│  ┌─────────────┐           ┌─────────────┐                 │
│  │   Model     │           │   Entity    │  (plain object) │
│  │  ─────────  │           │  ─────────  │                 │
│  │  data       │           │  data only  │                 │
│  │  save()     │           └──────┬──────┘                 │
│  │  delete()   │                  │                        │
│  │  find()     │           ┌──────▼──────┐                 │
│  └─────────────┘           │   Mapper    │  (handles DB)   │
│                            │  ─────────  │                 │
│  Pros: Simple              │  save()     │                 │
│  Cons: Coupled             │  find()     │                 │
│                            └─────────────┘                 │
│                            Pros: Decoupled                 │
│                            Cons: More code                 │
└─────────────────────────────────────────────────────────────┘

Database Migrations

Migrations are version-controlled scripts that define incremental changes to your database schema, allowing teams to evolve the database structure safely and consistently across all environments.

// Using Sequelize CLI
// Run: npx sequelize-cli migration:generate --name create-users

// migrations/20240115120000-create-users.js
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('users', {
      id: {
        type: Sequelize.UUID,
        defaultValue: Sequelize.UUIDV4,
        primaryKey: true
      },
      email: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true
      },
      name: {
        type: Sequelize.STRING(100),
        allowNull: false
      },
      createdAt: {
        type: Sequelize.DATE,
        defaultValue: Sequelize.NOW
      },
      updatedAt: Sequelize.DATE
    });

    // Add index
    await queryInterface.addIndex('users', ['email']);
  },

  async down(queryInterface) {
    await queryInterface.dropTable('users');
  }
};

// migrations/20240116120000-add-user-status.js
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addColumn('users', 'status', {
      type: Sequelize.ENUM('active', 'inactive', 'banned'),
      defaultValue: 'active'
    });
  },

  async down(queryInterface) {
    await queryInterface.removeColumn('users', 'status');
  }
};

// package.json scripts
{
  "scripts": {
    "migrate": "sequelize-cli db:migrate",
    "migrate:undo": "sequelize-cli db:migrate:undo",
    "migrate:status": "sequelize-cli db:migrate:status"
  }
}
┌─────────────────────────────────────────────────────────────┐
│                   MIGRATION WORKFLOW                         │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  Development          Staging            Production         │
│  ┌─────────┐         ┌─────────┐        ┌─────────┐        │
│  │ v1.0    │         │ v1.0    │        │ v1.0    │        │
│  │─────────│         │─────────│        │─────────│        │
│  │ users   │         │ users   │        │ users   │        │
│  └────┬────┘         └────┬────┘        └────┬────┘        │
│       │migrate            │migrate           │migrate       │
│       ▼                   ▼                  ▼              │
│  ┌─────────┐         ┌─────────┐        ┌─────────┐        │
│  │ v1.1    │         │ v1.1    │        │ v1.1    │        │
│  │─────────│         │─────────│        │─────────│        │
│  │ users   │         │ users   │        │ users   │        │
│  │ +status │         │ +status │        │ +status │        │
│  └─────────┘         └─────────┘        └─────────┘        │
│                                                             │
│  SequelizeMeta table tracks applied migrations              │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Seeding Data

Seeders populate your database with initial or test data, essential for setting up development environments, running tests with consistent data, and initializing production databases with required reference data.

// seeders/20240115-demo-users.js
module.exports = {
  async up(queryInterface) {
    await queryInterface.bulkInsert('users', [
      {
        id: '550e8400-e29b-41d4-a716-446655440001',
        name: 'Admin User',
        email: 'admin@example.com',
        role: 'admin',
        createdAt: new Date(),
        updatedAt: new Date()
      },
      {
        id: '550e8400-e29b-41d4-a716-446655440002',
        name: 'Test User',
        email: 'user@example.com',
        role: 'user',
        createdAt: new Date(),
        updatedAt: new Date()
      }
    ]);
  },

  async down(queryInterface) {
    await queryInterface.bulkDelete('users', null, {});
  }
};

// Using Faker for realistic test data
const { faker } = require('@faker-js/faker');

module.exports = {
  async up(queryInterface) {
    const users = Array.from({ length: 100 }, () => ({
      id: faker.string.uuid(),
      name: faker.person.fullName(),
      email: faker.internet.email(),
      avatar: faker.image.avatar(),
      createdAt: faker.date.past(),
      updatedAt: new Date()
    }));

    await queryInterface.bulkInsert('users', users);
  }
};

// Mongoose seeding
async function seedDatabase() {
  await User.deleteMany({});
  
  const users = await User.insertMany([
    { name: 'Admin', email: 'admin@test.com', role: 'admin' },
    { name: 'User', email: 'user@test.com', role: 'user' }
  ]);

  await Post.insertMany([
    { title: 'First Post', author: users[0]._id },
    { title: 'Second Post', author: users[1]._id }
  ]);
  
  console.log('Database seeded!');
}

// Run seeds: npx sequelize-cli db:seed:all

Transaction Handling

Transactions ensure that a group of database operations either all succeed or all fail together (atomicity), maintaining data integrity for complex operations like financial transfers or multi-table updates.

// Sequelize Transactions
const { sequelize } = require('./models');

app.post('/transfer', async (req, res) => {
  const { fromAccountId, toAccountId, amount } = req.body;
  
  // Managed transaction (auto commit/rollback)
  const result = await sequelize.transaction(async (t) => {
    const fromAccount = await Account.findByPk(fromAccountId, {
      transaction: t,
      lock: t.LOCK.UPDATE  // Pessimistic locking
    });
    
    if (fromAccount.balance < amount) {
      throw new Error('Insufficient funds');
    }
    
    await fromAccount.decrement('balance', { by: amount, transaction: t });
    
    const toAccount = await Account.findByPk(toAccountId, { transaction: t });
    await toAccount.increment('balance', { by: amount, transaction: t });
    
    await Transaction.create({
      fromAccountId,
      toAccountId,
      amount,
      type: 'transfer'
    }, { transaction: t });
    
    return { success: true };
  });
  
  res.json(result);
});

// Manual transaction control
app.post('/complex-operation', async (req, res) => {
  const t = await sequelize.transaction();
  
  try {
    await User.create({ name: 'John' }, { transaction: t });
    await Profile.create({ bio: 'Hello' }, { transaction: t });
    
    await t.commit();
    res.json({ success: true });
  } catch (error) {
    await t.rollback();
    res.status(500).json({ error: error.message });
  }
});

// MongoDB Transactions (Replica Set required)
app.post('/order', async (req, res) => {
  const session = await mongoose.startSession();
  session.startTransaction();
  
  try {
    const order = await Order.create([req.body], { session });
    await Inventory.updateOne(
      { productId: req.body.productId },
      { $inc: { quantity: -req.body.quantity } },
      { session }
    );
    
    await session.commitTransaction();
    res.json(order);
  } catch (error) {
    await session.abortTransaction();
    throw error;
  } finally {
    session.endSession();
  }
});
┌─────────────────────────────────────────────────────────────┐
│                   TRANSACTION FLOW                           │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│   BEGIN TRANSACTION                                         │
│         │                                                   │
│         ▼                                                   │
│   ┌─────────────┐                                          │
│   │ Operation 1 │──────┐                                   │
│   └─────────────┘      │                                   │
│         │              │                                   │
│         ▼              │                                   │
│   ┌─────────────┐      │ Error?                            │
│   │ Operation 2 │──────┼──────────┐                        │
│   └─────────────┘      │          │                        │
│         │              │          ▼                        │
│         ▼              │    ┌───────────┐                  │
│   ┌─────────────┐      │    │ ROLLBACK  │                  │
│   │ Operation 3 │──────┘    │ All       │                  │
│   └─────────────┘           │ Reverted  │                  │
│         │                   └───────────┘                  │
│         ▼                                                   │
│   ┌───────────┐                                            │
│   │  COMMIT   │  All changes persisted                     │
│   └───────────┘                                            │
│                                                             │
└─────────────────────────────────────────────────────────────┘