think-sequelize

Wrap sequelize for ThinkJS 3.x

Sequelize is a promise-based ORM for Node.js v4 and up. It supports the dialects PostgreSQL, MySQL, SQLite and MSSQL and features solid transaction support, relations, read replication and more.

Install

npm install think-sequelize --save

# add one of the following:
$ npm install --save pg@6 pg-hstore # Note that `pg@7` is not supported yet
$ npm install --save mysql2
$ npm install --save sqlite3
$ npm install --save tedious # MSSQL

How to Use

Config Extend

Change file src/config/extend.js (in multi module project, file is src/common/config/extend.js), add config:

const sequelize = require('think-sequelize');

module.exports = [
  sequelize(think.app)
]

When add sequelize extend, it will add methods below:

• think.Sequel {Class} Base class(it's extends from sequelize model), model class should extends this class.

  • think.Sequel.Sequel sequelize object (equal require('sequelize'))
  • think.Sequel.Relation sequelize relation type

• think.sequel {Function} get sequel instance

• ctx.sequel {Function} get sequel instance

• controller.sequel {Function} get sequel instance

• service.sequel {Function} get sequel instance

Config Adapter

Change file src/config/adapter.js (in multi module project, file is src/common/config/adapter.js), add config:

exports.model = {
  type: 'sequel',
  sequel: {
    prefix: 'think_',
    logConnect: false,
    database: 'think-demo',
    user: 'root',
    password: 'root',
    options: {
      host: '127.0.0.1',
      dialect: 'mysql',
      logging: false,
      define: {
        timestamps: false
      }
    }
  },
}

or config connectionString:

exports.model = {
  type: 'sequel',
  sequel: {
    connectionString: 'mysql://root:[email protected]/think-demo',
    prefix: 'think_',
    logConnect: false,
    options: {
      logging: false
    }
  }
}

For more options see at http://docs.sequelizejs.com/class/lib/sequelize.js~Sequelize.html.

Create Model Class

Create model class extends from think.Sequel:

// src/model/player.js
module.exports = class extends think.Sequel {
  get schema() {
    return {
      attributes: {
        id: {
          type: think.Sequel.Sequel.BIGINT,
          primaryKey: true
        },
        teamId: think.Sequel.Sequel.BIGINT,
        name: think.Sequel.Sequel.STRING(255),
      },
      options: { // will merge with options.define config of sequel in src/config/adapter.js
        timestamps: false,
        freezeTableName: true,
        tableName: 'think_player',
      }
    }
  }
}

Schema's attributes and options will be passed to sequelize's define method. For more model definition see at http://docs.sequelizejs.com/manual/tutorial/models-definition.html;

sequelize.define('name', {attributes}, {options})

The schema's options will merge with options.define in src/config/adapter.js.

If you want every model's timestamps: false, you can write in sequel's options.define config of src/config/adapter.js.

And you can rewrite common schema config in every model's schema.

Add instance methods

module.exports = class extends think.Sequel {
  constructor(...props) {
    super(...props);
  }

  get schema() {
    return {
      attributes: {
        id: {
          type: think.Sequel.Sequel.BIGINT,
          primaryKey: true
        },
        teamId: think.Sequel.Sequel.BIGINT, // belongsTo, 为当前模型添加外键
        name: think.Sequel.Sequel.STRING(255),
      },
      options: {
        timestamps: false,
        freezeTableName: true,
        tableName: 'think_player',
      }
    }
  }

  get instanceMethods() {
    return {
      test() {
        console.log(this.id);
      }
    }
  }
}

instanceMethods return an object,each object's item is a function. Arrow function is disabled.

If you just want add instance methods one by one in model, you can do like this:

module.exports = class extends think.Sequel {
  constructor(...props) {
    super(...props);
    this.addInstanceMethod(function test() { // anonymous fn is disabled, arrow fn is disabled
      console.log(this.id);
    });
  }
}

These methods are added to sequelize.define(*).prototype!

Get Model Instance

You can get sequel class instance by think.sequel, ctx.sequel, service.sequel or controller.sequel.

module.exports = class extends think.Controller {
  async indexAction() {
    const player = this.sequel('player');
    const data = await player.findAll();
  }
}

If default model adapter type is not sequel, the second argument must be set, such as:

// in src/config/adapter.js (in multi module project, file is `src/common/config/adapter.js`)
exports.model = {
  type: 'mysql',
  common: {
    // ...
  },
  sequel: {
    // ...
  },
  mysql: {
    // ...
  }
}

With the config above, you should use sequelize like this:

module.exports = class extends think.Controller {
  async indexAction() {
    const player = this.sequel('player', 'sequel'); // use `sequel` adapter type
    const data = await player.findAll();
  }
}

Relation

Sequelize support hasOne,belongsTo,hasMany,belongsToMany model relation type. think.Sequel.Relation wrap the relation types, it has the following values：

think.Sequel.Relation = {
  HAS_ONE: 'hasOne',
  BELONG_TO: 'belongsTo',
  HAS_MANY: 'hasMany',
  MANY_TO_MANY: 'belongsToMany'
};

For a better understanding, we give an example：

• one player has one partner
• one player belongs to one team
• one player has owned many trophy
• one player has many teacher, and one teacher has many player

// src/model/player.js
module.exports = class extends think.Sequel {
  constructor(...props) {
    super(...props);
  }

  get schema() {
    return {
      attributes: {
        id: {
          type: think.Sequel.Sequel.BIGINT,
          primaryKey: true
        },
        teamId: think.Sequel.Sequel.BIGINT,
        name: think.Sequel.Sequel.STRING(255),
      },
      options: {
        timestamps: false,
        freezeTableName: true,
        tableName: 'think_player',
      },
      relations: [
        { 'team': think.Sequel.Relation.BELONG_TO },
        { 'partner': think.Sequel.Relation.HAS_ONE },
        { 'trophy': think.Sequel.Relation.HAS_MANY },
        {
          'sequel/teacher': think.Sequel.Relation.MANY_TO_MANY,
          options: {
            through: think.sequel('teacher_player', 'sequel') // do not use this.sequel in schema
          }
        },
      ]
    }
  }
}

NOTE: If you want use sequelize model in schema, you should use think.sequel method instead of this.sequel, because this.sequel has't been initialized at this time.

Default tableName equal ${db prefix}_${model name}, If you want custom tableName:

get schema () {
  return {
    // ...
    options: {
      freezeTableName: true,     // set true
      tableName: 'think_player', // custom tableName here
    }
  }
}

One player has one partner:

// src/model/partner.js
module.exports = class extends think.Sequel {
  constructor(...props) {
    super(...props);
  }

  get schema() {
    return {
      attributes: {
        id: {
          type: think.Sequel.Sequel.BIGINT,
          primaryKey: true
        },
        playerId: think.Sequel.Sequel.BIGINT,
        name: think.Sequel.Sequel.STRING(255),
      }
    }
  }
}

Then you can use like this:

module.exports = class extends think.Controller {
  constructor(...props) {
    super(...props);
  }
  indexAction() {
    let player = this.sequel('player', 'sequel');
    let partner = this.sequel('partner', 'sequel');
    return this.json(await player.findAll({
      include: [
        {
          model: partner,
        }
      ]
    }));
  }
}

Or you can use it in another way:

// src/controller/index.js
module.exports = class extends think.Controller {
  constructor(...props) {
    super(...props);
  }
  async indexAction() {
    let player = this.sequel('player', 'sequel');
    return this.json(await player.getAllPlayer());
  }
}

// src/model/player.js
module.exports = class extends think.Sequel {
  // get schema and other ...
  getAllPlayer() {
    let partner = this.sequel('partner', 'sequel');
    return this.findAll({
      include: [
        { model: partner }
      ]
    });
  }
}

One player belongs to one team:

// src/model/team.js
module.exports = class extends think.Sequel {
  constructor(...props) {
    super(...props);
  }

  get schema() {
    return {
      attributes: {
        id: {
          type: think.Sequel.Sequel.BIGINT,
          primaryKey: true
        },
        name: think.Sequel.Sequel.STRING(255),
      }
    }
  }
}

One player owned many trophies:

// src/model/trophy.js
module.exports = class extends think.Sequel {
  constructor(...props) {
    super(...props);
  }

  get schema() {
    return {
      attributes: {
        id: {
          type: think.Sequel.Sequel.BIGINT,
          primaryKey: true
        },
        playerId: think.Sequel.Sequel.BIGINT,
        name: think.Sequel.Sequel.STRING(255),
      }
    }
  }
}

One player has many teachers, and one teacher has many players:

// src/model/teacher.js
module.exports = class extends think.Sequel {
  constructor(...props) {
    super(...props);
  }

  get schema() {
    return {
      attributes: {
        id: {
          type: think.Sequel.Sequel.BIGINT,
          primaryKey: true
        },
        name: think.Sequel.Sequel.STRING(255),
      }
    }
  }
}

// src/model/teacher_player.js
module.exports = class extends think.Sequel {
  constructor(...props) {
    super(...props);
  }

  get schema() {
    return {
      attributes: {
        id: {
          type: think.Sequel.Sequel.BIGINT,
          primaryKey: true
        },
        playerId: think.Sequel.Sequel.BIGINT,
        teacherId: think.Sequel.Sequel.BIGINT,
      }
    }
  }
}

CURD

You can use sequelize's model methods to execute ORM. Read documents http://docs.sequelizejs.com/ to get more information.