Node.js/Express RestAPIs CRUD – Sequelize ORM – PostgreSQL

Sequelize is a promise-based ORM for Node.js v4 and later. In the tutorial, we will show how to build Node.js/Express RestAPIs to interact with PostgreSQL using Sequelize CRUD.

Sequelize ORM

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, SQLite and Microsoft SQL Server. It has many solid features for transaction, relations, read replication and more.

Now getting started with PostgreSQL ->

Installation
$ npm install --save sequelize
$ npm install --save pg pg-hstore
Set up Connection
const Sequelize = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'host',
  dialect: 'dialect',
  operatorsAliases: false,
 
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
});
Sequelize Model
module.exports = (sequelize, Sequelize) => {
	const Customer = sequelize.define('customer', {
	  firstname: {
		type: Sequelize.STRING
	  },
	  lastname: {
		type: Sequelize.STRING
	  },
	  age: {
		  type: Sequelize.INTEGER
	  }
	});
	
	return Customer;
}
Sequelize Queries

– Find All:

Customer.findAll().then(customers => {
  // Send all customers to Client
  res.json(customers);
});

– Find by ID:

Customer.findById(customerId).then(customer => {
	res.json(customer);
})

– Update a Customer:

const id = customerId;
Customer.update( { firstname: firstname, lastname: lastname, age: age }, 
				 { where: {id: customerId} }
			   ).then(() => {
				 res.status(200).json({msg: "Updated Successfully -> Customer Id: " + id});
			   });

– Delete a Customer:

Customer.destroy({
  where: { id: id }
}).then(() => {
  res.status(200).json({msg: 'Deleted Successfully -> Customer Id: ' + id});
});

Practice

We create a NodeJS/Express-Sequelize project as below structure:

nodejs-express-restapis-sequelize-postgresql + project structure

Setting up NodeJs/Express project

Following the guide to create a NodeJS/Express project.

Install Sequelize with PostgreSQL:

$npm install sequelize pg pg-hstore --save

-> package.json file:

{
  "name": "nodejs-express-sequelizejs-postgresql",
  "version": "1.0.0",
  "description": "nodejs-express-sequelizejs-postgresql",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "NodeJs-Express-RestAPIs-SequelizeJs-CRUD-PostgreSQL"
  ],
  "author": "JSA",
  "license": "ISC",
  "dependencies": {
    "express": "^4.16.3",
    "pg": "^7.4.3",
    "pg-hstore": "^2.3.2",
    "sequelize": "^4.37.6"
  }
}

Setting up Sequelize PostgreSQL connection

– Create ./app/config/env.js file:

const env = {
  database: 'test',
  username: 'postgres',
  password: '123',
  host: 'localhost',
  dialect: 'postgres',
  pool: {
	  max: 5,
	  min: 0,
	  acquire: 30000,
	  idle: 10000
  }
};

module.exports = env;

– Setup Sequelize-PostgreSQL connection in ./app/config/db.config.js file:

const env = require('./env.js');

