Home | Prev

SQL to Sequelize Mapping Chart

I like SQL to MongoDB Mapping Chart post, so I create the same chart between SQL and Sequelize.

All demo runable codes at sequelize.js.

Create Table

SQL: CREATE TABLE

CREATE TABLE IF NOT EXISTS `user` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
 `gmt_create` datetime NOT NULL COMMENT 'create time',
 `gmt_modified` datetime NOT NULL COMMENT 'modified time',
 `name` varchar(100) NOT NULL COMMENT 'user name',
 `ip` varchar(64) NOT NULL COMMENT 'user last request ip',
 `npm_user` tinyint(1) DEFAULT '0' COMMENT 'user sync from npm or not, 1: true, other: false',
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`),
 KEY `gmt_modified` (`gmt_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='user base info';

Sequelize: sequelize.define()

var User = sequelize.define('User', {
  name: {
    type: Sequelize.STRING(100),
    allowNull: false,
    comment: 'user name',
  },
  ip: {
    type: Sequelize.STRING(64),
    allowNull: false,
    comment: 'user last request ip',
  },
  isNpmUser: {
    field: 'npm_user',
    type: Sequelize.BOOLEAN,
    allowNull: false,
    defaultValue: false,
    comment: 'user sync from npm or not, 1: true, other: false',
  }
}, {
  tableName: 'user',
  comment: 'user base info',
  indexes: [
    {
      unique: true,
      fields: ['name']
    },
    {
      fields: ['gmt_modified']
    }
  ],
  createdAt: 'gmt_create',
  updatedAt: 'gmt_modified',
  charset: 'utf8',
  collate: 'utf8_general_ci',
});

Insert

SQL: INSERT INTO

INSERT INTO user(name, ip, gmt_create, gmt_modified)
  VALUES ("fengmk2", "127.0.0.1", now(), now());

Sequelize: sequelize.build() and sequelize.save()

var user = yield User.build({
  name: 'fengmk2',
  ip: '127.0.0.1'
}).save();

Select

User.findAll({
  where: {
    id: {
      gt: 6,              // id > 6
      gte: 6,             // id >= 6
      lt: 10,             // id < 10
      lte: 10,            // id <= 10
      ne: 20,             // id != 20
      between: [6, 10],   // BETWEEN 6 AND 10
      nbetween: [11, 15]  // NOT BETWEEN 11 AND 15
    }
  }
})

Select all

• SQL

SELECT * FROM user;

• Sequelize

var users = yield User.findAll();

Select one row with all columns by primary id key

• SQL

SELECT * FROM user WHERE id = 1;

• Sequelize

var oneUser = yield User.find(1);

Select one row with all columns by name index

• SQL

SELECT * FROM user WHERE name = "fengmk2" LIMIT 1;

• Sequelize

var user = yield User.find({
  where: {
    name: 'fengmk2'
  }
});

Select one row with some columns by name index

• SQL

SELECT name, ip FROM user WHERE name = "fengmk2";

• Sequelize

var user = yield User.find({
  attributes: ['name', 'ip'],
  where: {
    name: 'fengmk2'
  }
});

Select rows where name not equal 'fengmk2'

• SQL

SELECT * FROM user WHERE name != "fengmk2";

• Sequelize

var users = yield User.findAll({
  where: {
    name: {
      ne: 'fengmk2'
    }
  }
});

Select rows where name not equal 'fengmk2' and id bigger than 100

• SQL

SELECT * FROM user WHERE name != "fengmk2" AND id > 2;

• Sequelize

var users = yield User.findAll({
  where: {
    name: {
      ne: 'fengmk2'
    },
    id: {
      gt: 2
    }
  }
});

Select rows where name equal 'fengmk2' or 'yubo'

• SQL

SELECT * FROM user WHERE name = "fengmk2" OR name = "yubo";

• Sequelize

var users = yield User.findAll({
  where: Sequelize.or(
    {
      name: 'fengmk2'
    },
    {
      name: 'yubo'
    }
  )
});

Select rows where name start with 'feng*'

• SQL

SELECT * FROM user WHERE name LIKE 'feng%';

• Sequelize

var users = yield User.findAll({
  where: {
    name: {
      like: 'feng%'
    }
  }
});

Select rows where name contains 'mk' and order by id desc

• SQL

SELECT * FROM user WHERE name LIKE '%mk%' ORDER BY id DESC;

• Sequelize

var users = yield User.findAll({
  where: {
    name: {
      like: '%mk%'
    }
  },
  order: [ [ 'id', 'desc' ] ]
});

Select COUNT(*)

• SQL

SELECT COUNT(*) FROM user;

• Sequelize

var count = yield User.count();

Select rows count where name contains %y%

• SQL

SELECT COUNT(*) FROM user WHERE name LIKE '%y%';

• Sequelize

var count = yield User.count({
  where: {
    name: {
      like: '%y%'
    }
  }
});

Select distinct ip count

• SQL

SELECT COUNT(DISTINCT(ip)) FROM user;

• Sequelize

TODO

Update

SQL: UPDATE SET

UPDATE user SET ip = '10.0.0.1', npm_user = 0, gmt_modified = now() WHERE id = 1;
UPDATE user SET npm_user = 1, gmt_modified = now() WHERE id = 2;

Sequelize: find() and save([columns])

var user = yield User.find(1);
user.ip = '10.0.0.1';
user.isNpmUser = false;
yield user.save(['ip', 'isNpmUser']);

var user = yield User.find(2);
user.isNpmUser = true;
yield user.save(['isNpmUser']);

Delete

SQL: DELETE FROM

DELETE FROM user WHERE id = 1;
DELETE FROM user;

Sequelize: Model.destroy(where)

yield User.destroy({
  id: 1
});

yield User.destroy();

Love 💗

Hold these can help you a litte bit on coding. :)

Comments