Sequelize Cannot Read Property 'findall' of Undefined

Express is 1 of the nigh popular web frameworks for Node.js that supports routing, middleware, view system… Sequelize is a hope-based Node.js ORM that supports the dialects for Postgres, MySQL, SQL Server… In this tutorial, I will testify you step by stride to build Node.js Restful CRUD API using Limited, Sequelize with MySQL database.

You should install MySQL in your car first. The installation instructions tin be found at Official MySQL installation transmission.

Related Posts:
– Build Node.js Rest APIs with Express & MySQL (without Sequelize)
– Node.js: Upload/Import Excel file data into MySQL Database
– Node.js: Upload CSV file information into MySQL Database

Fullstack:
– Vue.js + Node.js + Express + MySQL example
– Vue.js + Node.js + Express + MongoDB case
– Angular 8 + Node.js Limited + MySQL example
– Angular x + Node.js Express + MySQL example
– Athwart 11 + Node.js Express + MySQL example
– Athwart 12 + Node.js Limited + MySQL example
– React + Node.js + Limited + MySQL case
– React Redux + Node.js Express + MySQL case

Security: Node.js – JWT Authentication & Authorisation instance
Deployment:
– Deploying/Hosting Node.js app on Heroku with MySQL database
– Dockerize Node.js Limited and MySQL case – Docker Compose

Contents

Node.js Rest CRUD API overview
Demo Video
Create Node.js App
Setup Express web server
Configure MySQL database & Sequelize
Initialize Sequelize
Define the Sequelize Model
Create the Controller
- Create a new object
- Retrieve objects (with condition)
- Call up a single object
- Update an object
- Delete an object
- Delete all objects
- Observe all objects by condition
Define Routes
Test the APIs
Source code
Decision
Further Reading

Node.js Balance CRUD API overview

We will build Residue Apis that can create, call up, update, delete and find Tutorials past title.

First, we get-go with an Express spider web server. Next, we add configuration for MySQL database, create Tutorial model with Sequelize, write the controller. Then we define routes for handling all Grime operations (including custom finder).

The following tabular array shows overview of the Rest APIs that will be exported:

Methods	Urls	Deportment
Become	api/tutorials	go all Tutorials
Go	api/tutorials/:id	get Tutorial past `id`
POST	api/tutorials	add together new Tutorial
PUT	api/tutorials/:id	update Tutorial by `id`
DELETE	api/tutorials/:id	remove Tutorial by `id`
DELETE	api/tutorials	remove all Tutorials
Go	api/tutorials/published	find all published Tutorials
GET	api/tutorials?title=[kw]	find all Tutorials which title contains `'kw'`

Finally, we're gonna test the Rest Apis using Postman.

This is our project construction:

Demo Video

This is our Node.js Limited Sequelize awarding demo running with MySQL database and test Rest Apis with Postman.

Create Node.js App

First, we create a folder:

          $ mkdir nodejs-express-sequelize-mysql $ cd nodejs-express-sequelize-mysql

Next, we initialize the Node.js App with a parcel.json file:

          npm init name: (nodejs-limited-sequelize-mysql)  version: (one.0.0)  description: Node.js Residue Apis with Express, Sequelize & MySQL. entry betoken: (index.js) server.js examination command:  git repository:  keywords: nodejs, limited, sequelize, mysql, residue, api author: bezkoder license: (ISC) Is this ok? (yes) aye

We need to install necessary modules: express, sequelize, mysql2 and cors.
Run the command:

          npm install limited sequelize mysql2 cors --salve

The package.json file should look like this:

          {   "name": "nodejs-express-sequelize-mysql",   "version": "1.0.0",   "clarification": "Node.js Rest Apis with Express, Sequelize & MySQL",   "principal": "server.js",   "scripts": {     "examination": "echo \"Error: no examination specified\" && get out 1"   },   "keywords": [     "nodejs",     "express",     "rest",     "api",     "sequelize",     "mysql"   ],   "author": "bezkoder",   "license": "ISC",   "dependencies": {     "cors": "^2.8.5",     "express": "^4.17.1",     "mysql2": "^2.0.2",     "sequelize": "^5.21.2"   } }

