Luigi Morel

Posted on Apr 13, 2022 • Updated on Jun 4, 2023

Building an API using Express.js, Postgres, Sequelize CLI and Jest for Unit Testing.

#express #webdev #javascript

Crafting Application Programming Interfaces (APIs) is one of the tasks that developers have to do once in a while. Web developers use various tools to accomplish these tasks that are required in their day to day schedules.

In this tutorial, we'll explore how to build a simple e-commerce like API using PostgreSQL, Express.js and Sequelize CLI.

Here's what we will cover:

What is Sequelize?
Installing Sequelize CLI
Creating entity models using the CLI
Writing unit tests using Jest
Deployment to Heroku
Creating associations using Postgres and Sequelize CLI
CI/CD using Circle CI.

Pre-requisites

Basic knowledge about SQL
Express.js basics
HTTP methods

To follow along with this tutorial, you should have the following tools installed on your computer:

Nodejs 14x or higher (I'm using v14.17.3)
Yarn or NPM (I'm using Yarn v1.22.15)
A text editor (I'm using VS Code)
A local installation of PostgreSQL

What is Sequelize?

Sequelize is an easy-to-use JavaScript object relational mapping (ORM) tool that works with SQL databases.

Project set up

Installing Sequelize

Make a new directory that you'll use while working with this tutorial and install the project's dependencies.

mkdir sequelize-tutorial
cd sequelize-tutorial
yarn add sequelize cors pg morgan helmet && yarn add -D sequelize-cli nodemon dotenv

sequelize - Sequelize is a promise-based Node.js ORM tool for Postgres. It features solid transaction support, relations, eager and lazy loading, read replication and more.
cors - CORS is a node.js package for providing a Connect/Express middleware that can be used to enable CORS with various options.
pg -Non-blocking PostgreSQL client for Node.js.
morgan - HTTP request logger middleware for node.js
helmet - Helmet helps you secure your Express apps by setting various HTTP headers.
sequelize-cli - The Sequelize Command Line Interface (CLI)
nodemon - a tool that helps develop node.js based applications by automatically restarting the node application when file changes in the directory are detected.
dotenv - module that loads environment variables from a .env file into process.env

Add a .gitignore and .env files to the root of your project's folder.

touch .gitignore .env

Then, add the node_modules folder and .env file to your .gitignore

/node_modules
.env

Before we begin

Creating our entities

Our API has two entities

Product
Category

Relationships

One product belongs to one category.
One category has many products

Let's jump right into the code. 🤩

Initialize a Sequelize project, then open the directory in our code editor (VS Code):

yarn sequelize-cli init
code .

Configure the project to use with a Postgres database as the SQL dialect. Go to the config.json in the /config directory and change the code to this:

{
  "development": {
    "username": "postgres",
    "password": null,
    "database": "db_dev",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },

  "test": {
    "username": "postgres",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },

  "production": {
    "use_env_variable": "DATABASE_URL",
    "dialect": "postgres",
    "dialectOptions": {
      "ssl": {
        "rejectUnauthorized": false
      }
    }
  }
}

Since we're still in development mode of the project, add the username, database and password fields to the development object.
In my case, username is postgres, password is null (I'm not using a password - bad practice, I know) and database is db_dev.

Once done with the above step, create the database for the project using this command:

yarn sequelize-cli db:create

Defining models

Our goal is to associate the products with the categories that they belong to.

Let's create a Product model.

$ yarn sequelize-cli model:generate --name Product --attribute name:string,quantity:integer,inStock:boolean,productImage:string,expiryDate:date

Below are the contents for the migration file that has been created.

'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('Products', {
      id: {
        allowNull: false,
        primaryKey: true,
        type: Sequelize.UUID,
        defaultValue: Sequelize.UUIDV4,
      },
      name: {
        type: Sequelize.STRING,
        trim: true,
      },
      quantity: {
        type: Sequelize.INTEGER,
        trim: true,
        allowNull: false,
      },
      price: {
        type: Sequelize.INTEGER,
        allowNull: false,
        trim: true,
      },
      inStock: {
        type: Sequelize.BOOLEAN,
        defaultValue: false,
      },
      productImage: {
        type: Sequelize.STRING,
        allowNull: false,
        trim: true,
      },
      expiryDate: {
        type: Sequelize.DATE,
        allowNull: false,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    });
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('Products');
  },
};

Please note that I'm using UUID instead of the integer data type for the id field.

Read about how to use UUID here.

Here is an article about how to add an extension to your database via PSQL shell

When you run the model:generate command, Sequelize generates a model file, and a migration with the attributes specified.
We can now run the migration to enable us create the Products table in our database.

yarn sequelize-cli db:migrate

Let's generate the seed file for the Product model

yarn sequelize-cli seed:generate --name product

A new file ending with -product.js has been created in the /seeders folder. Copy and paste the code below which is sample data of the products.

'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.bulkInsert(
      'Products',
      [
        {
          id: '1373772c-125c-42d0-81ae-a6d020fcbe21',
          name: 'Bread',
          quantity: 4,
          inStock: true,
          productImage:
            'https://res.cloudinary.com/morelmiles/image/upload/v1649765314/download_nwfpru.jpg',
          // Store the price in cents e.g if price is $5, multiply by 100 cents e.g 5 * 100 = 500 cents
          price: 500,
          expiryDate: new Date(),
          createdAt: new Date(),
          updatedAt: new Date(),
        },
        {
          id: '9df55a7c-772c-459f-a21b-933a96981ca6',
          name: 'Milk',
          quantity: 4,
          inStock: true,
          productImage:
            'https://res.cloudinary.com/morelmiles/image/upload/v1647356184/milk_ckku96.jpg',
          // Store the price in cents e.g if the price is $5, multiply by 100 cents e.g 5 * 100 = 500 cents
          price: 100,
          expiryDate: new Date(),
          createdAt: new Date(),
          updatedAt: new Date(),
        },
      ],
      {}
    );
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.bulkDelete('Products', null, {});
  },
};

