Sequelize 是一款优秀的数据库 ORM 框架，支持 mysql、postgres、sqlite、mariadb、mssql。使用方法非常灵活多变，GitHub star 数目前 20k 左右，其周边工具 sequelize-auto 可自动从数据库生成模型文件，sequelize-cli 可以依据模型文件创建数据库，能力非常强大。
上篇文章讲解了 Sequelize 的基本使用，这篇文章从源码角度的解析 Sequelize，让大家用的明明白白！！
链接
以 mysql 为例
连接数据库
npm i -S sequelize mysql2
实例化 Sequelize 进行连接，参数在构造函数中配置
const Sequelize = require('sequelize');
// Option 1: Passing parameters separately
const sequelize = new Sequelize('database', 'username', 'password', {
host: 'localhost',
dialect: 'mysql',
// 额外的配置...
});
// Option 2: Passing a connection URI
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname');
Sequelize 构造函数解析
源码中 Sequelize 构造函数，列举常用的参数
databasestring数据库名username=nullstring用户名password=nullstring密码options={}Object配置options.host='localhost'stringhost 地址options.port=number数据库 serve 端口options.username=nullstring用户名，同上面用户名options.password=nullstring密码，同上面用户名，只需传其一options.database=nullstring数据库名，同上options.dialectstring要使用的数据库类型，目前支持mysql,postgres,sqlite,mssql.timezone='+00:00'string时区设置，默认中国时区需要变更为"+08:00"，如果有使用NOW函数一定要注意。The timezone used when converting a date from the database into a JavaScript date. The timezone is also used to SET TIMEZONE when connecting to the server, to ensure that the result of NOW, CURRENT_TIMESTAMP and other time related functions have in the right timezone. For best cross platform performance use the format +/-HH:MM. Will also accept string versions of timezones used by moment.js (e.g. 'America/Los_Angeles'); this is useful to capture daylight savings time changes.options.logging=console.logFunctionA function that gets executed every time Sequelize would log something.options.benchmark=falsebooleanPass query execution time in milliseconds as second argument to logging function (options.logging).options.replication=falsebooleanUse read / write replication. To enable replication, pass an object, with two properties, read and write. Write should be an object (a single server for handling writes), and read an array of object (several servers to handle reads). Each read/write server can have the following properties:host,port,username,password,databaseoptions.poolObject连接池配置options.pool.max=5number连接池最大连接数options.pool.min=0number连接池最小连接数options.pool.idle=10000numberThe maximum time, in milliseconds, that a connection can be idle before being released.闲置连接的最大存活时间options.pool.acquire=60000numberThe maximum time, in milliseconds, that pool will try to get connection before throwing error，出错之后再次连接的最长时间options.pool.evict=1000numberThe time interval, in milliseconds, after which sequelize-pool will remove idle connections.多长时间之后将会移除闲置连接options.operatorsAliasesObjectString based operator alias. Pass object to limit set of aliased operators.设置操作符别名options.hooksObject连接和断开数据库的一些钩子函数。 An object of global hook functions that are called before and after certain lifecycle events. Global hooks will run after any model-specific hooks defined for the same event (SeeSequelize.Model.init()for a list). Additionally,beforeConnect(),afterConnect(),beforeDisconnect(), andafterDisconnect()hooks may be defined here.
最简化的连接
// src/db/index.js
const Sequelize = require('sequelize');
const sequelize = new Sequelize('testdb', 'root', 'root', {
host: 'localhost',
port: 8889, // 默认是 3306，我的电脑设置的 8889
dialect: 'mysql',
});
module.exports = sequelize;
// App.js
const seq = require('./src/db');
seq
.authenticate()
.then(() => {
console.log('Connection has been established successfully.');
})
.catch(err => {
console.error('Unable to connect to the database:', err);
});
// node App.js
连接成功则打印如下
数据库模型
Sequelize 需要建立模型，才可以对数据库进行操作，对数据库的每个表建立模型文件太过繁琐。 可以使用 sequelize-auto 从数据库直接导出模型。
先创建数据库 testdb，执行 sql 语句自动建表 testdb。
安装 sequelize-auto
npm i -D sequelize-auto
自动从数据库导出模型
sequelize-auto 使用
[node] sequelize-auto -h <host> -d <database> -u <user> -x [password] -p [port] --dialect [dialect] -c [/path/to/config] -o [/path/to/models] -t [tableName] -C
Options:
-h, --host IP/Hostname for the database. [required]
-d, --database Database name. [required]
-u, --user Username for database.
-x, --pass Password for database.
-p, --port Port number for database.
-c, --config JSON file for Sequelize's constructor "options" flag object as defined here: https://sequelize.readthedocs.org/en/latest/api/sequelize/
-o, --output What directory to place the models.
-e, --dialect The dialect/engine that you're using: postgres, mysql, sqlite
-a, --additional Path to a json file containing model definitions (for all tables) which are to be defined within a model's configuration parameter. For more info: https://sequelize.readthedocs.org/en/latest/docs/models-definition/#configuration
-t, --tables Comma-separated names of tables to import
-T, --skip-tables Comma-separated names of tables to skip
-C, --camel Use camel case to name models and fields
-n, --no-write Prevent writing the models to disk.
-s, --schema Database schema from which to retrieve tables
-z, --typescript Output models as typescript with a definitions file.
简单配置
// package.json
"scripts": {
"sequelize": "sequelize-auto -o ./src/db/model -d testdb -h localhost -u root -p 8889 -x root -e mysql"
},
自动生成的模型如下
// src/db/model/blog.js
/* jshint indent: 2 */
module.exports = function(sequelize, DataTypes) {
return sequelize.define('blog', {
id: {
type: DataTypes.INTEGER(11),
allowNull: false,
primaryKey: true,
autoIncrement: true
},
school: {
type: DataTypes.STRING(255),
allowNull: true
},
name: {
type: DataTypes.STRING(255),
allowNull: true
}
}, {
tableName: 'blog',
timestamps: false,
});
};
// src/db/model/users.js
/* jshint indent: 2 */
module.exports = function(sequelize, DataTypes) {
return sequelize.define('users', {
id: {
type: DataTypes.INTEGER(11),
allowNull: false,
primaryKey: true,
autoIncrement: true
},
name: {
type: DataTypes.STRING(30),
allowNull: true
},
age: {
type: DataTypes.INTEGER(11),
allowNull: true
},
created_at: {
type: DataTypes.DATE,
allowNull: true
},
updated_at: {
type: DataTypes.DATE,
allowNull: true
}
}, {
tableName: 'users',
timestamps: false,
});
};
sequelize.define 与 model.init
源码中 define 定义如下，它实际就是 model.init，define 的三个参数被放到了 init 中。
define 上面有这句话，当 model 被 define 定义好之后，可以通过 sequelize.models.modelName 来执行数据库操作！！！
/**
* Define a new model, representing a table in the database.
*
* The table columns are defined by the object that is given as the second argument. Each key of the object represents a column
*
* @param {string} modelName The name of the model. The model will be stored in `sequelize.models` under this name
* @param {Object} attributes An object, where each attribute is a column of the table. See {@link Model.init}
* @param {Object} [options] These options are merged with the default define options provided to the Sequelize constructor and passed to Model.init()
*
* @see
* {@link Model.init} for a more comprehensive specification of the `options` and `attributes` objects.
* @see <a href="/manual/tutorial/models-definition.html">Model definition</a> Manual related to model definition
* @see
* {@link DataTypes} For a list of possible data types
*
* @returns {Model} Newly defined model
*
* @example
* sequelize.define('modelName', {
* columnA: {
* type: Sequelize.BOOLEAN,
* validate: {
* is: ["[a-z]",'i'], // will only allow letters
* max: 23, // only allow values <= 23
* isIn: {
* args: [['en', 'zh']],
* msg: "Must be English or Chinese"
* }
* },
* field: 'column_a'
* },
* columnB: Sequelize.STRING,
* columnC: 'MY VERY OWN COLUMN TYPE'
* });
*
* sequelize.models.modelName // The model will now be available in models under the name given to define
*/
define(modelName, attributes, options = {}) {
options.modelName = modelName;
options.sequelize = this;
const model = class extends Model {};
model.init(attributes, options);
return model;
}
模型字段定义 DataTypes
CHAR
Sequelize.CHAR(100) // CHAR(100)
Sequelize.STRING // VARCHAR(255)
Sequelize.STRING(1234) // VARCHAR(1234)
Sequelize.TEXT // TEXT
Sequelize.TEXT('tiny') // TINYTEXT
NUMBER
Sequelize.TINYINT // TINYINT
Sequelize.SMALLINT // SMALLINT
Sequelize.MEDIUMINT // MEDIUMINT
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.DOUBLE // DOUBLE
Sequelize.DOUBLE(11) // DOUBLE(11)
Sequelize.DOUBLE(11, 10) // DOUBLE(11,10)
Sequelize.DECIMAL // DECIMAL
Sequelize.DECIMAL(10, 2) // DECIMAL(10,2)
TIME
Sequelize.DATE // mysql / sqlite 为 DATETIME, postgres 为带时区的 TIMESTAMP
Sequelize.DATE
Sequelize.TIME
Sequelize.DATEONLY // DATE 不带时间.
BOOLEAN
Sequelize.BOOLEAN // TINYINT(1)
ENUM
Sequelize.ENUM('value 1', 'value 2') // 一个允许值为'value 1'和'value 2'的ENUM
blob
Sequelize.BLOB // BLOB (PostgreSQL 为 bytea)
Sequelize.BLOB('tiny') // TINYBLOB (PostgreSQL 为 bytea. 其余参数是 medium 和 long)
GEOMETRY
Sequelize.GEOMETRY // Spatial 列. 仅 PostgreSQL (带有 PostGIS) 或 MySQL.
Sequelize.GEOMETRY('POINT') // 带有 geometry 类型的 spatial 列. 仅 PostgreSQL (带有 PostGIS) 或 MySQL.
Sequelize.GEOMETRY('POINT', 4326) // 具有 geometry 类型和 SRID 的 spatial 列. 仅 PostgreSQL (带有 PostGIS) 或 MySQL.
integer, bigint, float 和 double 还支持 unsigned 和 zerofill 属性
Sequelize.INTEGER.UNSIGNED // INTEGER UNSIGNED
Sequelize.INTEGER(11).UNSIGNED // INTEGER(11) UNSIGNED
Sequelize.INTEGER(11).ZEROFILL // INTEGER(11) ZEROFILL
Sequelize.INTEGER(11).ZEROFILL.UNSIGNED // INTEGER(11) UNSIGNED ZEROFILL
Sequelize.INTEGER(11).UNSIGNED.ZEROFILL // INTEGER(11) UNSIGNED ZEROFILL
源代码 sequelize/lib/sequelize.js 中，将 DataTypes 整体导出的同时，还将所有 DataTypes 类型全部挂载在了 Sequelize 上，所以有以下两种使用方式
const Sequelize, { DataTypes } = require('sequelize')
// ...
created_at: {
type: Sequelize.DATE,
allowNull: true
}
// or
created_at: {
type: DataTypes.DATE,
allowNull: true
}
每个字段的所有定义方式如下
typestring|DataTypes字符串或者 DataTypeallowNull=trueboolean允许 NulldefaultValue=nullany字段默认值unique=falsestring|boolean是否唯一索引，If true, the column will get a unique constraint. If a string is provided, the column will be part of a composite unique index. If multiple columns have the same string, they will be part of the same unique indexprimaryKey=falseboolean是否为主键，If true, this attribute will be marked as primary keyautoIncrement=falseboolean自增，If true, this column will be set to auto incrementcomment=nullstring字段注释，Comment for this columnreferences=nullstring|ModelAn object with reference configurationsreferences.modelstring|ModelIf this column references another table, provide it here as a Model, or a stringreferences.key='id'stringThe column of the foreign table that this column referencesvalidateObject字段校验，见属性验证器
模型参数配置
sequelize 模型需要手动配置一些参数 Configuration
其他常用配置如下
timestamp=truebooleanSequelize 使用时会自动添加createdAt和updatedAt到模型中，如果表中没有这两个字段但是timestamp=true，则会报错，需要在模型定义中指定为 falsefreezeTableName=falsebooleanSequelize 默认会将所有的表名变更为复数，如果不想被自动变更，需要设置为 truemodelNamestring模型名，默认是类名，如下代码paranoid=falseboolean调用destroy不会删除记录，但是会设置deletedAt字段为当前的时间戳如果paranoid=true，需要timestamps=true才会走这个逻辑underscored=false``boolean不使用驼峰式命令规则，这样会在使用下划线分隔，updatedAt的字段名会是updated_attableNamestring表名，freezeTableName=false会让表名自动编程复数enginestring表的引擎，默认为InnoDBsequelizeObject给模型传入 sequelize 实例(new Sequelize(xxx))，不传入将会报错
class Bar extends Model {}
Bar.init({ /* bla */ }, {
// The name of the model. The model will be stored in `sequelize.models` under this name.
// This defaults to class name i.e. Bar in this case. This will control name of auto-generated
// foreignKey and association naming
modelName: 'bar',
// don't add the timestamp attributes (updatedAt, createdAt)
timestamps: false,
// don't delete database entries but set the newly added attribute deletedAt
// to the current date (when deletion was done). paranoid will only work if
// timestamps are enabled
paranoid: true,
// Will automatically set field option for all attributes to snake cased name.
// Does not override attribute with field option already defined
underscored: true,
// disable the modification of table names; By default, sequelize will automatically
// transform all passed model names (first parameter of define) into plural.
// if you don't want that, set the following
freezeTableName: true,
// define the table's name
tableName: 'my_very_custom_table_name',
// Enable optimistic locking. When enabled, sequelize will add a version count attribute
// to the model and throw an OptimisticLockingError error when stale instances are saved.
// Set to true or a string with the attribute name you want to use to enable.
version: true,
// Sequelize instance
sequelize,
})
注册 model 到 sequelize 实例
前面我们已经配置好了模型，建立好了数据连接，得到 sequelize 实例和模型文件，这步我们使用 sequelize.import 进行注册。
import 方法会对注册的 model 进行缓存，重复注册相同 model 不会有效果。它可以将 sequelize-auto 导出的 model 文件直接进行注册。
官方文档
源码定义，源码主要看 if (!this.importCache[importPath]) { 后面的，就是把 model 放到 this.importCache 上
我们来实战演示
基于数据库导出的 users 和 blog 两个 model，对 model 做以下更改，阻止默认行为
// src/db/model/blog.js
{
tableName: 'blog',
timestamps: false,
}
// src/db/model/users.js
{
tableName: 'users',
timestamps: false,
}
修改 index.js
// src/db/index.js
const Sequelize = require('sequelize');
const fs = require('fs');
const path = require('path');
const sequelize = new Sequelize('testdb', 'root', 'root', {
host: 'localhost',
port: 8889,
dialect: 'mysql',
});
// model 目录绝对路径
const modelPath = path.resolve(__dirname, './model');
// 读取所有 model 文件
const files = fs.readdirSync(modelPath);
const db = {};
// 将 model 挂到 db 上
files.forEach(fileName => {
const modelName = fileName.slice(0, -3);
db[modelName] = sequelize.import(path.resolve(modelPath, fileName));
});
module.exports = db;
App.js
const db = require('./src/db');
async function init() {
const users = await db.users.findAll();
const blog = await db.blog.findAll();
console.log(JSON.parse(JSON.stringify(users)));
console.log(JSON.parse(JSON.stringify(blog)));
}
init();
执行结果
更改 idle （闲置连接回收时间）
知道以上 sequelize 注册方式之后，可以很容易地把它嵌入到现有的 nodejs 框架中，挂载到上下文对象上即可在各个地方访问。
数据库操作
操作方法
增
create
创建一条记录，并返回这条数据库记录
public static create(values: Object, options: Object): Promise<Model>
src/db/model/users.js 更改如下
created_at: {
type: DataTypes.DATE,
allowNull: true,
defaultValue: sequelize.fn('NOW') // 填充默认时间为现在
},
updated_at: {
type: DataTypes.DATE,
allowNull: true,
defaultValue: sequelize.fn('NOW') // 填充默认时间为现在
}
// App.js
const users = await db.users.create({
name: 'lxfriday',
age: 33,
});
console.log(JSON.parse(JSON.stringify(users)));
插入之后返回记录
注意时区问题，在本篇文章最开始连接数据库的时候 options.timezone 设为 +08:00 即可变更为中国时间。
bulkCreate
创建多条记录，并返回一个一个对象数组，每个对象是一条记录
public static bulkCreate(records: Array, options: Object): Promise<Array<Model>>
const users = await db.users.bulkCreate([
{
name: 'lxfriday1',
age: 111,
},
{
name: 'lxfriday2',
age: 222,
},
{
name: 'lxfriday3',
age: 338,
},
]);
findOrCreate
先查找，没有找到则依据条件进行创建
public static findOrCreate(options: Object): Promise<Model, boolean>
const users = await db.users.findOrCreate({
where: {
name: 'lxfridaysss',
age: 3399,
},
});
const users2 = await db.users.findOrCreate({
where: {
name: 'lxfridaywww',
},
defaults: {
age: 3399,
}
});
// 会自动合并成{name,age}
没有找到的时候，会自动将 where 和 defaults 合并作为新的数据源
查
findAll
按条件查询所有
public static findAll(options: Object): Promise<Array<Model>>
主要注意查询条件和查询字段，操作符从 Sequelize.Op 导出。
whereObject查询条件attributesArray<string> | Object查询的字段attributes.includeArray<string>要包括的字段，同直接给attributes赋值attributes.excludeArray<string>不要要包括的字段orderArray | fn | col | literal排序，[['id', 'DESC'], 'age']
['age', 'DESC'],
// ['id', 'ASC'],
'id',// 同上
limitnumber限制查询条数offsetnumber偏移量
await db.users.findAll({
where: {
attr1: 42,
attr2: 'cake'
}
})
// WHERE attr1 = 42 AND attr2 = 'cake'
const users = await db.users.findAll({
attributes: {
exclude: ['name']
},
where: {
name: {
[like]: '%lxfriday%',
},
},
order: [
['age', 'DESC'],
['id', 'ASC'],
// 'id',
],
});
执行语句和结果
const {gt, lte, ne, in: opIn} = Sequelize.Op;
await db.users.findAll({
where: {
attr1: {
[gt]: 50
},
attr2: {
[lte]: 45
},
attr3: {
[opIn]: [1,2,3]
},
attr4: {
[ne]: 5
}
}
})
// WHERE attr1 > 50 AND attr2 <= 45 AND attr3 IN (1,2,3) AND attr4 != 5
const {or, and, gt, lt} = Sequelize.Op;
await db.users.findAll({
where: {
name: 'a project',
[or]: [
{id: [1, 2, 3]},
{
[and]: [
{id: {[gt]: 10}},
{id: {[lt]: 100}}
]
}
]
}
});
// WHERE `Model`.`name` = 'a project' AND (`Model`.`id` IN (1, 2, 3) OR (`Model`.`id` > 10 AND `Model`.`id` < 100));
findOne
查找一条，是 findAll({limit: 1, ...}) 版本，配置见 findAll
public static findOne(options: Object): Promise<Model>
findByPk
public static findByPk(param: number | string | Buffer, options: Object): Promise<Model>
按主键进行查询，主键可以是任何任意字段和类型，不仅仅是 id 才能当主键
blog_url 是主键
findAndCountAll
查到并统计结果数
public static findAndCountAll(options: Object): Promise<{count: number, rows: Model[]}>
改
update
更改数据
public static update(values: Object, options: Object): Promise<Array<number, number>>
返回一个数组，数组的参数是被修改的条数
upsert
更新，不存在则创建一条记录
public static upsert(values: Object, options: Object): Promise<boolean>
删
public static destroy(options: Object): Promise<number>
统计
count
public static count(options: Object): Promise<number>
min、max、sum
field 要查询的字段
public static min(field: string, options: Object): Promise<*>
聚合查询
aggregate
public static aggregate(attribute: string, aggregateFunction: string, options: Object): Promise<DataTypes|Object>
操作符
从 sequelize.Op 导出，
const Op = Sequelize.Op
[Op.and]: {a: 5} // 且 (a = 5)
[Op.or]: [{a: 5}, {a: 6}] // (a = 5 或 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.eq]: 3, // = 3
[Op.not]: true, // 不是 TRUE
[Op.between]: [6, 10], // 在 6 和 10 之间
[Op.notBetween]: [11, 15], // 不在 11 和 15 之间
[Op.in]: [1, 2], // 在 [1, 2] 之中
[Op.notIn]: [1, 2], // 不在 [1, 2] 之中
[Op.like]: '%hat', // 包含 '%hat'
[Op.notLike]: '%hat' // 不包含 '%hat'
[Op.iLike]: '%hat' // 包含 '%hat' (不区分大小写) (仅限 PG)
[Op.notILike]: '%hat' // 不包含 '%hat' (仅限 PG)
[Op.startsWith]: 'hat' // 类似 'hat%'
[Op.endsWith]: 'hat' // 类似 '%hat'
[Op.substring]: 'hat' // 类似 '%hat%'
[Op.regexp]: '^[h|a|t]' // 匹配正则表达式/~ '^[h|a|t]' (仅限 MySQL/PG)
[Op.notRegexp]: '^[h|a|t]' // 不匹配正则表达式/!~ '^[h|a|t]' (仅限 MySQL/PG)
[Op.iRegexp]: '^[h|a|t]' // ~* '^[h|a|t]' (仅限 PG)
[Op.notIRegexp]: '^[h|a|t]' // !~* '^[h|a|t]' (仅限 PG)
[Op.like]: { [Op.any]: ['cat', 'hat']} // 包含任何数组['cat', 'hat'] - 同样适用于 iLike 和 notLike
[Op.overlap]: [1, 2] // && [1, 2] (PG数组重叠运算符)
[Op.contains]: [1, 2] // @> [1, 2] (PG数组包含运算符)
[Op.contained]: [1, 2] // <@ [1, 2] (PG数组包含于运算符)
[Op.any]: [2,3] // 任何数组[2, 3]::INTEGER (仅限PG)
[Op.col]: 'user.organization_id' // = 'user'.'organization_id', 使用数据库语言特定的列标识符, 本例使用 PG
广告时间
欢迎关注，每日进步！！！
