How to Use Connection Pooling in Express for Efficient Database Access

To use connection pooling in Express, you set up a pool from your database client (like mysql2 or pg) and reuse connections for queries instead of opening a new one each time. This improves performance by managing multiple connections efficiently within your Express routes.

Syntax

Connection pooling involves creating a pool object from your database client library. This pool manages multiple connections and provides them on demand.

Typical parts:

• createPool(config): Creates the pool with settings like max connections.
• pool.query(sql, params): Runs a query using a pooled connection.
• pool.getConnection(): (Optional) Manually get a connection from the pool.

javascript

const mysql = require('mysql2');

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

pool.query('SELECT * FROM table', (err, results) => {
  if (err) throw err;
  console.log(results);
});

Example

This example shows an Express app using mysql2 connection pooling to query a database efficiently on each request.

javascript

import express from 'express';
import mysql from 'mysql2/promise';

const app = express();

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

app.get('/users', async (req, res) => {
  try {
    // Use pool to query
    const [rows] = await pool.query('SELECT id, name FROM users');
    res.json(rows);
  } catch (err) {
    res.status(500).send('Database error');
  }
});

app.listen(3000, () => {
  console.log('Server running on http://localhost:3000');
});

Output

Server running on http://localhost:3000

// When visiting /users, JSON list of users is returned

Common Pitfalls

Common mistakes when using connection pooling in Express include:

• Not using a pool and opening a new connection on every request, causing slowdowns.
• Not handling errors properly, which can crash the app.
• Forgetting to configure connectionLimit, leading to too many open connections.
• Using callbacks with promise-based pools incorrectly.

javascript

/* Wrong: Creating new connection on every request */
app.get('/data', async (req, res) => {
  const connection = await mysql.createConnection({
    host: 'localhost', user: 'root', password: 'password', database: 'testdb'
  });
  const [rows] = await connection.query('SELECT * FROM data');
  await connection.end();
  res.json(rows);
});

/* Right: Use pool to reuse connections */
app.get('/data', async (req, res) => {
  const [rows] = await pool.query('SELECT * FROM data');
  res.json(rows);
});

Quick Reference

Tips for using connection pooling in Express:

• Always create the pool once and reuse it across your app.
• Set connectionLimit to control max simultaneous connections.
• Use async/await with promise-based clients for cleaner code.
• Handle errors gracefully to avoid crashes.
• Close the pool on app shutdown if needed.

Key Takeaways

Use a connection pool to reuse database connections and improve Express app performance.

Create the pool once and share it across your routes instead of opening new connections each time.

Configure connection limits to avoid overwhelming your database server.

Use async/await with promise-based pools for clean, readable code.

Handle errors properly to keep your app stable.