Skip to content
  • About Us
  • Contact Us
  • Privacy Policy
  • Terms & Conditions
  • Site Map

XpertPhp

Expertphp Is The Best Tutorial For Beginners

  • Home
  • Javascript
    • Jquery
    • React JS
    • Angularjs
    • Angular
    • Nodejs
  • Codeigniter
  • Laravel
  • Contact Us
  • About Us
  • Live Demos

Home » Nodejs » Node js Express Sequelize and Mysql Example

Node js Express Sequelize and Mysql Example

Node js Express Sequelize and Mysql Example

Posted on October 6, 2021February 17, 2022 By XpertPhp

In this article, we will explain to you how to create Rest APIs example with Express, Sequelize & MySQL in node js(Node js Express Sequelize and Mysql Example).

Express is the most famous web framework for Node.js that supports routing, middleware, view system. Sequelize is a promising Node.js ORM that supports offers for Postgres, MySQL, SQL Server. In this tutorial, we will show you step by step to create Node.js Restful CRUD API using Express, Sequelize with MySQL.

So you can see our node js example.

Table of Contents

  • Application Directory Structure
  • Create Node Js Application
  • Install NPM Package
  • Setup Express web server
  • Setting up Sequelize MySQL connection
  • Create Sequelize model
  • Create controller
  • Create Route
  • Run Node js Application

Application Directory Structure

We have created the best directory structure for the rest API CRUD operations. so you can follow the directory structure below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/nodejs_rest_api
  /app
    /config
      db.config.js
      env.js
    /controller
      user.controller.js
    /model
      user.model.js
    /route
      user.route.js
  /node_modules
  package.json
  server.js

Create Node Js Application

First, we will open the command prompt and create the application in our directory. for this, you can follow the below command.

1
2
mkdir nodejs_rest_api
cd my_node_app

The run “npm init” command through we can create a new package.json file in our application directory

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
npm init
name: (nodejs_rest_api)
version: (1.0.0)
description:
entry point: (index.js) server.js
test command:
git repository:
keywords:
author:
license: (ISC)
 
Is this ok? (yes) yes
 
 
{
  "name": "nodejs_rest_api",
  "version": "1.0.0",
  "description": "",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "node server.js"
  },
  "keywords": [],
  "author": "",
  "license": "ISC"
}

Install NPM Package

After the done setup node js application, we will install the express, sequelize, mysql2 and cors npm package. so you can see the following npm command.

1
npm install express sequelize body-parser mysql2 cors --save

Setup Express web server

In this step, We will create the server.js file in our application directory. after then setup the our application.
server.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
const express = require("express");
const cors = require("cors");
const bodyParser   = require('body-parser');
const app = express();
 
 
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
app.use(cors());
 
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 }');
});
 
// api routes
app.get("/", (req, res) => {
  res.json({ message: "Welcome to Our App." });
});
 
require('./app/route/user.route.js')(app);
 
