DEV Community

Cover image for How To Build Rest API With NodeJS, Express, and MySQL
Julia Strichash
Julia Strichash

Posted on • Updated on

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

#node #express #mysql #jwt

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
Enter fullscreen mode Exit fullscreen mode

If it's not running, just use:

sudo service mysql start
Enter fullscreen mode Exit fullscreen mode

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  |
+---------+------------------------------+--------------------------------+
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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"
  }
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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": []
  }
Enter fullscreen mode Exit fullscreen mode

Create the src folder: 

mkdir src && cd src
Enter fullscreen mode Exit fullscreen mode

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

mkdir controllers models routes middleware db utils
Enter fullscreen mode Exit fullscreen mode

The project should look like this

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;
Enter fullscreen mode Exit fullscreen mode

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.
After creating server.js

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 
  );
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
}
*/
Enter fullscreen mode Exit fullscreen mode

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).
The project should look like this

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
    }
}
Enter fullscreen mode Exit fullscreen mode

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'
}
Enter fullscreen mode Exit fullscreen mode

utils directory

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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')
];
Enter fullscreen mode Exit fullscreen mode

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.
middleware directory

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;
Enter fullscreen mode Exit fullscreen mode

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:
HTTP method syntax

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.

Total project design

Source code:

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

Top comments (48)

Collapse
 
darkonecorp profile image
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.

Collapse
 
juliest88 profile image
Julia Strichash • • Edited

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]);
Enter fullscreen mode Exit fullscreen mode
  • 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 ;
Enter fullscreen mode Exit fullscreen mode

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;
    }
Enter fullscreen mode Exit fullscreen mode
Collapse
 
darkonecorp profile image
Darkone Corp •

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

Collapse
 
darkonecorp profile image
Darkone Corp •

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

Collapse
 
raviphad profile image
Ravikumar Phd • • Edited

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

Collapse
 
mauromontrond profile image
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.

Collapse
 
meghead profile image
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",
Enter fullscreen mode Exit fullscreen mode
Collapse
 
juliest88 profile image
Julia Strichash • • Edited

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"
        }
    ]
}
Enter fullscreen mode Exit fullscreen mode

Thanks!

Collapse
 
morganmattr profile image
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!

Thread Thread
 
juliest88 profile image
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! :)

Collapse
 
Sloan, the sloth mascot
Comment deleted
Collapse
 
juliest88 profile image
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.

Collapse
 
carlossoto2 profile image
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"

Collapse
 
andre7th profile image
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!

Collapse
 
ramakanthrapaka profile image
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

Collapse
 
juliest88 profile image
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]);
Enter fullscreen mode Exit fullscreen mode

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();
}
Enter fullscreen mode Exit fullscreen mode

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.

Collapse
 
ramakanthrapaka profile image
RamakanthRapaka • • Edited

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);
Enter fullscreen mode Exit fullscreen mode

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

Thank you

Collapse
 
krishilmodi profile image
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

Collapse
 
juliest88 profile image
Julia Strichash • • Edited

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).

Collapse
 
krishilmodi profile image
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": "darshanmodi2010@gmail.com",
"role": "SuperUser",
"age": 37
}

Thanks
Darshan Modi

Thread Thread
 
juliest88 profile image
Julia Strichash •

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

Collapse
 
Sloan, the sloth mascot
Comment deleted
Collapse
 
juliest88 profile image
Julia Strichash • • Edited

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);
});
Enter fullscreen mode Exit fullscreen mode

Is like:

const data = await promise();
console.log(data);
Enter fullscreen mode Exit fullscreen mode

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.

Collapse
 
riguidix profile image
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

Collapse
 
patrickcharda profile image
patrickcharda •

Thanks for sharing, very useful and formative !

Collapse
 
clintonksang_81 profile image
Clinton • • Edited

Hey Julia, Thanks for the precise guide, Im having this error when I try to Get from DB,

I doubt I'm having issues with configuring JWT. Kindly assist
dev-to-uploads.s3.amazonaws.com/i/...

