Skip to content

Sequelize for Egg.js ----- Multidatabase

License

Notifications You must be signed in to change notification settings

roy2an/egg-sequelize

 
 

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

82 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

egg-sequelize

Sequelize plugin for Egg.js.

NOTE: This plugin just for integrate Sequelize into Egg.js, more documentation please visit http://sequelizejs.com.

NPM version build status Test coverage David deps Known Vulnerabilities npm download

Install

$ 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 # MSSQL

Usage & configuration

  • config.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

Model files

Please put models under app/model dir.

Conventions

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.

Examples

Standard

Define a model first.

NOTE: app.model is 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;
    }
  }
}

Full example

// 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 };
    }
  }
}

Sync model to db

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});
      });
    }
  };

Migrations

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:up

For unittest environment:

$ EGG_SERVER_ENV=unittest npm run migrate:up

or for prod environment:

$ EGG_SERVER_ENV=prod npm run migrate:up

or for others environment:

$ EGG_SERVER_ENV=pre npm run migrate:up

This 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.

Recommended example

Questions & Suggestions

Please open an issue here.

License

MIT

About

Sequelize for Egg.js ----- Multidatabase

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Languages

  • JavaScript 100.0%