That's all for the Products model for now.

Create the Category model

$ yarn sequelize-cli model:generate --name Category --attributes name:string

Below is the migration file for the Category model.

'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('Categories', {
      id: {
        allowNull: false,
        primaryKey: true,
        type: Sequelize.UUID,
        defaultValue: Sequelize.UUIDV4,
      },
      name: {
        type: Sequelize.STRING,
        trim: true,
        allowNull: false,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    });
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('Categories');
  },
};

Let's create a seeder file for the Category model

yarn sequelize-cli seed:generate --name category

Below is the file with the seeder entries.

'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.bulkInsert(
      'Categories',
      [
        {
          id: 'a52467a3-3a71-45c4-bf1c-9ace5ad3668f',
          name: 'Confectionaries',
          createdAt: new Date(),
          updatedAt: new Date(),
        },
        {
          id: '33a9e6e0-9395-4f6c-b1cd-3cf1f87e195a',
          name: 'Drinks',
          createdAt: new Date(),
          updatedAt: new Date(),
        },
      ],
      {}
    );
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.bulkDelete('Categories', null, {});
  },
};

We can now create associations between the Products and the Category each belongs to.

Read more about associations : here

Replace the code in the models/product directory with the one below:

'use strict';

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

module.exports = (sequelize, DataTypes) => {
  class Product extends Model {
    static associate(models) {
      Product.belongsTo(models.Category, {
        foreignKey: 'categoryId',
      });
    }
  }

  Product.init(
    {
      name: DataTypes.STRING,
      quantity: DataTypes.INTEGER,
      inStock: DataTypes.BOOLEAN,
      productImage: DataTypes.STRING,
      price: DataTypes.INTEGER,
      expiryDate: DataTypes.DATE,
    },
    {
      sequelize,
      modelName: 'Product',
    }
  );
  return Product;
};

Replace the code in the models/category directory with the one below:

'use strict';

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

