egg 操作mysql数据库
一、配置和创建迁移文件
1、 配置
- 安装并配置egg-sequelize插件(它会辅助我们将定义好的 Model 对象加载到 app 和 ctx 上)和mysql2模块:
npm install --save egg-sequelize mysql2
- 在
config/plugin.js
中引入 egg-sequelize 插件
exports.sequelize = {
enable: true,
package: 'egg-sequelize',
};
- 在
config/config.default.js
config.sequelize = {
dialect: 'mysql',
host: '127.0.0.1',
username: 'root',
password: 'root',
port: 3306,
database: 'eggapi',
// 中国时区
timezone: '+08:00',
define: {
// 取消数据表名复数
freezeTableName: true,
// 自动写入时间戳 created_at updated_at
timestamps: true,
// 字段生成软删除时间戳 deleted_at
paranoid: true,
createdAt: 'created_at',
updatedAt: 'updated_at',
deletedAt: 'deleted_at',
// 所有驼峰命名格式化
underscored: true
}
};
- sequelize 提供了sequelize-cli工具来实现Migrations迁移,我们也可以在 egg 项目中引入 sequelize-cli。
npm install --save-dev sequelize-cli
- egg 项目中,我们希望将所有数据库 Migrations 相关的内容都放在
database
目录下,所以我们在项目根目录下新建一个.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'),
};
- 初始化 Migrations 配置文件和目录
npx sequelize init:config
npx sequelize init:migrations
// npx sequelize init:models
- 行完后会生成
database/config.json
文件和database/migrations
目录,我们修改一下database/config.json
(没有的话应该在config目录下,把它移过来)中的内容,将其改成我们项目中使用的数据库配置:
{
"development": {
"username": "root",
"password": null,
"database": "eggapi",
"host": "127.0.0.1",
"dialect": "mysql",
"timezone": "+08:00"
}
}
- 创建数据库
npx sequelize db:create
2、创建数据迁移表
npx sequelize migration:generate --name=init-user
- 执行完命令后,会在database / migrations / 目录下生成数据表迁移文件,然后定义
'use strict';
module.exports = {
up: async (queryInterface, Sequelize) => {
const { INTEGER, STRING, DATE, ENUM } = Sequelize;
// 创建表
await queryInterface.createTable('user', {
id: { type: INTEGER(20).UNSIGNED, primaryKey: true, autoIncrement: true },
username: { type: STRING(30), allowNull: false, defaultValue: '', comment: '用户名称', unique: true},
password: { type: STRING(200), allowNull: false, defaultValue: '' },
avatar_url: { type: STRING(200), allowNull: true, defaultValue: '' },
sex: { type: ENUM, values: ['男','女','保密'], allowNull: true, defaultValue: '男', comment: '用户性别'},
created_at: DATE,
updated_at: DATE,
deleted_at: DATE
});
},
down: async queryInterface => {
await queryInterface.dropTable('user')
}
};
- 执行 migrate 进行数据库变更
# 升级数据库
npx sequelize db:migrate
# 如果有问题需要回滚,可以通过 `db:migrate:undo` 回退一个变更
# npx sequelize db:migrate:undo
# 可以通过 `db:migrate:undo:all` 回退到初始状态
# npx sequelize db:migrate:undo:all
- 同样的如果要新建一张表,则创建另一张数据迁移表,然后升级数据库。
二、模型
1. 创建模型
// app / model / user.js
'use strict';
module.exports = app => {
const { STRING, INTEGER, DATE, ENUM } = app.Sequelize;
// 配置(重要:一定要配置详细,一定要!!!)
const User = app.model.define('user', {
id: { type: INTEGER(20).UNSIGNED, primaryKey: true, autoIncrement: true },
username: { type: STRING(30), allowNull: false, defaultValue: '', comment: '用户名称', unique: true},
password: { type: STRING(200), allowNull: false, defaultValue: '' },
avatar_url: { type: STRING(200), allowNull: true, defaultValue: '' },
sex: { type: ENUM, values: ['男','女','保密'], allowNull: true, defaultValue: '男', comment: '用户性别'},
created_at: DATE,
updated_at: DATE,
deleted_at: DATE
},{
timestamps: true, // 是否自动写入时间戳
tableName: 'user', // 自定义数据表名称
});
return User;
};
这个 Model 就可以在 Controller 和 Service 中通过 app.model.User
或者 ctx.model.User
访问到了,例如我们编写 app/controller/user.js
则可以进行数据库的增删改查了。
三、数据库操作
在 app/controller/user.js
中,通过 app.model.User 或者 ctx.model.User 访问。
1. 新增
// 单个新增
const res = await this.app.model.User.create(obj)
// 批量新增
const res = await this.app.model.User.bulkCreate([obj,obj...])
2. 查询
findByPk()
// 查询单个
const res = await this.app.model.User.findByPk(id) // 通过主键查询
findAll()
// ------- 简单查询 - findAll -------
const result = await this.app.model.User.findAll({
where: { // 筛选
username: {
[Op.like]: "%ceshi%", // like 模糊查询 包含ceshi字段的username
},
},
})
// ------- 多条件查询 - findAll -------
const page = this.ctx.query.page ? parseInt(this.ctx.query.page) : 1 // 第一页
const limit = 5 // 分页限制
const offset = (page -1) * limit // 分页偏移量
const Op = this.app.Sequelize.Op // 筛选
// 查询多个
const result = await this.app.model.User.findAll({
where: { // 筛选
// username: {
// [Op.like]: "%ceshi%", // like 模糊查询
// },
// id: {
// [Op.gt]: 1 // 大于1
// }
},
// attributes: ['id', 'username', 'sex'] // 指定返回的属性
attributes: {
exclude: ['password'], // 排除了这些属性
},
order: [
['id', 'DESC'] // 降序排序,最后创建的在前面, ASC:升序
],
limit, // 分页限制
offset // 分页偏移量
})
// ------- 也就是说 findAll() 可以查询特定条件的数据 -------
findAndCountAll()
// 查询多个并统计
// 和 findAll() 查询一样,但多了计数功能
// result = await this.app.model.User.findAndCountAll()
3. 修改
save()
// 修改单个
const data = await this.app.model.User.findByPk(id)
if(!data) {
return this.ctx.body = {
msg: 'error',
data: '不存在该记录'
}
}
data.username = '被修改了'
// 保存
data.save({
fields: ['username'] // 只允许修改该字段
})
update()
// 修改多个
const res = await data.update(pramas,{fields: ['username']}) // 限制修改字段
4. 删除
destroy()
// 删除单个
const data = await this.app.model.User.findByPk(id)
if(!data) {
return this.ctx.body = {
msg: 'error',
data: '不存在该记录'
}
}
// 删除单个
const res = await data.destroy()
this.ctx.body = {
msg: 'ok',
data: res
}
// 删除多个,筛选多个
const res = await this.app.model.User.destroy({
where: {
id: {
[Op.in]: [2,4]
}
}
})
5. 计数
count()
const res = await this.app.model.User.count(obj)
6. Sequelize.Op 的相关操作
const Op = this.app.model.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.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