Use MySQL in Node.js

In the last article, we created User RESTful APIs using Express.js. But for the persistence, we didn’t code anything. We just mocked the data since we had no database connection. This time we complete that example by connecting to MySQL. The focus of this article is to use MySQL in Node.js.

MySQL driver for Node.js

When it comes to connecting to a database in any platform there are two options:

ORM
Write your own code

Personally for any project as long as the queries are not too crazy and the performance does not need to be blazing fast, I go with ORMs since they provide much-added benefit.

In this example, however, I decided that we are better off using just a low-level library and write our own code. This is much easier in Node.js compare with Java since the data structure by default is JSON and much of crazy back and forth mapping can be eliminated using object destructuring/constructing.

At the time of writing this article, there are two dominant MySQL drivers available for Node.js, <a href="https://www.npmjs.com/package/mysql">mysql</a> and <a href="https://www.npmjs.com/package/mysql2">mysql2</a> respectively. mysql is the older driver which is very popular but lacks promises and some other goodies. But mysql2 has all of them and also compatible with mysql driver. Hence, we use that for this example.

So let’s get started.

Database design

Before jumping to writing the code, first, we need to design the database structure. Let’s assume that we have a database called user in which has a table users with the following structure,

CREATE TABLE IF NOT EXISTS users (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(512) NOT NULL,
  last_name VARCHAR(512) NOT NULL,
  age INTEGER NOT NULL
) engine=InnoDB;


INSERT IGNORE INTO users VALUES(1, "John", "Wick", 45);

Create a docker-compose file

It is a very good practice to use dockerize everything so the project setup will be smooth. This is equally important for the dev environment. Otherwise, developers have to spend much time to set up their local machines.

Luckily here we an extra simple app so we don’t have many complications. All we need to do is to create a MySQL docker-compose.yml file as follows,

version: "3.7"

services:
  db:
    hostname: mysql-user
    container_name: mysql-user
    image: mysql:8.0.19
    ports:
      - "3306:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=secret
      - MYSQL_DATABASE=user

Adding the dependency

Let’s add mysql2 driver to the project. For that, we run this command,

$ npm install --save mysql2

Externalzing the configuration

Never hardcode anything in any project, regardless of who can see your code. When coding all the time assume that you are working in an open-source project that your code is publicly available. Would you like to leave credentials there? Surely NOT!

The easiest way I found in Node.js is to use environment variables to pass any credentials. But I’d like to have it in a separate file called config.js. It only reads the values from env variables or set defaults if none available.

What we need is database URL, database name, database username and database password. I also decided to externalize the webserver port for the sake of flexibility. Our configuration then will look like this,

let config = {};

config.database = {};

config.web = {};

config.database.host = process.env.DATABASE_HOST || "localhost";
config.database.user = process.env.DATABASE_USER || "root";
config.database.password = process.env.DATABASE_PASSWORD || "secret";
config.database.name = "user";
config.web.port = process.env.WEB_PORT || 8090;

module.exports = config;

As you can see there are two sets of configurations web and database. And there is a default value for each field if the corresponding environment variable is not set.

Connecting to the database

Like what we did for the configuration, we also need to have a dedicated module for the database layer. Let’s call it store.js. This is the place where all the interaction with the database happens.

The first thing to do is to import the mysql2 library and create a connection pool as follows,

import config from "./config";
import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: config.database.host,
  user: config.database.user,
  password: config.database.password,
  database: config.database.name,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

Then right away we can start coding any necessary functions we need. If you recall from the previous tutorial we have CRUD APIs. That means we need the following functions:

getAllUsers
getUserById
createUser
deleteUserById
updateUser

In the following subsections, we go through the implementation of each function separately.

Implementation of getAllUsers

This the simplest function to implement. All we have to is to query the database and return the result,

async function getAllUsers() {
  const result = await pool.query(
    "SELECT id, first_name AS firstName, last_name AS lastName, age FROM users"
  );
  return result[0].length < 1 ? {} : result[0];
}

We compare the result set and if it’s empty we return empty array accordingly. Otherwise, we return the results.

Implementation of getUserById

All that’s required for this function implementation is the userId. But forming the query is tricker than the last part because part of it is dynamic. Additionally, if the user not found, an appropriate exception should be thrown.

async function getUserById(id) {
  const result = await pool.query(
    "SELECT id, first_name AS firstName, last_name AS lastName, age FROM users WHERE id = ?",
    [id]
  );
  if (result[0].length < 1) {
    throw new Error(`User with id = ${id} not found`);
  }
  return result[0][0];
}

As you can see first we created a prepared statement and then pass the id. The return part is weird though. First it check the length of the result set result[0].length and if it’s greater than 0 gets its first element result[0][0].

Implementation of createUser

