Why transfer from KOA2 to EGG (including database building)?

preface

• 【 Music Blog 】Online!

• Why not continue to use Koa2? (The following situation is what I want to solve when I personally develop KOA)

• Recently, WE want to change the interface to **RESTful API** style, and egg is friendly to support RESTful API style, of course, KOA can also be written in this style, just to say that someone else integrated egg, we do not need to re-create

• When the interface returned, I wanted to return the HTTP response code to the front-end, but FOUND that KOA could not get it, so I need to manually set the response status** response.status**, so this is the second reason why I want to transfer egg

• Koa is just a basic framework, and those of you who have developed Java know that the **MVC** architecture is comfortable for code visibility and development. Egg chose KOA as its basic framework, made some enhancements, and also developed in MVC architecture

• With its extensions, plug-ins, and code style, Egg is clearly better suited for enterprise applications than KOA

• In the later period, THE KOA of ** [music blog] ** gradually turned to Egg for reconstruction. If there were any problems, I would also write a summary of the article. I hope to learn from the students who love egg together!

Four aspects:

1. Install an egg

• Egg is the installed version of typescript

• npm init egg --type=ts

• After the project is successfully constructed, the directory is as follows:

• 

• You can see that the MVC hierarchy under the APP folder has been set up successfully

• Plugin.js is used to define plug-ins; Config.default. js is used as the basic configuration, and you can also create a new file to distinguish between the development environment, test environment, and online environment

• For details, please refer to the official documentation for details

2. Select and install the database

2.1. An egg – mysql

• Install egg-mysql in the Egg project

• npm i egg-mysql

• Once installed, NPM I installs dependencies and runs NPM run dev

• Enable egg-mysql plugin in {app_root}/config/plugin.js:

• ‘use strict’;

  /** @type Egg.EggPlugin */
  module.exports = {
      //mysql
      mysql:{
          enable:true,
          package:'egg-mysql',
      }
  };
  Copy the code

• In the configuration file to configure mysql database connection address {app_root} / config/config. The default. Js

