What are the best database integration patterns in Express.js?

Database Integration in Express.js

Express.js doesn't prescribe how to interact with databases. Choosing the right pattern depends on your application's complexity and scale.

1. Direct Query (Simple)

const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

app.get('/api/users', async (req, res, next) => {
  try {
    const { rows } = await pool.query('SELECT * FROM users LIMIT 50');
    res.json(rows);
  } catch (err) {
    next(err);
  }
});

Pros: Simple, direct, no abstraction Cons: SQL scattered through routes, hard to test

2. Repository Pattern

Abstract database operations into dedicated classes:

// repositories/user.repository.js
class UserRepository {
  constructor(pool) {
    this.pool = pool;
  }

  async findAll({ limit = 50, offset = 0 } = {}) {
    const { rows } = await this.pool.query(
      'SELECT * FROM users ORDER BY created_at DESC LIMIT $1 OFFSET $2',
      [limit, offset]
    );
    return rows;
  }

  async findById(id) {
    const { rows } = await this.pool.query(
      'SELECT * FROM users WHERE id = $1',
      [id]
    );
    return rows[0] || null;
  }

  async create(data) {
    const { rows } = await this.pool.query(
      'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
      [data.name, data.email]
    );
    return rows[0];
  }
}

module.exports = UserRepository;

// Usage in routes
const userRepo = new UserRepository(pool);

app.get('/api/users', async (req, res, next) => {
  try {
    const users = await userRepo.findAll({ limit: req.query.limit });
    res.json(users);
  } catch (err) {
    next(err);
  }
});

3. ORM Pattern (Prisma / Sequelize / TypeORM)

// With Prisma
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

app.get('/api/users', async (req, res) => {
  const users = await prisma.user.findMany({
    include: { posts: true },
    take: 50,
    orderBy: { createdAt: 'desc' }
  });
  res.json(users);
});

app.post('/api/users', async (req, res) => {
  const user = await prisma.user.create({
    data: { name: req.body.name, email: req.body.email }
  });
  res.status(201).json(user);
});

4. Service Layer Pattern

Add a service layer between routes and repositories:

// services/user.service.js
class UserService {
  constructor(userRepo, emailService) {
    this.userRepo = userRepo;
    this.emailService = emailService;
  }

  async createUser(data) {
    const existing = await this.userRepo.findByEmail(data.email);
    if (existing) throw new ConflictError('Email already exists');

    const user = await this.userRepo.create(data);
    await this.emailService.sendWelcome(user.email);
    return user;
  }

  async getUsers(params) {
    return this.userRepo.findAll(params);
  }
}

// routes
app.post('/api/users', async (req, res, next) => {
  try {
    const user = await userService.createUser(req.body);
    res.status(201).json(user);
  } catch (err) {
    next(err);
  }
});

Connection Management

// Singleton pool — share across the app
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,            // Max connections in pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000
});

// Graceful shutdown
process.on('SIGTERM', async () => {
  await pool.end();
  process.exit(0);
});

Transaction Support

async function transferMoney(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, fromId]
    );
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Pattern Comparison

Pattern	Complexity	Testability	Flexibility	Best For
Direct query	Low	Hard	Low	Prototypes
Repository	Medium	Easy	Medium	Most apps
ORM	Medium	Easy	High	Rapid development
Service + Repo	High	Best	Best	Large apps

Recommendation: Start with Repository pattern for clean separation. Add Service layer when business logic grows. Use Prisma or Drizzle for type-safe database access. Always use connection pooling and handle transactions properly.

Short Answer

Interview ready

Premium

A concise answer to help you respond confidently on this topic during an interview.

Finished reading?

Practice Problems