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 May 5, 2021.



Related Posts


2 thoughts 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?

  2. Hello, thanks for your projects, excellent information.

    I am new to this, I followed the process you explain, however, the project did not work for me.

    Then download the code and just change the password to “admin”. The project is still not working. Install everything from express. In the // FETCH All Customers section I put: console.log (“Check”) just to check if it works, in the Terminal, “Check” appears, but I see that it doesn’t bring anything from the DB.

    What should I install or test for the project to work for me?

    Thanks for your support, Best regards
    Fernando

Got Something To Say:

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

*