module.exports = (sequelize, DataTypes) => {
  class Category extends Model {
    static associate(models) {
      Category.hasMany(models.Product, {
        foreignKey: 'categoryId',
        onDelete: 'CASCADE',
      });
    }
  }
  Category.init(
    {
      name: DataTypes.STRING,
    },
    {
      sequelize,
      modelName: 'Category',
    }
  );
  return Category;
};

Let's now add the foreign key to the Product migration file.
Go to the /migrations sub-folder and add the code below to the file that ends with create-product.js:

categoryId: {
        type: Sequelize.UUID,
        allowNull: false,
        onDelete: 'CASCADE',
        references: {
          model: 'Categories',
          key: 'id',
          as: 'categoryId'
        }
      },

The above line of code adds the categoryId foreign key to the Products table in our database.

To add the categoryId column to the Products table, check the scripts in the package.json file.
There's a script named db:reset in the package.json file. Seen it? It'll help us to drop the database, create the database again, run the migrations and add the seeder data into our database.
Run it.

yarn db:reset

If you open PSQL and run SELECT * FROM "Products";, you should see the updated Products table.

We now have our products and categories table, let's look at how to make requests and get responses using Express.js.

We already have our dependencies install. If you haven't yet, do it now:

yarn add cors express  helmet morgan && yarn add -D dotenv nodemon

Adding the controllers.

Create a new directory named controllers and create three files: category.js, index.js and product.js

touch category.js index.js product.js

Open category.js in the controllers folder and paste the code below.

const { Category, Product } = require('../models');

/**
 * Creates a new category
 * @param {*} req
 * @param {*} res
 * @returns Object
 */
const createCategory = async (req, res) => {
  try {
    const category = await Category.create(req.body);
    return res.status(201).json({
      category,
    });
  } catch (error) {
    return res.status(500).json({ error: error.message });
  }
};

/**
 * Fetches all categories
 * @param {*} req
 * @param {*} res
 * @returns Object
 */
const getAllCategories = async (req, res) => {
  try {
    const categories = await Category.findAll({
      order: [['createdAt', 'DESC']],
    });
    return res.status(200).json({ categories });
  } catch (error) {
    return res.status(500).send(error.message);
  }
};

/**
 * Gets a single category by it's id
 * @param {*} req
 * @param {*} res
 * @returns boolean
 */
const getCategoryById = async (req, res) => {
  try {
    const { id } = req.params;

    const category = await Category.findOne({
      where: { id: id },
      order: [['createdAt', 'DESC']],
    });

    if (category) {
      return res.status(200).json({ category });
    }

    return res
      .status(404)
      .send('Category with the specified ID does not exist');
  } catch (error) {
    return res.status(500).send(error.message);
  }
};

/**
 * Updates a single category by it's id
 * @param {*} req
 * @param {*} res
 * @returns boolean
 */
const updateCategory = async (req, res) => {
  try {
    const { id } = req.params;
    const [updated] = await Category.update(req.body, { where: { id: id } });

    if (updated) {
      const updatedCategory = await Category.findOne({
        where: { id: id },
        include: [
          {
            model: Product,
          },
        ],
      });
      return res.status(200).json({ category: updatedCategory });
    }

    throw new Error('Category not found ');
  } catch (error) {
    return res.status(500).send(error.message);
  }
};

/**
 * Deletes a single category by it's id
 * @param {*} req
 * @param {*} res
 * @returns Boolean
 */
const deleteCategory = async (req, res) => {
  try {
    const { id } = req.params;
    const deleted = await Category.destroy({
      where: {
        id: id,
      },
    });

    if (deleted) {
      return res.status(204).send('Category deleted');
    }

    throw new Error('Category not found ');
  } catch (error) {
    return res.status(500).send(error.message);
  }
};

module.exports = {
  createCategory,
  getAllCategories,
  getCategoryById,
  updateCategory,
  deleteCategory,
};

Now go to the product.js file and paste the following code:

const { Product } = require('../models');

/**
 * Creates a new product
 * @param {*} req
 * @param {*} res
 * @returns Object
 */
