How To Build Rest API With NodeJS, Express, and MySQL

With knowledge of JavaScript and MySQL, we can build our NodeJS API using Express.

I did some research, and I was attempting to develop an API from scratch.
I like to simplify things and try to avoid code duplication.

This Guide will show you how to build an API from scratch:
You will learn how to create routes,
how to use mysql2, how to configure and connect to the database, and how to run queries with prepared statements.
How to create a middleware that can get an additional argument besides req, res, and next callback.
You will learn how to check the data from the request object using the Express Validator module.
You will learn how to use the JWT module to create a token for the user, verify the token, and get the object stored in the token.
In addition, you will learn how to provide users permission to access a certain route based on their user roles.

Technologies and Packages:

• NodeJS
• Express
• mysql2
• bcryptjs
• jsonwebtoken
• express-validator
• dotenv
• cors

Installing MySQL:

I use WSL, and you can use this tutorial to see how to install MySQL in WSL.
You need to make sure that MySQL is running with this command:

sudo service mysql status

If it's not running, just use:

sudo service mysql start

Application overview:

We'll build a rest API for CRUD operations: create, read, update, and delete users.

+---------+------------------------------+--------------------------------+
| Methods |             Urls             |            Actions             |
+---------+------------------------------+--------------------------------+
| Get     | /api/v1/users                | Get all users                  |
| Get     | /api/v1/users/id/1           | Get user with id=1             |
| Get     | /api/v1/users/username/julia | Get user with username='julia' |
| Get     | /api/v1/users/whoami         | Get the current user details   |
| Post    | /api/v1/users                | Create new user                |
| Patch   | /api/v1/users/users/id/1     | Update user with id=1          |
| Delete  | /api/v1/users/id/1           | Delete user with id=1          |
| Post    | /api/v1/users/login          | Login with email and password  |
+---------+------------------------------+--------------------------------+

Create the project folder and install all the dependencies:

mkdir mysql-node-express && cd mysql-node-express
npm init -y
npm i express express-validator mysql2 cors dotenv jsonwebtoken -S
npm i nodemon -D

Go to package.json file and change the "main" value to "src/server.js" and add these scripts to scripts object:

"start": "node src/server.js",
"dev": "nodemon"

package.json should look like this:

{
  "name": "mysql-node-express",
  "version": "1.0.0",
  "description": "",
  "main": "src/server.js",
  "scripts": {
    "start": "node src/server.js",
    "dev": "nodemon"
  },
  "author": "Julia Strichash",
  "license": "ISC",
  "dependencies": {
    "bcryptjs": "^2.4.3",
    "cors": "^2.8.5",
    "dotenv": "^8.2.0",
    "express": "^4.17.1",
    "express-validator": "^6.6.0",
    "jsonwebtoken": "^8.5.1",
    "mysql2": "^2.1.0"
  },
  "devDependencies": {
    "nodemon": "^2.0.4"
  }
}

Create .env file:

We will use the .env file to manage all of our environment variables.
The .env file is a hidden file that allows us to customize our environment variables using the ENV VARIABLE = VALUE syntax.
These variables are loaded using the dotenv module that we have already installed.
The .env file can be defined at different stages of the environment (develop / stage / production environments).

Create the .env file, copy the following lines, and update the file with your MySQL db_name, db_username, and password:

# DB Configurations
HOST=localhost
DB_USER=db_username
DB_PASS=db_password
DB_DATABASE=db_name


# local runtime configs
PORT=3000
SECRET_JWT=supersecret

Create nodemon.json file:

Nodemon is a tool that helps develop applications based on node.js by automatically restarting the node application when file changes are detected in the target directory.
The nodemon is a replacement wrapper for node. Instead of using the node command, we should use the nodemon command on the command line to execute our script.
We can easily add configuration switches while running nodemon on the command line, such as:

nodemon --watch src

We can also use a file (nodemon.json) to specify all of the switches.
If we want to watch several files in a directory, we can add the directory in the "watch" array.
If we want to search for a particular extension (such as a ts file) we may use the "ext" property.
If we want to ignore some files, we may define them in the "ignore"' array, and so on…
I use this file mostly when I'm creating a server with NodeJS based on typescript, but I think it's easier to have more places to include our app configurations.
This file is optional.

Create nodemon.json file and add this to the file:

{
    "watch": ["src"],
    "ext": ".js",
    "ignore": []
  }

Create the src folder:

mkdir src && cd src

In the src folder create sub-folders: controllers, models, routes, middleware, db, and utils:

mkdir controllers models routes middleware db utils

Setup Express server:

In the src directory create the file server.js and copy these lines:

const express = require("express");
const dotenv = require('dotenv');
const cors = require("cors");
const HttpException = require('./utils/HttpException.utils');
const errorMiddleware = require('./middleware/error.middleware');
const userRouter = require('./routes/user.route');

