Egg.js, an open source enterprise node of Alibaba JS framework. Compared with Express and Koa, egg JS is lighter, which is the enhancement of Koa, and the development cost and efficiency are more efficient.

Sequenize, a widely used ORM framework, supports multiple data sources such as MySQL, PostgreSQL, SQLite and MSSQL.

1, Install the configuration plug-in

Open vscode terminal and install egg mysql, mysql2

npm install --save egg-sequelize mysql2

In config / plugin Introducing the egg serialize plug-in into JS

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

In config / config default. JS

config.sequelize = {
  dialect: 'mysql',
  host: '123.45.67.890',
  port: 3306,
  database: 'test',
  username: 'root',
  password: '123456',
  // The configuration database time is Beijing time of Dongba district
  timezone: '+08:00',
  define: {  // Global configuration of model
    timestamps: true,   // Add create, update and delete timestamps
    paranoid: true,   // Add soft delete
    freezeTableName: true,  // Prevent modifying table names to plural
    underscored: false  // Prevent humped fields from being changed to underline by default
  },
  // Print log
  logging: true,
  // Time formatting
  dialectOptions: {
    dateStrings: true,
    typeCast: true
  }
};

Attention

1. Abnormal date display format

By default, the query date is 2022-01-02T09:14:03.102Z. We need to format it automatically.

dialectOptions: {
  dateStrings: true,
  typeCast: true 
}

In this way, it will be formatted as maozi: 2022-01-04 10:39:56

Two, model configuration

Model is the essence of sequenize A model is an abstraction that represents a table in a database In serialize, it is an extension class of model.

The model in serialize has a name that does not have to be the same as the name of the table it represents in the database. Typically, models have singular names (for example, Users) and tables have plural names (for example, Users), which is fully configurable.

Let's first create a parent in the model folder JS file defines the parents model:

'use strict';
/**
 * Parent table
 */
module.exports = app => {
  const { STRING, INTEGER, UUID, NOW, DATE, UUIDV4 } = app.Sequelize;
  const Parents = app.model.define('Parents', {
    id: { 
      type: UUID,      
      primaryKey: true,
      allowNull: false,
      defaultValue: UUIDV4,
      comment: 'parent id'
    },
    name: {
      type: STRING(36),
      allowNull: false,
      comment: 'Parent name'
    },
    age: {
      type: INTEGER,
      allowNull: false,
      comment: 'Parental age'
    },
    createDate: {
      type: DATE,
      defaultValue: NOW,
      field: 'create_date',
      comment: 'Creation time'
    },
    updateDate: {
      type: DATE,
      defaultValue: NOW,
      field: 'update_date',
      comment: 'Update time'
    }
  }, {
    // Remove createAt and updateatt
    timestamps: false,  
    // Table name corresponding to the instance
    tableName: 'parents'
  });
  return Parents;
};

Modify table name to plural configuration

Squelize will change Parents to plural by default, that is, add s directly to become Parents. In many cases, this is not what we want and can be modified.

1. In config JSON file, add the following configuration

This is global to avoid modifying settings in each model.

"define": {
   "freezeTableName": true
}

2. Yes, app model. The third parameter of define is configured

The project is already in config JSON is configured and has been commented out in the model. In addition, we can configure the desired table name tableName in the third parameter

Add default

Default values are necessary when updating or adding data. Generally, default values are set for id, create_date and updated_date.

id: { 
  defaultValue: UUIDV4
},
create_date: {
  defaultValue: NOW
},
update_date: {
  defaultValue: NOW
}

Three, data type of model field

Only some data types are shown here. Please refer to the document for details.

1. String (String)

DataTypes.STRING      // VARCHAR(255)
DataTypes.STRING(36)      // VARCHAR(36)
DataTypes.TEXT      // TEXT

2. Boolean

DataTypes.BOOLEAN      // TINYINT(1)

3. Number

DataTypes.INTEGER            // INTEGER
DataTypes.BIGINT(11)         // BIGINT(11)

DataTypes.FLOAT              // FLOAT
DataTypes.FLOAT(11)          // FLOAT(11)

4. Date

DataTypes.DATE          // DATETIME is applicable to mysql/sqlite

5,UUID

