This article is based on the completed development process from the perspective of example, step by step to build an out-of-the-box Egg. Js application, let’s get started!

Full project address: gitee.com/koukaile/eg…

Table of contents for this series

  • Egg enterprise actual combat (I) – frame building
  • Egg enterprise actual combat (2) – login module
  • Egg enterprise combat (3) – database

01. Use sequelize to manage the database

Sequelize is a widely used ORM framework in the Node.js community that supports multiple data sources such as MySQL, PostgreSQL, SQLite, and MSSQL.

The use of sequelize

  • The installation
npm install --save egg-sequelize mysql2
Copy the code
  • Introduce the egg-sequelize plug-in into config/plugin.js
exports.sequelize = {
  enable: true,
  package: 'egg-sequelize',
};
Copy the code
  • Write the Sequelize configuration in config/config.default.js (or as described in Environment Configuration) (note freezeTableName and timestamps)
Config. sequelize = {// Database type dialect: 'mysql', // host host: 'localhost', // Port number port: '3306', // User name user: 'admin', // password: '123456', // database name: Set timezone: '+08:00', define: {timezone: '+08:00'; FreezeTableName = create_time and update_time; freezeTableName = create_time and update_time; //freezeTableName = false; //freezeTableName = false;Copy the code

Sequelize – cli management

As each iteration of a project evolves, it is possible to make changes to the database data structure. How do you keep track of these changes from iteration to iteration and quickly change the data structure between different environments (development, test, CI) and iterations? This is where we need Migrations to help us manage changes to data structures. Sequelize provides the Sequelize-cli tool to implement Migrations, and we can also introduce sequelize-cli into the egg project.

  • Install sequelize – cli
npm install --save-dev sequelize-cli
Copy the code
  • In the Egg project, we want to keep 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'),
};
Copy the code
  • Initialize the Migrations profile and directory
npx sequelize init:config
npx sequelize init:migrations
Copy the code
  • The database/config.json file and the database/migrations directory

Let’s change the contents of database/config.json to the database configuration used in our project:

{ "development": { "username": "admin", "password": "123456", "database": "EggZoneFrame", "host": "127.0.0.1" and "the dialect", "mysql"}, "test" : {" username ":" admin ", "password" : "123456", "database" : "Dialect ": 127.0.0.1", "dialect": "mysql"}, "production": {"username": "admin", "password": "123456", "database" : "EggZoneFrame", "the host" : "127.0.0.1", "the dialect" : "mysql"}}Copy the code
  • Create a table

Now that sequelize-cli and related configuration are initialized, we can start writing the project’s first Migration file to create one of our User tables

npx sequelize migration:generate --name=init-user
Copy the code

${timestamp}-init-users.js (); ${timestamp}-init-users.js ();

'use strict'; Module. exports = {// create users table up: async (queryInterface, Sequelize) => { const { INTEGER, DATE, STRING } = Sequelize; await queryInterface.createTable('user', { id: { type: INTEGER, primaryKey: true, autoIncrement: true }, name: STRING(30), age: INTEGER, created_at: DATE, updated_at: DATE, }); }, // Delete the users table from the function called when the database is degraded: async queryInterface => {await queryinterface.droptable ('user'); }};Copy the code
  • Execute Migrate to make database changes
Migrate # NPX sequelize db:migrate # You can use 'db:migrate:undo' to roll back NPX sequelize. You can use 'db:migrate:undo' to roll back a change to NPX sequelize db:migrate:undo:allCopy the code

After that, our database initialization is complete.

Write the code

Now that we are ready to write the code to implement the business logic, let’s write the user model in the app/ Model/directory:

'use strict';

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

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

  return User;
};
Copy the code

The Model can then be accessed from the Controller and Service via app.model.User or ctx.model.User

  • Write controllerc layer
'use strict';

/**
 * @Controller
**/

const Controller = require('egg').Controller;

class HomeController extends Controller {
  async User() {
    const { ctx } = this;
    ctx.body = await this.service.home.User();
  }
}

module.exports = HomeController;
Copy the code
  • The service layer to write
'use strict';

const Service = require('egg').Service;

class HomeService extends Service {
  async User() 
    const { ctx } = this;
    const res = await ctx.model.User.findAll();
    return res
  }
}

module.exports = HomeService;
Copy the code
  • The controller mounts a route:
// app/router.js
module.exports = app => {
  const { router, controller } = app;
  router.resources('/user/login', controller.user);
};
Copy the code

02. Sequelize query syntax

increase

await this.ctx.model.UserAuth.create({
  identityType: IdentityType.username,
  identifier: username,
  credential: password,
});
Copy the code

delete

await this.ctx.model.User.destroy({ where: { id: userId } });
Copy the code

Modify the

await ctx.model.User.update({ alias: '233' }, { where: { id: userId } });
Copy the code

To find the

await this.ctx.model.User.findAll({ where: { id: userId } });
await this.ctx.model.User.findAndCountAll({ where: { id: userId } });
await this.ctx.model.User.findOne({ where: { id: userId } });
Copy the code

Single-to-single Associated Query (1:1)

User. Associate = function (){app.model.user.hasone (app.model.user.userone, app.model.userlogin, app.model.userlogin, app.model.userlogin, app.model.userlogin, app.model.userlogin, {as:'menu',sourceKey: 'uuid',foreignKey:'uuid'}); } //controller/user.js let user = await ctx.model.User.findOne({ include:{as:'menu',model:ctx.model.UserLogin}, where:{user_number:user_number} })Copy the code

One-to-many Associated Query (1: N)

Multiple permissions in a menu

User.associate = function (){ app.model.Menu.hasMany(app.model.Right, { as: 'rights', sourceKey: 'id', foreignKey: 'menuId' }); } this.ctx.model.Menu.findAll({ include: [ { as: 'rights', model: this.ctx.model.Right, attributes: [' id ', 'name', 'code', 'menuId'],},], / / ranking order: [[col (' rights. CreatedAt '), 'ASC']],}); Const rows = [{id: 1, name: 'front page' rights: [{id: 1, name: 'see' code: 'read' menuId: 1,},],},];Copy the code

Many-to-many Associated Query (n: N)

A user has multiple roles a role can be owned by many users

/ / need a relational table, there is a field userId and roleId / / when to delete User or a Role, you need to remove the corresponding lines associated app. Model. Role. BelongsToMany (app) model) User, {as: 'users', through: app.model.UserRoleRelation, foreignKey: 'roleId' }); app.model.User.belongsToMany(app.model.Role, { as: 'roles', through: app.model.UserRoleRelation, foreignKey: 'userId' }); const user = await this.ctx.model.User.findOne({ include: [ { model: this.ctx.model.Role, as: 'roles', through: { attributes: [] }, }, ], where: { id: userId, }, }); {id: "1", username: "admin", avatar: null, alias: "administrator ", roles: [{id: "1", name:" super administrator ", remark: "SuperAdmin ", type: "superAdmin",}]}Copy the code

03. Summary

If all of the above are set up, then the Node framework Sequelize database is set up. Give it a try and give it a like if you like it