// Init express
const app = express();
// Init environment
dotenv.config();
// parse requests of content-type: application/json
// parses incoming requests with JSON payloads
app.use(express.json());
// enabling cors for all requests by using cors middleware
app.use(cors());
// Enable pre-flight
app.options("*", cors());

const port = Number(process.env.PORT || 3331);

app.use(`/api/v1/users`, userRouter);

// 404 error
app.all('*', (req, res, next) => {
    const err = new HttpException(404, 'Endpoint Not Found');
    next(err);
});

// Error middleware
app.use(errorMiddleware);

// starting the server
app.listen(port, () =>
    console.log(`🚀 Server running on port ${port}!`));


module.exports = app;

In this file, we import express to build the rest APIs and use express.json() to parses incoming requests with JSON payloads.

We also import the dotenv module to read the .env config file to get the port number to run the server.

Cors is used to allow cross-site HTTP requests, in this case, by using a wildcard *, it allows access from any origin (any domain). We're going to call app.use(cors)); before we use the routes.

We also import userRouter.

After that, we have a middleware that handles 404 errors → if anyone looks for an endpoint that doesn't exist, they will get this error: 'Endpoint Not Found' with the 404 status code. After that, we're using error middleware which will get error data from the previous routes. if next(err) is called, you can see the 404 middleware as an example.
We listen to the port from the.env file and print it to the console that the server is running.

Create MySQL database and user table:

In the db directory, we will create the create-user-db.sql file and copy-paste these lines:

DROP DATABASE IF EXISTS test_db;   
CREATE DATABASE IF NOT EXISTS test_db;   
USE test_db; 

DROP TABLE IF EXISTS user; 

CREATE TABLE IF NOT EXISTS user 
  ( 
     id         INT PRIMARY KEY auto_increment, 
     username   VARCHAR(25) UNIQUE NOT NULL, 
     password   CHAR(60) NOT NULL, 
     first_name VARCHAR(50) NOT NULL, 
     last_name  VARCHAR(50) NOT NULL, 
     email      VARCHAR(100) UNIQUE NOT NULL, 
     role       ENUM('Admin', 'SuperUser') DEFAULT 'SuperUser', 
     age        INT(11) DEFAULT 0 
  ); 

In this script, we first drop the database if it exists so it can be reset quickly in case of a mistake (you can comment that line if you want to), then, we create the database if it does not exist. We set it as our active database and create a "user" table with all the columns (id, username, and so on), again allowing for a convenient reset if needed. You can run this query in your database client if you’re using one.

If you're using wsl, in the db directory you can run:

mysql -u [db_username] -p[db_password] < create-user-db.sql

Configure and Connect to MySQL database:

Create an additional file in the db directory calls db-connection.js, and copy-paste this:

const dotenv = require('dotenv');
dotenv.config();
const mysql2 = require('mysql2');

class DBConnection {
    constructor() {
        this.db = mysql2.createPool({
            host: process.env.DB_HOST,
            user: process.env.DB_USER,
            password: process.env.DB_PASS,
            database: process.env.DB_DATABASE
        });

        this.checkConnection();
    }

    checkConnection() {
        this.db.getConnection((err, connection) => {
            if (err) {
                if (err.code === 'PROTOCOL_CONNECTION_LOST') {
                    console.error('Database connection was closed.');
                }
                if (err.code === 'ER_CON_COUNT_ERROR') {
                    console.error('Database has too many connections.');
                }
                if (err.code === 'ECONNREFUSED') {
                    console.error('Database connection was refused.');
                }
            }
            if (connection) {
                connection.release();
            }
            return
        });
    }

    query = async (sql, values) => {
        return new Promise((resolve, reject) => {
            const callback = (error, result) => {
                if (error) {
                    reject(error);
                    return;
                }
                resolve(result);
            }
            // execute will internally call prepare and query
            this.db.execute(sql, values, callback);
        }).catch(err => {
            const mysqlErrorList = Object.keys(HttpStatusCodes);
            // convert mysql errors which in the mysqlErrorList list to http status code
            err.status = mysqlErrorList.includes(err.code) ? HttpStatusCodes[err.code] : err.status;

            throw err;
        });
    }
}

// like ENUM
const HttpStatusCodes = Object.freeze({
    ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: 422,
    ER_DUP_ENTRY: 409
});


module.exports = new DBConnection().query;

In this file, we first import the dotenv module and use to read database configuration info like db host, db user from the.env file.

We check the connection in case there is an issue with the database and then release the connection.

We have a query method that returns a promise of the result of the query.

We use a try-catch block to capture common MySQL errors and return appropriate HTTP status codes and messages.