Setup Express spider web server

In the root folder, let'due south create a new server.js file:

          const express = require("express"); const cors = require("cors"); const app = limited(); var corsOptions = {   origin: "http://localhost:8081" }; app.apply(cors(corsOptions)); // parse requests of content-blazon - application/json app.utilise(express.json()); // parse requests of content-blazon - application/10-www-course-urlencoded app.use(express.urlencoded({ extended: truthful })); // simple route app.get("/", (req, res) => {   res.json({ bulletin: "Welcome to bezkoder application." }); }); // set port, listen for requests const PORT = process.env.PORT || 8080; app.listen(PORT, () => {   console.log(`Server is running on port ${PORT}.`); });

What we do are:
– import express, and cors modules:

Express is for building the Residual apis
cors provides Limited middleware to enable CORS with diverse options.

– create an Express app, then add body-parser (json and urlencoded) and cors middlewares using app.apply() method. Notice that we prepare origin: http://localhost:8081.
– define a GET route which is simple for exam.
– listen on port 8080 for incoming requests.

Now allow'south run the app with command: node server.js.
Open up your browser with url http://localhost:8080/, you will see:

Yeah, the outset pace is washed. We're gonna piece of work with Sequelize in the next section.

Configure MySQL database & Sequelize

In the app folder, we create a separate config folder for configuration with db.config.js file like this:

          module.exports = {   HOST: "localhost",   USER: "root",   Password: "123456",   DB: "testdb",   dialect: "mysql",   pool: {     max: 5,     min: 0,     acquire: 30000,     idle: 10000   } };

First 5 parameters are for MySQL connection.
pool is optional, it will be used for Sequelize connection pool configuration:

max: maximum number of connection in pool
min: minimum number of connection in pool
idle: maximum time, in milliseconds, that a connection can be idle before being released
acquire: maximum time, in milliseconds, that pool volition endeavour to get connection before throwing error

For more details, please visit API Reference for the Sequelize constructor.

Initialize Sequelize

We're gonna initialize Sequelize in app/models folder that volition incorporate model in the next pace.

Now create app/models/index.js with the following code:

          const dbConfig = require("../config/db.config.js"); const Sequelize = require("sequelize"); const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.Countersign, {   host: dbConfig.HOST,   dialect: dbConfig.dialect,   operatorsAliases: false,   pool: {     max: dbConfig.pool.max,     min: dbConfig.pool.min,     acquire: dbConfig.pool.larn,     idle: dbConfig.pool.idle   } }); const db = {}; db.Sequelize = Sequelize; db.sequelize = sequelize; db.tutorials = crave("./tutorial.model.js")(sequelize, Sequelize); module.exports = db;

Don't forget to call sync() method in server.js:

          ... const app = express(); app.utilize(...); const db = crave("./app/models"); db.sequelize.sync(); ...

In evolution, y'all may need to drop existing tables and re-sync database. Just apply force: true as post-obit code:

                      db.sequelize.sync({ strength: true }).then(() => {   console.log("Drop and re-sync db."); });

Ascertain the Sequelize Model

In models binder, create tutorial.model.js file like this:

          module.exports = (sequelize, Sequelize) => {   const Tutorial = sequelize.define("tutorial", {     title: {       type: Sequelize.Cord     },     clarification: {       type: Sequelize.Cord     },     published: {       type: Sequelize.BOOLEAN     }   });   return Tutorial; };

This Sequelize Model represents tutorials tabular array in MySQL database. These columns will be generated automatically: id, title, description, published, createdAt, updatedAt.

After initializing Sequelize, we don't demand to write Grime functions, Sequelize supports all of them:

create a new Tutorial: create(object)
find a Tutorial past id: findByPk(id)
go all Tutorials: findAll()
update a Tutorial by id: update(data, where: { id: id })
remove a Tutorial: destroy(where: { id: id })
remove all Tutorials: destroy(where: {})
find all Tutorials by title: findAll({ where: { title: ... } })

