Koa2 + VUe2 +mysql full stack development record

I have a development record because I want to make a personal project myself (combing through the development process is also a knowledge consolidation process)

A general DEMO of koA2 +vue2+mysql personal (the example of this article)

Koa2 + vue2 + mysql making address

The front-end tool

• vue
• vue-router
• vuex
• axios
• element ui Page UI component
• echartsjs Baidu's powerful chart display
• vue-admin-template Flower pants big guy a practical management background template Form a complete set of tutorial
• vue-i18n internationalization
• scss

The back-end tool

• koa
• koa-bodyparser Parse the body in the PUT/POST request
• koa-convert
• koa-json
• koa-jwt JWT authentication
• koa-logger
• koa-mysql-session
• koa-onerror
• koa-router
• koa-session-minimal
• koa-static
• koa-views
• koa2-cors To deal with cross domain
• md5 encryption
• moment Time to deal with
• mysql

The front piece

There is nothing to write about in the front end, just some changes to vue-admin-template

SRC/utils/request. Js

• Request interceptor

  // Request interceptor
  service.interceptors.request.use(
    config= > {
      if (store.getters.token) {
        // config.headers['X-Token'] = getToken()
        // Because JWT authentication is used, the header is changed to the following
        config.headers['Authorization'] = 'Bearer ' + getToken() // Allow each request to carry a user-defined token. Change the token based on the actual situation
      }
      return config
    },
    error => {
      // Do something with request error
      console.log(error) // for debug
      Promise.reject(error)
    }
  )
Copy the code