const Sequelize = require('sequelize');
const sequelize = new Sequelize(env.database, env.username, env.password, {
  host: env.host,
  dialect: env.dialect,
  operatorsAliases: false,

  pool: {
    max: env.max,
    min: env.pool.min,
    acquire: env.pool.acquire,
    idle: env.pool.idle
  }
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

//Models/tables
db.customers = require('../model/customer.model.js')(sequelize, Sequelize);

module.exports = db;
Create Sequelize model
module.exports = (sequelize, Sequelize) => {
	const Customer = sequelize.define('customer', {
	  firstname: {
			type: Sequelize.STRING
	  },
	  lastname: {
			type: Sequelize.STRING
	  },
	  age: {
		  type: Sequelize.INTEGER
	  }
	});
	
	return Customer;
}
Express RestAPIs
Route

Define Customer’s routes in ./app/controller/customer.route.js file:

module.exports = function(app) {
    const customers = require('../controller/customer.controller.js');
 
    // Create a new Customer
    app.post('/api/customers', customers.create);
 
    // Retrieve all Customer
    app.get('/api/customers', customers.findAll);
 
    // Retrieve a single Customer by Id
    app.get('/api/customers/:id', customers.findById);
 
    // Update a Customer with Id
    app.put('/api/customers', customers.update);
 
    // Delete a Customer with Id
    app.delete('/api/customers/:id', customers.delete);
}
Controller

Implement Customer’s controller in ./app/controller/customer.controller.js file:

const db = require('../config/db.config.js');
const Customer = db.customers;

// Post a Customer
exports.create = (req, res) => {	
	// Save to PostgreSQL database
	Customer.create(req.body).then(customer => {		
			// Send created customer to client
			res.json(customer);
		}).catch(err => {
			console.log(err);
			res.status(500).json({msg: "error", details: err});
		});
};
 
// FETCH All Customers
exports.findAll = (req, res) => {
	Customer.findAll().then(customers => {
			// Send All Customers to Client
			res.json(customers);
		}).catch(err => {
			console.log(err);
			res.status(500).json({msg: "error", details: err});
		});
};

// Find a Customer by Id
exports.findById = (req, res) => {	
	Customer.findById(req.params.id).then(customer => {
			res.json(customer);
		}).catch(err => {
			console.log(err);
			res.status(500).json({msg: "error", details: err});
		});
};
 
// Update a Customer
exports.update = (req, res) => {
	const id = req.body.id;
	Customer.update( req.body, 
			{ where: {id: id} }).then(() => {
				res.status(200).json( { mgs: "Updated Successfully -> Customer Id = " + id } );
			}).catch(err => {
				console.log(err);
				res.status(500).json({msg: "error", details: err});
			});
};

// Delete a Customer by Id
exports.delete = (req, res) => {
	const id = req.params.id;
	Customer.destroy({
			where: { id: id }
		}).then(() => {
			res.status(200).json( { msg: 'Deleted Successfully -> Customer Id = ' + id } );
		}).catch(err => {
			console.log(err);
			res.status(500).json({msg: "error", details: err});
		});
};
Server.js
var express = require('express');
var app = express();
var bodyParser = require('body-parser');
app.use(bodyParser.json())

const db = require('./app/config/db.config.js');
  
// force: true will drop the table if it already exists
db.sequelize.sync({force: true}).then(() => {
  console.log('Drop and Resync with { force: true }');
});

require('./app/route/customer.route.js')(app);
 
// Create a Server
var server = app.listen(8081, function () {
 
  var host = server.address().address
  var port = server.address().port
 
  console.log("App listening at http://%s:%s", host, port)
})
Run & Check results

Start Node.js server, Logs ->

App listening at http://:::8081
Executing (default): DROP TABLE IF EXISTS "customers" CASCADE;
Executing (default): DROP TABLE IF EXISTS "customers" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "customers" ("id"   SERIAL , "firstname" VARCHAR(255), "lastname" VARCHAR(255), "age" INTEGER, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'customers' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Drop and Resync with { force: true }

Use Postman to check results ->

– Post request:

nodejs-express-restapis-sequelize-postgresql + post-request

-> Sequelize’s Insert Logs:

Executing (default): INSERT INTO "customers" ("id","firstname","lastname","age","createdAt","updatedAt") VALUES (DEFAULT,'Jack','Davis',25,'2018-07-10 09:58:38.699 +00:00','2018-07-10 09:58:38.699 +00:00') RETURNING *;
Executing (default): INSERT INTO "customers" ("id","firstname","lastname","age","createdAt","updatedAt") VALUES (DEFAULT,'Mary','Taylor',37,'2018-07-10 10:00:34.871 +00:00','2018-07-10 10:00:34.871 +00:00') RETURNING *;
Executing (default): INSERT INTO "customers" ("id","firstname","lastname","age","createdAt","updatedAt") VALUES (DEFAULT,'Adam','Johnson',48,'2018-07-10 10:01:07.276 +00:00','2018-07-10 10:01:07.276 +00:00') RETURNING *;

-> PostgreSQL records:

nodejs-express-restapis-sequelize-postgresql + postgresql-records

– Get requests:

nodejs-express-restapis-sequelize-postgresql + get-all-customer

nodejs-express-restapis-sequelize-postgresql + get-a-customer

– Put request:

nodejs-express-restapis-sequelize-postgresql + put-a-customer

– Delete request:

nodejs-express-restapis-sequelize-postgresql + delete-a-customer

-> Sequelize’s select, update, delete Logs:

Executing (default): SELECT "id", "firstname", "lastname", "age", "createdAt", "updatedAt" FROM "customers" AS "customer";
Executing (default): SELECT "id", "firstname", "lastname", "age", "createdAt", "updatedAt" FROM "customers" AS "customer" WHERE "customer"."id" = '1';
Executing (default): UPDATE "customers" SET "id"=1,"firstname"='Michael',"lastname"='Davis',"age"=41,"updatedAt"='2018-07-10 10:08:04.249 +00:00' WHERE "id" = 1
Executing (default): DELETE FROM "customers" WHERE "id" = '2'

-> final PostgreSQL’s records:

nodejs-express-restapis-sequelize-postgresql + final-postgresql-records

Sourcecode

Nodejs-Express-Sequelizejs-PostgreSQL



By grokonez | July 10, 2018.

Last updated on February 6, 2020.



Related Posts


1 thought on “Node.js/Express RestAPIs CRUD – Sequelize ORM – PostgreSQL”

  1. npm install found 2 high severity vulnerabilities

    If I force a fix, it break the application. What does this mean. Is there a fix?

Got Something To Say:

Your email address will not be published. Required fields are marked *

*