Let's create a Node.js REST CRUD server with MySql

Greetings!

In a previous, I briefly discussed the steps to create a simple REST server using Node.js. In this post, I'm discussing the steps needed to create a restful crud API using Node, Express, and MySql with Sequelize.

The complete source code can be found here.

What we build

We are going to create CRUD operations for books. It is very simple and we don't go too in-depth as the idea is to explore how we can build a restful API with Node and MySql.

Steps

Create the project
Create the express server
Define end points and layers
Implement endpoints layers

Package structure

Create the project

We will use ES6 features hence babel is used. Nodemon is used to auto-start the server with new changes.

npm init -y
npm install express body-parser mysql2 sequelize cors --save
npm install @babel/core @babel/node @babel/preset-env --save-dev
npm install nodemon --save-dev

{
  "name": "nodejs-rest-crud",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "type": "module",
  "scripts": {
    "start": "nodemon --exec babel-node src/index",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.20.0",
    "cors": "^2.8.5",
    "express": "^4.18.1",
    "mysql2": "^2.3.3",
    "sequelize": "^6.24.0"
  },
  "devDependencies": {
    "@babel/core": "^7.19.3",
    "@babel/node": "^7.19.1",
    "@babel/preset-env": "^7.19.3",
    "nodemon": "^2.0.20"
  }
}

{
  "presets": ["@babel/preset-env"]
}

Create the express server

Create index.js in the src folder -> src/index.js and add the below content to create a basic express server.

import express from "express";
import bodyParser from "body-parser";
import cors from "cors";

const app = express();

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

app.get("/", (req, res) => res.json({ message: "Hello World" }));

app.listen(3000, () => console.log("app listening on port 3000"));

Connect to the MySql database

We use Sequelize to perform MySql operations. Provide your MySql configurations here.

import { Sequelize } from "sequelize";

const db = new Sequelize('books', 'root', 'root', {
    host: "localhost",
    dialect: "mysql"
});

db.authenticate().then(() => {
  console.log('Connection has been established successfully.');
}).catch((error) => {
  console.error('Unable to connect to the database: ', error);
});

export default db;

Define entity model

Sequelize is an ORM for relational databases like MySql. We are using it here to define our entities.
Note that I have set timestamps to false as I do not want createdAt and updatedAt which are created by Sequelize.

import { DataTypes } from 'sequelize';
import db from '../../config/database.js';

const Book = db.define('Book', {
  id: {
    type: DataTypes.INTEGER,
    field: 'id',
    primaryKey: true
  },
  title: {
    type: DataTypes.STRING
  },
  author: {
    type: DataTypes.STRING
  }
}, {
  freezeTableName: true,
  timestamps: false
});

export default Book;

Create the repository

We have defined our Book model. We can use it to perform basic operations. Sequelize comes with very handy APIs for these operations. Anyway, I am not an expert on that (yet). These methods are self-explanatory. You can learn more about this in the documentation. (https://sequelize.org/docs/v6/)

import Book from './model/book-model.js';

class BookRepository {

  async findAll() {
    return await Book.findAll();
  }

  async findById(id) {
    return await Book.findByPk(id);
  }

  async save(book) {
    return await Book.create(book);
  }

  async update(book) {
    return await Book.update(book, {
      where: {
        id: book.id
      }
    });
  }

  async delete(bookId) {
    return await Book.destroy({
      where: {
        id: bookId
      }
    });
  }
}

export { BookRepository };

Implement the service layer

This is straightforward as we do not have any logic. We are delegating the request to the repository layer.
Take note that these methods return a promise as in repository later returns promises in its methods.

import { BookRepository } from '../repository/book-repository.js';

export class BookService {
  constructor() {
    this.bookRepository = new BookRepository();
  }

  findAll() {
    return this.bookRepository.findAll();
  }

  findById(id) {
    return this.bookRepository.findById(id);
  }

  save(book) {
    return this.bookRepository.save(book);
  }

  async update(id, book) {
    const saved = await this.findById(id);
    if (saved !== null) {
      book.id = id;
      this.bookRepository.update(book);
      return book;
    } else {
      return null;
    }
  }

  delete(id) {
    this.bookRepository.delete(id);
  }
}

Implementing the controller layer

This has more work to do as this is responsible for converting requests/responses and handling HTTP.

req.body - gives us the request body, as we have used json-bodyparser this gives us the json object.
req.params - gives us request parameters.
res.json() - returns the response in json format.
res.status() - we can set status codes

class BookController {
  constructor() {
    this.bookService = new BookService();
  }

  async findAll(req, res) {
    const books = await this.bookService.findAll();
    if (books) {
      res.json(books);
    } else {
      res.status(500).json({ message: 'something terrible happened' });
    }
  }

  async findById(req, res) {
    const id = req.params.id;
    const book = await this.bookService.findById(id);
    if (book) {
      res.json(book);
    } else {
      res.status(404).json({ message: `book not found for id ${id}` });
    }
  }

  async save(req, res) {
    const book = await this.bookService.save(req.body);
    if (book) {
      res.json(book);
    } else {
      res.status(500).json({ message: 'something terrible happened' });
    }
  }

  async update(req, res) {
    const id = req.params.id;
    const book = await this.bookService.update(id, req.body);
    if (book) {
      res.json(book);
    } else {
      res.status(404).json({ message: `book not found for id ${id}` });
    }
  }

  async delete(req, res) {
    await this.bookService.delete(req.params.id);
    res.status(204).json({ message: 'Book removed' });
  }
}

export { BookController };

Implement routes

Now it is time to implement our endpoints. We do this in the route layer to separate it from the main index.js.
Take note that we use arrow functions here instead of passing the functions directly. This is to preserve calling context unless bookController object will be lost.

import express from "express";
import { BookController } from '../controller/book-controller.js';

const bookRouter = express.Router();

const bookControler = new BookController();

bookRouter.get('/', async (req, res) => bookControler.findAll(req, res));
bookRouter.get('/:id', async (req, res) => bookControler.findById(req, res));
bookRouter.post('/', async (req, res) => bookControler.save(req, res));
bookRouter.put('/:id', async (req, res) => bookControler.update(req, res));
bookRouter.delete('/:id', async (req, res) => bookControler.delete(req, res));

export { bookRouter };

Finally, update the index.js

As all the layers are completed, use the route in the index.

import { bookRouter } from './route/book-router.js';
app.use("/books", bookRouter);

Demo

Now it is time to test this.

npm start

curl --location --request GET 'http://localhost:3000/books'

curl --location --request GET 'http://localhost:3000/books/8'

curl --location --request DELETE 'http://localhost:3000/books/8'

curl --location --request POST 'http://localhost:3000/books' \
--header 'Content-Type: application/json' \
--data-raw '{
    "title": "Clean Code",
    "author": "Robert C. Martin"
}'

curl --location --request PUT 'http://localhost:3000/books/1' \
--header 'Content-Type: application/json' \
--data-raw '{
    "title": "Clean Code",
    "author": "Robert C. Martin"
}'

That is it. It is very easy and simple.
Happy learning ☺

Manju