koa 与 mysql

搭建koa脚手架 （koa2 my-project 一键生成）

onst koa=require("koa")
const app=new koa()
const path=require("path")
const router=require("./router/index")
const bodyParser=require("koa-bodyparser") //解析请求参数
const koaViews=require("koa-views") 
const views=path.join(__dirname,"./views")
app.use(bodyParser())
app.use(require("koa-static")(__dirname+"./public")) //解析静态资源
app.use(koaViews(views,{
    extension:'html'
}))
app.use(router.routes(),router.allowedMethods()) //开启路由
const PORT=process.env.PORT||8080  //端口号
app.listen(PORT)
module.exports=app

路由

一级路由
const router=require("koa-router")();
const userRouter=require("./user")
const classRouter=require("./class")
router.use("/user",userRouter.routes(),userRouter.allowedMethods()) //开启子路由
router.use("/class",classRouter.routes(),classRouter.allowedMethods())
module.exports=router

二级路由 user
const router=require("koa-router")();
const {query}=require("../mysql/index")
router.post("/addUser",async ctx=>{ //向数据库中添加数据
    let {username,department,position,date,classname,school}=ctx.request.body;
    let res=await query(`insert into user(username,department,position,date,classname,school) values(?,?,?,?,?,?)`,[
        username,department,position,date,classname,school
    ])
    ctx.body=res
})
router.post("/removeUser",async ctx=>{ //从数据库中删除数据
    let{id}=ctx.request.body;
    let res=await query(`delete from user where id=${id}`)
    ctx.body=res
})
module.exports=router

二级路由 class
const router=require("koa-router")();
const {query}=require("../mysql/index")
router.post("/search",async ctx=>{  //查询数据 多表连查
    let {username}=ctx.request.body;
    let res=await query(`select * from user where username='${username}'`)
    let userInfo=res[0]
    let result=await query(`select * from class where userid=${userInfo.id}`)
    ctx.body={
        userInfo,
        result:result[0]
    }
})
module.exports=router

数据库配置

config
module.exports={
    host     : 'localhost',
    user     : 'root',
    password : '123321',
    database : 'mynode'
  }

mysql
const mysql  = require('mysql');
const mysqlConfig=require("../config/index")
const connection = mysql.createConnection({
 ...mysqlConfig
});
connection.connect();
const query=($sql,options)=>{
   return new Promise((resolve,reject)=>{
        let callback=(error,result)=>{
            if(error){
                reject(error)
            }else{
                resolve(result)
            }
        }
        options=options?options:callback; 
        callback=options?callback:null;
        connection.query($sql,options,callback);
    })
}
module.exports={query}