Building an API using Express.js, Postgres, Sequelize CLI and Jest for Unit Testing.
Tags: express, webdev, javascript
Published: 2022-04-13 16:06:14

---
title: Building an API using Express.js, Postgres, Sequelize CLI and Jest for Unit Testing. 
published: true
description:
tags: express, webdev, Javascript
cover_image: https://res.cloudinary.com/morelmiles/image/upload/v1649756626/cover_pfmhkn.png
---

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:

1. What is Sequelize?
2. Installing Sequelize CLI
3. Creating entity models using the CLI
4. Writing unit tests using Jest
5. Deployment to Heroku
6. Creating associations using Postgres and Sequelize CLI
7. 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](https://sequelize.org/) is an easy-to-use JavaScript object relational mapping ([ORM](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping)) 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.

```bash
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](https://nodejs.org/en/about/) [ORM tool](https://en.wikipedia.org/wiki/Object-relational_mapping) for [Postgres](https://en.wikipedia.org/wiki/PostgreSQL).
  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](http://www.senchalabs.org/connect/)/[Express](http://expressjs.com/) middleware that can be used to enable [CORS](http://en.wikipedia.org/wiki/Cross-origin_resource_sharing) 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](https://sequelize.org) 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`](https://nodejs.org/docs/latest/api/process.html#process_process_env)

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

```bash
touch .gitignore .env
```

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

```bash
/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. 🤩

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

```bash
yarn sequelize-cli init
code .
```

2. 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:

```json
{
  "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:

```bash
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.**

```bash
$ 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.

```js
'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](https://www.postgresql.org/docs/current/uuid-ossp.html).

>[Here](https://pcx3.com/db/postgresql/how-to-add-uuid-ossp-extension-to-postgresql/) 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.

```bash
yarn sequelize-cli db:migrate
```

Let's generate the seed file for the `Product` model

```bash
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.

```js
'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**

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

Below is the migration file for the `Category` model.

```js
'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

```bash
yarn sequelize-cli seed:generate --name category
```

Below is the file with the seeder entries.

```js
'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](https://sequelize.org/docs/v6/core-concepts/assocs/)

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

```js
'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:

```js
'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`:

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

```bash
yarn db:reset
```

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

![image of the psql shell.](https://res.cloudinary.com/morelmiles/image/upload/v1649866234/Screenshot_2022-04-13_191013_kals5y.jpg)

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:

```bash
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`

```bash
touch category.js index.js product.js
```

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

```js
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:

```js
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:

```js
//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`

```bash
mkdir routes
cd routes
touch index.js
```

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

```js
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]