NodeJS CRUD operation with MySQL

In this chapter, we will discuss in detail about connecting our Node.js  travel application service with MySQL server and perform various CRUD operations. This chapter is a enhancement to our previous chapter which is as follows:

https://www.nodexplained.com/crud-operations-with-express-js-using-in-memory-storage-travel-application/

If you haven't already installed MySQL server in your machine, go to the following links to install and configure it.

https://www.nodexplained.com/install-and-configure-mysql-on-linux-macos-and-windows-machines/

https://www.nodexplained.com/create-database-and-perform-crud-operations-in-mysql-server/

Let's create a configs folder at the root directory & then create a config file for storing database credentials inside of that folder. Also, create a helpers folder at the root directory and then create a helper file for connecting with MySQL server inside of that folder. Issue following commands:

   
mkdir configs && cd configs && touch database.js
cd ../
mkdir helpers && cd helpers && touch database.js
cd ../
   

Now our directory structure looks like below:

Directory structure of Node.js project after integrating mysql database

To interact with MySQL database from Node.js service, we need a MySQL driver. When it comes to MySQL drivers, we have multiple options and some of the most popular ones are as follows:

https://github.com/mysqljs/mysql

https://github.com/sidorares/node-mysql2

We can use either of these package and both are extremely popular among the developer communities. Navigate to the following link to see a comparison between two:

https://npmcompare.com/compare/mysql,mysql2


For this chapter, we will be using node-mysql2 package, as it is getting more popular and the frequency of releases with new features and/or enhancements is also higher with it. As per the official documentation, MySQL2 is mostly API compatible with mysqljs and supports majority of features. MySQL2 also offers these additional features:

Connecting to MySQL server from node.js service

Issue following command to install mysql2 package in our project repository:

   
	npm install --save mysql2
   

To connect with a database server, we need database credentials and connection details. Let's store these informations in our config file. In configs/database.js file, paste the following contents:

   
module.exports = {
    host: 'localhost',
    port: 3306,
    user: 'writer',
    password: 'Tr#2022NpK',
    database: 'travel_app'
};
   

Note:

Providing database credentials as shown above is not recommended at all and can cause a serious security issue to our application. Correct way of providing credentials value is using environment variables, which we will discuss in our later chapters.

In helpers/database.js file, start by importing mysql2 package in the following way:

   
	const mysql = require('mysql2');
   

Also, import configs/database.js file:

   
const dbConfig = require('../configs/database');
const { host, port, user, password, database } = dbConfig;
   


Next, we need to establish a connection with MySQL database from our Node.js service. For that, we have a createConnection() function exposed by mysql instance, which can be used to create a connection object. It accepts many connection options and some of the most commonly used ones are as follows:

  • host

    location of a server in which database server is hosted. Default value is localhost.
  • port

    port exposed on a server in which MySQL server listens for database connection requests. Default value is 3306.
  • user

    username of a user having access to the specified database.
  • password

    password of a user having access to the specified database.
  • database

    name of a database to which we want to establish a connection from node.js service
  • multipleStatements

    Supports the execution of a string containing multiple SQL statements separated by semicolon ( ; ) characters

For other options, refer to the following link:

https://github.com/sidorares/node-mysql2/blob/master/lib/connection_config.js

   
// create a connection to the database
const connection = mysql.createConnection({
      host,
      port,
      user,
      password,
      database,
      multipleStatements: true
    });
   

We can use this connection object to perform various actions on MySQL tables. Our final helpers/database.js file looks like below:

   
const mysql = require('mysql2');
const dbConfig = require('../configs/database');
const { host, port, user, password, database } = dbConfig;
const logger =  require('../utils/logger');

const getDatabaseConnection = () => {
  try {
    // create a connection to the database
    const connection = mysql.createConnection({
      host,
      port,
      user,
      password,
      database,
      multipleStatements: true
    });
    logger.debug('Connection to MySQL database is successful');
    return connection;
  } catch (err) {
    logger.error('Error Connecting to MySQL database ', JSON.stringify(err));
    throw err;
  }
}

module.exports = getDatabaseConnection();
   


Import this database helper file in a controller hotel/index.js file and run the node application server.

   
const { v4: uuidv4 } = require('uuid');
const dbConnection = require('../../helpers/database');
   

If we see a message “Connection to MySQL database is successful” in the terminal, then our node.js travel service has successfully connected to the MySQL database. If some other message is shown, then verify the correctness of mysql server credentials again.

