Suggest an edit

Improve this article

Refine the answer for “What are the best database integration patterns in Express.js?”. Your changes go to moderation before they’re published.

Approval required

Content

What you’re changing

Title (EN)

Short answer (EN)

Shown above the full answer for quick recall.

Answer (EN)

**Database integration pattern** - an architectural choice for how your Express.js app talks to a database. That choice determines how testable your code is, how easy it is to change a query later, and how long it takes to track down a bug six months from now.

## Theory

### TL;DR

- **Direct Query**: SQL written directly in route handlers. Works in minutes, breaks at scale.
- **Repository Pattern**: SQL isolated in dedicated classes with injected pool. Testable, reusable, the right default for production.
- **ORM (Prisma, Sequelize, TypeORM)**: no raw SQL, schema-first approach. Type-safe but less query control.
- **Service Layer**: sits above repositories, holds business logic. Add it when routes start doing too much.
- Decision rule: Repository for most apps. Direct Query only for throwaway scripts. ORM when the schema has complex relationships and type safety matters.

### Quick example

```js
// ❌ Direct Query - SQL in the route, hard to test
app.get('/users', async (req, res) => {
  const result = await pool.query('SELECT * FROM users');
  res.json(result.rows); // Output: [{ id: 1, name: 'Alice' }]
});

// ✅ Repository Pattern - SQL in a class, injectable in tests
class UserRepository {
  constructor(pool) { this.pool = pool; }

async getAll() {
    const { rows } = await this.pool.query('SELECT * FROM users');
    return rows; // Output: [{ id: 1, name: 'Alice' }]
  }
}

app.get('/users', async (req, res) => {
  const users = await userRepo.getAll();
  res.json(users); // same result, now mockable
});
```

The repository doesn't change what goes to the database. It changes what you can do in tests.

### Why the pattern choice matters

With Direct Query, each route handler does two jobs: it handles HTTP and it queries the database. That's fine at 5 routes. At 50 routes, you're repeating the same SQL in a dozen places. Changing a column name becomes a grep session across the whole codebase.

The Repository Pattern moves all SQL for one entity into one class. Change the query once, every caller gets the fix. More importantly: inject a mock pool into the constructor in tests, and your unit tests never touch a real database. That's the actual payoff, not the abstraction itself.

In practice, most production codebases I've seen commit to a pattern in the first week and rarely revisit it. Choose deliberately.

### When to use each pattern

- **Direct Query**: proof-of-concept, learning Express basics, one-off scripts
- **Repository**: any production app, any team with 2+ developers, any code that needs unit tests
- **ORM (Prisma)**: complex schema with many relationships, teams that want type-safe queries
- **ORM (Sequelize)**: legacy Node.js projects, existing schemas with associations you don't want to hand-write
- **Query Builder (Knex.js)**: you want SQL control but also schema migrations tracked in code
- **Service + Repository**: business logic grows past simple CRUD (duplicate checks, emails, external APIs)

### Comparison table

| Pattern | Setup | SQL Control | Testability | Best For |
|---------|-------|-------------|-------------|----------|
| Direct Query | Minutes | Full | Poor | Prototypes |
| Repository | Hours | Full | Excellent | Most production apps |
| Query Builder (Knex) | Hours | High | Good | Complex queries + migrations |
| ORM (Prisma) | Hours | Low | Good | Rapid dev, type safety |
| ORM (Sequelize) | Days | Low | Good | Legacy apps, complex relations |
| Service + Repository | Hours | Full | Best | Apps with real business logic |

### How connection pooling works

When `pool.query()` runs, Node.js hands the query to the database driver (pg, mysql2). The driver picks an open connection from the pool, sends the query asynchronously, and returns the result. Your route handler waits without blocking other requests.

The pool has a hard limit. With `max: 20`, request 21 waits for a connection to free up. If nothing frees up within `connectionTimeoutMillis`, the request fails. This is why connection leaks kill production apps: each forgotten `client.release()` permanently shrinks the available pool.

The Repository Pattern doesn't touch any of this. It just wraps `pool.query()` in methods you can swap out during testing.

### Common mistakes

**Mistake 1: Forgetting to release connections**

```js
// ❌ Pool shrinks with every request that hits this route
app.get('/users', async (req, res) => {
  const client = await pool.connect();
  const result = await client.query('SELECT * FROM users');
  res.json(result.rows);
  // client.release() never called - connection leaks
});

// ✅ finally block runs even when the query throws
app.get('/users', async (req, res) => {
  const client = await pool.connect();
  try {
    const result = await client.query('SELECT * FROM users');
    res.json(result.rows);
  } finally {
    client.release();
  }
});
```

**Mistake 2: N+1 queries in loops**

```js
// ❌ 1 query for users + 1 per user = 101 total queries
const users = await pool.query('SELECT * FROM users LIMIT 100');
for (let user of users.rows) {
  user.posts = await pool.query(
    'SELECT * FROM posts WHERE user_id = $1', [user.id]
  );
}

// ✅ One JOIN query does the same job
const result = await pool.query(`
  SELECT u.*, json_agg(p.*) as posts
  FROM users u
  LEFT JOIN posts p ON u.id = p.user_id
  GROUP BY u.id
`);
// Output: users array, each with a posts array - 1 query total
```

**Mistake 3: SQL injection via string interpolation**

```js
// ❌ Attacker sends name = "'; DROP TABLE users; --"
const query = `SELECT * FROM users WHERE name = '${req.query.name}'`;
await pool.query(query);

// ✅ Parameterized query - driver handles escaping
await pool.query(
  'SELECT * FROM users WHERE name = $1',
  [req.query.name]
);
```

**Mistake 4: No timeout on slow queries**

