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

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 ..

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

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.

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 .

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

  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.

Connecting DB (assuming that you are familiar with node)

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

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