The above connection object has a method named query(), which can be used to execute various SQL scripts. This method takes three parameters:

  • SQL query
  • Array (optional)

    Whenever we use placeholders (?) in a SQL query, then we pass the placeholder values in an array
  • Callback function

And the callback function also accepts three parameters –

  • error

    Any errors generated due to execution of SQL query  is assigned to this object
  • results

    results of a successful query execution is assigned to this object
  • fields (optional)

    It contains extra meta data about results

Let's implement query() method to fetch a list of hotels using above database connection object:

   
const listAllHotels = async (req, res) => {
    try {
        req.logger.debug('Fetching all the hotel information');
        dbConnection.query('SELECT * FROM hotels', (err, rows, fields) => {
          if (err) {
            throw err;
          }
          res
          .status(200)
          .json({
              data: rows
          })
        });
    } catch (err) {
        req.logger.error('Error fetching hotels ', err);
        throw err;
    }
}
   

As we all know, with callbacks, there is always a chance that a code logic might end up with deeply nested callbacks, which makes our code unreadable. If proper care is not taken, it can also result in callback hell. Wherever possible, we should always try to use async-await feature. MySQL2 also support Promise API, which works very well with ES7 async await. Let's re-write our database connection helper file using promise wrapper of mysql2 package.

Modify helpers/database.js in the following way:

   
const mysql = require('mysql2/promise');
const dbConfig = require('../configs/database');
const { host, port, user, password, database } = dbConfig;
const logger =  require('../utils/logger');

const getDatabaseConnection = async () => {
  try {
      // create a connection to the database
    const connection = await mysql.createConnection({
      host,
      port,
      user,
      password,
      database,
      multipleStatements: true
    });
    logger.debug('Connection to MySQL database is successful');
    return connection;
  } catch (err) {
    logger.error('Error Connecting to MySQL database ', JSON.stringify(err));
    throw err;
  }
}

module.exports = getDatabaseConnection();
   

Now, we can replace callbacks with async-await feature. It makes the code super clean and also readable. Let's re-write our listAllHotels() function in the following way:

   
const listAllHotels = async (req, res) => {
    try {
        req.logger.debug('Fetching all the hotel information');
        const db = await dbConnection;
        const result = await db.query('SELECT * FROM hotels');
        res
          .status(200)
          .json({
              data: result[0]
          })
    } catch (err) {
        req.logger.error('Error fetching hotels ', err);
        throw err;
    }
}
   

When we execute SQL statement, it will return the response in an array (length = 2), with first element of an array being the actual data and last element of an array being metadata about the results.

We will be using Postman to test all the api endpoints of a travel-application.

MySQL crud operation using Node.js - List all the records

To fetch detailed information about a particular hotel, we can use hotelId in SQL query in the following way:

   
const getHotelDetailInformation = async (req, res) => {
    let hotelId = '';
    try {
        const hotelId = req.params.hotelId;
        req.logger.debug('Fetching detailed information about the hotel for id ', hotelId);
        const db = await dbConnection;
        const result = await db.query(`SELECT * FROM hotels WHERE hotel_id = "${hotelId}"`);
        res
          .status(200)
          .json({
              data: result[0]
          });
    } catch (err) {
        req.logger.error(`Error fetching hotel detail info with id ${hotelId}`, err);
        throw err;
    }
}
   

MySQL crud operation using Node.js - Fetch detail info about a particular record

As we can see, from above image, api endpoint returns the correct result. Everything works and seems perfect. But, there is some serious security flaw in the code that we have implemented above. The way we have executed SQL queries can cause SQL injection attacks. SQL injection attacks is one of the most common attack techniques used by malicious users, in which malicious SQL statements are inserted into an user inputted field for execution. Here, in our get detail api, we can supply SQL Code as a value for the hotelId parameter.

Sensitive Information Leaks due to SQL Injection Attack


Let's try to pass SELECT * FROM users where "1" = "1 as part of hotelId query parameter value in the following way:

http://localhost:3000/api/hotel/374d85fa-fbb7-43d7-82fa-2497fe7f3472"; SELECT * FROM users where "1" = "1

MySQL data leak due to SQL injection attack

From above image, we can see, users data is also sent in the response. This is a very critical issue as malicious attackers can use those sensitive information for comprising user accounts.

Data Deletion due to SQL Injection Attack


Let's pass DELETE FROM hotels where "1" = "1 as part of hotelId query parameter value in the following way:

http://localhost:3000/api/hotel/d347d745-c10a-4aa6-9d88-110be9145f93"; DELETE FROM hotels where "1" = "1

MySQL data delete action due to SQL injection attack

