Hello! In this tutorial, we will learn how to Sequelize ORM in Node.js Application. More specifically, we will build a RESTful application in a Node.js environment running on an Express.js server and PostgreSQL database to perform the CURD operations. We will also use the promise-based ORM dependency known as Sequelize to create the data model.
1. Introduction
RESTful API stands for the standard web service interface used by the applications to communicate with each other. This API conforms to the REST architectural style and constraints. It is scalable, stateless, cacheable, and has a uniform interface. It utilizes HTTP requests and the four most common HTTP methods are POST, PUT, GET, and DELETE. Express.js on the other hand is the most popular Node.js web framework that provides a robust set of features to develop web and mobile applications. It offers features like –
- Set up middleware to respond to HTTP requests
- Defines the routing table to perform different actions based on HTTP methods
- Allows to render HTML pages dynamically
1.1 Setting up Node.js
To set up Node.js on windows you will need to download the installer from this link. Click on the installer (also include the NPM package manager) for your platform and run the installer to start with the Node.js setup wizard. Follow the wizard steps and click on Finish when it is done. If everything goes well you can navigate to the command prompt to verify if the installation was successful as shown in Fig. 1.
1.2 Setting up PostgreSQL server
To start with the tutorial, I am hoping that you have the PostgreSQL up and running in your localhost environment. For easy setup, I have the server up and running on the docker environment. You can execute the below command to get the container running on docker in minutes. Make note postgresql server docker command –
- Will contain the
postgresuser password - Will automatically create the
fakedatabasepost successful startup of the container
Docker commands
-- run the postgresql container – docker run -d -p 5433:5432 -e POSTGRES_PASSWORD=password -e POSTGRES_DB=fakedatabase --name postgres postgres
If everything goes well the container would be started successfully as shown in Fig. 2. You can use the docker ps -a command to confirm the status. For further information on docker basics, you can navigate to this tutorial.
2. Sequelize ORM in Node.js Application
At this point, we have successfully created the initial database required for our application. To set up the Node.js app, Express.js server, and Sequelize ORM model, we will need to navigate to a path where our project will reside. For programming stuff, I am using Visual Studio Code as my preferred IDE. You’re free to choose the IDE of your choice.
2.1 Setting up dependencies
Navigate to the project directory and run npm init -y to create a package.json file. This file holds the metadata relevant to the project and is used for managing the project dependencies, script, version, etc. Add the following code to the file wherein we will specify the required dependencies.
package.json
{
"name": "sequelizeorm-express",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1",
"start": "node index.js"
},
"keywords": [],
"author": "",
"license": "ISC",
"dependencies": {
"body-parser": "^1.19.0",
"express": "^4.17.1",
"pg": "^8.6.0",
"pg-hstore": "^2.3.3",
"sequelize": "^6.6.2",
"underscore": "^1.13.1"
},
"devDependencies": {
"nodemon": "^2.0.7"
}
}
To download the dependencies navigate to the directory path containing the file and use the npm install command. If everything goes well the dependencies will be loaded inside the node_modules folder and you are good to go with the further steps.
2.2 Creating a configuration file
Create a db seed file in the config folder that will be used to create a connection to the database with the help of the Sequelize dependency. You are free to change these details as per your application or database configuration setup.
database.js
const {Sequelize} = require('sequelize');
// TODO
// 1. export the Database variables from a config file
// 2. create a new user. in the production environment never use the default user
module.exports = new Sequelize('INIT_DB', 'DB_USER', 'DB_PWD', {
host: 'localhost',
port: 5433,
dialect: 'postgres',
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000
}
});
2.3 Creating a model file
Create a model file in the models folder that will be used to create the table (playlists) and columns (id, name, and deleted) in the database (fakedatabase) with the help of the Sequelize dependency on application startup. You are free to change these details as per your requirement.
playlists.js
const Sequelize = require('sequelize');
const db = require('../config/database');
const Playlists = db.define('playlists',
{
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true
},
name: {
type: Sequelize.STRING
},
deleted: {
type: Sequelize.BOOLEAN,
allowNull: false
}
},
{
timestamps: false // Disable timestamp attributes (createdAt, updatedAt)
});
module.exports = Playlists;
2.4 Creating the controller file
Create a controller file in the routes folder. This file will expose endpoints that will be responsible to perform the CRUD operations with the help of the model object (Playlist).
api.js
const express = require('express');
const router = express.Router();
const Playlist = require('../models/playlists');
const _ = require('underscore');
// create and save a new playlist
// http://localhost:4001/create
// note - provide the json request body
/*
{
"name": "golden sounds",
"deleted": "true"
}
*/
router.post('/create', (req, res) => {
if (_.isEmpty(req.body.name)) {
res.status(400).json({info: 'Name cannot be null'});
}
const obj = {
name: req.body.name,
deleted: req.body.deleted || false
};
// save in db
Playlist.create(obj)
.then(data => {
// console.log(data);
res.status(201).json({info: `Entity ${data.id} created successfully`});
})
.catch((err) => console.log('Error: ' + err)); // TODO - Replace with a error response
});
// get all playlists | [where condition]
// http://localhost:4001/findAll
// or
// http://localhost:4001/findAll&deleted=false
router.get('/findAll', (req, res) => {
const condition = _.isEmpty(req.query.deleted) ? {} : {where: {deleted: req.query.deleted}};
Playlist.findAll(condition)
.then(data => {
// console.log(data);
res.status(200).json({info: data});
})
.catch((err) => console.log('Error: ' + err)); // TODO - Replace with a error response
});
// get a single playlist
// http://localhost:4001/findById&id=1
router.get('/findById', (req, res) => {
const playlistId = req.query.id;
if (_.isEmpty(playlistId)) {
res.status(400).json({info: 'Id cannot be null'});
}
Playlist.findByPk(playlistId)
.then(data => {
// console.log(data);
res.status(200).json({info: data});
})
.catch((err) => console.log('Error: ' + err)); // TODO - Replace with a error response
});
// other HTTP methods like PUT, DELETE are skipped for brevity.
// you can add them on your own.
module.exports = router;
Similarly, you can also create another controller file for the application health check endpoints (such as health.js).
2.5 Creating an index file
Create an index file that will act as an entry point for our server. The file will contain the code –
- To test the database connectivity on application startup
- Set up the table and columns on application startup with the help of the model object
syncmethod - Define routes to the application endpoints
index.js
// Database
const db = require('./config/database');
// DAO model
const Playlist = require('./models/playlists');
// Test DB connection
db.authenticate()
.then(() => console.log('Database is connected'))
.catch((err) => console.log('Error: ' + err));
// Automatically creating table on application startup
Playlist.sync({force: true}).then(() => {
console.log("Drop and re-sync table");
});
const express = require('express');
const bodyParser = require('body-parser');
const app = express();
// Parse requests of Content-Type - application/json
app.use(bodyParser.json());
// Application routes
app.use('/', require('./routes/health'));
app.use('/api', require('./routes/api'));
const PORT = process.env.port || 4001;
app.listen(PORT, () => {
console.log(`Server started on port ${PORT}`);
});
3. Run the Application
To run the application navigate to the project directory and enter the following command as shown in Fig. 4. If everything goes well the application will be started successfully on port number 4001 and the Sequelize dependency will automatically create the table and the columns.
4. Project Demo
When the application is started, open the Postman tool to hit the application endpoints. You are free to choose any tool of your choice.
Application endpoints
// To determine application health status
HTTP GET url - http://localhost:4001/
// CRUD endpoints
// create a new playlist
HTTP POST url - http://localhost:4001/create
// note - provide the json request body
// {
// "name": "golden sounds",
// "deleted": "true"
// }
// get all playlists
HTTP GET url - http://localhost:4001/findAll
// get all playlists based on the deleted flag
HTTP GET url - http://localhost:4001/findAll&deleted=false
// get a single playlist
HTTP GET url - http://localhost:4001/findById&id=1
Similarly, you can create other endpoints. That is all for this tutorial and I hope the article served you with whatever you were looking for. Happy Learning and do not forget to share!
5. Summary
In this tutorial, we learned:
- Introduction to RESTful API and Express.js
- Steps to setup Node.js and start PostgreSQL server using Docker
- Sample programming stuff to perform CRUD operations using RESTful endpoints via Express.js and Sequelize dependency
You can download the source code of this tutorial from the Downloads section.
6. Download the Project
This was a programming tutorial on Sequelize ORM in the node.js application.
You can download the full source code of this example here: Sequelize ORM in Node.js Application
