As developers, we frequently interact with databases to store and retrieve data, that is our source of truth. We need to be careful with how we manage connections to the database, because as the app scales, poorly constructed connection handling can lead to performance bottlenecks, memory leaks, and even database crashes.

If you have ever experienced too many connections errors or had problems with slow queries, you are not alone. In this post, I will be explaining the best practices for managing database connections efficiently.

Why Proper Database Connection Management Matters

We can think of databases as limited capacity restaurants: they can only serve a certain number of customers at a time. If too many people come in, the service might slow down, or even worse, they can start turning people away.

Almost the same thing happens with databases. If your app opens a new connection for every request without closing it, you will quickly run out of available connections, and this will lead to failures and degraded performance.

Here are some common issues caused by poor connection handling:

  • Memory leaks: Unclosed connections consume more and more system resources.
  • Connection exhaustion: Your database runs out of free connections, and not allowing new requests to be processed.
  • Slow performance: Having too many open connections might lead to slow query execution.

If you are experiencing any of these issues, it is time to start reconsidering your connection handling strategy.

Best Practices for Managing Database Connections

1. Use a Connection Pool Instead of Creating New Connections

It is important to understand that creating a new database connection for every request is inefficient. Instead, you should use a connection pool, which reuses existing connections to handle multiple requests.

Most of the popular database clients in Node.js support connection pooling.

Here is how connection pooling works, with some keywords to remember about this concept:

  • First, it creates a pool of connections to the database for later use.
  • When your app needs a database connection, it borrows one from the created pool.
  • Once the query is executed, the connection is released back to the pool for reuse.
  • This concept is allowing us to reuse the same connections, instead of creating a new one for every request.

Example: Using a Connection Pool with PostgreSQL (pg)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

const { Pool } = require('pg');

const pool = new Pool({
  user: 'your_username',
  host: 'your_database_host',
  database: 'your_database_name',
  password: 'your_password',
  port: 5432,
  max: 10, // Maximum number of connections in the pool
  idleTimeoutMillis: 30000, // Close idle connections after 30 seconds
});

// Using the connection pool
async function fetchUsers() {
  const client = await pool.connect();
  try {
    const result = await client.query('SELECT * FROM users');
    return result.rows;
  } finally {
    client.release(); // Release connection back to the pool
  }
}

Key points to remember:

  • max: This is the maximum number of connections to be stored in the pool.
  • idleTimeoutMillis: This is the time in milliseconds after which idle connections will be closed to free up resources.
  • Always call client.release() to return the connection back to the pool.

Tip: You should fine-tune the max value based on your application’s needs and the capabilities of your database. Having too many connections in the pool while you don’t need them might lead to performance issues, because every connection is consuming resources on your database whether it is being used or not.

2. Avoid Global Database Connections

Another common mistake is creating a single global database connection and trying to reuse it across all requests. This approach has two major problems:

  • Concurrency issues: If multiple requests are using the same connection, it can lead to unexpected behaviors.
  • Potential connection loss: If the connection drops, the entire app might break because all requests are depending on that connection.
1
2
3
4
5
6
7
8

// ❌ Bad practice: Single global connection
const client = new Client({ /* config */ });
await client.connect();

app.get('/users', async (req, res) => {
  const result = await client.query('SELECT * FROM users'); 
  res.json(result.rows);
});

Instead, use a connection pool, as shown in the previous section.

3. Handle Connection Errors Properly

Databases, like any other service, can fail for various reasons. It might be a network issue, incorrect credentials, or high load, or any other reason we don’t have control over. We should always handle connection errors to prevent our app from crashing.

Example: Handling Errors with PostgreSQL (pg)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

// main error handler for the connection pool
pool.on('error', (err) => {
  console.error('Database error:', err.message);
  handleDatabaseError(err);
});

function handleDatabaseError(err) {
  if (isTransientError(err)) {
    console.warn('Temporary issue detected:', err.message);
    retryConnection();
  } else if (isFatalError(err)) {
    console.error('Critical database failure:', err);
    gracefulShutdown();
  }
}

I am not putting any details for the helper functions, because they are not the main point of this post and the implementation details can be different for different databases. What I want to stress here is the concept of handling errors properly.

Tip: Always log errors but avoid exposing sensitive database details in error messages. Remember that these logs can be accessed by malicious users, so be careful with what you log.

4. Optimize Queries and Indexing

Even if everything is working fine with connection management, poorly optimized queries can still slow down your database. Here are some best practices you should consider while writing queries:

  • Use indexes: You should use indexes on frequently queried columns. But also remember that indexes can slow down the insert and update operations if you use them on every column. You should analyse your needs and create indexes accordingly. Always try to find a balance between query performance and write operations.
  • Avoid SELECT *: You don’t need to fetch all the columns from the table. Only fetch the columns you need.
  • Use connection pooling along with efficient queries for the best performance.

For PostgreSQL, you can check query performance using:

1

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

For MongoDB, use:

1

db.users.find({ email: "test@example.com" }).explain("executionStats");

Conclusion

Managing database connections might seem like a small detail, but it can make or break your app’s performance. Here is what you should take away:

  • Connection pools are your friend they handle the heavy lifting of connection management
  • Always handle errors properly: Make sure your app can handle DB issues
  • Keep an eye on your queries and indexes - even the best connection setup won’t save poorly written queries

Remember, your database is a finite resource. Treat it wisely, and it will serve your app well. If you are dealing with connection issues in your Node.js app right now, start by implementing a connection pool and proper error handling, and you will likely see immediate improvements.

Got questions or experiences to share about database connection management? Drop them in the comments below!