At the end of the file, we create an instance of the DBConnection class and use the query method, and in the model.js (which we will see in the next step), we'll use the query method again.

Create Error Handler:

Next, we're going to create our error handler.

To do so, first, we will create the HttpException.utils.js file under the utils directory, and copy-paste the following:

class HttpException extends Error {
    constructor(status, message, data) {
        super(message);
        this.status = status;
        this.message = message;
        this.data = data;
    }
}

module.exports = HttpException;

The HttpException class inherits the Error class.
The constructor will get the status, message, and data. We will pass the message variable to the parent constructor using super(message), and then we will initialize the status, message, and data instance variables.

After that, we will create a middleware error handler in the middleware directory.
We will create an error. middleware.js file and copy-paste the following:

function errorMiddleware(error, req, res, next) {
    let { status = 500, message, data } = error;

    console.log(`[Error] ${error}`);

    // If status code is 500 - change the message to Intrnal server error
    message = status === 500 || !message ? 'Internal server error' : message;

    error = {
        type: 'error',
        status,
        message,
        ...(data) && data
    }

    res.status(status).send(error);
}

module.exports = errorMiddleware;
/*
{
    type: 'error',
    status: 404,
    message: 'Not Found'
    data: {...} // optional
}
*/

We can see at the bottom of the file how the object is going to be.

The middleware will get req, res, and next callback, but it will also get an additional argument, error (by using next(error) before we get to this middleware).

We use destructuring to get the variables from the error object and set the status to 500 if it has not been configured before.

After this, whether the status is 500, we'll make sure to change the message so the user will recieve a generic internal server error message without revealing the exact nature of the failure.

After that, we create an error object with the type, status, and message properties (data is optional).

Create utils (helpers) files:

In the utils directory, we create two more files, common.utils.js, and userRoles.utils.js.

common.utils.js:

exports.multipleColumnSet = (object) => {
    if (typeof object !== 'object') {
        throw new Error('Invalid input');
    }

    const keys = Object.keys(object);
    const values = Object.values(object);

    columnSet = keys.map(key => `${key} = ?`).join(', ');

    return {
        columnSet,
        values
    }
}

This function helps to set multiple fields for prepared queries with key value pairs.
ColumnSet the array of key =? pairs,
The values should therefore be in the same order as the columnSet array.

userRoles.utils.js:

module.exports = {
    Admin: 'Admin',
    SuperUser: 'SuperUser'
}

Create Async function:

Create another file called awaitHandlerFactory.middleware.js in the middleware directory and copy-paste this:

const awaitHandlerFactory = (middleware) => {
    return async (req, res, next) => {
        try {
            await middleware(req, res, next)
        } catch (err) {
            next(err)
        }
    }
}

module.exports = awaitHandlerFactory;

In general, we know that middleware is only an asynchronous method that gets the req, the res, and the next arguments, so, if we want this middleware to get an additional argument, we'll do it this way (we'll use this in the auth middleware as well in the next step).

This function will get a callback, run the middleware script, and will attempt to trigger this callback in the try block.
If something goes wrong here, it will catch the error and we'll use the next(err) (which will transfer it to the next middleware => error.middleware.js).

Create Authentication Middleware:

Another middleware that we need is the auth middleware that we'll use to check user permissions via the JWT module.

const HttpException = require('../utils/HttpException.utils');
const UserModel = require('../models/user.model');
const jwt = require('jsonwebtoken');
const dotenv = require('dotenv');
dotenv.config();

const auth = (...roles) => {
    return async function (req, res, next) {
        try {
            const authHeader = req.headers.authorization;
            const bearer = 'Bearer ';

            if (!authHeader || !authHeader.startsWith(bearer)) {
                throw new HttpException(401, 'Access denied. No credentials sent!');
            }

            const token = authHeader.replace(bearer, '');
            const secretKey = process.env.SECRET_JWT || "";

            // Verify Token
            const decoded = jwt.verify(token, secretKey);
            const user = await UserModel.findOne({ id: decoded.user_id });

            if (!user) {
                throw new HttpException(401, 'Authentication failed!');
            }

            // check if the current user is the owner user
            const ownerAuthorized = req.params.id == user.id;

            // if the current user is not the owner and
            // if the user role don't have the permission to do this action.
            // the user will get this error
            if (!ownerAuthorized && roles.length && !roles.includes(user.role)) {
                throw new HttpException(401, 'Unauthorized');
            }

            // if the user has permissions
            req.currentUser = user;
            next();

        } catch (e) {
            e.status = 401;
            next(e);
        }
    }
}

module.exports = auth;

Similar to awaitHandlerFactory.middleware.js middleware, we have a middleware here that requires additional argument (which is optional) => roles.

I used try-catch to adjust the error status in the catch area to 401 (if the token has expired, for example).

