we will be using Postgres database for this tutorial along with Sequelize. Sequelize is an ORM for Node.js and Relational databases such as MySQL,MSSQL,Postgres and SQLite.
Firstly, postgres and Node.js needs to be installed in your machine. Follow this instruction to install postgres and node.js on your machine.
Once postgres is installed, Start postgres server and you will see a dashboard like following
create a folder called node-sequelize and install express with the following command.
npm init --yes
npm install express body-parser
After that, Install Sequelize and postgres with the following command.
npm install sequelize sequelize-cli pg pg-hstore
sequelize-cli can be installed in two ways. one is globally and another one is locally to the project. Here, we install locally to the project.
Initialize the Sequelize using the following command
node_modules/.bin/sequelize init
Since, we installed sequelize locally to the project, we need to refer by using node_modules/.bin/sequelize
Above command will create a bunch of folders such as config,migrations and models
config.json will contain three environments such as development,test and production. Change the config.json to postgres database in development environment
"development": {
"username": "postgres",
"password": null
"database": "testdb",
"host": "127.0.0.1",
"port" : 5432,
"dialect": "postgres"
}
In models folder, create files such as post.js and user.js and add the following code.
//POST Schema
module.exports = (sequelize,DataTypes) => {
let Post = sequelize.define('Post',{
title : DataTypes.STRING
});
Post.associate = function (models) {
Post.belongsTo(models.User,{
onDelete : "CASCADE",
foreignKey : 'userId'
});
};
return Post;
}
//USER Schema
module.exports = (sequelize,DataTypes) => {
let User = sequelize.define('User',{
email : DataTypes.STRING
});
User.associate = function(models) {
User.hasMany(models.Post,{
foreignKey : 'userId',
as : 'posts'
});
};
return User;
}
import this files in the model index.js
consider a scenario , where user will have lots of posts and a post will belongs to user. In technical terms of database design, it is called as relationships. there are three types of relationship between two entities.they are,
To implement this concepts, we have Assocation in postgres sequelize.
Once you create a models, create files in migrations to create tables in database.
//auser-migration.js
module.exports = {
up: (queryInterface, Sequelize) =>
queryInterface.createTable('Users', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER,
},
email: {
type: Sequelize.STRING,
allowNull: false,
},
createdAt: {
allowNull: false,
type: Sequelize.DATE,
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE,
},
}),
down: (queryInterface /* , Sequelize */) => queryInterface.dropTable('Users'),
};
//create-post.js
module.exports = {
up: (queryInterface, Sequelize) =>
queryInterface.createTable('Posts', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER,
},
title: {
type: Sequelize.STRING,
allowNull: false,
},
userId : {
type : Sequelize.INTEGER,
onDelete : 'CASCADE',
references : {
model : 'Users',
key : 'id',
as : 'userId'
},
},
createdAt: {
allowNull: false,
type: Sequelize.DATE,
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE,
},
}),
down: (queryInterface /* , Sequelize */) => queryInterface.dropTable('Posts'),
};
Run the following command to migrate the tables to database
node_modules/.bin/sequelize db:migrate
As a result,it will create tables in postgres database.
Firstly, create a folder called controller. In controller, create files user.js and post.js . basically, we are going to write API to create User and Post.
//user.js
const User = require('../models').User;
module.exports = {
async getAllUsers(req,res) {
try {
const userCollection = await User.find({});
res.status(201).send(userCollection);
}
catch(e){
console.log(e);
res.status(500).send(e);
}
},
async create(req,res) {
try {
const userCollection = await User
.create({
email : req.body.email,
});
res.status(201).send(userCollection);
}
catch(e){
console.log(e);
res.status(400).send(e);
}
},
async update(req,res) {
try{
const userCollection = await User.find({
id : req.params.userId
});
if(userCollection){
const updatedUser = await User.update({
id : req.body.email
});
res.status(201).send(updatedUser)
}
else{
res.status(404).send("User Not Found");
}
}
catch(e){
console.log(e);
res.status(500).send(e);
}
}
}
//post.js
const Post = require('../models').Post;
const User = require('../models').User;
module.exports = {
async getAllPostsOfUser(req,res) {
try {
const userCollection = await User.find({
id : req.params.userId
});
if(userCollection){
const postCollection = await Post.find({
userId : req.params.userId
})
res.status(201).send(postCollection);
}
else{
re.status(404).send("User Not Found")
}
}
catch(e){
console.log(e);
res.status(500).send(e);
}
},
async createPost(req,res) {
try {
const post = await Post.create({
title : req.body.title,
userId : req.body.userId
});
res.status(201).send(post)
}
catch(e){
console.log(e);
res.status(400).send(e);
}
},
async update(req,res) {
try{
const postCollection = await Post.find({
id : req.params.postId
});
if(postCollection){
const updatedPost = await postCollection.update({
title : req.body.title
})
res.status(201).send(updatedPost);
}
else{
res.status(404).send("Post Not Found");
}
}
catch(e){
console.log(e);
res.status(400).send(e);
}
}
}
In addition,import post.js and user.js in the controller index file.
create a folder called router and add the following code in index.js
//index.js
const userController = require('../controller').user;
const postController = require('../controller').post;
module.exports = (app) => {
app.get('/api',(req,res) => {
res.status(200).send({
data : "Welcome Node Sequlize API v1"
})
})
app.get('/api/users',userController.getAllUsers);
app.post('/api/user/create',userController.create);
app.put('/api/user/:userId',userController.update);
app.get('/api/:userId/posts',postController.getAllPostsOfUser);
app.post('/api/post/create',postController.createPost);
app.put('/api/:postId',postController.update);
}
Finally, index.js file will look like
const express = require('express');
const bodyParser = require('body-parser');
const app = express();
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended : false}));
require('./server/routes')(app);
const PORT = 3456;
app.listen(PORT,() => {
console.log(`Server is listening to port ${PORT}`)
})
Run the Application using the command
Complete Source can be found here
To sum up, we can Build REST API in Node/Express Application using Sequelize for Postgres database.
Originally published by GANESHMANI009 *** ***at cloudnweb.dev
===========================================
Thanks for reading :heart: If you liked this post, share it with all of your programming buddies! Follow me on Facebook | Twitter
Creating a REST API with Node, Express and MongoDB
Build a REST API to manage users and roles using Firebase and Node.js
Build a REST API to manage users and roles using Firebase and Node.js
Creating your first Node.js REST API with Nest and Typescript
#node-js #rest #express #web-development