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

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

  3. Your style is unique in comparison to other people I’ve read stuff from.
    I appreciate you for posting when you’ve got the opportunity, Guess I will
    just bookmark this web site.

  4. This is very interesting, You’re a very skilled blogger.
    I’ve joined your feed and look forward to seeking more
    of your fantastic post. Also, I’ve shared your site in my social networks!

  5. hi!,I love your writing so a lot! percentage we communicate extra about your article on AOL?
    I require an expert in this space to resolve my problem.
    Maybe that is you! Taking a look ahead to peer you.

  6. Hmm is anyone else encountering problems with the
    pictures on this blog loading? I’m trying to figure out if its a problem on my end or if it’s the blog.

    Any responses would be greatly appreciated.

  7. Somebody necessarily lend a hand to make critically articles I’d state.

    This is the first time I frequented your web page and
    up to now? I amazed with the analysis you made to create this particular put up amazing.
    Fantastic process!

  8. First off I want to say great blog! I had
    a quick question that I’d like to ask if you don’t mind.
    I was interested to find out how you center yourself and clear your thoughts before writing.
    I have had a tough time clearing my thoughts in getting my
    ideas out there. I do take pleasure in writing however it just seems like the first 10 to 15
    minutes are lost simply just trying to figure out how to
    begin. Any ideas or hints? Thank you!

  9. Hmm is anyone else encountering problems with the images on this blog loading?
    I’m trying to find out if its a problem on my end or if it’s the blog.
    Any responses would be greatly appreciated.

  10. Heya! I just wanted to ask if you ever have any
    trouble with hackers? My last blog (wordpress)
    was hacked and I ended up losing many months
    of hard work due to no data backup. Do you have any
    methods to protect against hackers?

  11. You have made some good points there. I checked on the internet to find out more about the
    issue and found most individuals will go along with your views
    on this web site.

  12. My partner and I stumbled over here coming from a different web address and thought I might as well check things out.

    I like what I see so i am just following you. Look forward
    to looking at your web page again.

  13. I believe everything typed made a ton of sense. However, consider this, what if
    you were to write a awesome headline? I ain’t suggesting
    your information is not solid., however what
    if you added something that makes people want more?
    I mean ozenero | Mobile & Web Programming Tutorials is kinda
    vanilla. You might glance at Yahoo’s home page and note how they write article headlines to get people to
    click. You might add a video or a pic or two to grab people excited about what you’ve got to say.
    In my opinion, it could bring your posts a little livelier.

  14. Just want to say your article is as amazing.
    The clearness for your post is simply spectacular and i could assume you’re an expert in this subject.
    Fine together with your permission let me to clutch your RSS feed to stay up to date with impending post.
    Thank you one million and please carry on the enjoyable work.

  15. Very nice post. I just stumbled upon your blog and wanted to
    say that I have truly enjoyed surfing around your blog posts.
    After all I will be subscribing to your feed and I hope you write again very soon!

  16. You actually make it seem so easy along with your presentation but I in finding this topic
    to be actually something that I feel I might by no means understand.
    It seems too complex and very extensive for me.
    I am looking forward on your subsequent put up, I’ll attempt to get the dangle of it!

  17. Hello! I simply would like to offer you a big thumbs up for the great info you have got here
    on this post. I am coming back to your web site for more soon.

  18. I’ve been surfing on-line greater than three hours nowadays, yet I by no means
    found any fascinating article like yours. It is lovely value sufficient for me.
    In my view, if all website owners and bloggers made just right content as
    you did, the net will probably be a lot more helpful than ever before.

  19. I’m amazed, I have to admit. Seldom do I encounter a blog that’s both educative and engaging, and let me
    tell you, you have hit the nail on the head. The issue is something that too few folks
    are speaking intelligently about. I’m very happy I found this in my
    search for something concerning this.

  20. Howdy very cool website!! Guy .. Beautiful ..
    Superb .. I’ll bookmark your site and take the feeds also?
    I’m satisfied to search out numerous useful info right here within the post,
    we want develop more strategies in this regard, thank you for sharing.

    . . . . .

  21. Having read this I thought it was really informative.
    I appreciate you spending some time and effort to put this content together.
    I once again find myself personally spending a lot
    of time both reading and leaving comments. But so what, it was still worthwhile!

  22. Great goods from you, man. I have understand your stuff previous to and you’re just extremely great.
    I really like what you have acquired here, really like what you are saying and the way in which you say it.
    You make it enjoyable and you still care for to keep it wise.
    I cant wait to read far more from you. This is actually a wonderful site.

  23. It’s a pity you don’t have a donate button! I’d certainly donate to this fantastic blog!
    I guess for now i’ll settle for bookmarking and adding your RSS feed to my Google
    account. I look forward to new updates and will talk about this blog with my Facebook group.
    Talk soon!

  24. I loved as much as you’ll receive carried out right here.
    The sketch is attractive, your authored subject matter stylish.
    nonetheless, you command get got an edginess over that you wish be delivering
    the following. unwell unquestionably come further formerly again since exactly
    the same nearly a lot often inside case you shield this hike.

  25. Its like you read my mind! You appear to know so much about this, like
    you wrote the book in it or something. I think that
    you can do with some pics to drive the message home a bit,
    but other than that, this is excellent blog. A fantastic read.
    I’ll definitely be back.

  26. You made some decent points there. I checked on the web for more information about the issue and found most people will
    go along with your views on this site.

  27. Hello there, just became alert to your blog through Google, and found that it is really informative.

    I’m going to watch out for brussels. I’ll appreciate if you continue this in future.
    A lot of people will be benefited from your writing.
    Cheers!

  28. Pretty nice post. I simply stumbled upon your blog and wanted to
    say that I have truly enjoyed surfing around your weblog posts.
    After all I will be subscribing on your rss feed and I hope you write once more very soon!

  29. Hi there would you mind sharing which blog platform you’re using? I’m looking to start my own blog in the near future but I’m having a tough time selecting between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your layout seems different then most blogs and I’m looking for something unique. P.S My apologies for being off-topic but I had to ask!

  30. I have been exploring for a little for any high-quality articles or blog posts on this sort of area . Exploring in Yahoo I at last stumbled upon this web site. Reading this information So i am happy to convey that I have an incredibly good uncanny feeling I discovered exactly what I needed. I most certainly will make certain to dont forget this web site and give it a look regularly.

Leave a Reply

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