At first, we're looking for req.headers.authorization - whether it's not defined in the header or if the header doesn't start with "Bearer ", the user will receive a 401 response. If it begins with "Bearer ", we'll get the token and use the secret key from the.env file to decipher it.

We will verify the token by using the jwt.verify synchronous function, which gets the token, and the secretKey, as arguments and returns the decoded payload, whether the signature is valid and the optional expiration, audience or issuer fields are valid. Otherwise it will throw an error.

Now, we can find the user with this token by searching the user id.
If the user no longer exists, they will get an exception of 401 without any information.
If the user exists, we will check whether the current user is the owner who searching for his routes or whether the user has the role to access this route.
We're saving the current user just in case he wants to get his data on the next middleware (like the "whoami" route).

Data validation using Express Validator module:

In the middleware directory, we will create an additional file that we will use to verify the req.body properties.

Create a subfolder in the middleware directory called validators and create a file in this directory, userValidator.middleware.js. Copy-paste this:

const { body } = require('express-validator');
const Role = require('../../utils/userRoles.utils');


exports.createUserSchema = [
    body('username')
        .exists()
        .withMessage('username is required')
        .isLength({ min: 3 })
        .withMessage('Must be at least 3 chars long'),
    body('first_name')
        .exists()
        .withMessage('Your first name is required')
        .isAlpha()
        .withMessage('Must be only alphabetical chars')
        .isLength({ min: 3 })
        .withMessage('Must be at least 3 chars long'),
    body('last_name')
        .exists()
        .withMessage('Your last name is required')
        .isAlpha()
        .withMessage('Must be only alphabetical chars')
        .isLength({ min: 3 })
        .withMessage('Must be at least 3 chars long'),
    body('email')
        .exists()
        .withMessage('Email is required')
        .isEmail()
        .withMessage('Must be a valid email')
        .normalizeEmail(),
    body('role')
        .optional()
        .isIn([Role.Admin, Role.SuperUser])
        .withMessage('Invalid Role type'),
    body('password')
        .exists()
        .withMessage('Password is required')
        .notEmpty()
        .isLength({ min: 6 })
        .withMessage('Password must contain at least 6 characters')
        .isLength({ max: 10 })
        .withMessage('Password can contain max 10 characters'),
    body('confirm_password')
        .exists()
        .custom((value, { req }) => value === req.body.password)
        .withMessage('confirm_password field must have the same value as the password field'),
    body('age')
        .optional()
        .isNumeric()
        .withMessage('Must be a number')
];

exports.updateUserSchema = [
    body('username')
        .optional()
        .isLength({ min: 3 })
        .withMessage('Must be at least 3 chars long'),
    body('first_name')
        .optional()
        .isAlpha()
        .withMessage('Must be only alphabetical chars')
        .isLength({ min: 3 })
        .withMessage('Must be at least 3 chars long'),
    body('last_name')
        .optional()
        .isAlpha()
        .withMessage('Must be only alphabetical chars')
        .isLength({ min: 3 })
        .withMessage('Must be at least 3 chars long'),
    body('email')
        .optional()
        .isEmail()
        .withMessage('Must be a valid email')
        .normalizeEmail(),
    body('role')
        .optional()
        .isIn([Role.Admin, Role.SuperUser])
        .withMessage('Invalid Role type'),
    body('password')
        .optional()
        .notEmpty()
        .isLength({ min: 6 })
        .withMessage('Password must contain at least 6 characters')
        .isLength({ max: 10 })
        .withMessage('Password can contain max 10 characters')
        .custom((value, { req }) => !!req.body.confirm_password)
        .withMessage('Please confirm your password'),
    body('confirm_password')
        .optional()
        .custom((value, { req }) => value === req.body.password)
        .withMessage('confirm_password field must have the same value as the password field'),
    body('age')
        .optional()
        .isNumeric()
        .withMessage('Must be a number'),
    body()
        .custom(value => {
            return !!Object.keys(value).length;
        })
        .withMessage('Please provide required field to update')
        .custom(value => {
            const updates = Object.keys(value);
            const allowUpdates = ['username', 'password', 'confirm_password', 'email', 'role', 'first_name', 'last_name', 'age'];
            return updates.every(update => allowUpdates.includes(update));
        })
        .withMessage('Invalid updates!')
];

exports.validateLogin = [
    body('email')
        .exists()
        .withMessage('Email is required')
        .isEmail()
        .withMessage('Must be a valid email')
        .normalizeEmail(),
    body('password')
        .exists()
        .withMessage('Password is required')
        .notEmpty()
        .withMessage('Password must be filled')
];

In this file, I used the express-validator module, which is very easy to use whenever we need to check some properties, check whether the property exists, or create custom checks with a custom message to the user if any property value is not valid.

Now we can start creating our route, controller, and model files.