• The response of interceptors

  // Response interceptor
  service.interceptors.response.use(
    response= > {
      /** * if the code is not 0, it can be changed according to its own business */
      const res = response.data
      if(res.code ! = =0) { // If the value returned by the background is 0, success is achieved
        Message({
          message: res.message,
          type: 'error'.duration: 5 * 1000
        })

        // 70002: invalid token; 50012: Another client is logged in. 50014:Token expired;
        if (res.code === 70002 || res.code === 50012 || res.code === 50014) {
          MessageBox.confirm(
            'You have been logged out, you can cancel to remain on this page, or log in again'.'Sure to log out',
            {
              confirmButtonText: 'Log in again'.cancelButtonText: 'cancel'.type: 'warning'
            }
          ).then((a)= > {
            store.dispatch('FedLogOut').then((a)= > {
              location.reload() // To re-instantiate vue-Router objects to avoid bugs})})}return Promise.reject('error')}else {
        return response.data
      }
    },
    error => {
      console.log('err' + error) // for debug
      Message({
        message: error.message,
        type: 'error'.duration: 5 * 1000
      })
      return Promise.reject(error)
    }
  )
Copy the code

Encapsulates an Echart component

• Specifically refer to my other article using Echarts usage records in Vue

The back-end article

Build the project directory

• The KOA-generator is generated through the project generator

1. npm install -g koa-generator
2. koa2 /server && cd /server
3. npm install

• Installation of components

1. npm i jsonwebtoken koa-jwt koa-mysql-session koa-session-minimal koa2-cors md5 moment mysql save --save

• Configure the app. Js

  const Koa = require('koa')
  const jwt = require('koa-jwt')
  const app = new Koa()
  const views = require('koa-views')
  const json = require('koa-json')
  const onerror = require('koa-onerror')
  const bodyparser = require('koa-bodyparser')
  const logger = require('koa-logger')
  const convert = require('koa-convert');

  var session = require('koa-session-minimal')
  var MysqlStore = require('koa-mysql-session')
  var config = require('./config/default.js')
  var cors = require('koa2-cors')

  const users = require('./routes/users')
  const account = require('./routes/account')

  // error handler
  onerror(app)

  // JWT configuration error returned
  app.use(function(ctx, next) {
    return next().catch(err= > {
      if (401 == err.status) {
        ctx.status = 401
        ctx.body = ApiErrorNames.getErrorInfo(ApiErrorNames.INVALID_TOKEN)
        // ctx.body = {
        // // error: err.originalError ? err.originalError.message : err.message
        // }
      } else {
        throw err
      }
    })
  })

  // Unprotected middleware
  app.use(function(ctx, next) {
    if (ctx.url.match(/^\/public/)) {
      ctx.body = 'unprotected\n'
    } else {
      return next()
    }
  })

  // Middleware below this line is only reached if JWT token is valid

  app.use(
    jwt({ secret: config.secret, passthrough: true }).unless({
      path: [/\/register/, /\/user\/login/]
    })
  )

  // middlewares
  app.use(convert(bodyparser({
    enableTypes: ['json'.'form'.'text']
  })))
  app.use(convert(json()))
  app.use(convert(logger()))
  app.use(require('koa-static')(__dirname + '/public'))

  app.use(views(__dirname + '/views', {
    extension: 'pug'
  }))

  // logger
  app.use(async (ctx, next) => {
    const start = new Date(a)await next()
    const ms = new Date() - start
    console.log(`${ctx.method} ${ctx.url} - ${ms}ms`)})// cors
  app.use(cors())

  // routes
  app.use(users.routes(), users.allowedMethods())
  app.use(account.routes(), account.allowedMethods())

  // error-handling
  app.on('error', (err, ctx) => {
    console.error('server error', err, ctx)
  });

  module.exports = app

Copy the code

• Create a config folder to store database connections and other operations

default.js

// Database configuration
  const config = {
    port: 3000.database: {
      DATABASE: 'xxx'./ / database
      USERNAME: 'root'./ / user
      PASSWORD: 'xxx'./ / password
      PORT: '3306'./ / port
      HOST: '127.0.0.1' // Service IP address
    },
    secret: 'jwt_secret'
  }

  module.exports = config

Copy the code

Database related (mysql)

CreateTables createTables createTables createTables createTables createTables createTables

// Create a database table
const createTable = {
  users: 'CREATE TABLE IF NOT EXISTS user_info (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT ', User_id VARCHAR (100) NOT NULL COMMENT 'iD ', user_name VARCHAR (100) NOT NULL COMMENT' iD ', user_name VARCHAR (100) NOT NULL COMMENT 'id ', User_pwd VARCHAR (100) NOT NULL COMMENT 'password ', user_head VARCHAR (225) COMMENT' profile ', User_mobile VARCHAR (20) COMMENT 'mobile ', user_email VARCHAR (64) COMMENT' email ', User_creatdata TIMESTAMP NOT NULL DEFAULT NOW() COMMENT 'register date ', User_login_time TIMESTAMP DEFAULT NOW() COMMENT '新 时 ', user_count INT COMMENT' 新 时 ') ENGINE = INNODB charset = utf8; `.role: 'CREATE TABLE IF NOT EXISTS ROLE_info (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT ', Role_name VARCHAR (20) NOT NULL COMMENT 'id ', Role_description VARCHAR (255) DEFAULT NULL COMMENT 'description') ENGINE = INNODB charset = utf8; `.permission: 'CREATE TABLE IF NOT EXISTS permission_info (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT ', Permission_name VARCHAR (20) NOT NULL COMMENT 'id ', Permission_description VARCHAR (255) DEFAULT NULL COMMENT 'description') ENGINE = INNODB charset = utf8; `.userRole: 'CREATE TABLE IF NOT EXISTS user_role (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT ', User_id INT NOT NULL COMMENT 'Associate user ', role_id INT NOT NULL COMMENT' associate role ', KEY fk_USER_ROLE_ROLE_info_1 (role_id), KEY fk_user_role_user_info_1 ( user_id ), CONSTRAINT fk_user_role_role_info_1 FOREIGN KEY ( role_id ) REFERENCES role_info ( id ) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_user_role_user_info_1 FOREIGN KEY ( user_id ) REFERENCES user_info ( id ) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = INNODB charset = utf8; `.rolePermission: 'CREATE TABLE IF NOT EXISTS roLE_Permission (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT ', Role_id INT NOT NULL COMMENT 'delete ', permission_id INT NOT NULL COMMENT' delete ', KEY fk_role_permission_role_info_1 ( role_id ), KEY fk_role_permission_permission_info_1 ( permission_id ), CONSTRAINT fk_role_permission_role_info_1 FOREIGN KEY ( role_id ) REFERENCES role_info ( id ) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_role_permission_permission_info_1 FOREIGN KEY ( permission_id ) REFERENCES permission_info ( id ) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = INNODB charset = utf8; `
}

module.exports = createTable
Copy the code

• Create a lib folder to store database queries

mysql.js

const mysql = require('mysql')
const config = require('.. /config/default')
const createTables = require('.. /config/createTables.js')

var pool = mysql.createPool({
  host: config.database.HOST,
  user: config.database.USERNAME,
  password: config.database.PASSWORD,
  database: config.database.DATABASE
})

let query = function(sql, values) {
  return new Promise((resolve, reject) = > {
    pool.getConnection(function(err, connection) {
      if (err) {
        resolve(err)
      } else {
        connection.query(sql, values, (err, rows) => {
          if (err) {
            reject(err)
          } else {
            resolve(rows)
          }
          connection.release()
        })
      }
    })
  })
}

let createTable = function(sql) {
  return query(sql, [])
}

/ / table
// createTable(createTables.users)
// createTable(createTables.role)
// createTable(createTables.permission)
// createTable(createTables.userRole)
// createTable(createTables.rolePermission)

// Check whether the user exists
let findUser = async function(id) {
  let _sql = `
        SELECT * FROM user_info where user_id="${id}" limit 1;
    `
  let result = await query(_sql)

  if (Array.isArray(result) && result.length > 0) {
    result = result[0]}else {
    result = null
  }
  return result
}
// Query users and user roles
let findUserAndRole = async function(id) {
  let _sql = `
      SELECT u.*,r.role_name FROM user_info u,user_role ur,role_info r where u.id=(SELECT id FROM user_info where user_id="${id}" limit 1) and ur.user_id=u.id and r.id=ur.user_id limit 1;
    `
  let result = await query(_sql)

  if (Array.isArray(result) && result.length > 0) {
    result = result[0]}else {
    result = null
  }
  return result
}

// Update the login times and login time
let UpdataUserInfo = async function(value) {
  let _sql =
    'UPDATE user_info SET user_count = ? , user_login_time = ? WHERE id = ? ; '
  return query(_sql, value)
}

module.exports = {
  // Method of exposure
  createTable,
  findUser,
  findUserAndRole,
  UpdataUserInfo,
  getShopAndAccount
}

Copy the code

Koa route configuration

• Interface error message Unified method to create an error folder

ApiErrorNames.js

/**
 * API错误名称
 */
var ApiErrorNames = {};

ApiErrorNames.UNKNOW_ERROR = "UNKNOW_ERROR";
ApiErrorNames.SUCCESS = "SUCCESS";

/* Error: 10001-19999 */
ApiErrorNames.PARAM_IS_INVALID = 'PARAM_IS_INVALID';
ApiErrorNames.PARAM_IS_BLANK = 'PARAM_IS_BLANK';
ApiErrorNames.PARAM_TYPE_BIND_ERROR = 'PARAM_TYPE_BIND_ERROR';
ApiErrorNames.PARAM_NOT_COMPLETE = 'PARAM_NOT_COMPLETE';

/* User error: 20001-29999*/
ApiErrorNames.USER_NOT_LOGGED_IN = 'USER_NOT_LOGGED_IN';
ApiErrorNames.USER_LOGIN_ERROR = 'USER_LOGIN_ERROR';
ApiErrorNames.USER_ACCOUNT_FORBIDDEN = 'USER_ACCOUNT_FORBIDDEN';
ApiErrorNames.USER_NOT_EXIST = 'USER_NOT_EXIST';
ApiErrorNames.USER_HAS_EXISTED = 'USER_HAS_EXISTED';

/* Service error: 301-39999 */
ApiErrorNames.SPECIFIED_QUESTIONED_USER_NOT_EXIST = 'SPECIFIED_QUESTIONED_USER_NOT_EXIST';

/* System error: 40001-49999 */
ApiErrorNames.SYSTEM_INNER_ERROR = 'SYSTEM_INNER_ERROR';

/* Data error: 50001-599999 */
ApiErrorNames.RESULE_DATA_NONE = 'RESULE_DATA_NONE';
ApiErrorNames.DATA_IS_WRONG = 'DATA_IS_WRONG';
ApiErrorNames.DATA_ALREADY_EXISTED = 'DATA_ALREADY_EXISTED';

/* Interface error: 60001-69999 */
ApiErrorNames.INTERFACE_INNER_INVOKE_ERROR = 'INTERFACE_INNER_INVOKE_ERROR';
ApiErrorNames.INTERFACE_OUTTER_INVOKE_ERROR = 'INTERFACE_OUTTER_INVOKE_ERROR';
ApiErrorNames.INTERFACE_FORBID_VISIT = 'INTERFACE_FORBID_VISIT';
ApiErrorNames.INTERFACE_ADDRESS_INVALID = 'INTERFACE_ADDRESS_INVALID';
ApiErrorNames.INTERFACE_REQUEST_TIMEOUT = 'INTERFACE_REQUEST_TIMEOUT';
ApiErrorNames.INTERFACE_EXCEED_LOAD = 'INTERFACE_EXCEED_LOAD';

/* Permission error: 701-79999 */
ApiErrorNames.PERMISSION_NO_ACCESS = 'PERMISSION_NO_ACCESS';
ApiErrorNames.INVALID_TOKEN = 'INVALID_TOKEN';

/** * Error message corresponding to API error name */
const error_map = new Map(a); error_map.set(ApiErrorNames.SUCCESS, {code: 0.message: 'success' });
error_map.set(ApiErrorNames.UNKNOW_ERROR, { code: - 1.message: 'Unknown error' });
/* Error: 10001-19999 */
error_map.set(ApiErrorNames.PARAM_IS_INVALID, { code: 10001.message: 'Parameter invalid' });
error_map.set(ApiErrorNames.PARAM_IS_BLANK, { code: 10002.message: 'Parameter is null' });
error_map.set(ApiErrorNames.PARAM_TYPE_BIND_ERROR, { code: 10003.message: 'Parameter type error' });
error_map.set(ApiErrorNames.PARAM_NOT_COMPLETE, { code: 10004.message: 'Parameter missing' });
/* User error: 20001-29999*/
error_map.set(ApiErrorNames.USER_NOT_LOGGED_IN, { code: 20001.message: 'User not logged in' });
error_map.set(ApiErrorNames.USER_LOGIN_ERROR, { code: 20002.message: 'Account does not exist or password is incorrect' });
error_map.set(ApiErrorNames.USER_ACCOUNT_FORBIDDEN, { code: 20003.message: 'Account has been disabled' });
error_map.set(ApiErrorNames.USER_NOT_EXIST, { code: 20004.message: 'User does not exist' });
error_map.set(ApiErrorNames.USER_HAS_EXISTED, { code: 20005.message: 'User already exists' });
/* Service error: 301-39999 */
error_map.set(ApiErrorNames.SPECIFIED_QUESTIONED_USER_NOT_EXIST, { code: 30001.message: 'A business has a problem' });
/* System error: 40001-49999 */
error_map.set(ApiErrorNames.SYSTEM_INNER_ERROR, { code: 40001.message: 'System busy, please try again later' });
/* Data error: 50001-599999 */
error_map.set(ApiErrorNames.RESULE_DATA_NONE, { code: 50001.message: 'Data not found' });
error_map.set(ApiErrorNames.DATA_IS_WRONG, { code: 50002.message: 'Wrong data' });
error_map.set(ApiErrorNames.DATA_ALREADY_EXISTED, { code: 50003.message: 'Data already exists' });
/* Interface error: 60001-69999 */
error_map.set(ApiErrorNames.INTERFACE_INNER_INVOKE_ERROR, { code: 60001.message: 'Internal system interface call exception' });
error_map.set(ApiErrorNames.INTERFACE_OUTTER_INVOKE_ERROR, { code: 60002.message: 'External system interface call exception' });
error_map.set(ApiErrorNames.INTERFACE_FORBID_VISIT, { code: 60003.message: 'This interface is not accessible' });
error_map.set(ApiErrorNames.INTERFACE_ADDRESS_INVALID, { code: 60004.message: 'Interface address invalid' });
error_map.set(ApiErrorNames.INTERFACE_REQUEST_TIMEOUT, { code: 60005.message: 'Interface request timed out' });
error_map.set(ApiErrorNames.INTERFACE_EXCEED_LOAD, { code: 60006.message: 'Interface load is too high' });
/* Permission error: 701-79999 */
error_map.set(ApiErrorNames.PERMISSION_NO_ACCESS, { code: 70001.message: 'No access' });
error_map.set(ApiErrorNames.INVALID_TOKEN, { code: 70002.message: 'invalid token' });

// Get error information based on the error name
ApiErrorNames.getErrorInfo = (error_name) = > {

    var error_info;

    if (error_name) {
        error_info = error_map.get(error_name);
    }

    // If there is no corresponding error message, default 'unknown error'
    if(! error_info) { error_name = UNKNOW_ERROR; error_info = error_map.get(error_name); }return error_info;
}

// Return the correct information
ApiErrorNames.getSuccessInfo = (data) = > {

    var success_info;
    let name = 'SUCCESS';
    success_info = error_map.get(name);
    if (data) {
        success_info.data = data
    }

    return success_info;
}

module.exports = ApiErrorNames;
Copy the code

• Create controller folder

Write logic to route Users

const mysqlModel = require('.. /lib/mysql') // Introduce database methods
const jwt = require('jsonwebtoken')
const config = require('.. /config/default.js')
const ApiErrorNames = require('.. /error/ApiErrorNames.js')
const moment = require('moment')

/** * Common login */
exports.login = async (ctx, next) => {
  const { body } = ctx.request
  try {
    const user = await mysqlModel.findUser(body.username)
    if(! user) {// ctx.status = 401
      ctx.body = ApiErrorNames.getErrorInfo(ApiErrorNames.USER_NOT_EXIST)
      return
    }
    let bodys = await JSON.parse(JSON.stringify(user))
    // Match whether the passwords are equal
    if ((await user.user_pwd) === body.password) {
      let data = {
        user: user.user_id,
        // Generate a token and return it to the client
        token: jwt.sign(
          {
            data: user.user_id,
            // Set the token expiration time
            exp: Math.floor(Date.now() / 1000) + 60 * 60 // 60 seconds * 60 minutes = 1 hour
          },
          config.secret
        )
      }
      ctx.body = ApiErrorNames.getSuccessInfo(data)
    } else {
      ctx.body = ApiErrorNames.getErrorInfo(ApiErrorNames.USER_LOGIN_ERROR)
    }
  } catch (error) {
    ctx.throw(500)}}/** * Get user information */
exports.info = async (ctx, next) => {
  const { body } = ctx.request
  // console.log(body)
  try {
    const token = ctx.header.authorization
    let payload
    if (token) {
      payload = await jwt.verify(token.split(' ') [1], config.secret) // Decrypt the payload
      const user = await mysqlModel.findUserAndRole(payload.data)
      if(! user) { ctx.body = ApiErrorNames.getErrorInfo(ApiErrorNames.USER_NOT_EXIST) }else {
        let cont = user.user_count + 1
        let updateInfo = [
          cont,
          moment().format('YYYY-MM-DD HH:mm:ss'),
          user.id
        ]
        await mysqlModel
        .UpdataUserInfo(updateInfo)
        .then(res= > {
          let data = {
            avatar: 'https://wpimg.wallstcn.com/f778738c-e4f8-4870-b634-56703b4acafe.gif'.name: user.user_id,
              // roles: [user.user_admin === 0 ? 'admin' : '']
            roles: [user.role_name]
          }
          ctx.body = ApiErrorNames.getSuccessInfo(data)
        })
        .catch(err= > {
          ctx.body = ApiErrorNames.getErrorInfo(ApiErrorNames.DATA_IS_WRONG)
        })
      }
    } else {
      ctx.body = ApiErrorNames.getErrorInfo(ApiErrorNames.INVALID_TOKEN)
    }
  } catch (error) {
    ctx.throw(500)}}/** * Log out */
exports.logout = async (ctx, next) => {
  try {
    // ctx.status = 200
    ctx.body = ApiErrorNames.getSuccessInfo()
  } catch (error) {
    ctx.throw(500)}}Copy the code

Routes of the users. Js

const router = require('koa-router') ()// Import the routing function
const userControl = require('.. /controller/users') // Import logic
// const config = require('.. /config/default.js')

router.get('/'.async (ctx, next) => {
  'use strict'
  ctx.redirect('/user/login')})// In the middle of the route, the page is routed to /, which is the port number, and the page is directed to // user/login

router.get('/user/info', userControl.info)
router.post('/user/logout', userControl.logout)
router.post('/user/login', userControl.login)

module.exports = router
// Expose the page
Copy the code

note

Ctx.request Gets the body in the POST request ctx.query.xx gets the router.prefix(‘/account’) parameter in the GET request and adds a prefix to the router instance