Sequelize CRUD MariaDB example | Build CRUD Node.js/Express RestAPIs example

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

Related posts:

Sequelize ORM – Build CRUD RestAPIs with NodeJs/Express, Sequelize, MySQL
Angular 6 HttpClient – Upload Files/Download Files to MySQL with Node.js/Express RestAPIs – using Multer + Sequelize ORM
Angular 6 HttpClient Crud + Node.js Express Sequelize + MySQL – Get/Post/Put/Delete RestAPIs
NodeJS – GridFS APIs Upload/Download Files to MongoDB – Mongoose
Angular 6 Client – Upload Files/Download Files to PostgreSQL with SpringBoot RestAPIs example
Sequelize One-To-One association – NodeJS/Express, MySQL


Related pages:

Overview

Project Design

nodejs-express-rest-apis-post-get-put-delete-request-sequelize-crud-apis-mariadb + architecture-project

Sequelize ORM

Installation


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

Setup 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', {
	  name: {
		type: Sequelize.STRING
	  },
	  age: {
		  type: Sequelize.INTEGER
	  }
	});
	
	return Customer;
}

Sequelize Create/Queries

– Create Object:


Customer.create({  
		name: req.body.name,
		age: req.body.age
	})
	.then(customer => {		
		// Send created customer to client
		res.json(customer);
	})
	.catch(error => res.status(400).send(error))

– Find All:


Customer.findAll({
		attributes: { exclude: ["createdAt", "updatedAt"] }
	})
	.then(customers => {
		res.json(customers);
	})
	.catch(error => res.status(400).send(error))

– Find By ID:


Customer.findById(req.params.customerId,
			{attributes: { exclude: ["createdAt", "updatedAt"] }}
		)
		.then(customer => {
				if (!customer){
					return res.status(404).json({message: "Customer Not Found"})
				}
				return res.status(200).json(customer)
			}
		)
		.catch(error => res.status(400).send(error));

– Update a Customer:


Customer.findById(req.params.customerId)
		.then(
			customer => {
				if(!customer){
					return res.status(404).json({
						message: 'Customer Not Found',
					});
				}
				return customer.update({
										name: req.body.name,
										age: req.body.age
									})
									.then(() => res.status(200).json(customer))
									.catch((error) => res.status(400).send(error));
				}
			)
		.catch((error) => res.status(400).send(error));		

– Delete a Customer:


Customer
	.findById(req.params.customerId)
	.then(customer => {
		if(!customer) {
			return res.status(400).send({
				message: 'Customer Not Found',
			});
		}

		return customer.destroy()
				.then(() => res.status(200).json({message: "Destroy successfully!"}))
				.catch(error => res.status(400).send(error));
	})
	.catch(error => res.status(400).send(error));

Practice

Create a NodeJS/Express-Sequelize project as below structure ->

nodejs-express-rest-apis-post-get-put-delete-request-sequelize-crud-apis-mariadb + nodejs-project-structure

Setup NodeJs/Express Project

Following the guide to create a NodeJS/Express project.
Install Sequelize & MySQL:


$npm install sequelize mysql2 --save

-> package.json file:


{
  "name": "nodejs-express-sequelizejs-mariadb",
  "version": "1.0.0",
  "description": "nodejs-express-sequelizejs-mariadb",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "Node.js-Express-SequelizeJS-MariaDB"
  ],
  "author": "Grokonez.com",
  "license": "ISC",
  "dependencies": {
    "express": "^4.16.3",
    "mysql2": "^1.5.3",
    "sequelize": "^4.37.6"
  }
}

Setup Sequelize MariaDB Connection

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


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

module.exports = env;

– Setup Sequelize-MariaDB 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;

Sequelize Model


