Node.js + MySQL - CRUD API Example and Tutorial

Tutorial built with Node.js, Sequelize and MySQL

Other versions available:

• Node: Node.js + MSSQL
• .NET: .NET 7.0 + Dapper + MSSQL, .NET 7.0 + Dapper + PostgreSQL, .NET 7.0 + Dapper + MySQL, .NET 7.0 + Dapper + SQLite, .NET 6.0 + EF Core, .NET 5.0 + EF Core

In this tutorial we'll cover how to build a simple Node.js API that supports CRUD operations and stores data in a MySQL database. The example API includes routes to retrieve, update, create and delete records in a MySQL database, the records in the example are user records but the same CRUD pattern and code structure could be applied to any type of data e.g. products, services, articles etc.

MySQL Code First DB Generation with Sequelize

The Node.js API automatically creates the MySQL database on startup (if required) and synchronizes db tables and columns with javascript models defined using the Sequelize ORM library. Database creation and model synchronization is performed by the initialize() function in the MySQL database wrapper.

Code on GitHub

The CRUD API project is available on GitHub at https://github.com/cornflourblue/node-mysql-crud-api.

Tutorial Contents

• Tools required for this tutorial
• Run the Node + MySQL CRUD API locally
• Run a React App with the Node.js API
• Run an Angular App with the Node.js API
• Node + MySQL CRUD API Project Structure

Tools required for this tutorial

To follow the steps in this tutorial you'll need the following:

• Node.js & npm - includes the Node.js runtime, command line tools and package manager, install it from https://nodejs.org/.
• MySQL - you'll need access to running MySQL server instance for the API to connect to, it can be remote (e.g. Azure, AWS etc) or on your local machine. The Community Server version is available for free from https://dev.mysql.com/downloads/mysql/, ensure it is started so the API can connect to it. Installation instructions are available at https://dev.mysql.com/doc/refman/8.0/en/installing.html.
• A code editor to view and edit the API code, it doesn't matter which one, personally I use Visual Studio Code which is a free editor that runs on Windows, Mac and Linux, you can download it at https://code.visualstudio.com/.

Run the Node + MySQL CRUD API Locally

1. Download or clone the project source code from https://github.com/cornflourblue/node-mysql-crud-api
2. Install all required npm packages by running npm install or npm i from the command line in the project root folder (where the package.json is located).
3. Update the database credentials in /config.json to connect to your MySQL server instance, and ensure MySQL server is running.
4. Start the API by running npm start (or npm run start:dev to start with nodemon) from the command line in the project root folder, you should see the message Server listening on port 4000.
5. You can test the API directly with a tool such as Postman or hook it up with the example React or Angular applications below.

Sequelize model synchronization

On startup you should also see output like below from the Sequelize model synchronization that is executed in the MySQL database wrapper, it shows the creation and syncing of the Users table based on the Sequelize user model.

