How to Cache MySQL Data with Redis® and Node.js

Published on August 18, 2022•Updated on June 22, 2024

Introduction

The principal advantage of Node.js is handling multiple requests by placing them in an event queue. This event-driven architecture makes Node.js highly suitable for coding modern real-time applications. Although Node.js is fast, you can improve its database access performance using a cache.

Many database caching solutions exist, but this guide focuses on Redis®. Redis® is a key-value store that utilizes your computer's RAM for storage. The RAM's transfer speed is several times faster than a typical solid-state drive (SSD).

When accessing databases with Node.js, you should use the Redis® server to cache query results for the frequently accessed data. For instance, you can cache products in e-commerce software, payment methods in accounting applications, or a list of countries in a customer registration database.

This guide shows you how to cache MySQL data with Redis® and Node.js on Ubuntu 20.04.

Prerequisites

To follow along with this guide:

1. Set Up a MySQL Database

The first step is setting up a database for permanently storing data on a disk. Because Redis® utilizes the computer's RAM for data storage, it is unsuitable for relational data. Although Redis® can persist data to disk, it isn't designed for that purpose and may not perform optimally. Follow the steps below to create a MySQL database, a user account, and a sample table:

Enter your MySQL root password and press Enter to proceed. Then, issue the following SQL commands to create a sample e-commerce database and a user account. Replace EXAMPLE_PASSWORD with a strong password.

 mysql> CREATE DATABASE e_commerce;
        CREATE USER 'e_commerce_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
        GRANT ALL PRIVILEGES ON e_commerce.* TO 'e_commerce_user'@'localhost';
        FLUSH PRIVILEGES;

Output.

 ...
 Query OK, 0 rows affected (0.01 sec)

Switch to the new e_commerce database.
```
 mysql> USE e_commerce;
```
Output.
```
 Database changed
```
Create a countries table. This table stores a list of countries for later use in frontend applications. For instance, when customers are signing up for your services. You should consider caching objects that rarely change, such as countries, payment methods, and products, to avoid serving stale data to frontend users.
```
 mysql> CREATE TABLE countries (
            country_id BIGINT NOT NULL PRIMARY KEY,
            country_name VARCHAR(100)
        ) ENGINE = InnoDB;
```
Output.
```
 Query OK, 0 rows affected (0.01 sec)
```

Insert sample data into the countries table.

 mysql> INSERT INTO countries (country_id, country_name) values ('1', "USA");
        INSERT INTO countries (country_id, country_name) values ('39', "ITALY");
        INSERT INTO countries (country_id, country_name) values ('86', "CHINA");
        INSERT INTO countries (country_id, country_name) values ('81', "JAPAN");
        INSERT INTO countries (country_id, country_name) values ('27', "SOUTH AFRICA");

Output.

 ...
 Query OK, 1 row affected (0.01 sec)

Query the countries table to ensure the data is in place.

 mysql> SELECT
            country_id,
            country_name
        FROM countries;

Output.

 +------------+--------------+
 | country_id | country_name |
 +------------+--------------+
 |          1 | USA          |
 |         27 | SOUTH AFRICA |
 |         39 | ITALY        |
 |         81 | JAPAN        |
 |         86 | CHINA        |
 +------------+--------------+
 5 rows in set (0.01 sec)

Log out from the MySQL server.
```
 mysql> QUIT;
```
Output.
```
 Bye
```

After setting up a database, the next step focuses on creating a Node.js code that queries the database table to retrieve the list of countries. Later, this guide shows you how to cache the results from the MySQL server to a Redis® server.

2. Create a `mysql_database` Class

To improve code readability and enhance support, you must create a separate module for each function in this sample application. The first module that you require is a mysql_database class. This class allows you to connect to the MySQL database and retrieve data from the countries table using the Node.js mysql module. Follow the steps below to create the class:

Make a project directory for your application.
```
 $ mkdir project
```
Navigate to the new project directory.
```
 $ cd project
```
Open a new mysql_database.js in a text editor.
```
 $ nano mysql_database.js
```