Serialize use serialize Uuidv1 or serialize Uuidv4 generates UUID as the primary key.

{
  type: DataTypes.UUID,
  defaultValue: Sequelize.UUIDV4 // Or serialize UUIDV1
}

4, Data query

1. Add

Single addition

const parents = await Parents.create({
  name: 'Da Chui Wang'
});

Other fields of the parents entity are set to default values, and the entity with new data will be returned.

Batch add

const parents = await Parents.bulkCreate({
  name: 'Da Chui Wang'
}, {
  name: 'Quack quack'
});

2. Inquiry

const result = await this.ctx.model.Parents.findAll({
  limit: 10, // Number of current pages
  offset: 0, // Start subscript
  order: [['create_date', 'desc']], // Sorting rules
  where: {
    age: {
      [Op.gt]: 36
    },
    name: {
      [Op.like]: 'king%'
    }
  },
  attributes: [ // Specifies the properties to return
    'id',
    'name',
    // The first parameter is an attribute, the second parameter is an alias, and the returned data is returned as an alias
    ['create_date', 'createDate'] 
  ]
});

Operator

Serialize provides a variety of operators. The common ones are written in the examples. For others, please refer to the documentation.

const result = await this.ctx.model.Parents.findAll({
  where: {
    age: {
      // [Op.gt]: 36,
      // [Op.eq]: 39, // = 39
      // [Op.ne]: 39 // != 39
      // [Op.gt]: 32 // > 32
      // [Op.gte]: 32 // >= 32
      // [Op.lt]: 32 // < 32
      // [Op.lte]: 32 // <= 32
      // [Op.between]: [32, 35],  // BETWEEN 32 AND 35
      // [Op.notBetween]: [32, 35], // NOT BETWEEN 32 AND 35
      // [Op.in]: [36, 38], // IN [36, 38]
      // [Op.notIn]: [36, 38],  // NOT IN [36, 38]  
      
      // combination
      // [Op.or]: {
      //   [Op.lt]: 36,
      //   [Op.eq]: 60
      // }

    },

    // Op.in abbreviation
    // age: [32, 39], same as ` age: {[op.in]: [32, 39]}`
    
    name: {
      // [Op.like]: 'Wang%',
      // [Op.like]: '%hat', // LIKE '%hat'
      // [Op.notLike]: '%hat',  // NOT LIKE '%hat'
      // [Op.startsWith]: 'hat',  // LIKE 'hat%'
      // [Op.endsWith]: 'hat',  // LIKE '%hat'
    },

    // Op.not instance
    // [Op.not]: [{
    //   age: [36,37,38]
    // }, {
    //   name: {
    //     [Op.like]: 'Wang%'
    //   }
    // }],

    create_date: {
      [Op.lt]: new Date(),
      [Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000)
    }
  }
});

3. Renew

const effectedNum = await this.ctx.model.Parents.update({
  name: 'Wang Xiaochui'
}, {
  where: {
    id: '123456789'
  }
})

4. Delete

const effectedNum = await this.ctx.model.Parents.destroy({
  where: '123456789'
})

5,count

The count method only counts the number of occurrences of elements in the database.

const effectedNum = await this.ctx.model.Parents.count({
  where: {
    age: {
      [Op.gt]: 25
    }
  }
})

6. max, min and sum

await this.ctx.model.Parents.max('age'); // 63

await this.ctx.model.Parents.max('age', {
  where: {
    age: {[Op.lt]: 40}
  }
}); // 39

await this.ctx.model.Parents.sum('age'); // 1027

7. Model lookup

This query method will be much more efficient.

findByPk

The findByPk method obtains only one entry from the table using the provided primary key.

await this.ctx.model.Parents.findByPk('12');

findOne

The findOne method gets the first entry it finds (which satisfies the optional query parameters provided).

await Project.findOne({ where: { age: 40 } });

findAndCountAll

It is very useful when dealing with paging related queries.

const { count, rows } = await this.ctx.model.Parents.findAndCountAll({
  where: {
    name: {
      [Op.like]: 'king%'
    }
  },
  offset: 10,
  limit: 0
});
console.log(count);
console.log(rows);

communication

You can always believe in light. Wechat search [front end technology post] pays attention to this daily fitness program!