Egg-sequelize plugin use and extension

The installation

## Egg-sequelize plugin installed
npm install egg-sequelize --save

Install mysql2 for mysql or Mariadb database
npm install mysql2 --save

Install pgHE PG-hstore for postgreSQL database
npm install pg pg-hstore --save

## install msSQL database
npm install tedious --save

Copy the code

Note:

• MSSQL is Microsoft SQLServer database
• Egg-sequelize is used very frequently in mysql business

In addition to downloading egg-sequelize, what other database support modules (eg:mysql2/pg-hstore/tedious) should be selected according to the configuration of dialect: ‘mysql’ in the config.js file.

configuration

Open the plug-in

// config/plugin.js

exports.sequelize={
    enable:true.package:'egg-sequelize'
}

Copy the code

Database Configuration

It is well known that mysql database connections require username/password/port/host configuration, and the egg-Sequelize plugin is no exception

// config/config.{env}.js

exports.sequelize = {
  dialect: 'mysql'.// Support mysql, Mariadb, Postgres, MSSQL and other databases
  database: 'test'.// Database name
  host: 'localhost'.// Service host address
  port: 3306./ / port
  username: 'root'./ / user name
  password: ' './ / password
  delegate: 'myModel'.// [Optional] Load all model models into the 'app[delegate]' and 'CTX [delegate]' object. The default is model
  baseDir: 'my_model'.// [Optional] Load all js files in 'app/${baseDir}' folder as models. The default is' model '
  exclude: 'index.js'.// [Optional] Ignore 'app/${baseDir}/index.js' file when loading all model Models, support file paths and arrays
  
  
  // Other default parameters
};

Copy the code

In addition to the common Sequelize configuration parameters listed above, the egg-Sequelize plug-in has some default configurations as follows:

{
    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.// Check whether the table name matches the js file name of the model
      underscored: true,}};Copy the code

Template file

By default, mode files corresponding to database table fields are placed under app/ Model and automatically loaded and processed by the Egg-Sequelize plug-in.

The default conventions

The model file	The loaded 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

In addition:

• By default, the table generated by model has timestamp type time fields: create_at–>datetime, update_at–>datetime

• Column names are named with underscores, for example, user_id and create_time

Use the sample

basis

Defining the Model file

// app/model/user.js

module.exports = app= > {
  // Field data type
  const { STRING, INTEGER, DATE } = app.Sequelize;

  / / the model definition
  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,
  });

  // Define the findByLogin() method, which is normally placed in the service layer
  User.findByLogin = async function(login) {
      // Notice that this object is the current model instance
    return await this.findOne({
      where: {
        login: login
      }
    });
  }

  // Arrow functions cannot be used
  User.prototype.logSignIn = async function() {
    return await this.update({ last_sign_in_at: new Date()}); }return User;
};
Copy the code

The app.model object is a Sequelize instance and can easily use its built-in functions, such as:

• app.model.sync(): Database model synchronizes with fields
• app.model.query(): Executes a custom SQL statement

You can now manipulate the database in the Controller layer using encapsulated methods

// 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; }}Copy the code

associated

Associate relationships between tables can be defined by model.associate (), and egg-sequelize will execute associate() after the module Model is loaded to create the association

Multiple data sources

Egg-sequelize supports loading multiple independent database configurations and connecting to multiple database data sources. You can use the config. Sequelize. Datasources to configure and load multiple data sources

// config/config.default.js
exports.sequelize = {
  datasources: [{delegate: 'model'.// Load all model files into ctx.model and app.model to delegate
      baseDir: 'model'.// Load model files from 'app/model/*.js'
      database: 'biz'.// Additional configuration about sequelize
    },
    {
      delegate: 'admninModel'.// Load all model files into ctx.admninModel and app.admninModel to delegate
      baseDir: 'admin_model'.// Load model files from 'app/admin_model/*.js'
      database: 'admin'.// Additional configuration about sequelize},]};Copy the code

Following the example above, after configuring multiple data sources, a model can be defined as follows:

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

Copy the code

If you define the same Model for different data sources as configured above, the same Model file will be executed multiple times in different databases, so you can use the second parameter to get the Sequelize instance object.

// app/model/user.js

// If js files under model will be loaded multiple times in different data sources. The second argument (app,model) is used to retrieve 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;
};

Copy the code

Custom sequelize

By default, egg-Sequelize will use sequelize@5, version V5. Can be configured config. Sequelize. Sequelize sequelize object version from definition.

// config/config.default.js
exports.sequelize = {

  // Require introduces the sequelize version downloaded by the project itself
  Sequelize: require('sequelize'),};Copy the code

Complete example

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

  // Create an association between tables
  Post.associate = function() {
    app.model.Post.belongsTo(app.model.User, { as: 'user' });
  }

  return Post;
};
Copy the code

Use the Model in the Controller layer to manipulate the database

// app/controller/post.js
class PostController extends Controller {
  async index() {
    const posts = await this.ctx.model.Post.findAll({
      // Query the specified field
      attributes: [ 'id'.'user_id'].// Associated query
      include: { model: this.ctx.model.User, as: 'user' },
      // Conditional query
      where: { status: 'publish' },
      / / sorting
      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() {
    // params transmits the route parameter
    const post = await this.ctx.model.Post.findByPk(this.params.id);
    await post.destroy();
    this.ctx.body = { success: true}; }}Copy the code

Synchronize the model to the database

It is strongly recommended to use sequelize-migrations to create or migrate data

Of course, in a development environment, you can use the sync() method for database synchronization.

The migration

Using sequelize-CLI to help manage databases, data structures, and raw data is also based on sequelize-migrations

extension

Based on egg-Sequelize plug-in, I developed egg-Sequelize-Plus plug-in to solve the problem of automatic database creation in production environment and optimize the sequelize object loading.