module.exports = (sequelize, Sequelize) => {
	const Customer = sequelize.define('customer', {
	  name: {
		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/create', 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 MariaDB database
	Customer.create({  
			name: req.body.name,
			age: req.body.age
		})
		.then(customer => {		
			// Send created customer to client
			res.json(customer);
		})
		.catch(error => res.status(400).send(error))
};
 
// Fetch all Customers
exports.findAll = (req, res) => {
	Customer.findAll({
			attributes: { exclude: ["createdAt", "updatedAt"] }
		})
		.then(customers => {
			res.json(customers);
		})
		.catch(error => res.status(400).send(error))
};

// Find a Customer by Id
exports.findById = (req, res) => {	
	Customer.findById(req.params.customerId,
				{attributes: { exclude: ["createdAt", "updatedAt"] }}
			)
			.then(customer => {
					if (!customer){
						return res.status(404).json({message: "Customer Not Found"})
					}
					return res.status(200).json(customer)
				}
			)
			.catch(error => res.status(400).send(error));
};
 
// Update a Customer
exports.update = (req, res) => {
	return Customer.findById(req.params.customerId)
		.then(
			customer => {
				if(!customer){
					return res.status(404).json({
						message: 'Customer Not Found',
					});
				}
				return customer.update({
										name: req.body.name,
										age: req.body.age
									})
									.then(() => res.status(200).json(customer))
									.catch((error) => res.status(400).send(error));
				}
			)
		.catch((error) => res.status(400).send(error));			 
};
 
// Delete a Customer by Id
exports.delete = (req, res) => {
	return Customer
					.findById(req.params.customerId)
					.then(customer => {
						if(!customer) {
							return res.status(400).send({
								message: 'Customer Not Found',
							});
						}

						return customer.destroy()
														.then(() => res.status(200).json({message: "Destroy successfully!"}))
														.catch(error => res.status(400).send(error));
					})
					.catch(error => res.status(400).send(error));
};

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 ->


> 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 , `name` 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 }

– POST customers ->

nodejs-express-rest-apis-post-get-put-delete-request-sequelize-crud-apis-mariadb + post-request

– GET all customers ->

nodejs-express-rest-apis-post-get-put-delete-request-sequelize-crud-apis-mariadb + get-all-request

– GET a customer by ID:

nodejs-express-rest-apis-post-get-put-delete-request-sequelize-crud-apis-mariadb + get-a-customer-by-id-request

– PUT a customer ->

nodejs-express-rest-apis-post-get-put-delete-request-sequelize-crud-apis-mariadb + put-a-customer-by-id

– DELETE a customer by ID ->

nodejs-express-rest-apis-post-get-put-delete-request-sequelize-crud-apis-mariadb + destroy-request

Sequelize’s Logs ->


Executing (default): INSERT INTO `customers` (`id`,`name`,`age`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Jack',47,'2018-08-18 04:06:48','2018-08-18 04:06:48');
Executing (default): INSERT INTO `customers` (`id`,`name`,`age`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Mary',37,'2018-08-18 04:07:55','2018-08-18 04:07:55');
Executing (default): INSERT INTO `customers` (`id`,`name`,`age`,`createdAt`,`updatedAt`) VALUES (DEFAULT,'Adam',26,'2018-08-18 04:08:18','2018-08-18 04:08:18');
Executing (default): SELECT `id`, `name`, `age` FROM `customers` AS `customer`;
Executing (default): SELECT `id`, `name`, `age` FROM `customers` AS `customer` WHERE `customer`.`id` = '1';
Executing (default): SELECT `id`, `name`, `age` FROM `customers` AS `customer` WHERE `customer`.`id` = '4';
Executing (default): SELECT `id`, `name`, `age` FROM `customers` AS `customer` WHERE `customer`.`id` = '1';
Executing (default): SELECT `id`, `name`, `age`, `createdAt`, `updatedAt` FROM `customers` AS `customer` WHERE `customer`.`id` = '2';
Executing (default): UPDATE `customers` SET `name`='Jack',`age`=49,`updatedAt`='2018-08-18 04:19:43' WHERE `id` = 2
Executing (default): SELECT `id`, `name`, `age`, `createdAt`, `updatedAt` FROM `customers` AS `customer` WHERE `customer`.`id` = '1';
Executing (default): DELETE FROM `customers` WHERE `id` = 1 LIMIT 1

SourceCode

How to work with the below sourcecode?

– Download the sourcecode -> We get a file Nodejs-Express-Sequelizejs-MariaDB.zip.
– Extract the zip file, then cd to folder Nodejs-Express-Sequelizejs-MariaDB.
– Install libs by commandline: npm install
– Start MariaDB
– Start Node.js server by cmd: npm start

Sourcecode ->

Nodejs-Express-Sequelizejs-MariaDB

5 thoughts on “Sequelize CRUD MariaDB example | Build CRUD Node.js/Express RestAPIs example”

  1. Thank you for this great article!

    Just a quick note:
    With Sequelize v5, findById() was replaced with findByPk().

  2. I was suggested this web site by my cousin. I’m not sure
    whether this put up is written via him as nobody else realize such targeted approximately my problem.
    You’re wonderful! Thanks!

Leave a Reply

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