We had many posts on API and I am moving on sequelize ORM which allows us to perform CURD operation in REST manner or using Modals, just like we done with mongoose.
Sequelize
Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.
PostgreSQL
Postgres is an opensource DBMS, it is free and perform faster results. It come with pg-admin web interface which allows navigate through database. You can download and install it from the official website
Todo API
Our API is used to create the todos ,store in pg (PostgreSQL) , and perform all CURD operations using sequelize modal.
First we need to create the above table in your pg. Now lets create a basic Nodejs project.
md sequelize-api cd sequelize-api npm init -y npm i sequelize express body-parser pg pg-hstore dotenv --save npm i -D nodemon
Our project structure look like above. So we need to create
- package.json
- .dotenv file
- configuration file
- modals
- main indexjs
Package.json
The package file of the project look like the following , also we need a start script for running the project.
{
"name": "sequelize-api",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1",
"start": "nodemon index.js"
},
"keywords": [],
"author": "",
"license": "ISC",
"dependencies": {
"body-parser": "^1.19.0",
"dotenv": "^8.2.0",
"express": "^4.17.1",
"pg": "^8.5.1",
"pg-hstore": "^2.3.3",
"sequelize": "^6.3.5"
},
"devDependencies": {
"nodemon": "^2.0.7"
}
}
Configuration
Under config folder create database.js with following content in it.
const Sequelize=require('sequelize')
module.exports=new Sequelize('todoDb','postgres','123',{
host:'localhost',
port:4000,
dialect:'postgres',
pool:{
max:5,
min:0,
acquire:30000,
idle:10000
}
})
In the above code we imported the sequelize and create basic configuration. Here todoDB is our database, postgres is the user and 123 is password ( we have already created pg database). Remember to correctly configure the port , which also configured while install Postgres.
Dotenv
dotenv is a npm module, which help us to keep configurations in seperate files and access it through process.env. Our .env file includes the port number. We can place Database URI, user,password and other configurations for our Node project
PORT=3004
Modal
Next thing we need to create the modal (Model/todo.model.js) ,which is a minimal modal for out ToDos.
const Sequelize=require('Sequelize')
const db = require('../config/database')
module.exports= db.define('todos',{
item:{type:Sequelize.STRING,allowNull:false},
description:{type:Sequelize.STRING}
})
In the modal we need to use the configuration , so we import it as db and create modal todo with two fields.
Our database had three more columns id, CreatedAt, UpdatedAt. The first one is auto generated field and rest is filled when adding or updating records.
Routes and index.js
We can implement routes in separate file, it help us to organize them in systematic manner. For simplicity I placed them along with index.js ( index is the starting point of our project which is defined pacakge.json)
const express = require("express");
const bodyParser = require("body-parser");
const db = require("./config/database");
require("dotenv").config();
const Todo = require("./models/todo.model");
const app = express();
app.use(bodyParser.urlencoded({extended:false}))
//Test DB
db.authenticate()
.then(() => console.log("Database connected"))
.catch((e) => console.log("Error:" + e));
app.get("/", (req, res) => {
res.send("Hello world");
});
app.get("/todo", (req, res, next) => {
Todo.findAll()
.then((model) => {
res.json({
error: false,
data: model
})
})
.catch(error => res.json({
error: true,
data: [],
error: error
}))
});
app.get("/todo/:id", (req, res, next) => {
const id =req.params.id
Todo.findByPk(id)
.then((model) => {
res.json({
error: false,
data: model
})
})
.catch(error => res.json({
error: true,
data: [],
error: error
}))
});
app.put('/todo/update/:id',bodyParser.json(),(req, res,next)=>{
const id =req.params.id
const {item,description}=req.body
Todo.update({item:item},{where:{id:id}})
.then((uTodo) => {
console.log('got item and updated ' + uTodo);
res.json({
error: false,
data: uTodo
})
})
.catch(error => res.json({
error: true,
data: [],
error: error
}))
})
app.delete('/todo/delete/:id' ,(req, res,next)=>{
const id =req.params.id
Todo.destroy({where:{id:id}})
.then((dTodo) => {
console.log('got item and deleted ' + dTodo);
res.json({
error: false,
data: dTodo
})
})
.catch(error => res.json({
error: true,
data: [],
error: error
}))
})
app.post("/todo", bodyParser.json(),(req, res, next) => {
const {item,description}=req.body
Todo.create({item:item,description:description})
.then((model) => {
res.status(200).send(model);
})
.catch((e) => {
res.status(400).send("Error:" + e);
});
});
const PORT = process.env.PORT || 5000;
app.listen(PORT, () => console.log(`server running on PORT ${PORT}`));
In the index.js, we first want to make sure the DB is working using authenticate method. Then we have the routes for the following
- Add new todo – get request
- Delete todo = delete request
- get-todo – using id
- get all todos – get request
- update todo – put request
Routes specific use of JSON parsing
You may note that , we are used route specific body parsing. This is because we need body-parsing for two routes, update,save/add.
The global parsing will end up the JSON error in the route get (‘/todo/’) which fetch the items. So to avoid such error we isolate JSON parser.
Note that all the CURD operations are done using modal , that is the beauty of the REST.
That is all you need to know. The complete source code of this project can be found at https://github.com/manojap/Express-Node-RSET-API-Postgres
You may like to read these API posts
- How to add data on MongoDB using Node-Express API - How to use Nodejs-Express API to perform CURD operations on MongoDB
- What is an API and how to create an API ? - What is an API, what is the need of an API. Learn to build and use API in web applications and non web applications
- How to create a REST API using Mongodb and Express in Nodejs - How to create a REST API using Express and MongoDB in Nodejs
- How to create an API using Express in Nodejs - How to create a simple API using Express in Nodejs
