Sequelize ORM – Build CRUD RestAPIs with NodeJs/Express, Sequelize, MySQL

sequelize-orm-build-crud-restapis-with-nodejs-express-sequelize-mysql-feature-image

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

– Related posts:
Sequelize One-To-One association – NodeJS/Express, MySQL

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.

Getting started with MySQL:

Installation

$ npm install --save sequelize
$ npm install --save mysql2

Set up a 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.send(customers);
});

– Find By ID:

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

– Update a Customer:

const id = customerId;
Customer.update( { firstname: firstname, lastname: lastname, age: age }, 
				 { where: {id: customerId} }
			   ).then(() => {
				 res.status(200).send("updated successfully a customer with id = " + id);
			   });

– Delete a Customer:

Customer.destroy({
  where: { id: id }
}).then(() => {
  res.status(200).send('deleted successfully a customer with id = ' + id);
});

Practice

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

/nodejs-express-sequelizejs-mysql
	/app
		/config
			db.config.js
			env.js
		/controller
			customer.controller.js
		/model
			customer.model.js
		/route
			customer.route.js
	/node_modules
	package.json
	server.js

Setting up NodeJs/Express project

Following the guide to create a NodeJS/Express project.

Install Sequelize with MySQL:

$npm install sequelize mysql2 --save

-> package.json file:

{
  "name": "nodejs-express-sequelizejs-mysql",
  "version": "1.0.0",
  "description": "nodejs-express-sequelizejs-mysql",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "NodeJs-Express-SequelizeJs-MySQL"
  ],
  "author": "JSA",
  "license": "ISC",
  "dependencies": {
    "express": "^4.16.3",
    "mysql2": "^1.5.3",
    "sequelize": "^4.37.6"
  }
}

Setting up Sequelize MySQL connection

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

const env = {
  database: 'testdb',
  username: 'root',
  password: '12345',
  host: 'localhost',
  dialect: 'mysql',
  pool: {
	  max: 5,
	  min: 0,
	  acquire: 30000,
	  idle: 10000
  }
};

module.exports = env;

– Setup Sequelize-MySQL 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/:customerId', customers.findById);
 
    // Update a Customer with Id
    app.put('/api/customers/:customerId', customers.update);
 
    // Delete a Customer with Id
    app.delete('/api/customers/:customerId', 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 MySQL database
	Customer.create({  
	  firstname: req.body.firstname,
	  lastname: req.body.lastname,
	  age: req.body.age
	}).then(customer => {		
		// Send created customer to client
		res.send(customer);
	});
};
 
// FETCH all Customers
exports.findAll = (req, res) => {
	Customer.findAll().then(customers => {
	  // Send all customers to Client
	  res.send(customers);
	});
};

// Find a Customer by Id
exports.findById = (req, res) => {	
	Customer.findById(req.params.customerId).then(customer => {
		res.send(customer);
	})
};
 
// Update a Customer
exports.update = (req, res) => {
	const id = req.params.customerId;
	Customer.update( { firstname: req.body.firstname, lastname: req.body.lastname, age: req.body.age }, 
					 { where: {id: req.params.customerId} }
				   ).then(() => {
					 res.status(200).send("updated successfully a customer with id = " + id);
				   });
};
 
// Delete a Customer by Id
exports.delete = (req, res) => {
	const id = req.params.customerId;
	Customer.destroy({
	  where: { id: id }
	}).then(() => {
	  res.status(200).send('deleted successfully a customer with id = ' + id);
	});
};

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 NodeJs server:

nodejs-express-sequelizejs-mysql>node server.js
App listening at http://:::8081
Executing (default): DROP TABLE IF EXISTS `customers`;
Executing (default): DROP TABLE IF EXISTS `customers`;
Executing (default): CREATE TABLE IF NOT EXISTS `customers` (`id` INTEGER NOT NULL auto_increment , `firstname` VARCHAR(255), `lastname` VARCHAR(255), `age` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `customers`
Drop and Resync with { force: true }

Use Postman to check result ->

– POST customers:

nodejs-express-restapis-sequelize-crud-post

nodejs-express-restapis-sequelize-crud-record-in-database

– GET all customers:

nodejs-express-restapis-sequelize-crud-get-all-customers

– GET a customer by id:

nodejs-express-restapis-sequelize-crud-get-a-customer-by-id

– PUT a customer:

nodejs-express-restapis-sequelize-crud-put-a-customer

– DELETE a customer by Id:

nodejs-express-restapis-sequelize-crud-delete-a-customer-with-id

Log from Server:

Executing (default): INSERT INTO `customers` (`id`,`firstname`,`lastname`,`age`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Jack','Davis',25,'2018-04-16 15:09:15','2018-04-16 15:09:15');
Executing (default): INSERT INTO `customers` (`id`,`firstname`,`lastname`,`age`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Mary','Taylor',37,'2018-04-16 15:09:19','2018-04-16 15:09:19');
Executing (default): INSERT INTO `customers` (`id`,`firstname`,`lastname`,`age`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Adam','Johnson',48,'2018-04-16 15:09:23','2018-04-16 15:09:23');
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 `firstname`='Jack',`lastname`='Davis',`age`=47,`updatedAt`='2018-04-16 15:14:03' WHERE `id` = '1'
Executing (default): DELETE FROM `customers` WHERE `id` = '2'

– Check customer table:

nodejs-express-restapis-sequelize-crud-check-database-customer-table

Sourcecode

Nodejs-Express-Sequelizejs-Mysql

0 0 votes
Article Rating
Subscribe
Notify of
guest
1.4K Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments