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:
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:
-> 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:
– Get requests:
– Put request:
– Delete request:
-> 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:
Sourcecode
Nodejs-Express-Sequelizejs-PostgreSQL