Collapse
 
juliest88 profile image
Julia Strichash • • Edited

Hey,
I can't see your error. if you tried to upload an image, you need to copy and paste it to your comment.

Collapse
 
clintonksang_81 profile image
Clinton •

I've edited

Thread Thread
 
juliest88 profile image
Julia Strichash • • Edited

Thanks!
I think it's something related to an invalid token.

You have to get the token from the login response and then paste it into the header after the "Bearer .." in the Authorization

Collapse
 
gosan profile image
GOsan • • Edited

Emm i need support for issue
after post localhost:3331/api/v1/users
with password and confirm_password the same value still not create a new user?
dev-to-uploads.s3.amazonaws.com/i/...

Collapse
 
juliest88 profile image
Julia Strichash • • Edited

Hi, I was able to reproduce the error you saw.
Since it's a POST method, all the parameters should be sent as part of the body.
In the request you prepared, all the parameters were part of the headers, which caused the error to occur.

You didn't get errors in all the other parameters because they already existed in the request object (req.headers), but I checked req.body.password for the password validation and therefore you got an error (because the body was an empty object, without the password property).

There really needs to be a verification that all the parameters have been entered in the body parameter and not somewhere else.

In the userValidator.middleware.js file, we can use the body function instead of the check function (from the express-validator library).
This way we're only going to check the req.body.
I updated the code.

In any case, if you put all the parameters in the body, as I described in the image, it won't be a problem to create a user with the parameters you entered.

dev-to-uploads.s3.amazonaws.com/i/...

Collapse
 
carlossoto2 profile image
Charles •

Hello Julia

I need a help i'm a novice

i'm having this errors

1.- Class properties must be methods. Expected '(' but instead saw '='. (E054)jshint(E054)

2.- Class properties must be methods. Expected '(' but instead saw 'callback'. (E054)jshint(E054)

query = async(sql, values) => {
return new Promise((resolve, reject) => {
const callback = (error, result) => {
if (error) {
reject(error);
return;
}
resolve(result);
}

any help

Collapse
 
andylink profile image
andylink •

Hi Julia great article. Wonder if you could help me with something? How would I alter get CurrentUser in usercontroller.js to only return username and email of the current user?

I'm struggling to work out how userWithoutPassword works.

Thanks

Andy

Collapse
 
andylink profile image
andylink •

Ah, typical, posted for help and worked it out shortly afterwards.

Wasn't aware of the es6 spread operator, altering as per below removes those items from being sent.

getCurrentUser = async (req, res, next) => {
const { password, first_name, last_name...userWithLessInfo } = req.currentUser;
res.send(userWithLessInfo);
};

Collapse
 
zifherx profile image
Fernando_R •

Hello Julia,
I appreciate your article,
But when consuming the Login I get this error:
[Error] ReferenceError: columnSet is not defined
Maybe I am omitting something but I would appreciate it if you would give me a hand.

Collapse
 
juliest88 profile image
Julia Strichash • • Edited

Hi Fernando,
Can you describe the steps you did?
I need more information.
Did you send an email and a password on the body request?

Basically, userLogin method performs the following steps on the user.controller.js file:

  • Check for validation
  • Extract email and password from req.body
  • Use findOne method by email <= (this is where we are using columnSet).
  • If there is no such user with this email => throw HTTP error exception.
  • Checking whether the user input password matches the user password from the DB.
  • If not => throw HTTP error exception.
  • If the user matched => create a token by secretKey
  • Send back the user (without the password) and the token.

Perhaps it's something that needs if block (or something like that),
But I need to see the steps that you have taken.

Thanks! :)

View full discussion (46 comments)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.

Read next

zenstok profile image

NestJS Dependency Injection in Worker Threads

zenstok -

ethernmyth profile image

Blending in with Pure-Blend

Ethern Myth -

thevinitgupta profile image

The Last Event Loop Guide you will Ever Need

Vinit Gupta -

vaibhav_31 profile image

Real time chat app using sockets

Vaibhav Arora -