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.

Blog