精华
SQL to Sequelize Mapping Chart
因为最近都在看 Sequelize 相关的一些文档, 为了备忘, 我自己整理了一下 SQL 到 Sequelize 的转换对应表.
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. :) 持续更新链接 SQL to Sequelize Mapping Chart
12 回复
我用 knex
@xieren58 有点小众啊
我用 bearcat-dao
ORM 我目前一直在测试自己写的 Toshihiko,最近一直处于测试阶段,已经用于一些脚本处理了(炮灰)。
我觉得可以列一个sequelize跳坑指南,上个项目踩了不下三个
用这些千万要弄明白业务对象之间的关系,而不是表与表之间的关系
你好,sequelize事务怎么用
orm 会对应用程序性能 影响大么?
请问一下,为什么可以和yield配合使用呢,sequelize做了什么?
@Summerlve promise而已
sequelize 坑太多,用了一段时间 bug 百出,最后放弃了
卧槽居然是有人挖坟