Node Koa 连接Mysql 简单案例

基础代码

const Koa = require('koa')
const Router = require('koa-router')

// 实例
const app = new Koa()
const router = new Router()

// 服务
/*路由接口*/
// 可以使用链式调用
router
  .get('/', async ctx => {
    ctx.status = 200 //状态码
    ctx.body = 'Hello'
  })
  .get('/cxk', async ctx => {
    ctx.status = 200 //状态码
    ctx.body = 'Hello cxk'
  })
//.push(...)
//.delete(...)
//....

/*配置路由*/
app.use(router.routes()).use(router.allowedMethods())

// 监听服务端口
app.listen(3000, () => {
  console.log('http://localhost:3000')
})

需要增加的依赖: npm i mysql koa2-cors koa-bodyparser -s

新建文件bd.js

///bd.js
const mysql = require('mysql')

// 数据库
const poolSql = mysql.createPool({
  host: 'localhost', //url
  port: 3306, //端口
  user: 'root', //库名称
  password: '123456', //数据库密码
  database: 'sys', //表名称
})

// 查询
function query(sql, value = []) {
  return new Promise((resolve, reject) => {
    poolSql.query(sql, value, (error, result) => {
      error && reject(error) //有错误信息时reject
      resolve(result) //抛出查询结果
    })
  })
}
module.exports = query

表结构

最终代码

const Koa = require('koa')
const Router = require('koa-router')
const poolSql = require('./bd.js') //sql操作
const cors = require('koa2-cors') //跨域
const parser = require('koa-bodyparser') //获取接口参数

// 实例
const app = new Koa()
const router = new Router()

/*路由接口*/
// 可以使用链式调用
router
  .get('/', async ctx => {
    ctx.status = 200 //状态码
    ctx.body = '<h1>Hello</h1>'
  })
  // 查询
  .get('/user', async ctx => {
    ctx.status = 200 //状态码
    let body = { code: '0', data: null, message: 'ok' }
    try {
      let _sql = 'SELECT * FROM sys_user'
      let data = await poolSql(_sql)
      body.data = data
    } catch (error) {
      body.message = error
      body.code = '1'
    } finally {
      ctx.body = body
    }
  })
  // 添加
  .post('/add', async ctx => {
    let body = { code: '0', data: null, message: 'ok' }
    ctx.status = 200 //状态码
    let _info = ctx.request.body ?? {} //获取参数
    if (!_info.name) {
      body.message = '用户名不能为空'
      body.data = null
      ctx.body = body
      return
    }
    try {
      let _sql = 'INSERT INTO sys_user (name,password) VALUES (?,?)'
      let value = [_info.name, _info.password ?? ''] //数组元素会替换对应的问号
      let data = await poolSql(_sql, value)
      body.data = data
    } catch (error) {
      if (error?.errno == '1062') {
        body.message = '用户名已存在'
      } else {
        body.message = error
      }
      body.code = '1'
    } finally {
      ctx.body = body
    }
  })
  // 更新
  .put('/put', async ctx => {
    let body = { code: '0', data: null, message: 'ok' }
    ctx.status = 200 //状态码
    let _info = ctx.request.body ?? {} //获取参数
    if (!_info.name) {
      body.message = '用户名不能为空'
      ctx.body = body
      return
    }
    if (!_info.id) {
      body.message = '用户id不能为空'
      ctx.body = body
      return
    }
    if (!Number(_info.id)) {
      body.message = 'id格式不正确'
      ctx.body = body
      return
    }
    try {
      let _sql = 'UPDATE sys_user SET name=? WHERE id=?'
      let value = [_info.name, Number(_info.id)] //数组元素会替换对应的问号
      let data = await poolSql(_sql, value)
      body.data = data
    } catch (error) {
      body.message = error
      body.code = '1'
    } finally {
      ctx.body = body
    }
  })
  // 删除
  .delete('/delete/:id', async ctx => {
    let body = { code: '0', data: null, message: 'ok' }
    ctx.status = 200 //状态码
    let _info = ctx.params ?? {} //获取参数
    if (!Number(_info.id)) {
      body.message = 'id格式不正确'
      ctx.body = body
      return
    }
    try {
      let _sql = 'DELETE FROM sys_user WHERE id=?'
      let value = [Number(_info.id)]
      let data = await poolSql(_sql, value)
      body.data = data
    } catch (error) {
      body.message = '删除失败'
      body.code = '1'
    } finally {
      ctx.body = body
    }
  })

// 添加接口固定前缀
router.use('/api', router.routes())
// .use('/pc', pcRouter.routes())
// .use('/android', androidRouter.routes())

app
  .use(cors() /*跨域 */)
  .use(parser() /*获取接口参数 */)
  .use(router.routes() /*配置路由*/)
  .use(router.allowedMethods() /*配置路由*/)

// 服务端口
app.listen(3000, () => {
  console.log('http://localhost:3000')
})