Using MySQL with Express Routes

Photo by Oliver Paaske on Unsplash

This is a walkthrough on using MySQL with Express.js routes. This walkthrough assumes MySQL is already installed on the development machine, and that the reader has MySQL knowledge/experience. The MySQL queries seen are based on a fictional apiary business database. You will need to create a MySQL database and tables on your own; based on any dataset you want.

Getting Started

Create a new directory for the Express app. Then, create the Express app using npx express-generator.

This will produce the following:

create : public\
create : public\javascripts\
create : public\images\
create : public\stylesheets\
create : public\stylesheets\style.css
create : routes\
create : routes\index.js
create : routes\users.js
create : views\
create : views\error.jade
create : views\index.jade
create : views\layout.jade
create : app.js
create : package.json
create : bin\
create : bin\www

Now that the basic Express app has been created, run npm install at the root of the project. (You can find all of the Express.js setup info here: expressjs.com).

Next, run npm start. In your browser, you should be able to view the Express app running at http://localhost:3000/. This page is being rendered from the server-side, using Jade: jade-lang.com.

Next, run npm install mysql2 to download the mysqljs2 npm package. This is a MySQL driver for Node.js: https://www.npmjs.com/package/mysql2.

To recap everything that just took place:
1. A new directory was created. This directory will hold the Express app.
2. Inside the directory, the Express app was created by running npx express-generator.
3. At the root of the Express app, npm install was run in order to install all of the needed NPM packages and dependencies.
4. Still at the root of the Express app, npm start was run. This starts a local server and runs the app.
5. Finally, the MySQL driver for Node.js was installed by running npm install mysql2.

The Basic Implementation

The most basic usage of MySQL with Express would look something like:

// app.js file.
// 1.
var express = require('express');
var mysql = require('mysql2');
// 2.
var app = express();
// 3.
var database = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'companyDB
});
// 4.
database.connect((err => {
    if (err) throw err;
    console.log('MySQL Connected');
}));
// 5.
app.get('/inventory', (req, res) => {
    let sql = 'SELECT * FROM inventory';
    connection.query(sql, (err, result) => {
        if (err) throw err;
        console.log(result);
        res.send('Inventory received');
    });
});

1. Here, the express and mysql2 node packages are required.
2. The Express app is initialized.
3. The database connection options are set up via the createConnection method. There are a few options that need to be passed through here.

First, the host. This is the hostname of the database, or, the server where the database is hosted. Since it’s common to have one server for JavaScript code, and one for the database, the JavaScript code needs to know the name of the server the database is running on. Using `localhost` is stating that the database and scripts run on the same server. If your database is hosted elsewhere, then this value would represent that URL. For example, if you’re using MySQL with Amazon Relational Database Service, it would look something like mydb.123456789012.us-east-1.rds.amazonaws.com.

The next option is the user. This is root in the example, as that is the name I gave the main user when setting up MySQL. Please note that your main user’s name might be different than root. The main user doesn’t have to be used when creating the MySQL connection — just make sure the user you do use has access to the database. Next, the user’s password. And finally, the name of the database.

4. After setting up the connection options, the next step is to establish the connection with the database. This step is crucial; without it, the database connection is never established. The connect method takes a callback function as a parameter and the callback function will handle any errors; in this case, the error is simply thrown. If there is no error, then the database has successfully established a connection.

5. The last bit of code is a GET request, with the endpoint /inventory. This endpoint fires off a query to the database using the string in the sql property. The query will check for any errors, then print the result to the console and return the message Inventory received as a response.

To reiterate the objective of this article: I wanted to be able to access the database within different files of the Express app instead of solely through the app.js file, as seen previously. I have found that this can be done in two ways; either through the createConnection method as seen in the previous example or through the createPool method.

Use a Database Connection

By using createConnection alongside the connect method, a single connection on a single thread is created and established. The connect method is what establishes the connection, while the connection.end() method closes a connection. Below are two ways of using a single database connection.

Singular Shared Connection

The first way to use a single database connection is to create a “singular shared connection” and pass it by reference throughout the application. A singular shared connection is always going to run synchronously, which can be an issue. If a single connection is used for multiple requests, then each subsequent request is going to be delayed until the previous one is fulfilled. For example, there are two users actively using the app: UserA and UserB. If UserA requests data from TableA, then UserB requests data from TableA, the request from UserB will not be initiated until the request from UserA is complete because the thread is blocked by the first request.

