blog

Approaches for database connection with Express.js

While experimenting with different databases and Node.js, I saw several different approaches for pooling connections from the database and ensuring they can handle a lot of traffic.

When using MySQL, for instance, the simplest way to make a connection would be like this:

const mysql = require('mysql');
const express = require('express');
const app = express();

const dbSettings = {
    host    : 'localhost',
    user    : 'root',
    password: 'password',
    database: 'myDatabase'
};

// Index route
app.get('/', (req, res) => {

    const connection = mysql.createConnection(dbSettings).connect();

    // Here we get all rows from a table, end the connection,
    // and respond
    connection.query('SELECT * FROM Table', (err, doc) => {
        connection.end();

        if (err) {
            res.json(err);
        }
        else {
            res.json(doc);
        }
    });

});

This is a very simple way to connect to a database: for every request that we get, we connect to the database, fetch the results, end the connection, and respond. But it has a drawback: it is slow and does not support several connections at the same time.

To solve this problem, we can use a pool of connections - a cache of database connections that can be reused and can handle several connections at the same time. This is how it would look like using a pool:

const mysql = require('mysql');
const express = require('express');
const app = express();

const dbSettings = {
    connectionLimit : 100,

    host    : 'localhost',
    user    : 'root',
    password: 'password',
    database: 'myDatabase'
};

const pool = mysql.createPool(dbSettings);

// Index route
app.get('/', (req, res) => {

    pool.getConnection((err, connection) => {
        if (err) {
            res.json(err);
            return;
        }

        // Here we get all rows from a table, end the connection,
        // and respond
        connection.query('SELECT * FROM Table', (err, doc) => {
            connection.release();

            if (err) {
                res.json(err);
            }
            else {
                res.json(doc);
            }
        });
    });
});

This is a much better way to handle connections in production.

For NoSQL, however, the pattern that I found was a bit different: instead of starting the server before anything, we first connect to the database, and then we start the server. The connections will be kept alive until the application is terminated:

const mongodb = require('mongodb').MongoClient;
const express = require('express');
const app = express();

// Index route
app.get('/', (req, res) => {

    // Here we just get any document from a collection and respond
    app.locals.db.collection('myCollection').findOne({}, (err, doc) => {
        if (err) {
            res.json(err);
        }
        else {
            res.json(doc);
        }
    });

});

// Connecting to MongoDB before starting the server
mongodb.connect('mongodb://localhost:27017/myDatabase', (err, db) => {

    // Aborting in case of error
    if (err) {
        console.log('Unable to connect to Mongo.');
        process.exit();
    }

    // Making the connection available to the application instance
    app.locals.db = db;

    // After the connection has been stablished, we listen for connections
    app.listen(3000, () => console.log('Listening on port 3000'));

});