This is the sixth day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

What is Sequelize?

Sequelize is a Promise-based Node.js ORM with powerful transaction support, association, preread and lazy loading, read replication, and more.

2. Use Sequelize to operate the MySQL database

For more operations, please refer to the official document: eggjs.org/zh-cn/tutor…

  1. Install egg-sequelize and mysql2

  2. Introduce the egg-sequelize plug-in in config/plugin.js

exports.sequelize = {
    enable: true.package: 'egg-sequelize'
};
Copy the code
  1. Add the following configuration in config/config.default.js
config.sequelize = {
    dialect: 'mysql'.host: '127.0.0.1'.port: 3306.database: 'bank'.username: 'root'.password: "123456"
  };
Copy the code

Sequelize MySQL to add, delete, modify, and query data

  1. Create a new folder model under app and create a file user.js in model

Note: If user is in define, we define the table to be users, that is, plural.

'use strict';

module.exports = app= > {
  const { STRING, INTEGER, DATE } = app.Sequelize;

  const User = app.model.define('user', {
    id: { type: INTEGER, primaryKey: true.autoIncrement: true },
    username: STRING(30),
    age: INTEGER,
    sex: STRING(30),
    created_at: DATE,
    updated_at: DATE,
  });

  return User;
};
Copy the code
  1. The following data table structure can be created using the Navicat visualization tool

Increase the data

  // Add data
  async create() {
    const user = await this.ctx.model.User.create({username: "Zhang".age: 20});
    this.ctx.body = user;
  }
Copy the code

Query data

More other query methods, please see the official document: www.sequelize.com.cn/core-concep…

  1. Query all data
  // Query data
  async query() {
    const userList = await this.ctx.model.User.findAll();
    this.ctx.body = userList;
  }
Copy the code
  1. Query the data of a specified field
// Query the data of the specified field
const userList = await this.ctx.model.User.findAll({attributes: ['id'.'username']});
Copy the code
  1. Apply where to query
const userList = await this.ctx.model.User.findAll({where: {"id": 2}});
Copy the code
  1. Sort the data
const userList = await this.ctx.model.User.findAll({order: [['id'.'ASC']]});
Copy the code

Modify the data

  • Modify according to the primary key
  async update() {
    // Modify according to the primary key
    const user = await this.ctx.model.User.findByPk(2);
    user.update({"username": "Emperor Qin Ii"."age": 10});
    this.ctx.body = "Modified successfully"
  }
Copy the code

Delete the data

  • Delete data based on the primary key
  async delete() {
    const data = await this.ctx.model.User.findByPk(2);
    if(! data) {this.ctx.state = 404;
      return;
    }
    data.destroy();
    this.ctx.body = "Deleted successfully";
  }
Copy the code