To create a singular shared connection, the database setup work is going to be moved out of the app.js file, and into a new file; database.js. This file can be anywhere, but I’ve put it at the root of the project. Here is what the file looks like:

// database.js file.
var mysql = require(‘mysql2’);
 
// 1.
require(‘dotenv’).config();
 
// 2.
var connection = mysql.createConnection({
    host : process.env.DATABASE_HOST,
    user : process.env.DATABASE_USER,
    password : process.env.DATABASE_PASSWORD,
    database : process.env.DATABASE_NAME
});
connection.connect((err => {
    if(err) throw err;
    console.log(‘MySQL Connected’);
}));
 
// 3.
exports.databaseConnection = connection;

There are some additions to this version of creating and connecting to a database:
1. require(‘dotenv’).config();. I’ve moved all of the sign-in info into a .env file. This line of code allows the usage of environment variables within a file.
2. Here, all of the raw data has been replaced with the corresponding environment variable.
3. The connection property is exported as databaseConnection. This is what allows the usage of the database within different files throughout the app.

The require method is used to include modules that have been exported. To use the database, it must be “required” in whatever file needs access. In this case, the app.js file needs access:

// app.js file.
var express = require(‘express’);
var app = express();
 
// 1. Require the connection to the database.
var connection = require(‘./database’).databaseConnection;
 
// 2. Make the GET request.
app.get(‘/inventory’, (req, res) => {
    let sql = ‘SELECT * FROM inventory’;
 
    connection.query(sql, (err, result) => {
        if(err) throw err;
        console.log(result);
        res.send(‘Inventory received’);
    });
});
 
module.exports = app;

1. Require the module that was exported from the database.js file. You can see here that I just added the .databaseConnection property to the end. This is for conciseness and quick access to the property.
2. In this example, the HTTP endpoint is in the app.js file, but the basic concept of importing the database can be used within any routes of the Express app, as seen below:

// routes/inventory.js
let express = require(‘express’);
let router = express.Router();
 
let connection = require(‘../database’).databaseConnection;
 
router.get(‘/get-inventory’, (req, res) => {
    let sql = ‘SELECT * FROM inventory’;
    
    connection.query(sql, (err, result) => {
        if(err) throw err;
        console.log(result);
        res.send(‘Inventory received.’);
    });
});
 
module.exports = router;

This runs the same query as seen before, but using the Express.Router module. If this file was in a project, the file path would be routes/inventory.js. To learn more about routing in Express, check out the Express docs.

This was the first way to use a single database connection. This type of connection remains open, is passed by reference throughout the app, and is never closed down. You can think of it as a “singular shared connection.” This isn’t the best method to use, as users will be blocked if there are multiple users making requests; but it’s a good place to start.

Setting Up and Tearing Down Connections

Another way to use single database connections is by setting up and tearing down the connection as it’s needed. This can be accomplished by creating the database connection in each API call that needs access to the database, making the query to the database, then closing the connection.

router.get(‘/get-inventory’, (req, res) => {
    let sql = ‘SELECT * FROM inventory’;
 
    // Create the connection with the applicable information.
    var connection = mysql.createConnection({
        host : process.env.DATABASE_HOST,
        user : process.env.DATABASE_USER,
        password : process.env.DATABASE_PASSWORD,
        database : process.env.DATABASE_NAME
    });
 
    // Establish a connection to the database by invoking a query.
    connection.query(sql, (err, result) => {
        if(err) throw err;
        console.log(“My SQL Connected via a new open connection.”);
         res.send(‘Inventory received.’);
    });
 
    // Close the connection.
    connection.end((err => {
        if(err) throw err;
        console.log(“Connection closed.”);
    }));
});

Each request will look similar to this — create a connection, make the query, then close the connection. Again, this is not the most efficient way to access the resources of a database, but it can prevent the users from getting blocked. The blocking is prevented because connections are constantly being created and shut down whenever they are needed, rather than a single connection being used for all requests. This method is resource-intensive, and a better way to manage multiple connections is by using a “connection pool.”

Use a Connection Pool

In the previous example, the database was created via the createConnection method. Using this method, a one-to-one connection is created and managed throughout the app. Instead of managing connections to the database one by one, connection pools allow for the re-use of database connections.

A connection pool works by creating connections, using the connections, then storing them until the next time a connection to the database is needed. This prevents new connections from being created constantly, which reduces the resources needed to create and manage connections. When using a connection pool, new connections are only created if all of the previously created connections in the pool are being used, and the total amount of connections does not equal the value of the limit set for the pool. If the limit is reached, new connections will not be made. Instead, they will be queued until a connection in the pool becomes available. Let’s see how this works in code.

