NodeJS Sequelize MySQL

Sequelize

Sequelize is ORM for node.js and io.js
It covers MySQL, PostgreSQL, MariaDB, SQLite, MSSQL

With Express and MySQL

package

Add package to package.json

"mysql": "~2.9.0",
"sequelize": "~3.6.0"

Connection

Sequelize Documentation prepares to use this with Express.
From Documentation, I prepare models folder under project

Projectroot
|- config
|- models
|- public
|- routes
|- app.js

Also, prepare index.js in models folder.
index.js has db setting and read model files
Next is example for this

var fs        = require("fs");
var path      = require("path");
var Sequelize = require("sequelize");
var config    = require('../config/config.js');
var sequelize = new Sequelize("mrdb", "root", "iamroot", {
        dialect: "mysql",
        port:    3303});
var db        = {};

fs
    .readdirSync(__dirname)
    .filter(function(file) {
        return (file.indexOf(".") !== 0) && (file !== "index.js");
    })
    .forEach(function(file) {
        var model = sequelize.import(path.join(__dirname, file));
        db[model.name] = model;
    });

Object.keys(db).forEach(function(modelName) {
    if ("associate" in db[modelName]) {
        db[modelName].associate(db);
    }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

This is almost all copy

After this, we need to prepare table model in models folder

MySQL and Models

No need to create schema from MySQL, MySQL Tool(Workbench)
Crete table schema from code(model)
We need to prepare table definition(model) file under models

module.exports = function(sequelize, DataTypes) {
    var User = sequelize.define("User", {  // user
        id: {
            type: DataTypes.BIGINT,
            autoIncrement: true,
            primaryKey: true
        },
        email: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: true
        },
        name: {
            type: DataTypes.STRING,
            unique: true
        }createdate: {
            type: DataTypes.DATE,
            defaultValue: DataTypes.NOW()
        }
    }, {
        tableName: 'user',
        timestamps: false,
        indexes: [
            {
                unique: true,
                fields: ['email']
            },
            {
                unique: true,
                fields: ['name']
            }
        ]
    });
    return User;
};

By default, MySQL Sequelize supports connection pooling.
If you want to set special for this, need to change parameters.

app.js

To read models file and table definition, we need to add some to app.js

var models = require("./models");

models.sequelize.sync().then(function () {
    var server = app.listen(app.get('port'), function() {
        console.log('Express server listening');
    });
});

In case of express, table was created when running application.
Following are SQL

CREATE TABLE IF NOT EXISTS `user` (`id` BIGINT auto_increment , 
`email` VARCHAR(255) NOT NULL UNIQUE, 
`name` VARCHAR(255) UNIQUE, `firstname` VARCHAR(255), 
`createdate` DATETIME,
`UserImageFkId` BIGINT, PRIMARY KEY (`id`))
`ENGINE=InnoDB;

Use it

This is an example

var models  = require('../models');

var name = "atmarkplant@gmail.com";
models.User.findOne({ where: {email: name} }).then(function(user){
    if (!user) {
      console.log("No User");
    }
    else {
      console.log("User was found");
    }
}).error(function(err){
  console.log("Error:" + err);
});