• ‘use strict’;

  /** @mysql */ config.mysql = { //database configuration client:{ //host host:'localhost', //port port:'3306', //username user:'root', //password password:'123456', //database database:'qynbgl' }, // Load into app,default is open // Load into app,default is open app:true, //load into agent,default is close // Load into agent, The default value is "off" agent:false,};Copy the code

At this point, our egg-mysql configuration is successful, and we can use it

• ** Egg -mysql **

• Get to find something

• let result = await this.app.mysql.get("user",{id:1})

• Get Searches for multiple pieces of data

• const page = 1, per = 10; // Pass the value to the service through the Controller layer

  const data = await (this.app as any).mysql.select(this.model, { // columns: [' id ', 'name'], / / query field, and you don't write full query the equivalent query * where: {/ / name: 'Jack'}, / / query condition the orders: [['id', 'asC '] // descending asC], limit: (page - 1) * per, offset: per // offset: per // page - 1 offset: per // page - 1 offset: per // page - 1 offset: per})Copy the code

• Add data

• let result = await this.app.mysql.insert("user",{username:"lisi",password:"1234"})

• The first way to modify data is by primary key (the object must contain the primary key ID)

• Let result = await this.app.mysql.update('user',{id:2, username:' id '});

• The second way to modify data is through SQL

• let results=await this.app.mysql.query( 'update user set username = ? where id = ? ',[" Wang Wu ",2]);

• Delete data (by ID)

• let result= await this.app.mysql.delete('user',{ id:3 });

• Execute SQL

• this.app.mysql.query(sql,values);

2.2. An egg – sequelize

• introduce

• The Egg-mysql plug-in is the underlying operational database, so performance is best

• In complex applications, we may need an ORM framework to help manage the data layer code

• Sequelize is a widely used ORM framework that supports multiple data sources such as MySQL, PostgreSQL, SQLite, and MSSQL.

• The installation

• npm install --save egg-sequelize mysql2

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

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

• Write the sequelize configuration in config/config.default.js

• ‘use strict’;

  /** @sequelize */ config.sequelize = {dialect: 'mysql', host: '127.0.0.1', port: 3306, database: 'qynbgl', username: 'root', // database user name password: '123456'};Copy the code

There is a hole in the sequelize object on the official website where username and password Settings are not complete

• Initialize the database and Migrations **

• We can set up the table using mysql commands, but there is a problem. If the structure changes, how can we change the data structure quickly?

• This is where Migrations are needed to help us manage changes to our data structures.

• Sequelize provides the sequelize-CLI tool to implement Migrations

• Install sequelize – cli

• npm install --save-dev sequelize-cli

• In the Egg project, we wanted to put all migrations-related content in the Database directory, so we created 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

• Initializes Migrations configuration files and directories

• npx sequelize init:config npx sequelize init:migrations

• Json file and database/migrations directory will be generated after execution. We will modify the contents of database/config.json to the database configuration used in our project:

• ‘use strict’;

  /** @config*/ { "development": { "username": "root", "password": "123456", "database": "qynbgl", "host": "127.0.0.1", "dialect": "mysql"}, "test": {... }, "production" : {" username ":" root ", "password" : "123456", "database" : "qynbgl", "the host" : "127.0.0.1", "the dialect" : "mysql" } }Copy the code

• With sequelize-CLI and associated configuration initialized, we can start writing the project’s first Migration file to create one of our Users tables.

• npx sequelize migration:generate --name=init-users

• The migration file (${timestamp}-init-users.js) is generated in the database/migrations directory, and we modify it to handle initializing the Users table:

• ‘use strict’;

  Module.exports = {// creates the users table up when performing database upgrade: async (queryInterface, Sequelize) => { const { INTEGER, DATE, STRING } = Sequelize; await queryInterface.createTable('users', { id: { type: INTEGER, primaryKey: true, autoIncrement: true }, name: STRING(30), age: INTEGER, created_at: DATE, updated_at: DATE, }); }, // Drop the users table down: async queryInterface => {await queryInterface. DropTable ('users'); }};Copy the code

• Execute migrate to change the database

• NPX sequelize DB :migrate NPX sequelize DB :migrate # NPX sequelize DB :migrate:undo # NPX sequelize DB :migrate:undo # NPX sequelize DB :migrate:undo:all db:migrate:undo:all

After execution, our database initialization is complete (open Navicat to see if the table was created successfully).

• Write the code

• Now we are ready to write the code to implement the business logic. First, we will write the user model in app/model/ :

• ‘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

• This Model can be accessed in Controller and Service via app.model.User or ctx.model.User, for example we write app/ Controller /users.js

• ‘use strict’;

  const Controller = require('egg').Controller; function toInt(str) { if (typeof str === 'number') return str; if (! str) return str; return parseInt(str, 10) || 0; } class UserController extends Controller { async index() { const ctx = this.ctx; const query = { limit: toInt(ctx.query.limit), offset: toInt(ctx.query.offset) }; ctx.body = await ctx.model.User.findAll(query); } async show() { const ctx = this.ctx; ctx.body = await ctx.model.User.findByPk(toInt(ctx.params.id)); } async create() { const ctx = this.ctx; const { name, age } = ctx.request.body; const user = await ctx.model.User.create({ name, age }); ctx.status = 201; ctx.body = user; } async update() { const ctx = this.ctx; const id = toInt(ctx.params.id); const user = await ctx.model.User.findByPk(id); if (! user) { ctx.status = 404; return; } const { name, age } = ctx.request.body; await user.update({ name, age }); ctx.body = user; } async destroy() { const ctx = this.ctx; const id = toInt(ctx.params.id); const user = await ctx.model.User.findByPk(id); if (! user) { ctx.status = 404; return; } await user.destroy(); ctx.status = 200; } } module.exports = UserController;Copy the code

• Finally we mount the controller to the route:

// app/router.js

module.exports = app => { const { router, controller } = app; router.resources(‘users’, ‘/users’, controller.users); };

• forusersThe tableCURDThe interface to the operation is developed and can be passedPostmanVerify by GET requesthttp://127.0.0.1:8000/usersData can be requested

As much as possible, database operations are performed at the Service layer

With a choice between mysql (simple, good performance) and Sequelize (complex), depending on the project, you can choose which way to operate the database

Mysql common statements

(1)关联表查询（查询评价表，关联用户表头像和昵称）
select evaluate.*, user.name, user.figureurl from evaluate 
left join user on evaluate.userId = user.id where cId = 123

(2)更新商品点赞+1，并且一次更新id为1，2，3三条记录
update commodity set praise = praise + 1 where id in (1,2,3)
Copy the code

3. RESTful API 

• ‘use strict’;

  /** @app/router.js*/
  module.exports = app => {
    const { router, controller } = app;
    router.resources('posts', '/api/posts', controller.posts);
    router.resources('users', '/api/v1/users', controller.v1.users); // app/controller/v1/users.js
  };
  Copy the code

• The code above deploys a set of CRUD paths on the /posts path, corresponding to Controller ‘app/ Controller /posts.js’. Then, you just need to implement the corresponding functions in posts.js.

• 

• Egg-sequelize API /controller/users.js API /controller/users.js API /controller/users.js API /controller/users.js API /controller/users.js API

4. Write middleware (uniformly obtain GET and POST parameters)

• We know that we get parameters from egg

• Get the argument to get: ctx.query

• Get the argument to POST: ctx.request.body

• Implementation effect: We now want ctx.params to GET the parameters of the request, whether it is a GET or POST request, and write a middleware below

• There are several steps to writing middleware:

• Create a new Middleware folder in your app directory

• Create a new params.js in the middleware, with the following content

• Middleware * can use ctx.params to get or post request parameters */

  module.exports = options => { return async function params(ctx, next) { ctx.params = { ... ctx.query, ... ctx.request.body } await next(); }; };Copy the code

• In the/config/config. Default. Injection of middleware in js

• /** * appInfo */

  'use strict'; module.exports = appInfo => { const config = exports = {}; Config. Middleware = ['params',]; return config; };Copy the code

• use

• /** * Add article interface */

  'use strict'; const Service = require('egg').Service; class ArticleService extends Service { async add() { const { ctx } = this; Const {userId, title, content,} = ctx.params; const result = await ctx.model.Article.create({ userId, title, content, }); return result; } } module.exports = ArticleService;Copy the code

The original address

(6) — an egg egg study note + mysql (sequelize) + vue implementation curd: segmentfault.com/a/119000001…