A quick note from the MySQL.js docs:

Connections are lazily created by the pool. If you configure the pool
to allow up to 100 connections, but only ever use 5 simultaneously,
only 5 connections will be made. Connections are also cycled
round-robin style, with connections being taken from the top of the
pool and returning to the bottom.

I have created a connection pool within the same database.js file that was previously used. Most of the options are the same as the ones in the createConnection method, but there is an additional option used here called connectionLimit. connectionLimit determines the maximum number of connections to the database that can be created.

The database.js file:

require(‘dotenv’).config();
 
var pool = mysql.createPool({
    host : process.env.DATABASE_HOST,
    user : process.env.DATABASE_USER,
    password : process.env.DATABASE_PASSWORD,
    database : process.env.DATABASE_NAME,
    connectionLimit : 15
});
 
exports.pool = pool;

Again, very similar to creating a connection via the createConnection method. The only difference here is the aforementioned connectionLimit setting, which sets the limit to 15 in this case.

To make a query on the database, the query method is used:

pool.query(‘SELECT * FROM inventory’, (err, result) => {
    if (err) throw err;
    console.log(result);
});

The docs state that this is a shortcut for the pool.getConnection() -> connection.query() -> connection.release() code flow. The shortcut is sufficient in cases where only a single query statement is being used. If there are multiple queries that fire off sequentially and need to use the same connection, writing out the entire pool.getConnection() method is the most efficient way to do this.

First off, pool.getConnection. This method requests a connection from the pool. If an unused connection is available, it’s returned by the pool. If there are no unused connections available, the pool will create a new connection as long as the limit has not been reached. If the limit has been reached, the pool will wait until a connection is available. The method takes a callback as a parameter, and the callback has two of its own parameters — err and connection.

// Obtain a connection from the pool.
pool.getConnection((err, connection) => {
    if (err) throw err;
});

Secondly, the connection.query() method sends a query to the database and will either return an error, or the data that was queried.

pool.getConnection((err, connection) => {
    if (err) throw err;
 
    // Add the query.
    connection.query(‘SELECT * FROM inventory’, (err, result) => {
        if (error) throw error;
        console.log(results);
        res.send(‘Inventory received via pool.’);
    });
});

Finally, to release the connection back to the pool, the connection.release() method is used. The connection should only be released after all queries have been made and the connection is no longer needed. By releasing the connection, it becomes available for use in the future and prevents the need to open a new one.

pool.getConnection((err, connection) => {
    if (err) throw err;
    
    connection.query(‘SELECT * FROM inventory’, (err, result) => {
        if (error) throw error;
        console.log(results);
        res.send(‘Inventory received via pool.’);
        // Release the connection.
        connection.release();
    });
});

This method is used in an API call, similarly to the singular database connection:

// routes/inventory.js
require(‘dotenv’).config();
let express = require(‘express’);
let router = express.Router();
let pool = require(‘../database’).pool;
 
router.get(‘/get-inventory’, (req, res) => {
    let sql = ‘SELECT * FROM inventory’;
 
    pool.getConnection((err, connection) => {
        if (err) throw err;
 
        connection.query(sql, (err, result) => {
            if (err) throw err;
            console.log(result);
            res.send(‘Inventory received via pool.’);
 
            connection.release();
        });
    });
});

If the use case calls for completely removing the connection from the pool after it has been used, connection.destroy() would replace connection.release(). If the connection is destroyed, the next time a connection is needed the pool will create one if:
1. There is not one available for use *and,*
2. The limit of connections has not been met.

The value of using a connection pool is clear when it comes to using resources efficiently. A singular shared connection almost guarantees that user requests will be blocked, and creating and destroying connections constantly is a blatant waste of resources. By using a connection pool, users will have access to connections faster than if they were created upon request, and their requests are much less likely to get blocked.

Conclusion

Thanks for reading! Hopefully, this brought some clarity on how to use MySQL and Express. There are certainly more concepts that can be extended upon here, so feel free to use the source code as a starting place.

Further Resources:
- Express.js, Express Routing Guide.
- Stack Overflow, How to properly pass mysql connection to routes with express.js.
- Traversy Media, Using MySQL With Node.js.
- mysql2: A Node.js driver for JavaScript.

Cheers, and happy coding.