Enter the following information into the mysql_database.js file. Replace EXAMPLE_PASSWORD with the correct password for the MySQL user.

 class mysql_database {

     getData(callBack) {

         const mysql = require('mysql');

         const mysql_con = mysql.createConnection({
             host: "localhost",
             user: "e_commerce_user",
             password: "EXAMPLE_PASSWORD",
             database: "e_commerce"
          });

         mysql_con.connect(function(err) {
             if (err) {
                 console.log(err.message);
             }
         });

        var queryString = "select * from countries";

        mysql_con.query(queryString , [], function (err, result) {
            if (err) {
                callBack(err, null);
            } else {
                callBack(null, result);
            }
       });

     }
 }

 module.exports = mysql_database;

Save and close the mysql_database.js file after you're through with editing.

The mysql_database.js file explained:

The mysql_database.js file encloses the database operations in a mysql_database class.
```
 class mysql_database {
     ...
 }
```
The mysql_database class has one method. That is the getData(...) method.
```
 getData(callBack) {
     ...
 }
```
The getData(...) method takes the database credentials and creates a connection to the database using the mysql.createConnection({...}) and connect(...) functions.
The queryString variable stores the SQL command (select * from countries) that retrieves records from the countries table.
Towards the end, the getData(...) method assigns the result of the database query to a callBack function for further processing. Later, this guide shows you how to code the callBack function.
The module.exports = mysql_database; line at the end of the file instructs Node.js to make the module available in other source code files.

Your mysql_database class is now ready. You may import that class in any source code file that requires database operations using the require('./mysql_database'); statement.

3. Create a `redis_server` Class

Like the mysql_database class, you need a separate redis_server class to connect to Redis® that stores and retrieves key values.

Open a new redis_server.js in a text editor.
```
 $ nano redis_server.js
```

Enter the information below into the file. This guide uses the localhost (127.0.0.1) because you've deployed Redis® on your local machine. If you want to access a remote Redis® server, update this value with the correct public IP address of the remote host. By default, the Redis® server listens for incoming connections on port 6379.

 class redis_server {

     redisConnect() {

         const redis = require('redis');

         const redisClient = redis.createClient('127.0.0.1', 6379);

         redisClient.connect();

         redisClient.on('error', err => {
             console.log('Error ' + err);
         });

         return redisClient;
     }

     setData(data) {
         var redisClient = this.redisConnect();
         redisClient.set('countries', data);
     }

     getData(callBack) {
         var redisClient = this.redisConnect();
         var resp = redisClient.get('countries');

         resp.then(function(result) {
             callBack(null, result)
         });
     }
 }

 module.exports = redis_server;

Save and close the redis_server.js when you're through with editing.

The redis_server.js file explained:

The redis_server.js file contains one redis_server class.
```
 class redis_server {
     ...
 }
```
The redis_server class hosts three methods:
- redisConnect(){...}: This method imports the redis module and connects to the Redis® server using the redis.createClient(...) and redisClient.connect() functions.
- setData(data){...}: This method accepts a data string argument containing MySQL database results. The setData(data){...} method then uses the Redis® connection (this.redisConnect(...)) to save data to the Redis® server using the redisClient.set('countries', data) statement. The countries variable is the name of the Redis® key that stores the data in the Redis® server.
- getData(callBack){}: This function connects to the Redis® server (this.redisConnect()) and uses the Redis® redisClient.get('countries') function to retrieve the value of the countries key.
The module.exports = redis_server; statement at the end of the file allows you to expose and import the redis_server functions in other source code files.

The redis_server module is now ready. You can import it into other source code files using the require('./redis_server'); statement.

4. Create a `main.js` File

The last file you need for this project is the main.js file. This file executes when you run the application. The main.js file is an entry point to your application. Follow the steps below to create the file:

Open a new main.js file in a text editor.
```
 $ nano main.js
```