It still seems okay as we are getting correct results in the response. Also, in the response, there is some additional unexpected data as well. Let's try to fetch hotel details again as shown in below image:

MySQL data delete verify

In the response, we can see an empty array, meaning all of the data in a hotels table is deleted due to SQL Injection Attack. We’re now in a serious trouble. Even though we can recover data from logs or backups, it takes some time to recover the data, thereby causing major disruptions in the customer experience. Still today, this is one of the most common attack vectors used by malicious attackers to try to compromise software services. But no worries, we can easily prevent against SQL Injection Attacks using following methods:

Preventing SQL Injection Attacks - Escape User Input


To prevent this attack, we can use mysql.escape() method to sanitize any user inputted value in the following way:

Note:

You need to import mysql package for running this function.

   
const getHotelDetailInformation = async (req, res) => {
  let hotelId = '';
  try {
      const hotelId = req.params.hotelId;
      req.logger.debug('Fetching detailed information about the hotel for id ', hotelId);
      const db = await dbConnection;
      const result = await db.query(`SELECT * FROM hotels WHERE hotel_id = ${mysql.escape(hotelId)}`);
      res
        .status(200)
        .json({
            data: result[0]
        });
  } catch (err) {
      req.logger.error(`Error fetching hotel detail info with id ${hotelId}`, err);
      throw err;
  }
}
   

MySQL prevent SQL Injection Attack - Escape input values

As we can see, when we inject SQL query in the hotelId parameter value, it just treats SQL query as a string value, for which we have no records in the hotels table.

Preventing SQL Injection Attacks - Using Prepared Statements

With MySQL2, we also get the prepared statements. With prepared statements, MySQL doesn't have to prepare plan for same query every time, this results in better performance. MySQL connection object also provides execute() method which will prepare and query the statement. It is highly recommended to use prepared statements as it prevents SQL Injection attacks.

To fetch list of hotels using execute() method:

   
const listAllHotels = async (req, res) => {
    try {
        req.logger.debug('Fetching all the hotel information');
        const db = await dbConnection;
        const result = await db.execute('SELECT * FROM hotels;');
        res
          .status(200)
          .json({
              data: result[0]
          })
    } catch (err) {
        req.logger.error('Error fetching hotels ', err);
        throw err;
    }
}
   

To fetch detailed information of a hotel by hotelId using execute() method:

   
const getHotelDetailInformation = async (req, res) => {
    let hotelId = '';
    try {
        const hotelId = req.params.hotelId;
        req.logger.debug('Fetching detailed information about the hotel for id ', hotelId);
        const db = await dbConnection;
        const result = await db.execute(`SELECT * FROM hotels WHERE hotel_id = ?`, [hotelId]);
        res
          .status(200)
          .json({
              data: result[0]
          });
    } catch (err) {
        req.logger.error(`Error fetching hotel detail info with id ${hotelId}`, err);
        throw err;
    }
}
   

