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



By grokonez | April 18, 2018.

Last updated on September 28, 2018.



Related Posts


16 thoughts on “Sequelize ORM – Build CRUD RestAPIs with NodeJs/Express, Sequelize, MySQL”

  1. Great article. I do not understand the part of the routes because in Express we usually import the Route () and use it like this:

    router.get ('/', function (req, res, next) {
      res.send ('respond with a resource');
    });
    

    I also found the structure of server.js a bit different from an Express application, but it works very well.
    Would this be a more modern form of construction?

    1. Hello Julio,

      Please take a look at the overall design structure of Node.js project,

      /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
      

      -> With about organized structure, we create an easy way to develop big Node.js projects, more easy way to extends functions with seperated controller & router files. It also a design to reduce many codes for server.js.

      With mini project, you can develop restAPIs right in server.js -> BUT not good for real development.

      Here is an option for your development.

      Thanks & Regards,
      JSA

  2. COOL GREAT, I WISH I COULD DONATE SOMETHING.

    However, with this Api, will a user be authenticated with a token before they can Post or Create something on the website ?

    For instance, if you have a website where user are authenticated before doing something, will this api apply or they are separately done? I wish we could talk more.

  3. Thank you so so much! You have helped me a lot.
    One question sir, how can I associate multiple models with this kind of setup?
    I’ve only seen examples of models being associated when they are on the same file.
    Please help me, sir. I’m totally lost.

  4. could you explain me this part i didn’t get it from database config file

    const db = {};
     
    db.Sequelize = Sequelize;
    db.sequelize = sequelize;
     
    //Models/tables
    db.customers = require('../model/customer.model.js')(sequelize, Sequelize);
    
  5. Hi, Great Job
    When doing a post for http://localhost:8081/api/customers
    the firstname, lastname and age is not inserting into the database please can anyone help

    Executing (default): INSERT INTO `customers` (`id`,`createdAt`,`updatedAt`) VALUES (DEFAULT,’2019-03-25 01:43:05′,’2019-03-25 01:43:05′);

    Please any help?

    Thanks

Got Something To Say:

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

*