DEV Community

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

Posted on • Updated on

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

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


Enter fullscreen mode Exit fullscreen mode
  • 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


Enter fullscreen mode Exit fullscreen mode

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



/node_modules
.env


Enter fullscreen mode Exit fullscreen mode

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):


yarn sequelize-cli init
code .


Enter fullscreen mode Exit fullscreen mode
  1. 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
      }
    }
  }
}


Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode

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');
  },
};


Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode

Let's generate the seed file for the Product model



yarn sequelize-cli seed:generate --name product


Enter fullscreen mode Exit fullscreen mode

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, {});
  },
};


Enter fullscreen mode Exit fullscreen mode

That's all for the Products model for now.

Create the Category model



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


Enter fullscreen mode Exit fullscreen mode

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');
  },
};


Enter fullscreen mode Exit fullscreen mode

Let's create a seeder file for the Category model



yarn sequelize-cli seed:generate --name category


Enter fullscreen mode Exit fullscreen mode

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, {});
  },
};


Enter fullscreen mode Exit fullscreen mode

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;
};


Enter fullscreen mode Exit fullscreen mode

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;
};


Enter fullscreen mode Exit fullscreen mode

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'
        }
      },


Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode

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

image of the psql shell.

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


Enter fullscreen mode Exit fullscreen mode
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


Enter fullscreen mode Exit fullscreen mode

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,
};


Enter fullscreen mode Exit fullscreen mode

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,
};


Enter fullscreen mode Exit fullscreen mode

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,
};


Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode

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;


Enter fullscreen mode Exit fullscreen mode

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]

Edit (2024): I no longer write Node.js and Javascript as ky main language and neither do I have the time to write technical articles anymore. This will probably never be continued.

My sincere apologies to whoever comes across it.

Top comments (2)

Collapse
 
profgreat_wonder_c8b18f1 profile image
Prof Great Wonder

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

Collapse
 
luigimorel profile image
Luigi Morel

I honestly no longer have the time to write up stuff. Furthermore, Node and JS aren't my main stack at the moment.