These functions volition be used in our Controller.

We can improve the case by adding Comments for each Tutorial. Information technology is the One-to-Many Relationship and I write a tutorial for this at:
Sequelize Associations: I-to-Many example – Node.js, MySQL

Or you can add Tags for each Tutorial and add together Tutorials to Tag (Many-to-Many Human relationship):
Sequelize Many-to-Many Association example with Node.js & MySQL

Create the Controller

Inside app/controllers folder, let'due south create tutorial.controller.js with these Grime functions:

create
findAll
findOne
update
delete
deleteAll
findAllPublished

          const db = require("../models"); const Tutorial = db.tutorials; const Op = db.Sequelize.Op; // Create and Relieve a new Tutorial exports.create = (req, res) => {    }; // Retrieve all Tutorials from the database. exports.findAll = (req, res) => {    }; // Find a unmarried Tutorial with an id exports.findOne = (req, res) => {    }; // Update a Tutorial by the id in the request exports.update = (req, res) => {    }; // Delete a Tutorial with the specified id in the request exports.delete = (req, res) => {    }; // Delete all Tutorials from the database. exports.deleteAll = (req, res) => {    }; // Notice all published Tutorials exports.findAllPublished = (req, res) => {    };

Permit's implement these functions.

Create a new object

Create and Salve a new Tutorial:

          exports.create = (req, res) => {   // Validate request   if (!req.body.title) {     res.status(400).send({       message: "Content tin can not be empty!"     });     return;   }   // Create a Tutorial   const tutorial = {     title: req.body.championship,     description: req.trunk.clarification,     published: req.body.published ? req.torso.published : false   };   // Relieve Tutorial in the database   Tutorial.create(tutorial)     .then(data => {       res.send(data);     })     .catch(err => {       res.status(500).transport({         message:           err.bulletin || "Some fault occurred while creating the Tutorial."       });     }); };

Remember objects (with condition)

Think all Tutorials/ find by championship from the database:

          exports.findAll = (req, res) => {   const title = req.query.title;   var condition = championship ? { title: { [Op.similar]: `%${title}%` } } : zippo;   Tutorial.findAll({ where: condition })     .then(information => {       res.send(information);     })     .grab(err => {       res.status(500).send({         message:           err.message || "Some error occurred while retrieving tutorials."       });     }); };

Nosotros use req.query.title to go query string from the Request and consider it as condition for findAll() method.

Think a single object

Find a single Tutorial with an id:

          exports.findOne = (req, res) => {   const id = req.params.id;   Tutorial.findByPk(id)     .then(data => {       if (information) {         res.send(data);       } else {         res.condition(404).send({           bulletin: `Cannot discover Tutorial with id=${id}.`         });       }     })     .grab(err => {       res.status(500).send({         message: "Mistake retrieving Tutorial with id=" + id       });     }); };

Update an object

Update a Tutorial identified by the id in the request:

          exports.update = (req, res) => {   const id = req.params.id;   Tutorial.update(req.body, {     where: { id: id }   })     .then(num => {       if (num == 1) {         res.send({           message: "Tutorial was updated successfully."         });       } else {         res.send({           message: `Cannot update Tutorial with id=${id}. Maybe Tutorial was non constitute or req.body is empty!`         });       }     })     .take hold of(err => {       res.status(500).send({         message: "Fault updating Tutorial with id=" + id       });     }); };

Delete an object

Delete a Tutorial with the specified id:

          exports.delete = (req, res) => {   const id = req.params.id;   Tutorial.destroy({     where: { id: id }   })     .and so(num => {       if (num == 1) {         res.send({           message: "Tutorial was deleted successfully!"         });       } else {         res.ship({           message: `Cannot delete Tutorial with id=${id}. Perhaps Tutorial was non institute!`         });       }     })     .catch(err => {       res.status(500).send({         message: "Could not delete Tutorial with id=" + id       });     }); };

Delete all objects

Delete all Tutorials from the database:

          exports.deleteAll = (req, res) => {   Tutorial.destroy({     where: {},     truncate: imitation   })     .then(nums => {       res.send({ message: `${nums} Tutorials were deleted successfully!` });     })     .catch(err => {       res.condition(500).ship({         message:           err.message || "Some error occurred while removing all tutorials."       });     }); };

Find all objects by condition

Discover all Tutorials with published = true:

          exports.findAllPublished = (req, res) => {   Tutorial.findAll({ where: { published: true } })     .and then(data => {       res.send(information);     })     .take hold of(err => {       res.status(500).transport({         message:           err.message || "Some error occurred while retrieving tutorials."       });     }); };

This controller can be modified a little to render pagination response:

          {     "totalItems": 8,     "tutorials": [...],     "totalPages": 3,     "currentPage": one }

You can detect more details at:
Server side Pagination in Node.js with Sequelize and MySQL

Define Routes

When a client sends request for an endpoint using HTTP request (Become, Post, PUT, DELETE), nosotros need to determine how the server will reponse by setting up the routes.

These are our routes:

/api/tutorials: GET, Post, DELETE
/api/tutorials/:id: Get, PUT, DELETE
/api/tutorials/published: Become

Create a turorial.routes.js inside app/routes folder with content similar this:

          module.exports = app => {   const tutorials = require("../controllers/tutorial.controller.js");   var router = require("express").Router();   // Create a new Tutorial   router.post("/", tutorials.create);   // Retrieve all Tutorials   router.go("/", tutorials.findAll);   // Call up all published Tutorials   router.get("/published", tutorials.findAllPublished);   // Recall a single Tutorial with id   router.get("/:id", tutorials.findOne);   // Update a Tutorial with id   router.put("/:id", tutorials.update);   // Delete a Tutorial with id   router.delete("/:id", tutorials.delete);   // Delete all Tutorials   router.delete("/", tutorials.deleteAll);   app.use('/api/tutorials', router); };

You lot can see that nosotros use a controller from /controllers/tutorial.controller.js.

We also need to include routes in server.js (right before app.mind()):

          ... require("./app/routes/turorial.routes")(app); // set up port, listen for requests const PORT = ...; app.listen(...);

Test the APIs

Run our Node.js application with command: node server.js.
The console shows:

          Server is running on port 8080. Executing (default): Driblet TABLE IF EXISTS `tutorials`; Executing (default): CREATE TABLE IF Not EXISTS `tutorials` (`id` INTEGER NOT NULL auto_increment , `title` VARCHAR(255), `description` VARCHAR(255), `published` TINYINT(ane), `createdAt` DATETIME Non Goose egg, `updatedAt` DATETIME NOT NULL, Principal Fundamental (`id`)) ENGINE=InnoDB; Executing (default): SHOW INDEX FROM `tutorials` Drop and re-sync db.

Using Postman, nosotros're gonna test all the Apis above.

Create a new Tutorial using Post /tutorials Api

After creating some new Tutorials, you can check MySQL table:

            mysql> select * from tutorials; +----+-------------------+-------------------+-----------+---------------------+---------------------+ | id | title             | description       | published | createdAt           | updatedAt           | +----+-------------------+-------------------+-----------+---------------------+---------------------+ |  1 | JS: Node Tut #1   | Tut#i Description |         0 | 2019-12-13 01:13:57 | 2019-12-13 01:13:57 | |  2 | JS: Node Tut #2   | Tut#2 Description |         0 | 2019-12-13 01:xvi:08 | 2019-12-13 01:xvi:08 | |  3 | JS: Vue Tut #three    | Tut#three Description |         0 | 2019-12-13 01:sixteen:24 | 2019-12-thirteen 01:16:24 | |  4 | Vue Tut #4        | Tut#4 Description |         0 | 2019-12-13 01:16:48 | 2019-12-13 01:sixteen:48 | |  5 | Node & Vue Tut #5 | Tut#5 Description |         0 | 2019-12-13 01:16:58 | 2019-12-13 01:xvi:58 | +----+-------------------+-------------------+-----------+---------------------+---------------------+

Think all Tutorials using Become /tutorials Api

Retrieve a single Tutorial by id using Get /tutorials/:id Api

Update a Tutorial using PUT /tutorials/:id Api

Check tutorials table after some rows were updated:

            mysql> select * from tutorials; +----+-------------------+-------------------+-----------+---------------------+---------------------+ | id | title             | description       | published | createdAt           | updatedAt           | +----+-------------------+-------------------+-----------+---------------------+---------------------+ |  1 | JS: Node Tut #1   | Tut#1 Clarification |         0 | 2019-12-13 01:13:57 | 2019-12-13 01:thirteen:57 | |  two | JS: Node Tut #2   | Tut#2 Description |         0 | 2019-12-13 01:xvi:08 | 2019-12-thirteen 01:xvi:08 | |  three | JS: Vue Tut #3    | Tut#iii Description |         one | 2019-12-xiii 01:16:24 | 2019-12-13 01:22:51 | |  4 | Vue Tut #four        | Tut#4 Description |         1 | 2019-12-xiii 01:sixteen:48 | 2019-12-thirteen 01:25:28 | |  5 | Node & Vue Tut #v | Tut#five Description |         one | 2019-12-13 01:sixteen:58 | 2019-12-13 01:25:30 | +----+-------------------+-------------------+-----------+---------------------+---------------------+

Find all Tutorials which championship contains 'node': GET /tutorials?title=node

Observe all published Tutorials using GET /tutorials/published Api

Delete a Tutorial using DELETE /tutorials/:id Api

Tutorial with id=two was removed from tutorials tabular array:

            mysql> select * from tutorials; +----+-------------------+-------------------+-----------+---------------------+---------------------+ | id | title             | description       | published | createdAt           | updatedAt           | +----+-------------------+-------------------+-----------+---------------------+---------------------+ |  1 | JS: Node Tut #1   | Tut#1 Description |         0 | 2019-12-thirteen 01:13:57 | 2019-12-13 01:13:57 | |  3 | JS: Vue Tut #three    | Tut#3 Clarification |         1 | 2019-12-xiii 01:xvi:24 | 2019-12-xiii 01:22:51 | |  iv | Vue Tut #4        | Tut#4 Clarification |         1 | 2019-12-13 01:16:48 | 2019-12-thirteen 01:25:28 | |  five | Node & Vue Tut #5 | Tut#5 Description |         ane | 2019-12-13 01:16:58 | 2019-12-thirteen 01:25:xxx | +----+-------------------+-------------------+-----------+---------------------+---------------------+

Delete all Tutorials using DELETE /tutorials Api

Now there are no rows in tutorials table:

            mysql> SELECT * FROM tutorials; Empty set (0.00 sec)

You can use the Simple HTTP Client using Axios to check information technology.

Or: Simple HTTP Client using Fetch API

Conclusion

Today, we've learned how to create Node.js Rest Apis with an Express spider web server. We also know way to add together configuration for MySQL database & Sequelize, create a Sequelize Model, write a controller and define routes for handling all Grime operations.

You lot can find more than interesting thing in the next tutorial:
– Server side Pagination in Node.js with Sequelize and MySQL
Return pagination information in response:

          {     "totalItems": 8,     "tutorials": [...],     "totalPages": 3,     "currentPage": ane }

– Deploying/Hosting Node.js app on Heroku with MySQL database

Or you lot can save Image to MySQL database:
Upload/store images in MySQL using Node.js, Express & Multer

Happy learning! See yous again.

Source code

You tin can find the complete source code for this example on Github.

If you desire to add together Comments for each Tutorial. It is the One-to-Many Association, there is a tutorial for that Relationship: Sequelize Associations: Ane-to-Many instance – Node.js, MySQL

Or you can add Tags for each Tutorial and add Tutorials to Tag (Many-to-Many Human relationship):
Sequelize Many-to-Many Association instance with Node.js & MySQL

Using SQL Server instead: Node.js Crud instance with SQL Server (MSSQL)

alifriontromes.blogspot.com

Source: https://www.bezkoder.com/node-js-express-sequelize-mysql/