To create hotel information:

   
const createHotelInformation = async (req, res) => {
    try {
        const hotelId = uuidv4();
        const hotelObj = {
            hotel_id: hotelId,
            name: req.body.name,
            description: req.body.description,
            cover_image_url: '',
            amenities: req.body.amenities,
            address: req.body.address,
            is_published: false,
            guest_capacity: req.body.guest_capacity,
            hotel_type: req.body.hotel_type,
            added_by: '056a3ce0-dc51-4c26-88fe-809a5a7a48b4'
        };

        const dataArr = [
          hotelObj.hotel_id,
          hotelObj.name,
          hotelObj.description,
          hotelObj.cover_image_url,
          hotelObj.amenities,
          hotelObj.address,
          hotelObj.is_published,
          hotelObj.guest_capacity,
          hotelObj.hotel_type,
          hotelObj.added_by
        ];

        req.logger.debug('Creating hotel with info ', hotelObj);
        const db = await dbConnection;
        const result = await db.execute(
          `INSERT INTO hotels(hotel_id, name, description, cover_image_url, amenities, address, is_published, guest_capacity, hotel_type, added_by)
          VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          dataArr);

        if (result[0].affectedRows > 0) {
          req.logger.debug('Hotel created successfully ');
          res
          .status(200)
          .json({
              message: 'Hotel created successfully',
              data: hotelObj
          });
        } else {
          throw new Error(`Unable to create hotel information `)
        }
    } catch (err) {
        req.logger.error('Error: ', err);
        throw err;
    }
}
   

MySQL crud operation using Node.js - create a new record

To verify if the execution of INSERT, UPDATE or DELETE statement is successful or not, we can check the affectedRows property in the result object. If it's greater than 1, then query execution is successful else some error has occurred. For UPDATE statement, you can also check for changedRows property in the result object. If the value of changedRows property is 0, it means there are no changes in the data sent for update operation.

To update hotel information:

   
const updateHotelInformation = async (req, res) => {
    try {
        const hotelId = req.params.hotelId;
        const hotelObj = {
            name: req.body.name,
            description: req.body.description,
            amenities: req.body.amenities,
            guest_capacity: req.body.guest_capacity,
            hotel_type: req.body.hotel_type,
            address: req.body.address
        };
        req.logger.debug('Updating hotel with info ', hotelObj);

        const db = await dbConnection;
        const result = await db.execute(
          'UPDATE hotels SET name = ?, description = ?, amenities = ?, guest_capacity = ?, hotel_type = ?, address = ? WHERE hotel_id = ?',
          [hotelObj.name, hotelObj.description, hotelObj.amenities, hotelObj.guest_capacity, hotelObj.hotel_type, hotelObj.address, hotelId]);

        if (result[0].affectedRows > 0) {
          req.logger.debug('Hotel updated successfully ');
          res
          .status(200)
          .json({
              message: 'Hotel updated successfully',
          });
        } else {
          throw new Error(`Unable to update hotel information for id ${hotelId}`)
        }
    } catch (err) {
        req.logger.error(`Error: `, err);
        throw err;
    }
}
   

MySQL crud operation using Node.js - update a particular record

To publish hotel information:

   
const publishHotelInformation = async (req, res) => {
    try {
        const hotelId = req.params.hotelId;
        req.logger.debug('Publishing hotel info for id ', hotelId);

        const db = await dbConnection;
        const result = await db.execute('UPDATE hotels SET is_published = true WHERE hotel_id = ?', [hotelId]);
        if (result[0].affectedRows > 0) {
          req.logger.debug('Hotel published successfully ');
          res
          .status(200)
          .json({
              message: 'Hotel published successfully'
          });
        } else {
          throw new Error(`Unable to publish hotel information for id ${hotelId}`)
        }
    } catch (err) {
        req.logger.error(`Error: `, err);
        throw err;
    }
}
   

MySQL crud operation using Node.js - publish - update a particular record

To remove hotel information:

   
const removeHotelInformation = async (req, res) => {
    let hotelId = '';
    try {
        hotelId = req.params.hotelId;
        req.logger.debug('Deleting hotel info with id ', hotelId);

        const db = await dbConnection;
        const result = await db.execute('DELETE FROM hotels WHERE hotel_id = ?', [hotelId]);
        if (result[0].affectedRows > 0) {
          req.logger.debug('Hotel deleted successfully ');
          return res
          .status(200)
          .json({
              message: 'Hotel deleted successfully'
          });
        } else {
          throw new Error(`Unable to remove hotel information for id ${hotelId}`)
        }
    } catch (err) {
        req.logger.error(`Error: `, err);
        throw err;
    }
}
   

MySQL crud operation using Node.js - remove a particular record


Here is the complete hotels controller file:

   
const { v4: uuidv4 } = require('uuid');
const dbConnection = require('../../helpers/database');

const listAllHotels = async (req, res) => {
    try {
        req.logger.debug('Fetching all the hotel information');
        const db = await dbConnection;
        const result = await db.execute('SELECT * FROM hotels ORDER BY added_on DESC');
        res
          .status(200)
          .json({
              data: result[0]
          })
    } catch (err) {
        req.logger.error('Error fetching hotels ', err);
        throw err;
    }
}

const getHotelDetailInformation = async (req, res) => {
    let hotelId = '';
    try {
        const hotelId = req.params.hotelId;
        req.logger.debug('Fetching detailed information about the hotel for id ', hotelId);
        const db = await dbConnection;
        const result = await db.execute(`SELECT * FROM hotels WHERE hotel_id = ?`, [hotelId]);
        res
          .status(200)
          .json({
              data: result[0]
          });
    } catch (err) {
        req.logger.error(`Error fetching hotel detail info with id ${hotelId}`, err);
        throw err;
    }
}

const createHotelInformation = async (req, res) => {
    try {
        const hotelId = uuidv4();
        const hotelObj = {
            hotel_id: hotelId,
            name: req.body.name,
            description: req.body.description,
            cover_image_url: '',
            amenities: req.body.amenities,
            address: req.body.address,
            is_published: false,
            guest_capacity: req.body.guest_capacity,
            hotel_type: req.body.hotel_type,
            added_by: '056a3ce0-dc51-4c26-88fe-809a5a7a48b4'
        };
        const dataArr = [
          hotelObj.hotel_id,
          hotelObj.name,
          hotelObj.description,
          hotelObj.cover_image_url,
          hotelObj.amenities,
          hotelObj.address,
          hotelObj.is_published,
          hotelObj.guest_capacity,
          hotelObj.hotel_type,
          hotelObj.added_by
        ];

        req.logger.debug('Creating hotel with info ', hotelObj);
        const db = await dbConnection;
        const result = await db.execute(
          `INSERT INTO hotels(hotel_id, name, description, cover_image_url, amenities, address, is_published, guest_capacity, hotel_type, added_by)
          VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          dataArr);
        if (result[0].affectedRows > 0) {
          req.logger.debug('Hotel created successfully ');
          res
          .status(200)
          .json({
              message: 'Hotel created successfully',
              data: hotelObj
          });
        } else {
          throw new Error(`Unable to create hotel information `)
        }
    } catch (err) {
        req.logger.error('Error: ', err);
        throw err;
    }
}

