MVC koa2操作数据库

1.db.js 数据库连接配置

const database = {
  user: "root",
  password: "root",
  port: 3306,
  host: "localhost",
  database: "mqj",
};
module.exports = database;

2.query.js 引入mysql，并封装

const mysql = require("mysql");
const mysql_connect = require("../config/db.js");
let pool = mysql.createPool(mysql_connect);
let query = (sql, values) => {
  return new Promise((resolve, reject) => {
    pool.getConnection((err, connection) => {
      if (err) {
        reject(err);
      } else {
        connection.query(sql, values, (err, rows) => {
          if (err) {
            reject(err);
          } else {
            resolve(rows);
          }
          connection.release();
        });
      }
    });
  });
};
module.exports = {
  query,
};

3.controller.js 接收用户请求，委托给模型进行处理

let router = require("koa-router")();
let apiModel = require("./impl");

router.get("/user/list", async (ctx, next) => {
  await Promise.all([apiModel.getList()])
    .then((res) => {
      ctx.body = {
        code: 200,
        data: res,
        message: "获取列表成功",
      };
    })
    .catch((err) => {
      ctx.body = {
        code: 500,
        message: err,
      };
    });
});

router.get("/user/listR", async (ctx, next) => {
    await Promise.all([apiModel.getListR()])
      .then((res) => {
        ctx.body = {
          code: 200,
          data: res,
          message: "获取列表成功",
        };
      })
      .catch((err) => {
        ctx.body = {
          code: 500,
          message: err,
        };
      });
  });
module.exports = router;

4.impl.js 模型接口的实现

const dao = require("./dao");
const getList = async () => {
  const getList = dao.getList();
  return getList;
};

const getListR = async () => {
    const getList = dao.getListR();
    return getList;
  };

module.exports = {
  getList: getList,
  getListR: getListR,
};

5.dao.js 数据持久层,与数据库进行交互

const mysql = require("../../utils/query.js");
const redis = require("redis");
const client = redis.createClient(6379, "localhost");
client.set("user1", "[1,2,3,4,5,6,7,8,9,12,23,34,54,76]");

const getList = () => {
  let _sql = `select
	*
from
	t_user tu
inner join t_role_data_authorization trda on
	tu.Fuser_id = trda.Fuser_id
inner join t_data_authorization tda on
	tda.Fid = trda.Fauthorization_id
where
	tu.Fuser_id = 110;`;
  return mysql.query(_sql);
};
const getListR = () => {
  client.get("user1", function (err, v) {
    console.log("redis get hello err,v", err, v);
    return v;
  });
  client.on('connect',() => {
    console.log('connected to redis successfully!');
  })

  client.set("user1", "[1,2,3,4,5,6,7,8,9,12,23,34,54,76]");
};
module.exports = {
  getList: getList,
  getListR: getListR,
};

6.app.js 引入路由（附一些中间件）

const Koa = require('koa')
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 mysql=require('./config/db.js')

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

const user = require('./src/user/controller')
app.use(user.routes(), user.allowedMethods())

// error handler
onerror(app)

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

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

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

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

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

module.exports = app

目录结构：