egg.js sequelize database operation configuration

egg.js sequelize database operation configuration

Article directory

• egg.js sequelize database operation configuration
• • 1. Database configuration
  • 2. Migrate configuration
  • 3.Data table design and migration
  • 4. Model Creation

1. Database configuration

• Install and configure the egg-sequelize plug-in (it will assist us in loading the defined Model objects into app and ctx) and the mysql2 module:

npm install --save egg-sequelize mysql2

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

exports.sequelize = {<!-- -->
  enable: true,
  package: "egg-sequelize",
};

• In config/config.default.js

config.sequelize = {<!-- -->
  dialect: "mysql",
  host: "127.0.0.1",
  username: "root",
  password: "root",
  port: 3306,
  database: "egg-wechat",
  // China time zone
  timezone: " + 08:00",
  define: {<!-- -->
    //Cancel the pluralization of data table names
    freezeTableName: true,
    // Automatically write timestamp created_at updated_at
    timestamps: true,
    //Field generates soft deletion timestamp deleted_at
    // paranoid: true,
    createdAt: "created_at",
    updatedAt: "updated_at",
    // deletedAt: 'deleted_at',
    //All camel case naming formatting
    underscored: true,
  },
};

2. Migrate configuration

sequelize provides the sequelize-cli tool to implement Migrations. We can also introduce sequelize-cli into the egg project.

npm install --save-dev sequelize-cli

In the egg project, we want to put all database Migrations related content in the database directory, so we create a new .sequelizerc configuration file in the project root directory:

"use strict";

const path = require("path");

module.exports = {<!-- -->
  config: path.join(__dirname, "database/config.json"),
  "migrations-path": path.join(__dirname, "database/migrations"),
  "seeders-path": path.join(__dirname, "database/seeders"),
  "models-path": path.join(__dirname, "app/model"),
};

Initialize Migrations configuration files and directories

npx sequelize init:config
npx sequelize init:migrations
# npx sequelize init:models

After running, the database/config.json file and database/migrations directory will be generated. Let’s modify the contents in database/config.json and change it to the database configuration used in our project:

{<!-- -->
  "development": {<!-- -->
    "username": "root",
    "password": null,
    "database": "eggapi",
    "host": "127.0.0.1",
    "dialect": "mysql",
    "timezone": " + 08:00"
  }
}

Create database

npx sequelize db:create
#Upgrade database
npx sequelize db:migrate
# If there is a problem and needs to be rolled back, you can roll back a change through `db:migrate:undo`
# npx sequelize db:migrate:undo
# You can return to the initial state through `db:migrate:undo:all`
# npx sequelize db:migrate:undo:all

3. Data table design and migration

Create data migration table

npx sequelize migration:generate --name=user

1. After executing the command, the data table migration file will be generated in the database/migrations/ directory, and then defined

"use strict";

module.exports = {<!-- -->
  up: async (queryInterface, Sequelize) => {<!-- -->
    const {<!-- --> INTEGER, STRING, DATE, ENUM } = Sequelize;
    //Create table
    await queryInterface.createTable("user", {<!-- -->
      id: {<!-- -->
        type: INTEGER(20).UNSIGNED,
        primaryKey: true,
        autoIncrement: true,
      },
      username: {<!-- -->
        type: STRING(30),
        allowNull: false,
        defaultValue: "",
        comment: "username",
        unique: true,
      },
      nickname: {<!-- -->
        type: STRING(30),
        allowNull: false,
        defaultValue: "",
        comment: "...",
      },
      email: {<!-- -->
        type: STRING(160),
        comment: "User Email",
        unique: true,
      },
      password: {<!-- -->
        type: STRING(200),
        allowNull: false,
        defaultValue: "",
      },
      avatar: {<!-- -->
        type: STRING(200),
        allowNull: true,
        defaultValue: "",
      },
      phone: {<!-- -->
        type: STRING(20),
        comment: "user mobile phone",
        unique: true,
      },
      sex: {<!-- -->
        type: ENUM,
        values: ["male", "female", "confidential"],
        allowNull: true,
        defaultValue: "Male",
        comment: "User gender",
      },
      status: {<!-- -->
        type: INTEGER(1),
        allowNull: false,
        defaultValue: 1,
        comment: "status",
      },
      sign: {<!-- -->
        type: STRING(200),
        allowNull: true,
        defaultValue: "",
        comment: "Personal signature",
      },
      area: {<!-- -->
        type: STRING(200),
        allowNull: true,
        defaultValue: "",
        comment: "region",
      },
      created_at: DATE,
      updated_at: DATE,
    });
  },

  down: async (queryInterface) => {<!-- -->
    await queryInterface.dropTable("user");
  },
};

Execute migrate to make database changes

npx sequelize db:migrate

4. Model Creation

// app/model/user.js
"use strict";
module.exports = (app) => {<!-- -->
  const {<!-- --> STRING, INTEGER, DATE, ENUM, TEXT } = app.Sequelize;
  // Configuration (Important: The configuration must be detailed, must!!!)
  const User = app.model.define("user", {<!-- -->
    id: {<!-- -->
      type: INTEGER(20).UNSIGNED,
      primaryKey: true,
      autoIncrement: true,
    },
    username: {<!-- -->
      type: STRING(30),
      allowNull: false,
      defaultValue: "",
      comment: "username",
      unique: true,
    },
    nickname: {<!-- -->
      type: STRING(30),
      allowNull: false,
      defaultValue: "",
      comment: "...",
    },
    email: {<!-- -->
      type: STRING(160),
      comment: "User Email",
      unique: true,
    },
    password: {<!-- -->
      type: STRING(200),
      allowNull: false,
      defaultValue: "",
    },
    avatar: {<!-- -->
      type: STRING(200),
      allowNull: true,
      defaultValue: "",
    },
    phone: {<!-- -->
      type: STRING(20),
      comment: "user mobile phone",
      unique: true,
    },
    sex: {<!-- -->
      type: ENUM,
      values: ["male", "female", "confidential"],
      allowNull: true,
      defaultValue: "Male",
      comment: "User gender",
    },
    status: {<!-- -->
      type: INTEGER(1),
      allowNull: false,
      defaultValue: 1,
      comment: "status",
    },
    sign: {<!-- -->
      type: STRING(200),
      allowNull: true,
      defaultValue: "",
      comment: "Personal signature",
    },
    area: {<!-- -->
      type: STRING(200),
      allowNull: true,
      defaultValue: "",
      comment: "region",
    },
    created_at: DATE,
    updated_at: DATE,
  });
  return User;
};