const updateHotelInformation = async (req, res) => {
    try {
        const hotelId = req.params.hotelId;
        const hotelObj = {
            name: req.body.name,
            description: req.body.description,
            amenities: req.body.amenities,
            guest_capacity: req.body.guest_capacity,
            hotel_type: req.body.hotel_type,
            address: req.body.address
        };
        req.logger.debug('Updating hotel with info ', hotelObj);

        const db = await dbConnection;
        const result = await db.execute(
          'UPDATE hotels SET name = ?, description = ?, amenities = ?, guest_capacity = ?, hotel_type = ?, address = ? WHERE hotel_id = ?',
          [hotelObj.name, hotelObj.description, hotelObj.amenities, hotelObj.guest_capacity, hotelObj.hotel_type, hotelObj.address, hotelId]);

        if (result[0].affectedRows > 0) {
          req.logger.debug('Hotel updated successfully ');
          res
          .status(200)
          .json({
              message: 'Hotel updated successfully',
          });
        } else {
          throw new Error(`Unable to update hotel information for id ${hotelId}`)
        }
    } catch (err) {
        req.logger.error(`Error: `, err);
        throw err;
    }
}

const publishHotelInformation = async (req, res) => {
    try {
        const hotelId = req.params.hotelId;
        req.logger.debug('Publishing hotel info for id ', hotelId);

        const db = await dbConnection;
        const result = await db.execute('UPDATE hotels SET is_published = true WHERE hotel_id = ?', [hotelId]);
        if (result[0].affectedRows > 0) {
          req.logger.debug('Hotel published successfully ');
          res
          .status(200)
          .json({
              message: 'Hotel published successfully'
          });
        } else {
          throw new Error(`Unable to publish hotel information for id ${hotelId}`)
        }
    } catch (err) {
        req.logger.error(`Error: `, err);
        throw err;
    }
}

const removeHotelInformation = async (req, res) => {
    let hotelId = '';
    try {
        hotelId = req.params.hotelId;
        req.logger.debug('Deleting hotel info with id ', hotelId);

        const db = await dbConnection;
        const result = await db.execute('DELETE FROM hotels WHERE hotel_id = ?', [hotelId]);
        if (result[0].affectedRows > 0) {
          req.logger.debug('Hotel deleted successfully ');
          return res
          .status(200)
          .json({
              message: 'Hotel deleted successfully'
          });
        } else {
          throw new Error(`Unable to remove hotel information for id ${hotelId}`)
        }
    } catch (err) {
        req.logger.error(`Error: `, err);
        throw err;
    }
}

module.exports = {
    listAllHotels,
    getHotelDetailInformation,
    createHotelInformation,
    updateHotelInformation,
    publishHotelInformation,
    removeHotelInformation
};

   


Note:

If you are not executing multiple SQL statements at once, you should not pass multipleStatements option while creating a connection object. It is set to false by default. Setting it to true allows for SQL injection attacks if proper care is not taken. In this article, i have set it to true so that we can perform the SQL Injection Attacks. Other than that, there is no need for it for the travel application.

   
// create the connection to database
const connection = mysql.createConnection({
      host,
      port,
      user,
      password,
      database,
    });
   

In our next chapter, we will further enhance this hotels controller file with best practices implementation. We will look into repository pattern in order to encapsulate the logic required to access data sources. Implementing repository pattern helps us with cleaner, more readable and maintainable code.

Prev Chapter                                                                                         Next Chapter