我报名参加金石计划1期挑战——瓜分10万奖池，这是我的第2篇文章，点击查看活动详情
基础
依赖包
npm init egg
npm i
npm install --save egg-sequelize mysql2
npm install --save-dev sequelize-cli
配置
-
config/plugin.js
exports.sequelize = { enable: true, package: 'egg-sequelize', }; -
config/config.default.js
config.sequelize = { dialect: 'mysql', // 表示使用mysql host: '127.0.0.1', // 连接的数据库主机地址 port: 3306, // mysql服务端口 database: 'egg_mysql_development', // 数据库名 username: 'root', // 数据库用户名 password: '123456', // 数据库密码 timezone: '+8:00', // 由于orm用的UTC时间，这里必须加上东八区，否则取出来的时间相差8小时 define: { // model的全局配置 timestamps: false, // 添加create,update,delete时间戳 freezeTableName: false, // 防止修改表名为复数 underscored: false, // 防止驼峰式字段被默认转为下划线 // 字段生成软删除时间戳 deleted_at paranoid: true, createdAt: 'created_at', updatedAt: 'updated_at', deletedAt: 'deleted_at', }, dialectOptions: { // 让读取date类型数据时返回字符串而不是UTC时间 dateStrings: true, typeCast(field, next) { if (field.type === 'DATETIME') { return field.string(); } return next(); }, }, }; -
.sequelizerc
'use strict'; const path = require('path'); module.exports = { config: path.join(__dirname, 'database/config.json'), 'migrations-path': path.join(__dirname, 'database/migrations'), 'seeders-path': path.join(__dirname, 'database/seeders'), 'models-path': path.join(__dirname, 'app/model'), }; -
database/config.json
{ "development": { "username": "root", "password": null, "database": "test", "host": "127.0.0.1", "dialect": "mysql", "timezone": "+08:00" } }
命令
-
初始化
sequelize init #初始化项目 sequelize init:config #初始化config sequelize init:migrations #初始化migrations sequelize init:models #初始化models sequelize init:seeders #初始化seeders -
数据库
sequelize db:create 创建数据库 sequelize db:drop 删除数据库 sequelize db:migrate 运行挂起的迁移 sequelize db:migrate:schema:timestamps:add sequelize db:migrate:status 列出所有迁移的状态 sequelize db:migrate:undo 撤销上一次迁移 sequelize db:migrate:undo:all 撤销所有迁移 sequelize db:migrate:undo --name xxx 撤销指定迁移 sequelize db:seed 运行指定种子文件 sequelize db:seed:undo 删除指定文件 sequelize db:seed:all 运行所有种子文件 sequelize db:seed:undo:all 删除所有指定文件 -
生成文件
sequelize migration:generate #生成一个新migrate文件[aliases: migration:create] sequelize migration:generate --name=[model] sequelize model:generate #生成一个新model文件[aliases: model:create] sequelize seed:generate #生成一个新seed文件[aliases: seed:create]
数据类型
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.
模型
migrations
-
创建
module.exports = { up: async (queryInterface, Sequelize) => { const { INTEGER, STRING, DATE, ENUM } = Sequelize; // 创建表 await queryInterface.createTable('tabelName', { }, { }); // 添加索引 queryInterface.addIndex('tabelName', [ '列名' ]); // 外键约束 queryInterface.addConstraint('tableName', ['user_id'], { type: 'foreign key', name: 'user_id', references: { //Required field table: 'users', field: 'id' }, onDelete: 'cascade', onUpdate: 'cascade' }); }, down: async queryInterface => { await queryInterface.dropTable('tabelName'); }, }; -
新增
module.exports = { up: (queryInterface, Sequelize) => { return queryInterface.sequelize.transaction(t => { //新增字段 return Promise.all([ queryInterface.addColumn('tabelName', '字段', { //字段相关参数 type: Sequelize.INTEGER, }, { transaction: t }), queryInterface.addColumn('tabelName', '字段', { type: Sequelize.STRING, }, { transaction: t }), ]); }); }, down: (queryInterface, Sequelize) => { return queryInterface.sequelize.transaction(t => { return Promise.all([ queryInterface.removeColumn('tabelName', '字段', { transaction: t }), queryInterface.removeColumn('tabelName', '字段', { transaction: t }), ]); }); }, };
model
module.exports = app => {
const modelInfo = app.model.define(
// 用于指定表的名称
'users表名称',
// 用于指定表中有哪些字段
{
字段名称: {
allowNull: false, // 是否允许为空
autoIncrement: true, // 字段是否是自增类型
primaryKey: true, // 字段是否是主键
type: Sequelize.INTEGER, // 字段是整型
defaultValue: null, // 字段默认值
unique: true, // 唯一索引
autoIncrement: true, // 整数列
field: 'field_with_underscores', // 自定义列名称
comment: '这是带有注释的列', // 注释列
// 外键列
references: {
// 这是对另一个模型的参考
model: Bar,
// 这是引用模型的列名
key: 'id',
// 使用 PostgreSQL,可以通过 Deferrable 类型声明何时检查外键约束.
deferrable: Deferrable.INITIALLY_IMMEDIATE,
// 参数:
// - `Deferrable.INITIALLY_IMMEDIATE` - 立即检查外键约束
// - `Deferrable.INITIALLY_DEFERRED` - 将所有外键约束检查推迟到事务结束
// - `Deferrable.NOT` - 完全不推迟检查(默认) - 这将不允许你动态更改事务中的规则
},
get() {
return this.getDataValue('字段名称');
},
set(val) {
this.setDataValue('字段名称', val);
},
},
},
// 用于配置表的一些额外信息
{
// 自定义表名
freezeTableName: true,
// tableName: 'users',
// 是否需要增加createdAt、updatedAt、deletedAt字段
timestamps: true,
// 不需要createdAt字段
createdAt: false,
// 将updatedAt字段改个名
updatedAt: 'utime',
// 将deletedAt字段改名
// 同时需要设置paranoid为true（此种模式下，删除数据时不会进行物理删除，而是设置deletedAt为当前时间
deletedAt: 'dtime',
paranoid: true,
// 指定索引
indexes: [],
}
);
// 生命周期
// 查询前
modelInfo.beforeFind((model, option) => {
console.log('查询前');
});
// 查询后
modelInfo.afterFind((model, option) => {
console.log('查询后');
});
// 新增前
modelInfo.beforeCreate((model, option) => {
console.log('新增前');
});
// 新增后
modelInfo.afterCreate((model, option) => {
console.log('新增后');
});
// 修改前
modelInfo.beforeUpdate((model, option) => {
console.log('修改前');
});
// 修改后
modelInfo.afterUpdate((model, option) => {
console.log('修改后'); // 真正修改才会触发，数据相同不会触发
});
// 删除前
modelInfo.beforeDestroy((model, option) => {
console.log('删除前');
});
// 删除后
modelInfo.afterDestroy((model, option) => {
console.log('删除后');
});
// 关联关系
modelInfo.associate = function(models) {
// 关联用户资料 一对一
modelInfo.hasOne(app.model.modelInfo);
};
// 关联用户表
modelInfo.associate = function(models) {
app.model.modelInfo.belongsTo(app.model.User);
};
return modelInfo;
};
seed
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.bulkInsert('表名',
//data数据
[],
// 其他配置信息
{});
},
down: (queryInterface, Sequelize) => {
return queryInterface.bulkDelete('表名', null, {});
},
};
关联操作
-
一对多
class City extends Model {} City.init({ countryCode: Sequelize.STRING }, { sequelize, modelName: 'city' }); class Country extends Model {} Country.init({ isoCode: Sequelize.STRING }, { sequelize, modelName: 'country' }); // 在这里,我们可以根据国家代码连接国家和城市 Country.hasMany(City, {foreignKey: 'countryCode', sourceKey: 'isoCode'}); City.belongsTo(Country, {foreignKey: 'countryCode', targetKey: 'isoCode'}); -
多对多
User.belongsToMany(Project, { as: 'Tasks', through: 'worker_tasks', foreignKey: 'userId' }) Project.belongsToMany(User, { as: 'Workers', through: 'worker_tasks', foreignKey: 'projectId' }) -
案例
// 获取关联模型对象，n对一不需要加s let userinfo = await user.getUserinfo(); // n对多需要加s await user.getPosts({ attributes: ['title'], where: { id: 3 } }); // 关联操作 // 1：用户创建文章（一对多） await this.ctx.model.Post.create({ title: "第一篇文章", user_id: user.id }); // 2.获取当前用户所有文章 await user.getPosts(); await user.getPosts({ attributes: ['id'], where:{ title:"测试" } }); // 3：用户删除文章（一对多） // (1) 获取当前用户的所有文章 let posts = await user.getPosts({ attributes: ['id'] }); posts = posts.map(v => v.id); await this.ctx.model.Post.destroy({ where: { id: posts } }); // 场景三：用户关注话题（多对多） await this.ctx.model.TopicUser.bulkCreate([{ user_id: user.id, topic_id: 1 },{ user_id: user.id, topic_id: 2 }]); // 用户关注话题（多对多） await this.ctx.model.TopicUser.destroy({ where: { user_id: user.id, topic_id: [1, 2] } });
查询
模型查询
-
主键查询
Model.findByPk(1) -
查找不存在则创建
//检查数据库中是否已存在某个元素. 如果元素存在,则查询 如果元素不存在,将会被创建. Model.findOrCreate() -
查询并计数
返回对象 - `count` - 一个整数,总数记录匹配where语句和关联的其它过滤器 - `rows` - 一个数组对象,记录在limit和offset范围内匹配where语句和关联的其它过滤器, Model.findAndCountAll() -
查询多个
Model.findAll() -
查询单个
Model.findOne()
条件
{
// 获取数量查询
limit: 10,
// 跳过数量查询
offset: 2,
// 分组查询
group: 'name',
// 字段过滤
attributes: [ 'foo', 'bar' ],
// 字段过滤，可以重命名
// attributes: ['foo', ['bar', 'baz']]
// 条件过滤查询
where: {},
// 排序查询
order: [
// 将返回 `name`
[ 'name' ],
// 将返回 `username` DESC
[ 'username', 'DESC' ],
// 将返回 max(`age`)
sequelize.fn('max', sequelize.col('age')),
// 将返回 max(`age`) DESC
[ sequelize.fn('max', sequelize.col('age')), 'DESC' ],
// 将返回 otherfunction(`col1`, 12, 'lalala') DESC
[ sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC' ],
// 将返回 otherfunction(awesomefunction(`col`)) DESC,这个嵌套是可以无限的！
[ sequelize.fn('otherfunction', sequelize.fn('awesomefunction', sequelize.col('col'))), 'DESC' ],
],
// 关联查询
include: [{
model: Task,
where: { state: Sequelize.col('project.state') },
}],
};
where
-
基础
[ // WHERE authorId = 2 { authorId: 2, }, // WHERE authorId = 12 AND status = 'active'; { authorId: 12, status: 'active', }, // WHERE authorId = 12 OR authorId = 13; { [Op.or]: [{ authorId: 12 }, { authorId: 13 }], }, // WHERE authorId = 12 OR authorId = 13; { authorId: { [Op.or]: [ 12, 13 ], }, }, // WHERE status = 'inactive'; { status: 'inactive', }, // WHERE deletedAt NOT NULL; { deletedAt: { [Op.ne]: null, }, }, // WHERE char_length(status) = 6; { where: sequelize.where(sequelize.fn('char_length', sequelize.col('status')), 6), }, ]; -
操作符
const { Op } = require("sequelize"); Post.findAll({ where: { [Op.and]: [{ a: 5 }, { b: 6 }], // (a = 5) AND (b = 6) [Op.or]: [{ a: 5 }, { b: 6 }], // (a = 5) OR (b = 6) someAttribute: { // 基本 [Op.eq]: 3, // = 3 [Op.ne]: 20, // != 20 [Op.is]: null, // IS NULL [Op.not]: true, // IS NOT TRUE [Op.or]: [5, 6], // (someAttribute = 5) OR (someAttribute = 6) // 使用方言特定的列标识符 (以下示例中使用 PG): [Op.col]: 'user.organization_id', // = "user"."organization_id" // 数字比较 [Op.gt]: 6, // > 6 [Op.gte]: 6, // >= 6 [Op.lt]: 10, // < 10 [Op.lte]: 10, // <= 10 [Op.between]: [6, 10], // BETWEEN 6 AND 10 [Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15 // 其它操作符 [Op.all]: sequelize.literal('SELECT 1'), // > ALL (SELECT 1) [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.startsWith]: 'hat', // LIKE 'hat%' [Op.endsWith]: 'hat', // LIKE '%hat' [Op.substring]: 'hat', // LIKE '%hat%' [Op.iLike]: '%hat', // ILIKE '%hat' (不区分大小写) (仅 PG) [Op.notILike]: '%hat', // NOT ILIKE '%hat' (仅 PG) [Op.regexp]: '^[h|a|t]', // REGEXP/~ '^[h|a|t]' (仅 MySQL/PG) [Op.notRegexp]: '^[h|a|t]', // NOT REGEXP/!~ '^[h|a|t]' (仅 MySQL/PG) [Op.iRegexp]: '^[h|a|t]', // ~* '^[h|a|t]' (仅 PG) [Op.notIRegexp]: '^[h|a|t]', // !~* '^[h|a|t]' (仅 PG) [Op.any]: [2, 3], // ANY ARRAY[2, 3]::INTEGER (仅 PG) [Op.match]: Sequelize.fn('to_tsquery', 'fat & rat') // 匹配文本搜索字符串 'fat' 和 'rat' (仅 PG) // 在 Postgres 中, Op.like/Op.iLike/Op.notLike 可以结合 Op.any 使用: [Op.like]: { [Op.any]: ['cat', 'hat'] } // LIKE ANY ARRAY['cat', 'hat'] // 还有更多的仅限 postgres 的范围运算符,请参见下文 } } }); -
范围选项
[Op.contains]: 2 // @> '2'::integer (PG range contains element operator) [Op.contains]: [1, 2] // @> [1, 2) (PG range contains range operator) [Op.contained]: [1, 2] // <@ [1, 2) (PG range is contained by operator) [Op.overlap]: [1, 2] // && [1, 2) (PG range overlap (have points in common) operator) [Op.adjacent]: [1, 2] // -|- [1, 2) (PG range is adjacent to operator) [Op.strictLeft]: [1, 2] // << [1, 2) (PG range strictly left of operator) [Op.strictRight]: [1, 2] // >> [1, 2) (PG range strictly right of operator) [Op.noExtendRight]: [1, 2] // &< [1, 2) (PG range does not extend to the right of operator) [Op.noExtendLeft]: [1, 2] // &> [1, 2) (PG range does not extend to the left of operator) -
组合
{ 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%'
实用方法
Model.count(“查询条件”)
Model.max(字段，查询条件);
Model.min(字段，查询条件);
Model.sum(字段，查询条件);
增删改
新增
//单新增字段限制
await User.create({ username: 'barfooz', isAdmin: true }, { fields: [ 'username' ] });
// 只有username有效
//批量新增字段限制
User.bulkCreate([
{ username: 'foo' },
{ username: 'bar', admin: true}
], { fields: ['username'] }).then(() => {
// admin 将不会被构建
})
// 新增
await this.ctx.model.User.create({});
// 批量新增
await this.ctx.model.User.bulkCreate([
{ },
{ },
{ },
]);
修改
//修改字段限制
user.title = 'foooo'
user.description = 'baaaaaar'
await user.save({fields: ['title']});
// title 现在将是 “foooo”,而 description 与以前一样
// 使用等效的 update 调用如下所示:
await user.update({ title: 'foooo', description: 'baaaaaar'}, {fields: ['title']});
// title 现在将是 “foooo”,而 description 与以前一样
//修改
await user.update(data);
//批量修改
await this.ctx.model.User.update(data, 修改条件);
//递增
await user.increment({
age: 3, // age每次递增3
other:2 // other每次递增2
});
//递减
await user.decrement({
age: 3, // age每次递减3
other:2 // other每次递减2
});
删除
// 软删除
this.app.model.define('user', { /* bla */}, {
// 同时需要设置paranoid为true（此种模式下，删除数据时不会进行物理删除，而是设置deletedAt为当前时间
paranoid: true,
});
//查询包括软删除内容
await this.ctx.model.User.findOne({
include:{
model:ctx.model.Video,
// 包括软删除
paranoid: false
},
where: {
id: 33
},
// 包括软删除
paranoid: false
});
//彻底删除
this.ctx.model.User.destroy({ force: true })
// 进行软删除...
this.ctx.model.User.destroy();
// 恢复软删除...
this.ctx.model.User.restore();
//条件删除
await this.ctx.model.User.destroy({
where: {
name: "批量修改"
}
});
//批量删除
await this.ctx.model.Post.destroy({
where: {
id: posts
}
});
模型自定义方法
// 模型
// 模型自定义方法
topic_user.ceshi = (param) => {
console.log('模型自定义方法');
console.log(param);
return param;
}
// 控制器
await this.ctx.model.TopicUser.ceshi(123);
