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: ```javascript 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: ```javascript 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: ```javascript 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')); }); ```