Executing (default): CREATE TABLE IF NOT EXISTS `Users` (`id` INTEGER NOT NULL auto_increment , `email` VARCHAR(255) NOT NULL, `passwordHash` VARCHAR(255) NOT NULL, `title` VARCHAR(255) NOT NULL, `firstName` VARCHAR(255) NOT NULL, `lastName` VARCHAR(255) NOT NULL, `role` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW FULL COLUMNS FROM `Users`;
Executing (default): SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'Users' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='node-mysql-crud-api' AND REFERENCED_TABLE_NAME IS NOT NULL;
Executing (default): ALTER TABLE `Users` CHANGE `email` `email` VARCHAR(255) NOT NULL;
Executing (default): ALTER TABLE `Users` CHANGE `passwordHash` `passwordHash` VARCHAR(255) NOT NULL;
Executing (default): ALTER TABLE `Users` CHANGE `title` `title` VARCHAR(255) NOT NULL;
Executing (default): ALTER TABLE `Users` CHANGE `firstName` `firstName` VARCHAR(255) NOT NULL;
Executing (default): ALTER TABLE `Users` CHANGE `lastName` `lastName` VARCHAR(255) NOT NULL;
Executing (default): ALTER TABLE `Users` CHANGE `role` `role` VARCHAR(255) NOT NULL;
Executing (default): ALTER TABLE `Users` CHANGE `createdAt` `createdAt` DATETIME NOT NULL;
Executing (default): ALTER TABLE `Users` CHANGE `updatedAt` `updatedAt` DATETIME NOT NULL;
Executing (default): SHOW INDEX FROM `Users`

Run a React App with the Node + MySQL CRUD API

For full details about the React CRUD app see the post React - CRUD Example with React Hook Form. But to get up and running quickly just follow the below steps.

1. Download or clone the React tutorial code from https://github.com/cornflourblue/react-hook-form-crud-example
2. Install all required npm packages by running npm install or npm i from the command line in the project root folder (where the package.json is located).
3. Remove or comment out the 2 lines below the comment // setup fake backend located in the /src/index.jsx file.
4. Start the application by running npm start from the command line in the project root folder, this will launch a browser displaying the application and it should be hooked up with the Node + MySQL CRUD API that you already have running.

Run an Angular App with the Node + MySQL CRUD API

For full details about the Angular CRUD app see the post Angular 11 - CRUD Example with Reactive Forms. But to get up and running quickly just follow the below steps.

1. Download or clone the Angular tutorial code from https://github.com/cornflourblue/angular-11-crud-example
2. Install all required npm packages by running npm install from the command line in the project root folder (where the package.json is located).
3. Remove or comment out the line below the comment // provider used to create fake backend located in the /src/app/app.module.ts file.
4. Start the application by running npm start from the command line in the project root folder, this will launch a browser displaying the application and it should be hooked up with the Node.js + MySQL CRUD API that you already have running.

Node.js + MySQL CRUD API Project Structure

The tutorial project is structured into feature folders (users) and non-feature / shared component folders (_helpers, _middleware). Shared component folders contain code that can be used across multiple features or other parts of the application, and are prefixed with an underscore _ to group them together and make it easy to differentiate between feature specific and shared code.

The CRUD example only contains a single feature (users) at the moment, but could be easily extended with other features by copying the users folder and following the same pattern.

Click any of the below links to jump down to a description of each file along with its code:

• _helpers
  • db.js
  • role.js
• _middleware
  • error-handler.js
  • validate-request.js
• users
  • user.model.js
  • user.service.js
  • users.controller.js
• config.json
• package.json
• server.js

Helpers Folder

The helpers folder contains all the bits and pieces that don't fit into other folders but don't justify having a folder of their own.

MySQL Database Wrapper

The MySQL database wrapper connects to MySQL using Sequelize & the MySQL2 client, and exports an object that exposes all of the database models for the application (currently only User). It provides an easy way to access any part of the database from a single point.

The initialize() function is executed once on API startup and performs the following actions:

• Connects to MySQL server using the mysql2 db client and executes a query to create the API database if it doesn't already exist.
• Connects to the API database with the Sequelize ORM.
• Initializes the User model and attaches it to the exported db object.
• Automatically creates/updates tables in the MySQL database to match the Sequelize model (if required) by calling await sequelize.sync({ alter: true }). For more info on Sequelize model synchronization options see https://sequelize.org/master/manual/model-basics.html#model-synchronization.

const config = require('config.json');
const mysql = require('mysql2/promise');
const { Sequelize } = require('sequelize');

module.exports = db = {};

initialize();

async function initialize() {
    // create db if it doesn't already exist
    const { host, port, user, password, database } = config.database;
    const connection = await mysql.createConnection({ host, port, user, password });
    await connection.query(`CREATE DATABASE IF NOT EXISTS \`${database}\`;`);

    // connect to db
    const sequelize = new Sequelize(database, user, password, { dialect: 'mysql' });

    // init models and add them to the exported db object
    db.User = require('../users/user.model')(sequelize);

    // sync all models with database
    await sequelize.sync({ alter: true });
}

Role Object / Enum

The role object defines the all the roles in the example application, I created it to use like an enum to avoid passing roles around as strings, so instead of 'Admin' and 'User' we can use Role.Admin and Role.User.

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

Express.js Middleware Folder

The middleware folder contains Express.js middleware functions that can be used by routes / features within the Node.js CRUD API.

Global Error Handler Middleware

The global error handler is used catch all errors and remove the need for duplicated error handling code throughout the CRUD application. It's configured as middleware in the main server.js file.

By convention errors of type 'string' are treated as custom (app specific) errors, this simplifies the code for throwing custom errors since only a string needs to be thrown (e.g. throw 'Error message'). Further to this if a custom error ends with the words 'not found' a 404 response code is returned, otherwise a standard 400 response is returned. See the user service for some examples of custom errors thrown by the API, errors are caught in the users controller for each route and passed to the next function which passes them to this global error handler.

module.exports = errorHandler;

function errorHandler(err, req, res, next) {
    switch (true) {
        case typeof err === 'string':
            // custom application error
            const is404 = err.toLowerCase().endsWith('not found');
            const statusCode = is404 ? 404 : 400;
            return res.status(statusCode).json({ message: err });
        default:
            return res.status(500).json({ message: err.message });
    }
}

Validate Request Middleware

The validate request middleware function validates the body of a request against a Joi schema object.

It is used by schema middleware functions in controllers to validate requests with a schema for a specific route (e.g. createSchema for the create user route in the users controller). For more info about Joi schema validation see https://www.npmjs.com/package/joi.

module.exports = validateRequest;

function validateRequest(req, next, schema) {
    const options = {
        abortEarly: false, // include all errors
        allowUnknown: true, // ignore unknown props
        stripUnknown: true // remove unknown props
    };
    const { error, value } = schema.validate(req.body, options);
    if (error) {
        next(`Validation error: ${error.details.map(x => x.message).join(', ')}`);
    } else {
        req.body = value;
        next();
    }
}

Users Feature Folder

The users folder contains all code that is specific to the users feature of the Node.js + MySQL CRUD API.

Sequelize User Model

The user model uses Sequelize to define the schema for the users table in the MySQL database. The exported Sequelize model object gives full access to perform CRUD (create, read, update, delete) operations on users in MySQL, see the user service below for examples of it being used (via the db helper).

The defaultScope configures the model to exclude the password hash from query results by default. The withHash scope can be used to query users and include the password hash in results. For more info on Sequelize scopes see https://sequelize.org/master/manual/scopes.html.

const { DataTypes } = require('sequelize');

module.exports = model;

function model(sequelize) {
    const attributes = {
        email: { type: DataTypes.STRING, allowNull: false },
        passwordHash: { type: DataTypes.STRING, allowNull: false },
        title: { type: DataTypes.STRING, allowNull: false },
        firstName: { type: DataTypes.STRING, allowNull: false },
        lastName: { type: DataTypes.STRING, allowNull: false },
        role: { type: DataTypes.STRING, allowNull: false }
    };

    const options = {
        defaultScope: {
            // exclude password hash by default
            attributes: { exclude: ['passwordHash'] }
        },
        scopes: {
            // include hash with this scope
            withHash: { attributes: {}, }
        }
    };

    return sequelize.define('User', attributes, options);
}

User Service

The user service is responsible for all database interaction and core business logic related to user CRUD operations, it encapsulates all interaction with the Sequelize user model and exposes a simple set of methods which are used by the users controller.

The top of the file contains the exported service object with just the method names to make it easy to see all the methods at a glance, the rest of the file contains the implementation functions for each service method, followed by local helper functions.

const bcrypt = require('bcryptjs');
const db = require('_helpers/db');

module.exports = {
    getAll,
    getById,
    create,
    update,
    delete: _delete
};

async function getAll() {
    return await db.User.findAll();
}

async function getById(id) {
    return await getUser(id);
}

async function create(params) {
    // validate
    if (await db.User.findOne({ where: { email: params.email } })) {
        throw 'Email "' + params.email + '" is already registered';
    }

    const user = new db.User(params);
    
    // hash password
    user.passwordHash = await bcrypt.hash(params.password, 10);

    // save user
    await user.save();
}

async function update(id, params) {
    const user = await getUser(id);

    // validate
    const usernameChanged = params.username && user.username !== params.username;
    if (usernameChanged && await db.User.findOne({ where: { username: params.username } })) {
        throw 'Username "' + params.username + '" is already taken';
    }

    // hash password if it was entered
    if (params.password) {
        params.passwordHash = await bcrypt.hash(params.password, 10);
    }

    // copy params to user and save
    Object.assign(user, params);
    await user.save();
}

async function _delete(id) {
    const user = await getUser(id);
    await user.destroy();
}

// helper functions

async function getUser(id) {
    const user = await db.User.findByPk(id);
    if (!user) throw 'User not found';
    return user;
}

Express.js Users Controller

The users controller defines all /users routes for the Node.js + MySQL CRUD API, the route definitions are grouped together at the top of the file and the implementation functions are below, followed by route schema validation functions. The controller is bound to the /users path in the main server.js file.

Routes that require schema validation include a middleware function with the naming convention <route>Schema (e.g. createSchema). Each schema validation function defines a schema for the request body using the Joi library and calls validateRequest(req, next, schema) to ensure the request body is valid. If validation succeeds the request continues to the next middleware function (the route function), otherwise an error is returned with details of why validation failed. For more info about Joi schema validation see https://www.npmjs.com/package/joi.

Express is the web server used by the CRUD API, it's one of the most popular web application frameworks for Node.js. For more info see https://expressjs.com/.

const express = require('express');
const router = express.Router();
const Joi = require('joi');
const validateRequest = require('_middleware/validate-request');
const Role = require('_helpers/role');
const userService = require('./user.service');

// routes

router.get('/', getAll);
router.get('/:id', getById);
router.post('/', createSchema, create);
router.put('/:id', updateSchema, update);
router.delete('/:id', _delete);

module.exports = router;

// route functions

function getAll(req, res, next) {
    userService.getAll()
        .then(users => res.json(users))
        .catch(next);
}

function getById(req, res, next) {
    userService.getById(req.params.id)
        .then(user => res.json(user))
        .catch(next);
}

function create(req, res, next) {
    userService.create(req.body)
        .then(() => res.json({ message: 'User created' }))
        .catch(next);
}

function update(req, res, next) {
    userService.update(req.params.id, req.body)
        .then(() => res.json({ message: 'User updated' }))
        .catch(next);
}

function _delete(req, res, next) {
    userService.delete(req.params.id)
        .then(() => res.json({ message: 'User deleted' }))
        .catch(next);
}

// schema functions

function createSchema(req, res, next) {
    const schema = Joi.object({
        title: Joi.string().required(),
        firstName: Joi.string().required(),
        lastName: Joi.string().required(),
        role: Joi.string().valid(Role.Admin, Role.User).required(),
        email: Joi.string().email().required(),
        password: Joi.string().min(6).required(),
        confirmPassword: Joi.string().valid(Joi.ref('password')).required()
    });
    validateRequest(req, next, schema);
}

function updateSchema(req, res, next) {
    const schema = Joi.object({
        title: Joi.string().empty(''),
        firstName: Joi.string().empty(''),
        lastName: Joi.string().empty(''),
        role: Joi.string().valid(Role.Admin, Role.User).empty(''),
        email: Joi.string().email().empty(''),
        password: Joi.string().min(6).empty(''),
        confirmPassword: Joi.string().valid(Joi.ref('password')).empty('')
    }).with('password', 'confirmPassword');
    validateRequest(req, next, schema);
}

Api Config

The API config file contains configuration data for the CRUD API, it includes the database connection options for the MySQL database, the database.database property is the name of the database that is automatically created by the API on startup.

{
    "database": {
        "host": "localhost",
        "port": 3306,
        "user": "root",
        "password": "",
        "database": "node-mysql-crud-api"
    }
}

Package.json

The package.json file contains project configuration information including Node.js package dependencies that get installed when you run npm install.

The scripts section contains scripts that are executed by running the command npm run <script name>, the start script can also be run with the shortcut command npm start.

The start script starts the CRUD API normally using node, and the start:dev script starts the API in development mode using nodemon which automatically restarts the Node.js API when a file is changed.

For more info see https://docs.npmjs.com/files/package.json.

{
    "name": "node-mysql-crud-api",
    "version": "1.0.0",
    "license": "MIT",
    "scripts": {
        "start": "node ./server.js",
        "start:dev": "nodemon ./server.js"
    },
    "dependencies": {
        "bcryptjs": "^2.4.3",
        "cors": "^2.8.5",
        "express": "^4.17.1",
        "joi": "^17.2.0",
        "mysql2": "^2.1.0",
        "rootpath": "^0.1.2",
        "sequelize": "^6.3.4"
    },
    "devDependencies": {
        "nodemon": "^2.0.4"
    }
}

Server Startup File

The server.js file is the entry point to the Node.js CRUD API, it configures application middleware, binds controllers to routes and starts the Express web server for the API.

require('rootpath')();
const express = require('express');
const app = express();
const cors = require('cors');
const errorHandler = require('_middleware/error-handler');

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

// api routes
app.use('/users', require('./users/users.controller'));

// global error handler
app.use(errorHandler);

// start server
const port = process.env.NODE_ENV === 'production' ? (process.env.PORT || 80) : 4000;
app.listen(port, () => console.log('Server listening on port ' + port));