egg的连接配置
- 安装
$ npm i --save egg-sequelize
$ npm install --save mysql2 # For both mysql and mariadb dialects
或者使用其他方式连接
$ npm install --save pg pg-hstore
$ npm install --save tedious
- 在
config/plugin.js
里配置插件
exports.sequelize = {
enable: true,
package: 'egg-sequelize'
}
- 在
conif/config.{env}.js
里进行连接配置
exports.sequelize = {
dialect: 'mysql', // 支持的数据连接类型: mysql, mariadb, postgres, mssql
database: 'test',
host: 'localhost',
port: 3306,
username: 'root',
password: '',
// delegate: 'myModel', // load all models to `app[delegate]` and `ctx[delegate]`, default to `model`
// baseDir: 'my_model', // load all files in `app/${baseDir}` as models, default to `model`
// exclude: 'index.js', // ignore `app/${baseDir}/index.js` when load models, support glob and array
// more sequelize options
};
- 也可以进行以下这样连接
exports.sequelize = {
dialect: 'mysql', // support: mysql, mariadb, postgres, mssql
connectionUri: 'mysql://root:@127.0.0.1:3306/test',
// delegate: 'myModel', // load all models to `app[delegate]` and `ctx[delegate]`, default to `model`
// baseDir: 'my_model', // load all files in `app/${baseDir}` as models, default to `model`
// exclude: 'index.js', // ignore `app/${baseDir}/index.js` when load models, support glob and array
// more sequelize options
};
- egg-sequelize 有一个默认的 sequelize 选项如下
{
delegate: 'model',
baseDir: 'model',
logging(...args) {
// if benchmark enabled, log used
const used = typeof args[1] === 'number' ? `[${args[1]}ms]` : '';
app.logger.info('[egg-sequelize]%s %s', used, args[0]);
},
host: 'localhost',
port: 3306,
username: 'root',
benchmark: true,
define: {
freezeTableName: false,
underscored: true,
},
};
模型文件
默认情况下,请将模型置于 app/model
目录下。
约定
model file | class name |
---|---|
user.js | app.model.User |
person.js | app.model.Person |
user_group.js | app.model.UserGroup |
user/profile.js | app.model.User.Profile |
- 表格始终具有时间戳字段:
created_at datetime
,updated_at datetime
。 - 使用下划线样式列名,如:
user_id
,comments_count
。
实例
标准
首先定义模型
注意:
options.delegate
默认为model
,所以app.model
是 Sequelize实例, 可以使用如下方法:app.model.sync, app.model.query ...
// app/model/user.js module.exports = app => { const { STRING, INTEGER, DATE } = app.Sequelize; const User = app.model.define('user', { login: STRING, name: STRING(30), password: STRING(32), age: INTEGER, last_sign_in_at: DATE, created_at: DATE, updated_at: DATE, }); User.findByLogin = async function(login) { return await this.findOne({ where: { login: login } }); } // don't use arraw function User.prototype.logSignin = async function() { return await this.update({ last_sign_in_at: new Date() }); } return User; };
现在您可以在控制器中使用它:
// app/controller/user.js
class UserController extends Controller {
async index() {
const users = await this.ctx.model.User.findAll();
this.ctx.body = users;
}
async show() {
const user = await this.ctx.model.User.findByLogin(this.ctx.params.login);
await user.logSignin();
this.ctx.body = user;
}
}
关联
在 Model.associate()
中定义所有关联,egg-sequelize 将在加载所有模型后执行它。请参见下面的示例:
多数据源
egg-sequelize 支持独立加载多个数据源,您可以使用 config.sequelize.datasources
配置和加载多个数据源。
// config/config.default.js
exports.sequelize = {
datasources: [
{
delegate: 'model', // load all models to app.model and ctx.model
baseDir: 'model', // load models from `app/model/*.js`
database: 'biz',
// other sequelize configurations
},
{
delegate: 'admninModel', // load all models to app.adminModel and ctx.adminModel
baseDir: 'admin_model', // load models from `app/admin_model/*.js`
database: 'admin',
// other sequelize configurations
},
],
};
我们可以这样定义模型:
// app/model/user.js
module.exports = app => {
const { STRING, INTEGER, DATE } = app.Sequelize;
const User = app.model.define('user', {
login: STRING,
name: STRING(30),
password: STRING(32),
age: INTEGER,
last_sign_in_at: DATE,
created_at: DATE,
updated_at: DATE,
});
return User;
};
// app/admin_model/user.js
module.exports = app => {
const { STRING, INTEGER, DATE } = app.Sequelize;
const User = app.adminModel.define('user', {
login: STRING,
name: STRING(30),
password: STRING(32),
age: INTEGER,
last_sign_in_at: DATE,
created_at: DATE,
updated_at: DATE,
});
return User;
};
如果为不同的数据源定义同一个模型,那么对于不同的数据库,同一个模型文件将执行两次,因此我们可以使用两个参数获取 sequelize 实例:
// app/model/user.js
// if this file will load multiple times for different datasource
// we can use the secound argument to get the sequelize instance
module.exports = (app, model) => {
const { STRING, INTEGER, DATE } = app.Sequelize;
const User = model.define('user', {
login: STRING,
name: STRING(30),
password: STRING(32),
age: INTEGER,
last_sign_in_at: DATE,
created_at: DATE,
updated_at: DATE,
});
return User;
};
自定义 Sequelize
默认情况下,egg-sequelize 将使用 sequelize@5,您可以自定义 sequelize 版本使用 config.sequelize.Sequelize
绕过,例如:
// config/config.default.js
exports.sequelize = {
Sequelize: require('sequelize'),
};
完整示例
// app/model/post.js
module.exports = app => {
const { STRING, INTEGER, DATE } = app.Sequelize;
const Post = app.model.define('Post', {
name: STRING(30),
user_id: INTEGER,
created_at: DATE,
updated_at: DATE,
});
Post.associate = function() {
app.model.Post.belongsTo(app.model.User, { as: 'user' });
}
return Post;
};
// app/controller/post.js
class PostController extends Controller {
async index() {
const posts = await this.ctx.model.Post.findAll({
attributes: [ 'id', 'user_id' ],
include: { model: this.ctx.model.User, as: 'user' },
where: { status: 'publish' },
order: 'id desc',
});
this.ctx.body = posts;
}
async show() {
const post = await this.ctx.model.Post.findByPk(this.params.id);
const user = await post.getUser();
post.setDataValue('user', user);
this.ctx.body = post;
}
async destroy() {
const post = await this.ctx.model.Post.findByPk(this.params.id);
await post.destroy();
this.ctx.body = { success: true };
}
}
同步模型到数据库
我们强烈建议您使用 Sequelize - Migrations 创建或迁移数据库。
此代码只应在开发中使用。
// {app_root}/app.js
module.exports = app => {
if (app.config.env === 'local' || app.config.env === 'unittest') {
app.beforeStart(async () => {
await app.model.sync({force: true});
});
}
};