Define Routes:

Create user.route.js file in the routes directory and copy-paste this:

const express = require('express');
const router = express.Router();
const userController = require('../controllers/user.controller');
const auth = require('../middleware/auth.middleware');
const Role = require('../utils/userRoles.utils');
const awaitHandlerFactory = require('../middleware/awaitHandlerFactory.middleware');

const { createUserSchema, updateUserSchema, validateLogin } = require('../middleware/validators/userValidator.middleware');


router.get('/', auth(), awaitHandlerFactory(userController.getAllUsers)); // localhost:3000/api/v1/users
router.get('/id/:id', auth(), awaitHandlerFactory(userController.getUserById)); // localhost:3000/api/v1/users/id/1
router.get('/username/:username', auth(), awaitHandlerFactory(userController.getUserByuserName)); // localhost:3000/api/v1/users/usersname/julia
router.get('/whoami', auth(), awaitHandlerFactory(userController.getCurrentUser)); // localhost:3000/api/v1/users/whoami
router.post('/', createUserSchema, awaitHandlerFactory(userController.createUser)); // localhost:3000/api/v1/users
router.patch('/id/:id', auth(Role.Admin), updateUserSchema, awaitHandlerFactory(userController.updateUser)); // localhost:3000/api/v1/users/id/1 , using patch for partial update
router.delete('/id/:id', auth(Role.Admin), awaitHandlerFactory(userController.deleteUser)); // localhost:3000/api/v1/users/id/1


router.post('/login', validateLogin, awaitHandlerFactory(userController.userLogin)); // localhost:3000/api/v1/users/login

module.exports = router;

The example above shows how to define routes. Let’s try to break it down into pieces:

• You can create a router using express.Router(). Each route can load a middleware function that handles the business logic. UserController, for example carries all the main middlewares. To use the router, the router should be exported as a module and used in the main app using app.use(router_module).
• We used auth middleware for user authentication and authorization, for checking user token or user role for the route. In our example, some of the routes use the auth middleware for checking user authentication and authorization. This middleware will be triggered before the main middleware (the one that holds the business logic). The next callback must be called to pass control to the next middleware method. Otherwise, the request will be left hanging.
• awaitHandlerFactory (try-catch middleware) is used to wrap all the asynchronous middleware. This way, if one of the middleware throws an error, awaitHandlerFactory will catch that error. You can see that all of our middleware functions are wrapped with awaitHandlerFactory middleware, which helps us to handle our errors by using try-catch in one place.
• In addition, we have the createUserSchema, updateUserSchema and validateLogin schema to validate the body before we start the next middleware.

The syntax of the HTTP method is:

Create the Controller:

Create user.controller.js file in the controllers directory and copy-paste this:

const UserModel = require('../models/user.model');
const HttpException = require('../utils/HttpException.utils');
const { validationResult } = require('express-validator');
const bcrypt = require('bcryptjs');
const jwt = require('jsonwebtoken');
const dotenv = require('dotenv');
dotenv.config();

/******************************************************************************
 *                              User Controller
 ******************************************************************************/
class UserController {
    getAllUsers = async (req, res, next) => {
        let userList = await UserModel.find();
        if (!userList.length) {
            throw new HttpException(404, 'Users not found');
        }

        userList = userList.map(user => {
            const { password, ...userWithoutPassword } = user;
            return userWithoutPassword;
        });

        res.send(userList);
    };

    getUserById = async (req, res, next) => {
        const user = await UserModel.findOne({ id: req.params.id });
        if (!user) {
            throw new HttpException(404, 'User not found');
        }

        const { password, ...userWithoutPassword } = user;

        res.send(userWithoutPassword);
    };

    getUserByuserName = async (req, res, next) => {
        const user = await UserModel.findOne({ username: req.params.username });
        if (!user) {
            throw new HttpException(404, 'User not found');
        }

        const { password, ...userWithoutPassword } = user;

        res.send(userWithoutPassword);
    };

    getCurrentUser = async (req, res, next) => {
        const { password, ...userWithoutPassword } = req.currentUser;

        res.send(userWithoutPassword);
    };

    createUser = async (req, res, next) => {
        this.checkValidation(req);

        await this.hashPassword(req);

        const result = await UserModel.create(req.body);

        if (!result) {
            throw new HttpException(500, 'Something went wrong');
        }

        res.status(201).send('User was created!');
    };

    updateUser = async (req, res, next) => {
        this.checkValidation(req);

        await this.hashPassword(req);

        const { confirm_password, ...restOfUpdates } = req.body;

        // do the update query and get the result
        // it can be partial edit
        const result = await UserModel.update(restOfUpdates, req.params.id);

        if (!result) {
            throw new HttpException(404, 'Something went wrong');
        }

        const { affectedRows, changedRows, info } = result;

        const message = !affectedRows ? 'User not found' :
            affectedRows && changedRows ? 'User updated successfully' : 'Updated faild';

        res.send({ message, info });
    };

