mysql Sequelize-cli in Node JS- beginers guide.
Published: 2022-02-01 22:16:59

Hi there amigos,


This post is intended to be a reference for using mysql - sequilize with nodeJS. We will be using sequelize cli for ease and a consistant project-structure. Express is used to demonstrate the working. _ yea its is gona be a silly demonstration ;) _

Fire up your ide, lets get started....

[github repo](https://github.com/Aravind-pk/seqnode)

## Setting up the node app.

* run `npm init` to initiate node project in your folder.
* Installing packages - just a couple of them
    * run `npm install sequelize mysql2 express`
    * run `npm install nodemon` if you don't have it already installed.

This will install all the necessery packages except the sequelize-cli.


## Sequelize-cli

Sequelize-cli is used to create databases, generate models, migrate dbs, seed files etc.

Even if you are not doing any sort of migrations or seeding, sequelize-cli makes it much more easier to setup sequalize, helps to maintain a project structure (crucial as the app gets bigger) and its simply more fun to use.

* You can install sequelize-cli 
    * globally using `npm install -g sequelize-cli` or
    * as dev dependency by using `npm install --save-dev sequelize-cli`


## Setting database using cli

* Now that we have installed sequelize-cli
   *run `sequelize init` 
    this will create some folders in project. For now we don't have to worry about migration and seeder, so after this you will end up with something like this ..

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u1p1yvsao6uzi71pwibc.png)


The config.json contains all the database configrations.
Change the development configrations to match your database, in my case it is ..

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mug3zu5kwki1sw8dc2hi.png)

Now back to cli- *(before that make sure your database is up and running, I am using phpmyadmin here)*

* lets start by creating the database
   * run `sequelize db:create`  - this will create the db specified in the config.json
* creating a User model
   * run `sequelize model:generate --name User --attributes name:string`

This will create a user.js with a ready to use model inside the models folder.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o2cebvcr31hyl69fbf6d.png)



## Finishing up

* Basically thats it, now you have a model `User` with one column `name` of type string.


## Modifying the model

* currently your User model will look like .

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zcmpsclemjua74hwltv1.png) 

You can add more atributes to User by changing User.init to

```javascript
  User.init({
    name: {
      type: DataTypes.STRING,
      allowNull:false,
      validate:{
        notEmpty : true
      }
    }

  }, {
    sequelize,
    modelName: 'User',
  });
```

Also you can add more columns just by copy pasting the same for example.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8gljvguwkdicl6lfgw8c.png)


## Connecting DB (assuming that you are familiar with node)

you can create an app.js and use the model that we created.

```javascript
const express = require ("express");
const app = express();
const db = require("./models");

// you can add more models like {User,Blog,..}
const { User } = require('./models')

app.get('/all',( req,res)=>{
    User.findAll()
        .then(result =>  res.send(result))  
        .catch(err => console.log(err));
    
});

app.post('/insert',( req,res)=>{
    User.create({
        name:"james",
        phone: 1234567890
    })
    .then(result => res.send(result))
    .catch(err =>console.log(err))
});

app.delete('/delete',( req,res)=>{
    User.destroy( {where:{id:2}})
    .then(result =>console.log(result))
    .catch(err => console.log(err))
});

app.post('/update',( req,res)=>{
    User.update(
        {phone:85487945211},
        {where:{id:1}}
        )
    .then(result =>console.log(result))
    .catch(err => console.log(err))
});

//database connection
db.sequelize.sync().then(req =>{ 
    app.listen(3000 , ()=>{
        console.log("server running");
    })
});
```

Lesgoo , **nodemon app** :D