```js
// ❌ 100 concurrent requests hit a long query, pool exhausts at 20 connections
app.get('/report', async (req, res) => {
  const result = await pool.query('SELECT * FROM huge_table JOIN another_table ...');
  res.json(result.rows);
});

// ✅ Statement timeout prevents pool exhaustion
app.get('/report', async (req, res) => {
  const client = await pool.connect();
  try {
    await client.query('SET statement_timeout = 5000'); // 5s max
    const result = await client.query('SELECT * FROM huge_table JOIN another_table ...');
    res.json(result.rows);
  } catch (err) {
    if (err.message.includes('statement timeout')) {
      res.status(408).json({ error: 'Query took too long' });
    } else {
      res.status(500).json({ error: err.message });
    }
  } finally {
    client.release();
  }
});
```

**Mistake 5: Not verifying DB connection on startup**

```js
// ❌ App starts fine, first request fails with ECONNREFUSED if DB is down
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
app.listen(3000);

// ✅ Fail fast - don't accept traffic if DB is unreachable
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
pool.query('SELECT 1').then(() => {
  app.listen(3000, () => console.log('Server and DB connected'));
}).catch(err => {
  console.error('Database connection failed:', err);
  process.exit(1);
});
```

### Real-world usage

- **Express + pg**: direct pool queries or Repository Pattern. Used in production at Stripe and Airbnb backend services.
- **Express + Prisma**: auto-generated type-safe client, growing adoption at startups. Figma uses it internally.
- **Express + Sequelize**: legacy Node.js apps and enterprise systems with complex associations.
- **Express + Knex.js**: query builder with migrations. Forms the base for Bookshelf.js and Objection.js.
- **Express + TypeORM**: TypeScript-first, decorator-based schema definition. The default choice in NestJS.

### Follow-up questions

**Q:** Why does wrapping `pool.query()` in a Repository class make testing easier?
**A:** Because the pool is injected via the constructor. In tests, you pass a mock: `{ query: jest.fn().mockResolvedValue({ rows: [...] }) }`. Without the repository, every test that touches a route handler requires a real database or complex module-level patching of the pool.

**Q:** What is the N+1 problem and why does it appear with ORMs?
**A:** N+1 happens when you load N parent records and then fire 1 query per record to load children. It appears with ORMs because lazy loading looks like a property access. `user.posts` reads like an array but triggers a query. Fix it with eager loading: `include: { posts: true }` in Prisma, `include: [Post]` in Sequelize.

**Q:** When should a Service Layer be added on top of repositories?
**A:** When route handlers start containing decisions. Checking for duplicate emails before creating a user, sending a welcome email after signup, triggering a webhook - none of that belongs in a route or a repository. The Service Layer is where multi-step business operations live. Add it when the same logic appears across more than one route.

**Q:** How do you handle transactions in the Repository Pattern?
**A:** Pass the client (not the pool) into repository methods, or accept an optional transaction parameter. The caller acquires the client, issues BEGIN, calls repository methods with that client, then COMMITs or ROLLBACKs in a finally block. Some teams put transaction wrappers in a base repository class to reduce repetition.

**Q:** (Senior) How would you scale reads across multiple database replicas?
**A:** Create separate pools for reads and writes. Route SELECT queries to replicas, INSERT/UPDATE/DELETE to primary. The hard part is replication lag: a user writes data, immediately reads it, and gets stale data from a replica. Options: route that user's reads to primary for a short window, use read-after-write consistency checks, or accept eventual consistency. In node-postgres this means two Pool instances. Prisma handles it via replica configuration.

## Examples

### Basic: Repository with dependency injection

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

async findById(id) {
    const { rows } = await this.pool.query(
      'SELECT * FROM users WHERE id = $1',
      [id]
    );
    return rows[0]; // Output: { id: 1, name: 'Alice', email: 'alice@example.com' }
  }

async create(userData) {
    const { rows } = await this.pool.query(
      'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
      [userData.name, userData.email]
    );
    return rows[0]; // Output: { id: 42, name: 'Bob', email: 'bob@example.com' }
  }
}

// In your test file - no real DB needed
const mockPool = {
  query: jest.fn().mockResolvedValue({ rows: [{ id: 1, name: 'Test' }] })
};
const repo = new UserRepository(mockPool);
await repo.findById(1);
expect(mockPool.query).toHaveBeenCalledWith(
  'SELECT * FROM users WHERE id = $1',
  [1]
); // passes - exact SQL is verified
```

Constructor injection is the whole point. The repository has no idea whether it got a real pool or a mock. That indifference is what makes the test work.

### Intermediate: Service Layer with business logic

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

async createUser(data) {
    // Business rule: no duplicate emails
    const existing = await this.userRepo.findByEmail(data.email);
    if (existing) {
      const err = new Error('Email already exists');
      err.status = 409;
      throw err;
    }

const user = await this.userRepo.create(data);
    await this.emailService.sendWelcome(user.email); // side effect lives here
    return user;
  }
}

// routes/users.js
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); // error handler picks up status + message
  }
});
```

The route calls the service and sends the response. Nothing else. All decisions sit in the service. That's the boundary.

### Advanced: Transaction handling across multiple queries

```js
// Both updates must succeed or both must fail
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; // re-throw so the caller knows it failed
  } finally {
    client.release(); // always, no exceptions
  }
}
```

If the second UPDATE throws, ROLLBACK undoes the first. Account balances stay consistent. Without the transaction, a crash between the two queries leaves the sender's account debited with nothing credited on the other side.

Markdown · drag & drop images · ⌘B / ⌘I shortcuts1941 words

For the reviewer

Note to the moderator (optional)

Visible only to the moderator. Helps review go faster.