野猪佩奇
Node.js 教程收录了这篇文章
一，简介
ORM 是"对象-关系映射"（Object/Relational Mapping） 的缩写，就是通过实例对象的语法，完成关系型数据库的操作的技术。面向对象编程把所有实体看成对象（object），关系型数据库则是采用实体之间的关系（relation）连接数据。很早就有人提出，关系也可以用对象表达，这样的话，就能使用面向对象编程，来操作关系型数据库。
ORM 把数据库映射成对象：
数据库的表（table） --> 类（class） 记录（record，行数据）--> 对象（object） 字段（field）--> 对象的属性（attribute）
二，举例
举例来说，下面是一行 SQL 语句:
SELECT id, first_name, last_name, phone, birth_date, sex FROM persons WHERE id = 10
程序直接运行 SQL，操作数据库的写法如下:
res = db.execSql(sql); name = res[0]["FIRST_NAME"];
改成 ORM 的写法如下:
p = Person.findOne({ where: { title: 'My Title' } });
p = await Person.findByPk(123); // 根据主键获取一条数据
name = p.first_name;
一比较就可以发现，ORM 使用对象，封装了数据库操作，因此可以不碰 SQL 语言。开发者只使用面向对象编程，与数据对象直接交互，不用关心底层数据库。
三，ORM的优缺点
ORM 的优点:
1.数据模型都在一个地方定义，更容易更新和维护，也利于重用代码。
2.ORM 有现成的工具，很多功能都可以自动完成，比如数据消毒、预处理、事务等等。
3.它迫使你使用 MVC 架构，ORM 就是天然的 Model，最终使代码更清晰。
4基于 ORM 的业务代码比较简单，代码量少，语义性好，容易理解。
5.你不必编写性能不佳的 SQL。
ORM 的缺点：
1.ORM 库不是轻量级工具，需要花很多精力学习和设置。
2.对于复杂的查询，ORM 要么是无法表达，要么是性能不如原生的 SQL。
3.ORM 抽象掉了数据库层，开发者无法了解底层的数据库操作，也无法定制一些特殊的 SQL。
四，Sequelize 教程
1，创建一个sequelize对象实例：
const Sequelize = require('sequelize');
const config = require('./config');
var sequelize = new Sequelize(
config.database,
config.username,
config.password,
{
host: config.host,
dialect: 'mysql',
pool: { max: 5,min: 0,idle: 30000}
});
2，定义模型Pet，告诉Sequelize如何映射数据库表：
var Pet = sequelize.define('pet', {
id: { type: Sequelize.STRING(50), primaryKey: true},
name: Sequelize.STRING(100),
gender: Sequelize.BOOLEAN,
birth: Sequelize.STRING(10),
createdAt: Sequelize.BIGINT,
updatedAt: Sequelize.BIGINT,
version: Sequelize.BIGINT
},
{
timestamps: false,
indexes: [{ unique: true, fields: ['someUnique'] }] //添加索引
});
用sequelize.define()定义Model时，传入名称pet，默认的表名就是pets。第二个参数指定列名和数据类型，如果是主键，需要更详细地指定。第三个参数是额外的配置，我们传入{ timestamps: false }是为了关闭Sequelize的自动添加timestamp的功能。所有的ORM框架都有一种很不好的风气，总是自作聪明地加上所谓“自动化”的功能，但是会让人感到完全摸不着头脑。
我们把通过sequelize.define()返回的Pet称为Model，它表示一个数据模型。
我们把通过Pet.findAll()返回的一个或一组对象称为Model实例，每个实例都可以直接通过JSON.stringify序列化为JSON字符串。但是它们和普通JSON对象相比，多了一些由Sequelize添加的方法，比如save()和destroy()。调用这些方法我们可以执行更新或者删除操作。
可以自定义表名，在第三个参数中，加入属性 freezeTableName: true。
3，数据操作
3.1 添加数据，我们可以用Promise的方式写：
var now = Date.now();
Pet.create({
id: 'g-' + now,
name: 'Gaffey',
gender: false,
birth: '2007-07-07',
createdAt: now,
updatedAt: now,
version: 0
}).then(function (p) {
console.log('created.' + JSON.stringify(p));
}).catch(function (err) {
console.log('failed: ' + err);
});
也可以用await写：
(async () => {
var dog = await Pet.create({
id: 'd-' + now,
name: 'Odie',
gender: false,
birth: '2008-08-08',
createdAt: now,
updatedAt: now,
version: 0
});
console.log('created: ' + JSON.stringify(dog));
})();
返回创建的用户数据。
3.2 查询数据时，用await写法如下：
var pets = await Pet.findAll({
where: {name: 'Gaffey'}
});
console.log(`find ${pets.length} pets:`);
for (let p of pets) {
console.log(JSON.stringify(p));
}
findOne() 查询成功返回一个对象，查询失败返回null；findAll() 查询成功返回数组，每个数组是一条记录所组成的对象。查询失败返回空数组。
3.3 更新数据
// 可以对查询到的实例调用save()方法 var p = await queryFromSomewhere(); p.gender = true; p.updatedAt = Date.now(); p.version ++; await p.save();
// 更新id是86的记录，把is_show设置为“1”
await Article.update({ is_show: "1" }, {
where: {id: 86}
});
update 返回一个数组，包含一个或者两个元素，第一个元素始终是表中被影响的行数。如果，更新的内容和表中原数据一致，被影响条数会是0。
3.4 删除数据
// 对查询到的实例调用destroy()方法 var p = await queryFromSomewhere(); await p.destroy();
// 使用 option.where 过滤需要删除的记录，返回一个 int 类型变量，被删除的行数
var rows = await articleModel.destory({
where: {id: 86}
});
3.5 Where 查询条件基本用法
const Op = Sequelize.Op;
Post.findAll({
where: {
authorId: 2
}
});
// SELECT * FROM post WHERE authorId = 2
Post.findAll({
where: {
authorId: 12,
status: 'active'
}
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';
Post.findAll({
where: {
[Op.or]: [{authorId: 12}, {authorId: 13}]
}
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;
Post.findAll({
where: {
authorId: {
[Op.or]: [12, 13]
}
}
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;
Post.destroy({
where: {
status: 'inactive'
}
});
// DELETE FROM post WHERE status = 'inactive';
Post.update({
updatedAt: null,
}, {
where: {
deletedAt: {
[Op.ne]: null
}
}
});
// UPDATE post SET updatedAt = null WHERE deletedAt NOT NULL;
Post.findAll({
where: sequelize.where(sequelize.fn('char_length', sequelize.col('status')), 6)
});
// SELECT * FROM post WHERE char_length(status) = 6;
3.6 Where 查询条件运算符
const Op = Sequelize.Op
[Op.and]: {a: 5} // AND (a = 5)
[Op.or]: [{a: 5}, {a: 6}] // (a = 5 OR a = 6)
[Op.gt]: 6, // > 6
[Op.gte]: 6, // >= 6
[Op.lt]: 10, // < 10
[Op.lte]: 10, // <= 10
[Op.ne]: 20, // != 20
[Op.eq]: 3, // = 3
[Op.not]: true, // IS NOT TRUE
[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'
[Op.iLike]: '%hat' // ILIKE '%hat' (case insensitive) (PG only)
[Op.notILike]: '%hat' // NOT ILIKE '%hat' (PG only)
[Op.startsWith]: 'hat' // LIKE 'hat%'
[Op.endsWith]: 'hat' // LIKE '%hat'
[Op.substring]: 'hat' // LIKE '%hat%'
[Op.regexp]: '^[h|a|t]' // REGEXP/~ '^[h|a|t]' (MySQL/PG only)
[Op.notRegexp]: '^[h|a|t]' // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)
[Op.iRegexp]: '^[h|a|t]' // ~* '^[h|a|t]' (PG only)
[Op.notIRegexp]: '^[h|a|t]' // !~* '^[h|a|t]' (PG only)
[Op.like]: { [Op.any]: ['cat', 'hat']}
// LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
[Op.overlap]: [1, 2] // && [1, 2] (PG array overlap operator)
[Op.contains]: [1, 2] // @> [1, 2] (PG array contains operator)
[Op.contained]: [1, 2] // <@ [1, 2] (PG array contained by operator)
[Op.any]: [2,3] // ANY ARRAY[2, 3]::INTEGER (PG only)
[Op.col]: 'user.organization_id' // = "user"."organization_id", with dialect specific column identifiers, PG in this example
3.7 查询组合
const Op = Sequelize.Op;
{
rank: {
[Op.or]: {
[Op.lt]: 1000,
[Op.eq]: null
}
}
}
// rank < 1000 OR rank IS NULL
{
createdAt: {
[Op.lt]: new Date(),
[Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000)
}
}
// createdAt < [timestamp] AND createdAt > [timestamp]
{
[Op.or]: [
{
title: {
[Op.like]: 'Boat%'
}
},
{
description: {
[Op.like]: '%boat%'
}
}
]
}
// title LIKE 'Boat%' OR description LIKE '%boat%'
3.8 Pagination / Limiting 分页和限数
// Fetch 10 instances/rows
Project.findAll({ limit: 10 })
// Skip 8 instances/rows
Project.findAll({ offset: 8 })
// Skip 5 instances and fetch the 5 after that
Project.findAll({ offset: 5, limit: 5 })
3.9 Order 排序
Subtask.findAll({
order: [
// Will escape title and validate DESC against a list of valid direction parameters
['title', 'DESC'],
// Will order by max(age)
sequelize.fn('max', sequelize.col('age')),
// Will order by max(age) DESC
[sequelize.fn('max', sequelize.col('age')), 'DESC'],
// Will order by otherfunction(`col1`, 12, 'lalala') DESC
[sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
// Will order an associated model's created_at using the model name as the association's name.
[Task, 'createdAt', 'DESC'],
// Will order through an associated model's created_at using the model names as the associations' names.
[Task, Project, 'createdAt', 'DESC'],
// Will order by an associated model's created_at using the name of the association.
['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. (preferred method)
[Subtask.associations.Task, 'createdAt', 'DESC'],
// Will order by a nested associated model's created_at using association objects. (preferred method)
[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'],
// Will order by a nested associated model's created_at simple association objects.
[{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
]
// Will order by max age descending
order: sequelize.literal('max(age) DESC')
// Will order by max age ascending assuming ascending is the default order when direction is omitted
order: sequelize.fn('max', sequelize.col('age'))
// Will order by age ascending assuming ascending is the default order when direction is omitted
order: sequelize.col('age')
// Will order randomly based on the dialect (instead of fn('RAND') or fn('RANDOM'))
order: sequelize.random()
})
3.10 筛选字段
Model.findAll({
attributes: ['foo', 'bar']
});
//等同于 SELECT foo, bar ...
Model.findAll({
attributes: ['foo', ['bar', 'baz']]
});
//等同于SELECT foo, bar AS baz
3.11 实用方法
// 返回ID大于25的数量
await Project.count({
where: {id: {[Op.gt]: 25}}
});
await User.max('age'); // 最大年龄
await User.min('age'); // 最小年龄
await User.sum('age'); // 年龄总和
五，执行原生SQL语句
1. 查询结构
sequelize.query('your query', [, options])
// 例子
sequelize.query("SELECT * FROM myTable").then(myTableRows => {
console.log(myTableRows)
})
2. 防注入参数绑定
当SQL语句中的占位符是“？”时，需要传入数组：
sequelize
.query(
'SELECT * FROM projects WHERE status = ?',
{ raw: true, replacements: ['active'] }
)
.then(projects => {
console.log(projects)
})
当SQL语句中的占位符是“:keyword”时，需要传入对象：
sequelize
.query(
'SELECT * FROM projects WHERE status = :status ',
{ raw: true, replacements: { status: 'active' } }
)
.then(projects => {
console.log(projects)
})
默认情况下，函数将返回两个参数 : 一个结果数组，以及一个包含元数据（受影响的行等）的对象。 请注意，由于这是一个原始查询，所以元数据（属性名称等）是具体的方言。 某些方言返回元数据 "within" 结果对象（作为数组上的属性）。 但是，将永远返回两个参数，但对于MSSQL和MySQL，它将是对同一对象的两个引用。
sequelize.query("UPDATE users SET y = 42 WHERE x = 12").spread((results, metadata) => {
// 结果将是一个空数组，元数据将包含受影响的行数。
})
在不需要访问元数据的情况下，您可以传递一个查询类型来告诉后续如何格式化结果。 例如，对于一个简单的选择查询你可以做：
sequelize.query("SELECT * FROM `users`", { type: sequelize.QueryTypes.SELECT})
.then(users => {
// 我们不需要在这里延伸，因为只有结果将返回给选择查询
})
参考： https://segmentfault.com/a/1190000011583833
另外一种原生查询是使用node-mysql ,参考 https://javascript.net.cn/article?id=343
六，数据类型
Sequelize支持的数据类型：
Sequelize.STRING // VARCHAR(255)
Sequelize.STRING(1234) // VARCHAR(1234)
Sequelize.STRING.BINARY // VARCHAR BINARY
Sequelize.TEXT // TEXT
Sequelize.TEXT('tiny') // TINYTEXT
Sequelize.CITEXT // CITEXT PostgreSQL and SQLite only.
Sequelize.INTEGER // INTEGER
Sequelize.BIGINT // BIGINT
Sequelize.BIGINT(11) // BIGINT(11)
Sequelize.FLOAT // FLOAT
Sequelize.FLOAT(11) // FLOAT(11)
Sequelize.FLOAT(11, 10) // FLOAT(11,10)
Sequelize.REAL // REAL PostgreSQL only.
Sequelize.REAL(11) // REAL(11) PostgreSQL only.
Sequelize.REAL(11, 12) // REAL(11,12) PostgreSQL only.
Sequelize.DOUBLE // DOUBLE
Sequelize.DOUBLE(11) // DOUBLE(11)
Sequelize.DOUBLE(11, 10) // DOUBLE(11,10)
Sequelize.DECIMAL // DECIMAL
Sequelize.DECIMAL(10, 2) // DECIMAL(10,2)
Sequelize.DATE // DATETIME for mysql / sqlite, TIMESTAMP WITH TIME ZONE for postgres
Sequelize.DATE(6) // DATETIME(6) for mysql 5.6.4+. Fractional seconds support with up to 6 digits of precision
Sequelize.DATEONLY // DATE without time.
Sequelize.BOOLEAN // TINYINT(1)
Sequelize.ENUM('value 1', 'value 2') // An ENUM with allowed values 'value 1' and 'value 2'
Sequelize.ARRAY(Sequelize.TEXT) // Defines an array. PostgreSQL only.
Sequelize.ARRAY(Sequelize.ENUM) // Defines an array of ENUM. PostgreSQL only.
Sequelize.JSON // JSON column. PostgreSQL, SQLite and MySQL only.
Sequelize.JSONB // JSONB column. PostgreSQL only.
Sequelize.BLOB // BLOB (bytea for PostgreSQL)
Sequelize.BLOB('tiny') // TINYBLOB (bytea for PostgreSQL. Other options are medium and long)
Sequelize.UUID // UUID datatype for PostgreSQL and SQLite, CHAR(36) BINARY for MySQL (use defaultValue: Sequelize.UUIDV1 or Sequelize.UUIDV4 to make sequelize generate the ids automatically)
Sequelize.CIDR // CIDR datatype for PostgreSQL
Sequelize.INET // INET datatype for PostgreSQL
Sequelize.MACADDR // MACADDR datatype for PostgreSQL
Sequelize.RANGE(Sequelize.INTEGER) // Defines int4range range. PostgreSQL only.
Sequelize.RANGE(Sequelize.BIGINT) // Defined int8range range. PostgreSQL only.
Sequelize.RANGE(Sequelize.DATE) // Defines tstzrange range. PostgreSQL only.
Sequelize.RANGE(Sequelize.DATEONLY) // Defines daterange range. PostgreSQL only.
Sequelize.RANGE(Sequelize.DECIMAL) // Defines numrange range. PostgreSQL only.
Sequelize.ARRAY(Sequelize.RANGE(Sequelize.DATE)) // Defines array of tstzrange ranges. PostgreSQL only.
Sequelize.GEOMETRY // Spatial column. PostgreSQL (with PostGIS) or MySQL only.
Sequelize.GEOMETRY('POINT') // Spatial column with geometry type. PostgreSQL (with PostGIS) or MySQL only.
Sequelize.GEOMETRY('POINT', 4326) // Spatial column with geometry type and SRID. PostgreSQL (with PostGIS) or MySQL only.
更多数据类型：http://docs.sequelizejs.com/manual/data-types.html
七，使用 association 连接表
连接查询时，如果要连接查询的两个模型间事先没有定义连接关系，或者要使用定义之外的连接关系。这时，可以通过association来定义或重新定义模型关系。
var assets = await assetModel.findAll({
include: [{
association: assetModel.belongsTo(userModel, { foreignKey: 'uuid', targetKey: 'uuid' }),
attributes: [['nickname', 'nickname'], 'uuid', 'avatar']
}],
where,
offset: start,
limit: perPage,
order: [['id', 'desc']]
});
或者查询 Company 模型中的任意一个公司，并查询该公司的管理员：
var include = [{
association: Company.hasOne(User, {foreignKey:'companyId', as:'manager'}),
where: {isManager:true}
}]
Company.findOne({include:include})
八，模型同步
定义模型时,你要告诉 Sequelize 有关数据库中表的一些信息. 但是,如果该表实际上不存在于数据库中怎么办？ 如果存在,但具有不同的列,较少的列或任何其他差异,该怎么办？
可以通过调用一个异步函数(返回一个Promise)model.sync(options). 通过此调用, Sequelize 将自动对数据库执行 SQL 查询. 请注意,这仅更改数据库中的表,而不更改 JavaScript 端的模型.
User.sync() - 如果表不存在,则创建该表(如果已经存在,则不执行任何操作)
User.sync({ force: true }) - 将创建表,如果表已经存在,则将其首先删除
User.sync({ alter: true }) - 这将检查数据库中表的当前状态，并修改表的结构
sequelize.sync() 自动同步所有模型
User.drop() 删除与模型相关的表
sequelize.drop() 删除所有表
参考：
http://www.ruanyifeng.com/blog/2019/02/orm-tutorial.html
https://www.liaoxuefeng.com
http://docs.sequelizejs.com/manual/querying.html
http://docs.sequelizejs.com/manual/usage.html#executing-raw-sql-queries
https://github.com/demopark/sequelize-docs-Zh-CN/blob/master/core-concepts/model-basics.md
https://itbilu.com/nodejs/npm/EkWJSmmFf.html
修改时间 2024-05-29