Enter the following information into the main.js file.

 const mysql_database = require('./mysql_database');
 const redis_server = require('./redis_server');

 const http = require('http');
 const hostname = '127.0.0.1';
 const port = 8080;

 const server = http.createServer(httpHandler);

 server.listen(port, hostname, () => {
     console.log(`Server running at http://${hostname}:${port}/`);
 });

 function httpHandler(req, res) {

     const mysqlDatabase = new mysql_database();
     const redisServer = new redis_server();

     redisServer.getData( (redisErr, redisResult) => {

         if (redisErr) {
             console.log(redisErr.message);
         } else {

             if (redisResult == null )  {

                 mysqlDatabase.getData((mysqlErr, mysqlResult) => {

                     jsonData = JSON.stringify(mysqlResult, null, 4)
                     redisServer.setData(jsonData);

                     var countries = {_source:'MySQL Server', data: JSON.parse(jsonData)};

                     res.write(JSON.stringify(countries, null, 4));
                     res.end();
                 });

             } else {

                 var countries = {_source:'Redis® Server', data: JSON.parse(redisResult)};

                 res.write(JSON.stringify(countries, null, 4));
                 res.end();
             }
     }

     });


 }

Save and close the main.js file.

The main.js file explained:

The first two lines at the top of the file import the mysql_database and redis_server modules you created earlier.

 ...
 const mysql_database = require('./mysql_database');
 const redis_server = require('./redis_server');
 ...

The following lines import the http server module. The module allows you to run the Node.js's inbuilt HTTP server in the application. The HTTP server listens for incoming connections on port 8080. You've then used the http.createServer(httpHandler); line to instruct the http module to forward HTTP requests to a httpHandler(...) function.
```
 ...
 const http = require('http');
 const hostname = '127.0.0.1';
 const port = 8080;

 const server = http.createServer(httpHandler);

 server.listen(port, hostname, () => {
     console.log(`Server running at http://${hostname}:${port}/`);
 });
 ...
```
The httpHandler(req, res) {...}) function runs when your application receives an HTTP request.
```
 ...
 function httpHandler(req, res) {
     ...
 }
 ...
```
Under the httpHandler(req, res) {...}) function, you're creating instances of MySQL (mysql_database) and Redis® (redis_server) database modules that you coded earlier using the following declarations.
```
 ...
 const mysqlDatabase = new mysql_database();
 const redisServer = new redis_server();
 ...
