# Sequelize with Node, Express & Mysql and it’s setup (ok) {% file src="" %} ## Sequelize with Node, Express & Mysql and it’s setup [![Irshad Vali](https://miro.medium.com/fit/c/56/56/1*o2geRcqGjIyTo4rfAdWgAA.jpeg)](https://medium.com/@irshad.vali?source=post_page-----ae69f0c02df7--------------------------------)[Irshad Vali](https://medium.com/@irshad.vali?source=post_page-----ae69f0c02df7--------------------------------)[Mar 16, 2019·4 min read](https://medium.com/@irshad.vali/sequelize-with-node-express-mysql-and-its-setup-ae69f0c02df7?source=post_page-----ae69f0c02df7--------------------------------)![Image for post](https://miro.medium.com/max/60/1*g42KjOnDF4ptB_8494FP5A.png?q=20)![Image for post](https://miro.medium.com/max/1125/1*g42KjOnDF4ptB_8494FP5A.png) 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:***![Image for post](https://miro.medium.com/max/60/1*X1hyrh43xHEO3FnP6mGdNQ.png?q=20)![Image for post](https://miro.medium.com/max/2820/1*X1hyrh43xHEO3FnP6mGdNQ.png) ***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.![Image for post](https://miro.medium.com/max/60/1*8aBhcc7JLFXzujRRCoQG1g.png?q=20)![Image for post](https://miro.medium.com/max/3213/1*8aBhcc7JLFXzujRRCoQG1g.png) Create few ***get and post api** as* below: ***`POST`*****` ``call api for creating author and book.`** **1-** [*http://localhost:3001/demoApi/author*](http://localhost:3001/demoApi/author) payload is: {\ “authorName”: “irshad vali\ } **2-** payloads are: {\ “bookName”: “System Architecture”,\ “authorId”: 1\ } **`All`` `*****`GET`*****` ``Apis`** ***1- Get all authors list*** ***2- Get all books list*** ***3- Get Author by id*** ***4- Get Book by id*** ***5- Get Author and his books*** 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*](https://github.com/irshadvali/sequelize_node_mysql_express) *.*