Sequelize with Node, Express & Mysql and it’s setup (ok)

https://medium.com/@irshad.vali/sequelize-with-node-express-mysql-and-its-setup-ae69f0c02df7

21KB

sequelize_node_mysql_express-master.zip

archive

Sequelize with Node, Express & Mysql and it’s setup

Irshad ValiMar 16, 2019·4 min read

In this post, we will know how to use sequelize with node express and mysql step by step.

What is sequelize ?

Sequelize.js is an ORM (Object/Relational Mapper) which provides easy access to MySQL, MariaDB, SQLite or PostgreSQL databases by mapping database entries to objects and vice versa. It has very powerful migrations mechanism that can transform existing database schema into a new version. It also provides database synchronisation mechanisms that can create database structure by specifying the model structure.

Advantage of sequelize:

Sequelize has all the characteristics that JavaScript has. It is easy to learn for web developer who knows JavaScript.

Setup and Create Project:

Execute the following command in the terminal for creating the Project:

 mkdir sequelize_node_mysql_express

Navigate to the Project folder:

cd sequelize_node_mysql_express

Execute below command: This command will create package.json file.

npm init

Install the packages by executing below commands:

npm install body-parser —- save
npm install express  --save
npm install mysql2  --save
npm install sequelize  --save

Create ‘models’ folder and files: Create models — author and book.

mkdir models
touch ./models/author.js ./models/book.js 
touch sequelize.js 
touch index.js
touch constants.js

Create a database as ‘demo’ in mysql database:

Now in Model:

./model/author.js

module.exports = (sequelize, type) => {
    return sequelize.define('author', {
        id: {
            type: type.INTEGER,
            primaryKey: true,
            autoIncrement: true
        },
        authorName: type.STRING,
    })
}

./model/book.js

return sequelize.define('book', {
        id: {
            type: type.INTEGER,
            primaryKey: true,
            autoIncrement: true
        },
        bookName: type.STRING,
        authorId: type.INTEGER
    })
}

inside constants.js file

const DATABASE_NAME='demo';const USERNAME='root';const PASSWORD='';const HOST='localhost';const  DIALECT='mysql';module.exports = {DATABASE_NAME: DATABASE_NAME,USERNAME:USERNAME,PASSWORD:PASSWORD,HOST:HOST,DIALECT:DIALECT}

File sequelize.js using for ORM and define relationships:

inside sequelize.js file

const Sequelize = require('sequelize')const AuthorModel = require('./models/author')const BookModel = require('./models/book')const {DATABASE_NAME,ROOT,PASSWORD,HOST,DIALECT} =require('./constants')const sequelize = new Sequelize(DATABASE_NAME, ROOT, PASSWORD, {host: HOST,dialect: DIALECT,pool: {max: 10,min: 0,acquire: 30000,idle: 10000}})const Book = BookModel(sequelize, Sequelize)const Author = AuthorModel(sequelize, Sequelize)// Author has Many to bookAuthor.hasMany(Book)sequelize.sync({ force: false })
.then(() => {
console.log(`Database & tables created here!`)
})module.exports = {
Author,
Book
}

Creating a api in index.js file using express:

const express = require('express')const bodyParser = require('body-parser')const { Author, Book } = require('./sequelize')const app = express()app.use(bodyParser.json())// Create a Authorapp.post('/demoApi/author', (req, res) => {console.log(req.body)Author.create(req.body).then(author => res.json(author))})// create a bookapp.post('/demoApi/book', (req, res) => {console.log("book==>", req.body)Book.create(req.body).then(author => res.json(author))})// get all booksapp.get('/demoApi/books', (req, res) => {Book.findAll().then(books =>res.json(books))})// get all authorsapp.get('/demoApi/authors', (req, res) => {Author.findAll().then(authors =>res.json(authors))})// get book by  bookIdapp.get('/demoApi/book/:id', (req, res) => {Book.findOne({where: { id: req.params.id, },}).then(book => res.json(book))})// get author by idapp.get('/demoApi/author/:id', (req, res) => {Author.findOne({where: { id: req.params.id, },}).then(author => res.json(author))})// get author with his book listapp.get('/demoApi/authorHasManyBooks/:id', (req, res) => {let query;query = Author.findAll({where: { id: req.params.id, },include: [{ model: Book }]})return query.then(author => res.json(author))})const port = 3001app.listen(port, () => {console.log(`Running on http://localhost:${port}`)})

Server run:

Go to Project and execute the below command in the terminal:

node index.js

After executing the above command, in the ‘demo’ database two tables ‘authors’ and ‘books’ will be created.

Create few get and post api as below:

POST call api for creating author and book.

1- http://localhost:3001/demoApi/author

payload is:

{ “authorName”: “irshad vali }

2- http://localhost:3001/demoApi/book

payloads are:

{ “bookName”: “System Architecture”, “authorId”: 1 }

All GET Apis

1- Get all authors list

http://localhost:3001/demoApi/authors

2- Get all books list

http://localhost:3001/demoApi/books

3- Get Author by id

http://localhost:3001/demoApi/author/1

4- Get Book by id

http://localhost:3001/demoApi/book/1

5- Get Author and his books

http://localhost:3001/demoApi/authorHasManyBooks/1

That’s done, I hope this tutorial will be helpful for setup of sequelize and created api using sequelize, node, express and mysql.

You can get whole code of this tutorial from github link .