    deleteUser = async (req, res, next) => {
        const result = await UserModel.delete(req.params.id);
        if (!result) {
            throw new HttpException(404, 'User not found');
        }
        res.send('User has been deleted');
    };

    userLogin = async (req, res, next) => {
        this.checkValidation(req);

        const { email, password: pass } = req.body;

        const user = await UserModel.findOne({ email });

        if (!user) {
            throw new HttpException(401, 'Unable to login!');
        }

        const isMatch = await bcrypt.compare(pass, user.password);

        if (!isMatch) {
            throw new HttpException(401, 'Incorrect password!');
        }

        // user matched!
        const secretKey = process.env.SECRET_JWT || "";
        const token = jwt.sign({ user_id: user.id.toString() }, secretKey, {
            expiresIn: '24h'
        });

        const { password, ...userWithoutPassword } = user;

        res.send({ ...userWithoutPassword, token });
    };

    checkValidation = (req) => {
        const errors = validationResult(req)
        if (!errors.isEmpty()) {
            throw new HttpException(400, 'Validation faild', errors);
        }
    }

    // hash password if it exists
    hashPassword = async (req) => {
        if (req.body.password) {
            req.body.password = await bcrypt.hash(req.body.password, 8);
        }
    }
}



/******************************************************************************
 *                               Export
 ******************************************************************************/
module.exports = new UserController;

As mentioned above, the controller file holds our business logic for handling our routes.
In our example, some methods use the UserModel class to query the database for getting the data.
To return the data in each middleware, we use res.send(result) to send a response to the client.

Create the Model:

And create the user.model.js file in models directory and copy-paste this:

const query = require('../db/db-connection');
const { multipleColumnSet } = require('../utils/common.utils');
const Role = require('../utils/userRoles.utils');
class UserModel {
    tableName = 'user';

    find = async (params = {}) => {
        let sql = `SELECT * FROM ${this.tableName}`;

        if (!Object.keys(params).length) {
            return await query(sql);
        }

        const { columnSet, values } = multipleColumnSet(params)
        sql += ` WHERE ${columnSet}`;

        return await query(sql, [...values]);
    }

    findOne = async (params) => {
        const { columnSet, values } = multipleColumnSet(params)

        const sql = `SELECT * FROM ${this.tableName}
        WHERE ${columnSet}`;

        const result = await query(sql, [...values]);

        // return back the first row (user)
        return result[0];
    }

    create = async ({ username, password, first_name, last_name, email, role = Role.SuperUser, age = 0 }) => {
        const sql = `INSERT INTO ${this.tableName}
        (username, password, first_name, last_name, email, role, age) VALUES (?,?,?,?,?,?,?)`;

        const result = await query(sql, [username, password, first_name, last_name, email, role, age]);
        const affectedRows = result ? result.affectedRows : 0;

        return affectedRows;
    }

    update = async (params, id) => {
        const { columnSet, values } = multipleColumnSet(params)

        const sql = `UPDATE user SET ${columnSet} WHERE id = ?`;

        const result = await query(sql, [...values, id]);

        return result;
    }

    delete = async (id) => {
        const sql = `DELETE FROM ${this.tableName}
        WHERE id = ?`;
        const result = await query(sql, [id]);
        const affectedRows = result ? result.affectedRows : 0;

        return affectedRows;
    }
}

module.exports = new UserModel;

This class makes the connection between the controller and the database.
Here we have all the methods that get the arguments from the controller, make a query, prepare statements, connect to the database by using the query method from the db-connection class, send the request with prepared statements array and get the result back.
Each function returns the result to the controller.

.gitIgnore:

In case you decide to add this project to your GitHub, don't forget to create a .gitignore file and copy-paste this:

node_modules
.env

This file just tells git which files it should ignore.
You should avoid node_modules directory because it is heavy and not necessary for the repository.
When someone clones this repository, they will use the “npm I” command to install all of the dependencies.
Ignoring the .env file is to hide your private configurations from other developers using your code.

Source code:

The complete source code for this sample can be found on Github.

Comments

[Darkone Corp]

Hey Julia, Thanks for the precise guide.

I have the following question, how can I execute a procedure that receives an input parameter?
Try the following way:
const sql = CALL insert_data(?);
const result = await query(sql, [dataJson]);

But I get the following error message:
Error] Error: Incorrect arguments to mysqld_stmt_execute.

[Julia Strichash]

Hey, I guess you used the Create Procedure statement, which I didn't mention in my post.
In any case, if you created a procedure with one parameter, all you need is to use

const result = await query(sql, [value]);

