node+koa链接MySQL数据库

app.js中引入koa
npm install koa
安装koa-bodyparser，解析post数据
npm install koa-bodyparser
安装数据库链接sequelize
npm install sequelize
安装koa路由koa-router
npm install koa-router
新建config文件夹，导出数据库基本信息

module.exports = {
  environment: 'dev',
  database: {
    dbName: 'koatest',
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'root'
  },
  security: {
    secretKey: "secretKey",
    // 过期时间 1小时
    expiresIn: 60 * 60
  }
}

新建db文件夹，开始链接数据库

const Sequelize = require('sequelize')

const {
  dbName,
  host,
  port,
  user,
  password
} = require('../config/config').database


const sequelize = new Sequelize(dbName, user, password, {
  dialect: 'mysql',
  host,
  port,
  logging: false,
  timezone: '+08:00',
  pool: {   //连接池设置
    max: 5, //最大连接数
    min: 0, //最小连接数
    idle: 10000
  }
})
// 用户信息表
const Account = sequelize.define('user', {
  username: {
    type: Sequelize.STRING,
    allowNull: false,
  },
  password: {
    type: Sequelize.STRING,
    allowNull: false,
  },
  role: {
    type: Sequelize.STRING,
    allowNull: false,
  },
});
// 创建模型
Account.sync({ force: false })

sequelize.authenticate().then(res => {
  console.log('Connection has been established successfully.');
}).catch(err => {
  console.error('Unable to connect to the database:', err);
})

module.exports = {
  Account
}

新建server文件，对用户信息表增删改查

const { Account } = require('../db/index');

class AccountService {
    // 获取所有用户
  async getAllUser() {
    console.log(await Account.findAll())
    return await Account.findAll({
      order: [
        ['id', 'desc']
      ]
    });
  };
  // 通过username 来查询
  async getAccountByUserName(name) {
    return await Account.findOne({
      where: {
        username: name,
      },
    });
  };
  // 新增账户
  async createAccount(account) {
    return Account.create(account);
  };
  // 更新账户
  async updateAccount(id, account) {
    const item = await getAccountById(id);
    if (item) {
      return item.update(account);
    } else {
      throw new Error('the account with id is not exist!');
    }
  }; 
};
module.exports = new AccountService();

新建控制文件controller

const AccountService = require("../service/user");

module.exports = {
  getAllUsers: async (ctx, next) => {
    const account = await AccountService.getAllUser();
    if (account.length) {
      ctx.body = {
        status: 0,
        msg: "success",
        data: account,
      };
    } else {
        console.log(this.body)
        this.body = {
        status: 1,
        data: [],
        msg: "用户信息不存在！",
      };
    }
  },
  login: async (ctx, next) => {
    console.log(ctx.request.body)
    const { username, password } = ctx.request.body;
    const account = await AccountService.getAccountByUserName(username);
    if (account) {
      console.log(account)
      if (password === account.password) {
        ctx.body = {
          status: 0,
          msg: "success",
          data: account,
        };
      } else {
        ctx.body = {
          status: 1,
          msg: "密码错误！",
        };
      }
    } else {
      ctx.body = {
        status: 1,
        msg: "用户信息不存在！",
      };
    }
  },
  addAccount: async (ctx, next) => {
    console.log(ctx.request, '222');
    const account = ctx.request.body;
    const count = AccountService.getAccountByUserName(account.username);
    ctx.type = "json";
    if (count > 0) {
      ctx.body = {
        status: 1,
        msg: "当前用户名已存在!",
      };
    } else {
      await AccountService.createAccount(account);
      ctx.type = "json";
      ctx.body = {
        status: 0,
      };
    }
  },
  updateAccount: async (ctx, next) => {
    const id = ctx.params.id;
    const account = ctx.request.body;
    await AccountService.updateAccount(id, account);
    ctx.type = "json";
    ctx.body = {
      status: 0,
    };
  },
};

新建router文件，设定路由

const router = require("koa-router")();
const accountController = require("../controller/user");

router.get("/", async (ctx, next) => {
  ctx.body = "Hello, world!";
});

router.get("/user/list", accountController.getAllUsers);
router.post("/login", accountController.login);
module.exports = router;

app.js中引用

const Koa = require("koa");
const bodyparser = require("koa-bodyparser");
const user = require('./router/user')

const app = new Koa();

app.use(bodyparser())
app.use(user.routes(), user.allowedMethods())

app.listen("5000", () => {
  console.log("Koa is listening in http://localhost:5000");
});