什么是ORM？
简单的讲就是对SQL查询语句的封装，让我们可以用OOP的方式操作数据库，优雅的生成安全、可维护的SQL代码。直观上，是一种Model和SQL的映射关系。
模型常用类型定义
BOOLEAN 布尔类型
STRING 字符串类型，类似varchar
CHAR 定长字符串
TEXT 文本类型
INTEGER 整数类型
FLOAT
DOUBLE
DECIMAL
DATE
ENUM 枚举类型
NOW 当前时间戳
UUID UUID类型列，其默认值可以为UUIDV1或UUIDV4
UUIDV1 v1：是基于时间的uuid，通过计算当前时间戳、随机数和机器MAC地址得到。
UUIDV2 v4：根据随机数，或者伪随机数生成UUID
uuid.v4().replace(/\-/g, '') //去除‘-’
模型字段验证
id: {
type: Sequelize.UUIDV4,
primaryKey: true, // 主键
allowNull: false, //不为空
// autoIncrement: true, //自增
unique: true,
defaultValue: NOW, //默认值的设置
comment:'', // 说明
validate: {
is: ["^[a-z]+$",'i'], // 只允许字母
is: /^[a-z]+$/i, // 只允许字母
not: ["[a-z]",'i'], // 不能使用字母
isEmail: true, // 检测邮箱格式 (foo@bar.com)
isUrl: true, // 检查Url格式 (http://foo.com)
isIP: true, // 检查 IPv4 或 IPv6 格式
isIPv4: true, // 检查 IPv4
isIPv6: true, // 检查 IPv6
isAlpha: true, // 不能使用字母
isAlphanumeric: true, // 只允许字母数字字符
isNumeric: true, // 只能使用数字
isInt: true, // 只能是整数
isFloat: true, // 只能是浮点数
isDecimal: true, // 检查数字
isLowercase: true, // 检查小写字母
isUppercase: true, // 检查大写字母
notNull: true, // 不允许null
isNull: true, // 只能为null
notEmpty: true, // 不能空字符串
equals: 'specific value', // 只能使用指定值
contains: 'foo', // 必须包含子字符串
notIn: [['foo', 'bar']], // 不能是数组中的任意一个值
isIn: [['foo', 'bar']], // 只能是数组中的任意一个值
notContains: 'bar', // 不能包含子字符串
len: [2, 10], // 值的长度必在 2 和 10 之间
isUUID: 4, // 只能是UUID
isDate: true, // 只能是日期字符串
isAfter: "2011-11-05", // 只能使用指定日期之后的时间
isBefore: "2011-11-05", // 只能使用指定日期之前的时间
max: 23, // 允许的最大值
min: 23, // 允许的最小值
isArray: true, // 不能使用数组
isCreditCard: true, // 检查是有效的信用卡
// 也可以自定义验证:
isEven: function(value) {
if(parseInt(value) % 2 != 0) {
throw new Error('Only even values are allowed!')
// we also are in the model's context here, so this.otherField
// would get the value of otherField if it existed
}
}
},
// 假设昵称后要加上 id 值
get() {
const id = this.getDataValue('id');
return this.getDataValue('nickName') + '-' + id;
},
// set 假设数据库中存储的邮箱都要是大写的，可以在此处改写
set(val) {
this.setDataValue('email', val.toUpperCase());
},
},详情看官方文档
模型使用
查询
- findById
await Role.findById(1);
- findByPk
await Role.findByPk(123)- findOne
await Role.findOne({
where: {
level: 1,
},
attributes: ['id', 'role_id']
});- findAndCountAll
await Role.findAndCountAll({
where: {
level: 1,
}
});- all()是 findAll 的别名
await Role.all({
where: {
level: {
[Op.gt]: 1,
// [Op.and]: {a: 5}, // AND (a = 5)
// [Op.or]: [{a: 5}, {a: 6}], // (a = 5 OR a = 6)
// [Op.gt]: 6, // id > 6
// [Op.gte]: 6, // id >= 6
// [Op.lt]: 10, // id < 10
// [Op.lte]: 10, // id <= 10
// [Op.ne]: 20, // id != 20
// [Op.between]: [6, 10], // BETWEEN 6 AND 10
// [Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
// [Op.in]: [1, 2], // IN [1, 2]
// [Op.notIn]: [1, 2], // NOT IN [1, 2]
// [Op.like]: '%hat', // LIKE '%hat'
// [Op.notLike]: '%hat', // NOT LIKE '%hat'
$and: {a: 5} // AND (a = 5)
$or: [{a: 5}, {a: 6}] // (a = 5 OR a = 6)
$gt: 6, // > 6
$gte: 6, // >= 6
$lt: 10, // < 10
$lte: 10, // <= 10
$ne: 20, // != 20
$not: true, // IS NOT TRUE
$between: [6, 10], // BETWEEN 6 AND 10
$notBetween: [11, 15], // NOT BETWEEN 11 AND 15
$in: [1, 2], // IN [1, 2]
$notIn: [1, 2], // NOT IN [1, 2]
$like: '%hat', // LIKE '%hat'
$notLike: '%hat' // NOT LIKE '%hat'
$iLike: '%hat' // ILIKE '%hat' (case insensitive) (PG only)
$notILike: '%hat' // NOT ILIKE '%hat' (PG only)
$like: { $any: ['cat', 'hat']}
// LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
$overlap: [1, 2] // && [1, 2] (PG array overlap operator)
$contains: [1, 2] // @> [1, 2] (PG array contains operator)
$contained: [1, 2] // <@ [1, 2] (PG array contained by operator)
$any: [2,3] // ANY ARRAY[2, 3]::INTEGER (PG only)
$col: 'user.organization_id' // = "user"."organization_id", with dialect specific column identifiers, PG in this example
}
},
limit: 3,
// 注意 raw, 默认为 false, 这时候 Sequelize 会为搜索出的每一条数据生成一个 Role 实例，用于更新，删除等操作
// 但当我们只想搜索出数据用于显示，并不想操作它，这个时候设置 raw: true 就会直接返回数据，而不会生成实例
raw: true,
});- count
await Role.count();
- findAndCountAll
await Role.findAndCountAll({
where: {
title: {
[Op.like]: 'foo%'
}
},
offset: 10,
limit: 2
})- findAndCountAll
await Role.findAndCountAll({
where: {
title: {
[Op.like]: 'foo%'
}
},
offset: 10,
limit: 2
})- 查询结果重命名
const user = await User.findOne({
where:{
id:userId
},
attributes:[['id','user_id'],['username','user_name']]
})- order
await Role.findAll({
'order': "id DESC"
});
Role.findAll({
order: [
// 将转义用户名，并根据有效的方向参数列表验证DESC
['title', 'DESC'],
// 将按最大值排序(age)
sequelize.fn('max', sequelize.col('age')),
// 将按最大顺序(age) DESC
[sequelize.fn('max', sequelize.col('age')), 'DESC'],
// 将按 otherfunction 排序(`col1`, 12, 'lalala') DESC
[sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
// 将使用模型名称作为关联的名称排序关联模型的 created_at。
[Task, 'createdAt', 'DESC'],
// Will order through an associated model's created_at using the model names as the associations' names.
[Task, Project, 'createdAt', 'DESC'],
// 将使用关联的名称由关联模型的created_at排序。
['Task', 'createdAt', 'DESC'],
// Will order by a nested associated model's created_at using the names of the associations.
['Task', 'Project', 'createdAt', 'DESC'],
// Will order by an associated model's created_at using an association object. (优选方法)
[Subtask.associations.Task, 'createdAt', 'DESC'],
// Will order by a nested associated model's created_at using association objects. (优选方法)
[Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],
// Will order by an associated model's created_at using a simple association object.
[{model: Task, as: 'Task'}, 'createdAt', 'DESC'],
// 嵌套关联模型的 created_at 简单关联对象排序
[{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
]
// 将按年龄最大值降序排列
order: sequelize.literal('max(age) DESC')
// 按最年龄大值升序排列，当省略排序条件时默认是升序排列
order: sequelize.fn('max', sequelize.col('age'))
// 按升序排列是省略排序条件的默认顺序
order: sequelize.col('age')
})- limit
// 获取10个实例/行
Project.findAll({ limit: 10 })- offset
// 跳过8个实例/行
Project.findAll({ offset: 8 })
- max
await Role.max('level', {
where: {
id: {
[Op.gt]: 5,
}
}
});- sum
await Role.sum('level');- COUNT,AVG,MAX,MIN,SUM等函数
// 可以使用sequelize.fn来执行这些函数
Role.findAll({
attributes: [[sequelize.fn('COUNT', sequelize.col('*')), 'total_count']],
});
// SELECT COUNT(*) AS `total_count` ...
Role.findAll({
attributes: [[sequelize.fn('COUNT', 1), 'total_count']],
});
// SELECT COUNT(1) AS `total_count` ...- OR写法
Role.findAll({
where: {
[Op.or]: [{ id: 2 }, { id: 3 }],
},
});
// ... where `role`.`id` = 2 OR `role`.`id` = 3
// 另一种写法
Role.findAll({
where: {
id: {
[Op.or]: [2, 3],
},
},
});
// ... where `role`.`id` = 2 OR `role`.`id` = 3
// 不同字段的写法
Role.findAll({
where: {
[Op.or]: [{ id: 2 }, { level: 3 }],
},
});
// ... where `role`.`id` = 2 OR `role`.`level` = 3
Role.findAll({
where: {
[Op.or]: [{ id: 2 }, { level: 3 }],
[Op.and]: [{ role_id: { [Op.ne]: 10001 } }],
},
});
// ... where (`role`.`id` = 2 OR `role`.`level` = 3) AND (`role`.`role_id` != 10001)- findOrCreate
await Role.findOrCreate({
where: {
role_name: 'alex'
},
defaults: {
role_id: 5,
role_name: 'alex',
level: 15,
},
});新建
- 创建一个没有写入数据库的实例，用save命令存入数据库中
const role = Role.build({
role_id: 1,
role_name: 'name-1'
});
await role.save(); // 在需要的时候调用 save 命令，可以将数据存入数据库中- 直接使用create命令直接创建一条写入数据库的数据
await Role.create({
role_id: 2,
role_name: 'name-2',
});- 批量创建实例
const l = [];
for (let i = 0; i < 5; i++) {
l.push({
role_id: 1000 + i,
role_name: `bulkname-${i}`,
level: i + 5,
});
}
const result = await Role.bulkCreate(l, {
// 这样创建语句中只有 role_id 和 role_name，会忽略 level
fields: ['role_id', 'role_name']
});更新
- 查询同时更新
await Role.update({
level: 4
}, {
where: {}
});
- 先查询，后更新
const role = await Role.findOne({
where: {}
});
await role.update({
// 注意这个role不是原型Role
level: 5
});增减
- 增加值使用increment, 减少值使用decrement，用法相同
// level加5
const role = await Role.findById(1);
await role.increment('level', {
by: 5
});删除
- 批量删除
await Role.destroy({
where: {
id: 1
}
});sequelize.query（原生sql）
默认情况下，函数将返回两个参数 - 一个结果数组，以及一个包含元数据（受影响的行等）的对象。 请注意，由于这是一个原始查询，所以元数据（属性名称等）是具体的方言。 某些方言返回元数据 "within" 结果对象（作为数组上的属性）。 但是，将永远返回两个参数，但对于MSSQL和MySQL，它将是对同一对象的两个引用。
const result1 = await mysql.query('SELECT id, img_url, url from carousel where status = 1');
//返回值是两个相同元素组成的数组
"result1": [
[
{
"id": 1,
"url": "/ClassDetail?id=4"
},
{
"id": 4,
"url": "/ClassDetail?id=2"
}
],
[
{
"id": 1,
"url": "/ClassDetail?id=4"
},
{
"id": 4,
"url": "/ClassDetail?id=2"
}
]
]解决办法有两种
- 传递一个查询类型来告诉后续如何格式化结果
const result= await mysql.query('SELECT id, img_url, url from carousel where status = 1', {
replacements: {},
type: mysql.QueryTypes.SELECT
});- 传递模型
const Carousel = require('../../models/Carousel');
const result2 = await mysql.query('SELECT id, img_url, url from carousel where status = 1',{ model: Carousel });替换
查询中的替换可以通过两种不同的方式完成：使用命名参数（以：开头），或者由？表示的未命名参数。 替换在options对象中传递。
- ?
如果传递一个数组, ? 将按照它们在数组中出现的顺序被替换
const banner2 = await mysql.query('SELECT id, img_url, url from carousel where id = ?', {
replacements: [1,4,5,6],
type: mysql.QueryTypes.SELECT,
});
// 返回的结果只有数组第一个元素下标对应的数据- ：
const banner3 = await mysql.query('SELECT id, img_url, url from carousel where id in (:[1,4,5,6])', {
replacements: {[1,4,5,6]},
type: mysql.QueryTypes.SELECT,
});
// 返回结果为符合数组的数据
// 拓展：模糊查询
sequelize.query('SELECT * FROM users WHERE name LIKE :search_name ',
{ replacements: { search_name: 'ben%' }, type: sequelize.QueryTypes.SELECT }
).then(projects => {
console.log(projects)
})
注意：
type: mysql.QueryTypes.SELECT
进行不同的原生操作是， QueryTypes的取值也不相同，这里误以为都是select坑死参考文档：
官方文档
Sequelize 中文文档 v4 - Raw queries - 原始查询
alex_my https://blog.csdn.net/alex_my...
查询结果
如果不想让它自动包装，可以在查询的时候，设置一个参数就会返回查询的源数据
Model.findAll({
raw: true, // 设置为 true，即可返回源数据
//Other parameters
});也可以在初始化 Sequelize 的时候直接全局设置：
const sequelize = new Sequelize('connectionUri', {
define: {
raw: true // 设置为 true，即可返回源数据
}
});
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。