• The first argument is the SQL query,
• The second argument is a flat array with all the values in the same order as '?' marks. The value should be simple, like string, int, and so on.

For example:
Create Procedure:

DELIMITER //
CREATE PROCEDURE insert_data(
  IN usrname VARCHAR(25),
  IN pass CHAR(60),
  IN firstname VARCHAR(50),
  IN lastname VARCHAR(50),
  IN useremail VARCHAR(100),
  IN userrole ENUM('Admin', 'SuperUser'),
  IN userage INT(11)
) BEGIN
INSERT INTO
  user (
    username,
    password,
    first_name,
    last_name,
    email,
    role,
    age
  )
VALUES
  (
    usrname,
    pass,
    firstname,
    lastname,
    useremail,
    userrole,
    userage
  );
  END//
DELIMITER ;

Add a method in user.model.js file, which uses the call command:

    create = async ({ username, password, first_name, last_name, email, role = Role.SuperUser, age = 0 }) => {
        const sql = 'CALL insert_data(?,?,?,?,?,?,?)';

        const result = await query(sql, [username, password, first_name, last_name, email, role, age]);
        const affectedRows = result ? result.affectedRows : 0;

        return affectedRows;
    }

[Darkone Corp]

Thanks Julia for your response and explanation, I could solve my problem.

[Darkone Corp]

Hi Julia.
I have to execute a procedure that returns an output parameter. How can I get that value ?

[Ravikumar Phd]

HI thanks for the mindblowing Article