```
The redisServer.getData(..){..} function then queries the Redis® server to check if there is a value in the countries key.
```
 ...
 redisServer.getData( (redisErr, redisResult) => {
     ...
 }
 ...
```

The logical statement if (redisResult == null ) {...} statement further examines the Redis® result to check if Redis® returns a null value. A null value means that the Redis® server doesn't have a copy of the MySQL results. In such a situation, the application queries the MySQL database (mysqlDatabase.getData(...)), returns the value in JSON format, and caches the data to the Redis® server (redisServer.setData(jsonData);). If the Redis® server has already cached the data, it returns the result.

 ...
         if (redisErr) {
             console.log(redisErr.message);
         } else {

             if (redisResult == null )  {

                 mysqlDatabase.getData((mysqlErr, mysqlResult) => {

                     jsonData = JSON.stringify(mysqlResult, null, 4)
                     redisServer.setData(jsonData);

                     var countries = {_source:'MySQL Server', data: JSON.parse(jsonData)};

                     res.write(JSON.stringify(countries, null, 4));
                     res.end();
                 });

             } else {

                 var countries = {_source:'Redis Server', data: JSON.parse(redisResult)};

                 res.write(JSON.stringify(countries, null, 4));
                 res.end();
             }
     }
 ...

The JSON.stringify(mysqlResult, null, 4) function converts data to a JSON string when passing it to Redis®. The function also displays a nice JSON output to the calling function.

You've now coded all the required modules and created a main.js file for your application. The next step is testing the application.

5. Test the Application

Your application is now ready for testing. This step focuses on importing the modules you've used in this application, initializing your package directory, and running tests using the Linux curl command.

Initialize your application's project directory.

 $ npm init

Reply with the following information:

 package name: (project) redis-cache :key_enter:
 version: (1.0.0) 1.0.0 :key_enter:
 description: Redis cache with Node.js and MySQL :key_enter:
 entry point: (main.js) main.js :key_enter:
 test command: :key_enter:
 git repository: :key_enter:
 keywords: redis, cache, mysql :key_enter:
 author: test author :key_enter:
 license: (ISC) :key_enter:
 ...
 Is this OK? (yes) yes :key_enter:

Output.

 npm notice
 npm notice New minor version of npm available! 8.11.0 -> 8.15.1
 npm notice Changelog: https://github.com/npm/cli/releases/tag/v8.15.1
 npm notice Run npm install -g npm@8.15.1 to update!
 npm notice

Ignore the npm notice for now. Your application should run fine with the installed npm version.

Use the Node.js package manager (npm) to download the mysql and redis modules.

 $ npm install mysql
 $ npm install redis

Output.

 added 11 packages, and audited 12 packages in 2s
 found 0 vulnerabilities
 ...
 added 10 packages, and audited 22 packages in 4s
 found 0 vulnerabilities

Use the node command to run the application. That command has a blocking function. Don't enter any other commands in your active terminal window.
```
 $ node main.js
```
Your application spins up a web server and displays the following output.
```
 Server running at http://127.0.0.1:8080/
```
Connect to your Ubuntu server in another terminal window and run the following curl command to send a GET request to the application.
```
 $ curl -X GET http://localhost:8080
```

Check the value of the _source attribute (MySQL Server). Because you've run the application for the first time, the application returns data directly from the MySQL server.

 {
     "_source": "MySQL Server",
     "data": [
         {
             "country_id": 1,
             "country_name": "USA"
         },
         {
             "country_id": 27,
             "country_name": "SOUTH AFRICA"
         },
         {
             "country_id": 39,
             "country_name": "ITALY"
         },
         {
             "country_id": 81,
             "country_name": "JAPAN"
         },
         {
             "country_id": 86,
             "country_name": "CHINA"
         }
     ]
 }

Repeat the curl command. This time, the _source attribute's value changes to Redis® Server. The output shows that your application has successfully cached the MySQL data to the Redis® server and the application now serves all requests from Redis®.

 {
     "_source": "Redis® Server",
     "data": [
         {
             "country_id": 1,
             "country_name": "USA"
         },
         {
             "country_id": 27,
             "country_name": "SOUTH AFRICA"
         },
         {
             "country_id": 39,
             "country_name": "ITALY"
         },
         {
             "country_id": 81,
             "country_name": "JAPAN"
         },
         {
             "country_id": 86,
             "country_name": "CHINA"
         }
     ]
 }

Your application is working as expected.

Conclusion

This guide demonstrates the idea of caching MySQL data with Redis® in a Node.js application. Although this guide caches a single MySQL database table, you can extend the source code to cache other objects depending on your application use case. However, avoid serving stale data by establishing a cache invalidation policy. A good policy should help you invalidate the Redis® keys after updating the base values in the MySQL server.

How to Cache MySQL Data with Redis® and Node.js

Introduction

Prerequisites

1. Set Up a MySQL Database

2. Create a `mysql_database` Class

3. Create a `redis_server` Class

4. Create a `main.js` File

5. Test the Application

Conclusion

Products

Features

Marketplace

Resources

Company

How to Cache MySQL Data with Redis® and Node.js

Introduction

Prerequisites

1. Set Up a MySQL Database

2. Create a mysql_database Class

3. Create a redis_server Class

4. Create a main.js File

5. Test the Application

Conclusion

2. Create a `mysql_database` Class

3. Create a `redis_server` Class

4. Create a `main.js` File