const createProduct = async (req, res) => {
  try {
    const product = await Product.create(req.body);

    return res.status(201).json({
      product,
    });
  } catch (error) {
    return res.status(500).json({ error: error.message });
  }
};

/**
 * Fetches all products
 * @param {*} req
 * @param {*} res
 * @returns Object
 */
const getAllProducts = async (req, res) => {
  try {
    const products = await Product.findAll({ order: [['createdAt', 'DESC']] });

    return res.status(200).json({ products });
  } catch (error) {
    return res.status(500).send(error.message);
  }
};

/**
 * Gets a single product by it's id
 * @param {*} req
 * @param {*} res
 * @returns boolean
 */
const getProductById = async (req, res) => {
  try {
    const { id } = req.params;
    const product = await Product.findOne({
      where: { id: id },
    });

    if (product) {
      return res.status(200).json({ product });
    }

    return res.status(404).send('Product with the specified ID does not exist');
  } catch (error) {
    return res.status(500).send(error.message);
  }
};
/**
 * Updates a single product by it's id
 * @param {*} req
 * @param {*} res
 * @returns boolean
 */
const updateProductById = async (req, res) => {
  try {
    const { id } = req.params;
    const product = await Product.update(req.body, {
      where: { id: id },
    });

    if (product) {
      const updatedProduct = await Product.findOne({ where: { id: id } });
      return res.status(200).json({ product: updatedProduct });
    }
    throw new Error('product not found');
  } catch (error) {
    return res.status(500).send(error.message);
  }
};

/**
 * Deletes a single product by it's id
 * @param {*} req
 * @param {*} res
 * @returns boolean
 */
const deleteProductById = async (req, res) => {
  try {
    const { id } = req.params;

    const deletedProduct = await Product.destroy({
      where: { id: id },
    });

    if (deletedProduct) {
      return res.status(204).send('Product deleted successfully ');
    }

    throw new Error('Product not found');
  } catch (error) {
    return res.status(500).send(error.message);
  }
};

module.exports = {
  createProduct,
  getAllProducts,
  getProductById,
  deleteProductById,
  updateProductById,
};

Open index.js in your text editor and paste in the following code:

//Exports the entity controllers in a single object

const productController = require('./product');
const categoryController = require('./category');

module.exports = {
  productController,
  categoryController,
};

Great! Well done for reading this far...

We're now going to create routes for the different controller operations (I mean HTTP methods 😉).

Create a new directory and name it routes. Inside the routes directory, create a new file named index.js

mkdir routes
cd routes
touch index.js

Insides the routes/index.js path, paste the following code .

const router = require('express').Router();

// Controller imports
const { categoryController, productController } = require('../controllers');

// Category routes
router.get('/v1/categories', categoryController.getAllCategories);
router.post('/v1/categories', categoryController.createCategory);
router.get('/v1/categories/:id', categoryController.getCategoryById);
router.put('/v1/categories/:id', categoryController.updateCategory);
router.delete('/v1/categories/:id', categoryController.deleteCategory);

// Product routes
router.get('/v1/products', productController.getAllProducts);
router.post('/v1/products', productController.createProduct);
router.get('/v1/products/:id', productController.getProductById);
router.put('/v1/products/:id', productController.updateProductById);
router.delete('/v1/products/:id', productController.deleteProductById);

module.exports = router;

You can now test your end points using whichever client you choose. The popular ones are Insomnia and Postman.
I'm using Postman.

[TO BE CONTINUED]

Top comments (1)

Prof Great Wonder • Feb 20

Great write u. Had to subscribe to dev.to so that I could comment. When is the writeup for the test coming up?

DEV Community

Building an API using Express.js, Postgres, Sequelize CLI and Jest for Unit Testing.

Pre-requisites

What is Sequelize?

Project set up

Before we begin

Defining models

Adding the controllers.

Top comments (1)

Read next

GitHub Sponsors: azu the Japanese TypeScript and JavaScript developer

Answers to Common Next.js Questions

Release of Node.js 22 [Live] What is new?!

JavaScript Histogram of Gaussian Distribution