but am facing the issue while starting the npm
the line no. 13 in user.controller.js
getAllUsers = async (req, res, next) => {
am using MySQL

Error:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\xampp\htdocs\mysql-node-express>npm start

mysql-node-express@1.0.0 start C:\xampp\htdocs\mysql-node-express
node src/server.js

C:\xampp\htdocs\mysql-node-express\src\controllers\user.controller.js:13
getAllUsers = async (req, res, next) => {
^

SyntaxError: Unexpected token =
at new Script (vm.js:79:7)
at createScript (vm.js:251:10)
at Object.runInThisContext (vm.js:303:10)
at Module._compile (internal/modules/cjs/loader.js:657:28)
at Object.Module._extensions..js (internal/modules/cjs/loader.js:700:10)
at Module.load (internal/modules/cjs/loader.js:599:32)
at tryModuleLoad (internal/modules/cjs/loader.js:538:12)
at Function.Module._load (internal/modules/cjs/loader.js:530:3)
at Module.require (internal/modules/cjs/loader.js:637:17)
at require (internal/modules/cjs/helpers.js:22:18)
npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! mysql-node-express@1.0.0 start: node src/server.js
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the mysql-node-express@1.0.0 start script.
npm ERR! This is probably not a problem with npm. There is likely additional log
ging output above.

npm ERR! A complete log of this run can be found in:
npm ERR! C:\Users\User\AppData\Roaming\npm-cache_logs\2021-02-24T09_31_30_8
14Z-debug.log

C:\xampp\htdocs\mysql-node-express>

please check screenshot

[mauro-montrond]

Hey man,
I think I was running into the same error, check your node version and update to the latest version so it recognizes the latest syntax.
I hope this helped you, cheers.

[meghead]

Hi Julia,

I installed from your Github repository instructions. I then tried to create a user from /api/v1/users using the Postman raw body example which you provided. But I got the following error;

"type": "error",
"status": 400,
"message": "Validation faild",  

[Julia Strichash]

Hi,
Can you please share the entire request body and the response?
As part of the response, you should get an array that includes the relevant errors.

For instance:

{
    "type": "error",
    "status": 400,
    "message": "Validation faild",
    "errors": [
        {
            "msg": "username is required",
            "param": "username",
            "location": "body"
        },
        {
            "msg": "Must be at least 3 chars long",
            "param": "username",
            "location": "body"
        },
        {
            "value": "12345",
            "msg": "Password must contain at least 6 characters",
            "param": "password",
            "location": "body"
        }
    ]
}

Thanks!

[morganmattr]

I was getting 400s (every key was returned as an error) problem, and it was just because in the Postman UI was sending the raw data as 'text' instead of 'json'. 'text' seems to be the default in the Postman UI, so make sure you switch it to json and you will stop getting the 400 error.

By the way Julia, love this guide and all your detailed explanations. Thank you for sharing!

[Julia Strichash]

Hi, I'm glad you were able to solve this, and you're right, we need to make sure it's a JSON format before we send the request.
Thanks a lot! You're very welcome! :)

[Julia Strichash]

Hi,
You're welcome :)
Question: did you install myqsl2 or mysql node module?
The error that you described occurs when I try to use mysql node module.

[Charles]

thanks a lot for your quick response
i think is mysql2

this is my package.json
"cors": "^2.8.5",
"dotenv": "^8.2.0",
"express": "^4.17.1",
"express-validator": "^6.10.0",
"jsonwebtoken": "^8.5.1",
"mysql2": "^2.2.5"

[Andre7th]

Hi Julia from Greece!
We thank you very much for the perfect guide!!!! I follow it faithfully!!!!
I would like you to answer a question for me about this point:

user.model.js ----->find = async (params = {}) => {.......

The question is why params is empty(={})?
I would like to use your folowing code:

if (!Object.keys(params).length) {
return await query(sql);
}

  const { columnSet, values } = multipleColumnSet(params)
   sql += ` WHERE ${columnSet}`;

but i dont know how to use...
Sorry for my English....
Thank you again!

[RamakanthRapaka]

Hi Julia,
Can you please help me,How can we write query for WHERE IN,
when passing array to query it is giving no records found 404 response
when am passing comma separated ids it is giving syntax error because comma separated ids converting as string '(6,5) '

SELECT farmer.*, farmer_address.country, farmer_address.region, farmer_address.landmark FROM farmer INNER JOIN farmer_address ON farmer.id = farmer_address.farmer_id WHERE farmer.id IN '(6,5)'

for where in query can please share small code snippet

[Julia Strichash]

Hi,
You need to use the prepared statement.
Just put placeholders (?) in the IN => IN(?, ?)
and pass the [5,6] array as a parameter to the query async method:

const result = await query(sql, [5, 6]);

In my repository(on GitHub), I added an additional function in the common.utils.js file. this function sets the placeholders(?) instead of the array items, and returns back a string like '?, ?'.

exports.getPlaceholderStringForArray = (arr) => {
    if (!Array.isArray(arr)) {
        throw new Error('Invalid input');
    }

    // if is array, we'll clone the arr 
    // and fill the new array with placeholders
    const placeholders = [...arr];
    return placeholders.fill('?').join(', ').trim();
}

So you can use this function to fill the placeholders as the number of your array something like => IN(getPlaceholderStringForArray([5, 6]))
Just don't forget to import the function.

[RamakanthRapaka]

Hi Julie,

var farmercropsLists = await UserCropsModel.findCropsByChampsArray({ 'farmer.id': farmar_ids });

if (!Object.keys(params).length) {
return await query(sql);
}
for (const [key, value] of Object.entries(params)) {
var values = value;
sql += WHERE ${key} IN ( + getPlaceholderStringForArray(value) + );
}
console.log(sql);

    return await query(sql, values);

can you please suggest JULIE how can we use where and where IN in single function

Thank you

[krishil-modi]

Hi Julia,
I am backend developer. your api code is working good in post method just like ( Create user and login ) api. my concern is get method. i have issue in get method. i tested with postman, my api end point is ( localhost:3000/api/v1/users/id/1 ) and method is GET but response is below
{
"type": "error",
"status": 401,
"message": "Access denied. No credentials sent!"
}

how to pass parameter in GET method please suggest me.

Thanks
Darshan Modi

[Julia Strichash]

Hi!
As you can see, I used the auth function almost on all of the routes instead of the create user, and login routes.

That means that after creating your user, first, you need to login to get a token.
Then put it in the Authorization in the headers with 'Bearer' before.

And then you'll have access to the rest of the routes (according to your user role).

[krishil-modi]

Thanks Julia,
As per follow your above comment my API is working fine in GET method.

Request: localhost:3000/api/v1/users/id/1 ( Method GET )
Headers: Authorization (Key) : Bearer token

Response:
{
"id": 1,
"username": "krishil",
"first_name": "Darshan",
"last_name": "Modi",
"email": "[e-mail]",
"role": "SuperUser",
"age": 37
}

Thanks
Darshan Modi

[Julia Strichash]

I'm glad it worked!
You're welcome! :)

[Julia Strichash]

Hi,
This post shows just how to combine NodeJS with MYSQL in a simple way.
You can run whatever queries you want using the query async method (await query(sql, []))
Just don't forget the prepared statements if you have user input.

How to do it is another part of the SQL.

If you want to make a query that depends on another query,
all you need to do is write the first query with await query-> get the result,
Then use it on another query.

async-await it's a new feature from ES8, and it's like the newest version of Promise.

promise.then(data => {
    console.log(data);
});

Is like:

const data = await promise();
console.log(data);

The line below the await promise() is like using the .then() method.

You can read more about it to understand how to use it.

The utils.js file is just a file to hold the common functions that we can reuse over and over; that's all.

[Riguidix Rodriguez]

Wow, pretty good... Will try to do an API that takes care of simplicity and don't have code duplication, I've been working one and it's an entire mess to keep Copy/Pasting the controllers over and over

[patrickcharda]

Thanks for sharing, very useful and formative !