We need to get three parameters firstName, lastName, and age. If for whatever reason fails to persist, we need to throw the error. Otherwise, the persisted user should be returned. For this part, we need to leverage getUserById function,

async function createUser(firstName, lastName, age) {
  const result = await pool.query(
    "INSERT INTO users SET first_name = ?, last_name = ?, age = ?",
    [firstName, lastName, age]
  );
  if (result[0].length < 1) {
    throw new Error(
      `Failed to create a new user ${firstName}, ${lastName}, ${age}`
    );
  }
  return getUserById(result[0].insertId);
}

To get the id of the persisted entity, we use result[0].insertId. Isn’t cool?

Implementation of deleteUserById

This implementation is also simple. It is just a basic delete statement,

async function deleteUserById(id) {
  const result = await pool.query("DELETE FROM users WHERE id = ?", [id]);
  if (result[0].affectedRows < 1) {
    throw new Error(`User with id ${id} does not exist`);
  }
  return "";
}

Implementation of updateUser

Update user is similar to createUser. We need to return the updated entity if the update process is successful. Hence, we need to user getUserById function here as well.

async function updateUser(id, user) {
  const result = await pool.query(
    "UPDATE users SET first_name = ?, last_name = ?, age = ? WHERE id = ?",
    [user.firstName, user.lastName, user.age, id]
  );
  if (result[0].affectedRows < 1) {
    throw new Error(`User with id ${id} does not exist`);
  }
  return getUserById(id);
}

To check whether the update process was successful, we check result[0].affectedRows < 1. If it is 0 means nothing has changed. This implies the given userId was wrong, so we throw an exception.

Glue everything together

So the database layer implementation is done. Now we just need to use it in the app and replace the mock data. This section covers that.

First, let’s import the config.js and store.js modules,

import config from "./config";
import store from "./store";

const PORT = config.web.port;
const app = express();

Now we can start replacing the mock data with the real database connection. Since store.js returns a promise we need to use two callbacks then and catch. Then triggers when the function executed successfully and catch triggers when there’s an exception. So we can implement it like this,

import express from "express";
import bodyParser from "body-parser";
import { restart } from "nodemon";
import config from "./config";
import store from "./store";

const PORT = config.web.port;
const app = express();

app.use(bodyParser.urlencoded());
app.use(bodyParser.json());
app.use(
  bodyParser.urlencoded({
    extended: true
  })
);

app.use("/api/v1/users/:id", function(req, res, next) {
  if (req.params.hasOwnProperty("id") && !isNaN(parseInt(req.params.id))) {
    next();
  } else {
    console.log("Request is invalid!");
    res.status(400).send({ error: "User id should be a number" });
  }
});

// Error handling for uncaught exceptions
app.use(function(err, req, res, next) {
  console.error(err.stack);
  res.status(500).send({ error: "Internal server error" });
});

app
  .route("/api/v1/users/:id")
  .get(function(req, res) {
    store
      .getUserById(req.params.id)
      .then(user => res.status(200).send(user))
      .catch(error => res.status(404).send({ error: error.message }));
  })
  .delete(function(req, res) {
    store
      .deleteUserById(req.params.id)
      .then(result => res.status(204).send())
      .catch(error => res.status(400).send({ error: error.message }));
  })
  .put(function(req, res) {
    let userId = parseInt(req.params.id);
    const { firstName, lastName, age } = req.body;
    const ageInt = parseInt(age);
    if (firstName && lastName && ageInt) {
      store
        .updateUser(userId, {
          firstName: firstName,
          lastName: lastName,
          age: ageInt
        })
        .then(result => res.status(200).send(result))
        .catch(error => res.status(404).send({ error: error.message }));
    } else {
      res.status(400).send({
        error: "The payload is wrong!"
      });
    }
  });

app.post(
  "/api/v1/users",
  (req, res, next) => {
    const { firstName, lastName, age } = req.body;
    let ageInt = parseInt(age);
    if (firstName && lastName && ageInt) {
      next();
    } else {
      res.status(400).send({
        error: "The payload is wrong!"
      });
    }
  },
  (req, res) => {
    store
      .createUser(req.body.firstName, req.body.lastName, req.body.age)
      .then(createdUser => res.status(201).send(createdUser))
      .catch(error =>
        res.status(500).send({ error: "Unable to insert the user" })
      );
  }
);

app.get("/api/v1/users", (req, res, next) => {
  store.getAllUsers().then(users => res.status(200).send(users));
});

app.listen(PORT, () => {
  console.log("Server has started");
});

Conclusion

In this tutorial, we covered how to use MySQL in Node.js. We created a simple Node application that exposes endpoints for CRUD operations and persists the data to a MySQL database. As always, the fully functional demo is available on GitHub.