Sequelize plugin for Egg.js.
NOTE: This plugin just for integrate Sequelize into Egg.js, more documentation please visit http://sequelizejs.com.
$ npm i --save egg-sequelize
$ npm install --save mysql2 # For both mysql and mariadb dialects
# Or use other database backend.
$ npm install --save pg pg-hstore # PostgreSQL
$ npm install --save tedious # MSSQLconfig.default.js
exports.sequelize = {
dialect: 'mysql', // support: mysql, mariadb, postgres, mssql
database: 'test',
host: 'localhost',
port: '3306',
username: 'root',
password: '',
};
or
exports.sequelize = [{
dialect: 'mysql', // support: mysql, mariadb, postgres, mssql
database: 'db1',
host: 'localhost',
port: '3306',
username: 'root',
password: '',
}, {
dialect: 'mysql', // support: mysql, mariadb, postgres, mssql
database: 'db2',
host: 'localhost',
port: '3306',
username: 'root',
password: '',
}];
or
// const getConfig = function(time) {
// return new Promise(function(resolve) {
// setTimeout(function() {
// resolve(
// [{
// port: '3306',
// host: '127.0.0.1',
// username: 'root',
// password: '',
// database: 'test',
// dialect: 'mysql',
// pool: {
// max: 5,
// min: 0,
// idle: 10000,
// },
// }, {
// port: '3306',
// host: '127.0.0.1',
// username: 'root',
// password: '',
// database: 'ga',
// dialect: 'mysql',
// pool: {
// max: 5,
// min: 0,
// idle: 10000,
// },
// }]
// );
// }, time);
// });
// };
exports.sequelize = async () => {
const data = await getConfig(3000);
return data;
};
//app.js
app.beforeStart(function* () {
// console.log('sync');
yield app.model.sync();
});
// app.on('authenticated', function() {
// app.model.sync();
// });
ctx.models[0] = app.models[0] = app.models.db1 = app.model
ctx.models[1] = app.models[1] = app.models.db2
ctx.models[2] = app.models[2] = app.models.db3
...config/plugin.js
exports.sequelize = {
enable: true,
package: 'egg-sequelize'
}package.json
{
"scripts": {
"migrate:new": "egg-sequelize migration:create",
"migrate:up": "egg-sequelize db:migrate",
"migrate:down": "egg-sequelize db:migrate:undo"
}
}More documents please refer to Sequelize.js
Please put models under app/model dir.
| folder | model file | db | class name |
|---|---|---|---|
. |
user.js |
db1 |
app.model.Administrator or app.models[0].User or app.models.db1.User |
. |
person.js |
db1 |
app.model.Person or app.models[0].Person or app.models.db1.Person |
. |
user_group.js |
db2 |
app.models[1].UserGroup or app.models.db2.UserGroup |
./subfolder/ |
monkey.js |
db1 |
app.model.Subfolder_Monkey or app.models[0].Subfolder_Monkey or app.models.db1.Subfolder_Monkey |
- Tables always has timestamp fields:
created_at datetime,updated_at datetime. - Use underscore style column name, for example:
user_id,comments_count.
Define a model first.
NOTE:
app.modelis an Instance of Sequelize, so you can use methods like:app.model.sync, app.model.query ...
// app/model/user.js
module.exports = app => {
const { STRING, INTEGER, DATE } = app.Sequelize;
//const User = app.models[0].define('user', {
//const User = app.models.db1.define('user', {
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 = function* (login) {
return yield this.findOne({
where: {
login: login
}
});
}
User.prototype.logSignin = function* () {
yield this.update({ last_sign_in_at: new Date() });
}
return User;
};Now you can use it in your controller:
// app/controller/user.js
module.exports = app => {
return class UserController extends app.Controller {
* index() {
//const users = yield this.ctx.models[0].User.findAll();
//const users = yield this.ctx.models.db1.User.findAll();
const users = yield this.ctx.model.Administrator.findAll();
this.ctx.body = users;
}
* show() {
//const user = yield this.ctx.models[0].User.findByLogin(this.ctx.params.login);
//const user = yield this.ctx.models.db1.User.findByLogin(this.ctx.params.login);
const user = yield this.ctx.model.Administrator.findByLogin(this.ctx.params.login);
yield user.logSignin();
this.ctx.body = user;
}
}
}// app/model/post.js
module.exports = app => {
const { STRING, INTEGER, DATE } = app.Sequelize;
//const Post = app.models[0].define('Post', {
//const Post = app.models.db1.define('Post', {
const Post = app.model.define('Post', {
name: STRING(30),
user_id: INTEGER,
created_at: DATE,
updated_at: DATE,
});
Post.associate = function() {
// app.models[0].Post.belongsTo(app.models[0].User, { as: 'user' });
// app.models.db1.Post.belongsTo(app.models.db1.User, { as: 'user' });
app.model.Post.belongsTo(app.model.Administrator, { as: 'user' });
}
return Post;
};// app/controller/post.js
module.exports = app => {
return class PostController extends app.Controller {
* index() {
//const posts = yield this.ctx.models[0].Post.findAll({
//const posts = yield this.ctx.models.db1.Post.findAll({
const posts = yield this.ctx.model.Post.findAll({
attributes: [ 'id', 'user_id' ],
include: { model: this.ctx.model.Administrator, as: 'user' },
where: { status: 'publish' },
order: 'id desc',
});
this.ctx.body = posts;
}
* show() {
//const post = yield this.ctx.models[0].Post.findById(this.params.id);
//const post = yield this.ctx.models.db1.Post.findById(this.params.id);
const post = yield this.ctx.model.Post.findById(this.params.id);
const user = yield post.getUser();
post.setDataValue('user', user);
this.ctx.body = post;
}
* destroy() {
//const post = yield this.ctx.models[0].Post.findById(this.params.id);
//const post = yield this.ctx.models.db1.Post.findById(this.params.id);
const post = yield this.ctx.model.Post.findById(this.params.id);
yield post.destroy();
this.ctx.body = { success: true };
}
}
}We strongly recommend you to use migrations to create or migrate database.
This code should only be used in development.
// {app_root}/app.js
module.exports = app => {
if (app.config.env === 'local') {
app.beforeStart(function* () {
//yield app.models[0].sync({force: true});
//yield app.models.db1.sync({force: true});
yield app.model.sync({force: true});
});
}
};If you have added scripts of egg-sequelize into your package.json, now you can:
| Command | Description |
|---|---|
| npm run migrate:new | Generate a new Migration file to ./migrations/ |
| npm run migrate:up | Run Migration |
| npm run migrate:down | Rollback once Migration |
For example:
$ npm run migrate:upFor unittest environment:
$ EGG_SERVER_ENV=unittest npm run migrate:upor for prod environment:
$ EGG_SERVER_ENV=prod npm run migrate:upor for others environment:
$ EGG_SERVER_ENV=pre npm run migrate:upThis will load database config from config/config.pre.js.
Write migrations with Generator friendly, you should use co.wrap method:
'use strict';
const co = require('co');
module.exports = {
up: co.wrap(function *(db, Sequelize) {
const { STRING, INTEGER, DATE } = Sequelize;
yield db.createTable('users', {
id: { type: INTEGER, primaryKey: true, autoIncrement: true },
name: { type: STRING, allowNull: false },
email: { type: STRING, allowNull: false },
created_at: DATE,
updated_at: DATE,
});
yield db.addIndex('users', ['email'], { indicesType: 'UNIQUE' });
}),
down: co.wrap(function *(db, Sequelize) {
yield db.dropTable('users');
}),
};And you may need to read Sequelize - Migrations to learn about how to write Migrations.
Please open an issue here.