Connection pools in Node.js with MariaDB

TL;DR:

• Find the GitHub project here: github.com/andyj/mariadb-connection-pool

Why Use a Connection Pool?

Database connection pooling critical for your web applications. Without a pool, every database query spins up a new connection which startes eating up your resources! A connection pool keeps a stash of open connections ready to go, slashing overhead and boosting performance.

This also gave me an excuse to go back over some old code of mine and start putting in console methods for debugging that I've covered recently

Benefits of Connection Pooling

• Reduced latency: Reusing connections cuts out the time spent establishing new ones.
• Better resource management: Caps the number of simultaneous connections to keep your database happy.
• Improved scalability: Handle more users without breaking a sweat.

Setting Up a MariaDB Connection Pool in Node.js

First things first, you'll need the mariadb package. Install it using:

npm install mariadb dotenv

Here's how to set up the pool:

Connection Pool Configuration

// db.js
require('dotenv').config(); // Load environment variables

const mariadb = require('mariadb');

const pool = mariadb.createPool({
  host: process.env.DB_HOST,         // Use DB_HOST environment variable from .env
  user: process.env.DB_USER,         // Use DB_USER environment variable from .env
  password: process.env.DB_PASSWORD, // Use DB_PASSWORD environment variable from .env
  database: process.env.DB_NAME,     // Use DB_NAME environment variable from .env
  port: process.env.DB_PORT,         // Use DB_PORT environment variable from .env
  connectionLimit: 10                // Maximum number of connections in the pool
});

module.exports = pool;

This creates a pool with up to 10 connections. Feel free to tweak the connectionLimit based on what your app needs.

Using the Pool in Your Application

Let's look at how to use the connection pool in an Express route. We'll sprinkle in some advanced console methods to make debugging a breeze.

const express = require('express');
const pool = require('./db'); // Import the pool
const app = express();

app.get('/data', async (req, res) => {
  console.group('Database Query');
  console.time('Query Time');

  let conn;
  try {
    conn = await pool.getConnection();
    console.assert(conn, 'Failed to obtain a database connection');

    const rows = await conn.query('SELECT current_timestamp()');
    console.table(rows);

    res.json(rows);
  } catch (err) {
    console.error('Error executing query');
    console.trace(err);
    res.status(500).send('Database query error');
  } finally {
    if (conn) conn.release(); // Release the connection back to the pool
    console.timeEnd('Query Time');
    console.groupEnd();
  }
});

app.listen(3000, () => {
  console.log('Server is running on port 3000');
});

Here's what those console methods are doing for you:

• console.group() and console.groupEnd(): Groups related log messages, making your console output neater.
• console.time() and console.timeEnd(): Measures how long your query takes, helping you spot performance issues.
• console.assert(): Checks if you got a connection. If not, it logs an error message.
• console.table(): Displays your query results in a nice table format in the console.
• console.trace(): Prints a stack trace from the point where it's called, super handy for debugging errors.

By using these methods, you get a clearer picture of what's going on under the hood, which is clutch when you're trying to debug or optimize your app.

Best Practices

• Keep an eye on pool usage to tweak connectionLimit as needed.
• Use connection timeouts to handle idle or long-running queries.
• Implement error handling to gracefully manage database hiccups.
• Leverage advanced console methods to make debugging smoother.

Conclusion

The mariadb package makes it easy to manage database connections efficiently.

And if you’re using managed database services like AWS RDS for MariaDB or Azure Database for MariaDB, there are additional settings you should consider tweaking for optimal performance:

• Connection Pool Limits: AWS and Azure instances often come with predefined limits on concurrent connections based on the instance size. Make sure your connectionLimit aligns with these constraints to avoid hitting caps that could cause connection errors.
• Timeouts and Idle Connections: Both AWS and Azure charge for resources tied to active connections. Use settings like idleTimeoutMillis to close unused connections after a certain period. This helps in reducing costs and freeing up database resources.
• Max Allowed Packet: Tweak this for handling large queries or data sets, especially if your app processes bulk data.
• Monitoring and Scaling: Both platforms offer native monitoring tools (e.g., Amazon CloudWatch or Azure Monitor) to keep an eye on metrics like CPU usage, connections, and query performance. Use these insights to adjust your pool size dynamically.