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

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