// set port, listen for requests
const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}.`);
});

Setting up Sequelize MySQL connection

In this step, we will create .env file and setup database connection using the sequelize with mysql.
app/config/env.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
const env = {
  database: 'myapidb',
  username: 'root',
  password: "",
  host: 'localhost',
  dialect: 'mysql',
  pool: {
  max: 5,
  min: 0,
  acquire: 30000,
  idle: 10000
  }
};
 
module.exports = env;

app/config/db.config.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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.users = require('../model/user.model.js')(sequelize, Sequelize);
 
 
module.exports = db;

Create Sequelize model

Now, We create the sequelize model and which is define database schema to this sequelize model. so you can see user.model.js file as example.
app/model/user.model.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
module.exports = (sequelize, Sequelize) => {
const User = sequelize.define('users', {
  firstname: {
type: Sequelize.STRING
  },
  lastname: {
type: Sequelize.STRING
  },
  email: {
  type: Sequelize.STRING
  },
  password: {
type: Sequelize.STRING
  }
});
 
 
return User;
}

Create controller

Here, we create the some different methods such as create, findall, update, and delete. which is helpful for getting data, insert data and update data with mysql.
app/controller/user.controller.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
const db = require('../config/db.config.js');
const env = require('../config/env.js');
 
const User = db.users;
 
// Post a User
exports.create = (req, res) => {
// Save to MySQL database
User.create({  
  firstname: req.body.firstname,
  lastname: req.body.lastname,
  email: req.body.email,
  password: req.body.password
}).then(user => {
// Send created user to client
res.send(user);
});
};
// FETCH all Users
exports.findAll = (req, res) => {
User.findAll().then(users => {
  // Send all users to Client
  res.send(users);
});
};
 
// Find a User by Id
exports.findById = (req, res) => {
User.findById(req.params.userId).then(user => {
res.send(user);
})
};
// Update a User
exports.update = (req, res) => {
const id = req.params.userId;
User.update( { firstname: req.body.firstname, lastname: req.body.lastname, email: req.body.email },
{ where: {id: req.params.userId} }
).then(() => {
res.status(200).send({ message: 'updated successfully a user with id = ' + id });
});
};
// Delete a User by Id
exports.delete = (req, res) => {
const id = req.params.userId;
User.destroy({
  where: { id: id }
}).then(() => {
  res.status(200).send({ message: 'deleted successfully a user with id = ' + id });
});
};

Create Route

Now, We will define the all users route. so you can see our route example.
app/route/user.route.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
module.exports = function(app) {
    const users = require('../controller/user.controller.js');
    // Create a new User
    app.post('/api/users', users.create);
    // Retrieve all User
    app.get('/api/users', users.findAll);
    // Retrieve a single User by Id
    app.get('/api/users/:userId', users.findById);
    // Update a User with Id
    app.put('/api/users/:userId', users.update);
    // Delete a User with Id
    app.delete('/api/users/:userId', users.delete);
 
}

Run Node js Application

we will run the node js application using the below command. so you can following the below command.

1
node server.js

Now you can run the example using the below Url in the browser.

1
http://localhost:3000/

Please follow and like us:
error
fb-share-icon
Tweet
fb-share-icon

Recommended Posts:

  • How to install node js on ubuntu 16.04 and 18.04
  • Node js convert image to base64 Example
  • How To Connect MySQL Database in Node Js App
  • File/Image Upload With Multer in Node js and Express
  • node js routes in separate file using express
Nodejs Tags:node, Node Express Sequelize ORM CRUD, nodejs, sequelize node js example

Post navigation

Previous Post: Node JS Http Request with Headers Example
Next Post: Node Js JWT Authentication Tutorial with Example

Latest Posts

  • Laravel Check If Foreach is Empty Example
  • Laravel Check Array Empty in Blade
  • PHP Datatables CRUD Example
  • How to Convert Date and Time from one timezone to another in php
  • how to get current date and time in php
  • Drag and Drop Reorder Items with jQuery, PHP & MySQL
  • Laravel 9 Toastr Notifications Example Tutorial
  • Laravel 9 CRUD Operation Example Using Google Firebase
  • Laravel 9 CKeditor Image Upload With Example
  • Laravel 9 Summernote Image Upload With Example

Tools

  • Compound Interest Calculator
  • Hex to RGB Color Converter
  • Pinterest Video Downloader
  • Birthday Calculator
  • Convert JSON to PHP Array Online
  • JavaScript Minifier
  • CSS Beautifier
  • CSS Minifier
  • JSON Beautifier
  • JSON Minifier

Categories

  • Ajax
  • Angular
  • Angularjs
  • Bootstrap
  • Codeigniter
  • Css
  • Htaccess
  • Javascript
  • Jquery
  • Laravel
  • MongoDB
  • MySql
  • Nodejs
  • Php
  • React JS
  • Shopify Api
  • Ubuntu

Tags

angular 10 tutorial angular 11 ci tutorial codeigniter 4 image upload Codeigniter 4 Tutorial codeigniter tutorial CodeIgniter tutorial for beginners codeigniter with mysql crud operation eloquent relationships file upload File Validation form validation Image Upload jQuery Ajax Form Handling jquery tricks jquery tutorial laravel 6 Laravel 6 Eloquent Laravel 6 Model laravel 6 relationship laravel 6 relationship eloquent Laravel 6 Routing laravel 7 Laravel 7 Eloquent laravel 7 routing laravel 7 tutorial Laravel 8 laravel 8 example laravel 8 tutorial laravel 9 example laravel 9 tutorial Laravel Framework laravel from scratch Laravel Socialite laravel social login nodejs pagination payment gateway php with mysql react js tutorial rewrite rule send mail validation wysiwyg editor

Copyright © 2018 - 2022,

All Rights Reserved Powered by XpertPhp.com