前端迈向架构师第一步-快速了解koa框架-链接数据库

「这是我参与11月更文挑战的第22天，活动详情查看：2021最后一次更文挑战」

Koa操作MySQL数据库

// 1.npm install --save mysql
 
// 2.引入模块
const mysql = require('mysql');
 
// 3.连接数据库
const connection = mysql.createConnection({
    host: '127.0.0.1',         // 数据库地址
    user: 'root',              // 用户名 
    password: '123456' ,       // 密码   
    database: 'my_data'        // 数据库名称   
})
 
// 4.执行sql语句操作数据库
connection.query('SELECT * FROM user',
     (error, results, fields) => {
        if (error) {
            console.log(error)
        };
        // 操作数据
 
        // 结束会话
        connection.release();
    }
);

注意，数据库会话操作执行完后，需要关闭掉，以免占用连接资源。

当有多个会话需要操作数据库时，为了避免每次会话都要重新连接，所以这时候就需要连接池管理会话。

// 1.npm install --save mysql
 
// 2.引入模块
const mysql = require('mysql');
 
// 3.创建数据池
const pool = mysql.createPool({
    host: '127.0.0.1',         // 数据库地址
    user: 'root',              // 用户名 
    password: '123456',        // 密码   
    database: 'my_data'        // 数据库名称 
})
 
// 4.在数据池中进行会话操作
pool.getConnection(function (err, connection) {
    // 5.执行sql语句操作数据库
    connection.query('SELECT * FROM user', 
        (error, results, fields) => {
        
            // 执行操作
 
            // 释放连接
            connection.release();
            // 如果有错误就抛出
            if (error) {
                throw error;
            }
        }
    )
})

连接mongoose

// app.js

const mongoose = require("mongoose");
const { connectionStr } = require("./config");

mongoose.connect(
  connectionStr,
  { useUnifiedTopology: true, useNewUrlParser: true },
  () => console.log("connect")
);
mongoose.connection.on("error", console.error);

定义Schema

// models/User.js

const mongoose = require("mongoose");

const { Schema, model } = mongoose;
const userSchema = new Schema(
  {
    name: { type: String, required: true },
    // select 为 false 时查询时默认隐藏
    password: {
      type: String,
      required: true,
      select: false,
      set(val) {
        return require("bcrypt").hashSync(val, 10);
      },
    },
    avatar_url: { type: String },
    gender: {
      type: String,
      enum: ["male", "female"],
      default: "male",
      required: true,
    },
    headline: { type: String },
    locations: {
      type: [{ type: Schema.Types.ObjectId, ref: "Topic" }],
      select: false,
    },
    business: { type: Schema.Types.ObjectId, ref: "Topic", select: false },
    employments: {
      type: [
        {
          company: { type: Schema.Types.ObjectId, ref: "Topic" },
          job: { type: Schema.Types.ObjectId, ref: "Topic" },
        },
      ],
      select: false,
    },
    educations: {
      type: [
        {
          school: { type: Schema.Types.ObjectId, ref: "Topic" },
          major: { type: Schema.Types.ObjectId, ref: "Topic" },
          diploma: {
            type: Number,
            enum: [1, 2, 3, 4, 5],
          },
          entrance_year: { type: Number },
          graduation_year: { type: Number },
        },
      ],
      select: false,
    },
    following: {
      type: [
        {
          type: Schema.Types.ObjectId,
          ref: "User",
        },
      ],
      select: false,
    },
    // 关注话题
    followingTopics: {
      type: [{ type: Schema.Types.ObjectId, ref: "Topic" }],
      select: false,
    },
    // 关注问题
    followingQuestions: {
      type: [{ type: Schema.Types.ObjectId, ref: "Question" }],
      select: false,
    },

    likingAnswers: {
      type: [
        {
          type: Schema.Types.ObjectId,
          ref: "Answer",
        },
      ],
      select: false,
    },
    dislikingAnswers: {
      type: [
        {
          type: Schema.Types.ObjectId,
          ref: "Answer",
        },
      ],
      select: false,
    },
    collectingAnswers: {
      type: [
        {
          type: Schema.Types.ObjectId,
          ref: "Answer",
        },
      ],
      select: false,
    },
  },
  {
    timestamps: true,
  }
);

module.exports = model("User", userSchema);

字段过滤

让一些字段默认不显示，方法是修改schema，在默认不显示的字段上添加select:false。在查询时如果需要这个字段，需要加上select("+fieldName")

权限认证

// controllers/users.js

const jwt = require("jsonwebtoken");
const User = require("../models/User");
const { secret } = require("../config");
const bcrypt = require("bcrypt");
class UsersCtl {

  // 登录
  async login(ctx) {
    const { name, password } = ctx.request.body;
    const user = await User.findOne({ name }).select("+password");
    if (!user) {
      ctx.throw(422, "用户不存在");
    }
    const isValid = bcrypt.compareSync(password, user.password);
    if (!isValid) {
      ctx.throw(422, "用户密码错误");
    }

    const token = jwt.sign({ _id: user._id, name }, secret, {
      expiresIn: "1d",
    });
    ctx.body = { token };
  }

  // 检查权限，只允许修改本人信息
  async checkOwner(ctx, next) {
    if (ctx.params.id !== ctx.state.user._id) {
      ctx.throw(403, "没有权限");
    }
    await next();
  }
}
module.exports = new UsersCtl();

// routes/users.js

const Router = require("koa-router");
const router = new Router({ prefix: "/users" });
const jwt = require("jsonwebtoken");
const { secret } = require("../config");
const {
  find,
  findById,
  create,
  update,
  del,
  login,
  checkOwner,
} = require("../controllers/users");

const auth = async (ctx, next) => {
  const { authorization = "" } = ctx.request.header;
  const token = authorization.replace("Bearer ", "");
  try {
    const user = jwt.verify(token, secret);
    ctx.state.user = user;
  } catch (error) {
    ctx.throw(401, error.message);
  }
  await next();
};

router.get("/", find);
router.get("/:id", findById);
router.post("/", create);
router.patch("/:id", auth, checkOwner, update);
router.delete("/:id", auth, checkOwner, del);
router.post("/login", login);

module.exports = router;

koa-jwt实现权限认证

// 1. 安装 npm i koa-jwt

// 2.使用
const Router = require("koa-router");
const router = new Router({ prefix: "/users" });
// const jwt = require("jsonwebtoken");
const jwt = require("koa-jwt");
const { secret } = require("../config");
const {
  find,
  findById,
  create,
  update,
  del,
  login,
  checkOwner,
} = require("../controllers/users");

const auth = jwt({ secret });

// 获取用户列表
router.get("/", find);
// 获取用户详情
router.get("/:id", findById);
// 创建用户
router.post("/", create);
// 更新用户 patch 可以更新部分
router.patch("/", auth, update);
// 删除用户
router.delete("/", auth, del);
// 登录
router.post("/login", login);

module.exports = router;