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 intoprocess.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 onecategory
. - One
category
has manyproducts
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]
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)
Great write u. Had to subscribe to dev.to so that I could comment. When is the writeup for the test coming up?
I honestly no longer have the time to write up stuff. Furthermore, Node and JS aren't my main stack at the moment.