node+koa2+mysql搭建博客后台

11,682 阅读5分钟

本文将详细讲解使用node+koa2+mysql搭建博客后台的全过程。

开发环境

  • node 8.3.0及以上
  • npm 5.3.0及以上
  • mysql 5.7.21

具体的环境配置可查看我的上一篇文章

准备工作

  • npm下载pm2(进程守护),并设置全局变量
  • 创建博客需要的数据库与表
    1. 开启mysql并创建数据库test: create database test;
    2. 切换到数据库testuse tests;,输入命令创建以下数据表:
//系统管理员表 t_user
CREATE TABLE `t_user` (
  `uid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `password` varchar(50) NOT NULL COMMENT '密码',
  `create_time` datetime NOT NULL COMMENT '注册时间',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_delete` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

//笔记本表 t_note
CREATE TABLE `t_note` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL COMMENT '笔记本名',
  `uid` int(11) NOT NULL COMMENT 'uid',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_delete` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

//博客记录表 t_blog
CREATE TABLE `t_blog` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL COMMENT '标题',
  `uid` int(11) DEFAULT '1' COMMENT 'uid',
  `content` text COMMENT '内容',
  `create_time` datetime NOT NULL COMMENT '注册时间',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `note_id` varchar(45) DEFAULT NULL,
  `publish` tinyint(4) DEFAULT '0' COMMENT '是否发布',
  `brief` text,
  `is_delete` tinyint(1) DEFAULT '0' COMMENT '是否删除',
  `ext_info` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

//图片上传记录表 t_img
CREATE TABLE `t_img` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL COMMENT 'uid',
  `create_time` datetime NOT NULL COMMENT '注册时间',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `name` varchar(40) DEFAULT NULL,
  `is_delete` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0  DEFAULT CHARSET=utf8;

node后台目录创建及npm包安装

  • 创建项目文件夹server,进入文件夹后初始化项目npm init
  • 在项目下创建以下文件夹和文件
    image
  • 安装以下依赖包:
    1. koa node框架
    2. koa-bodyparser 表单解析中间件
    3. koa-router 路由框架
    4. koa-session 基于koa的session模块
    5. mysql 数据库
    6. md5 md5加密
    7. async-busboy 带文件的表单解析模块
    8. is 数据格式校验插件

node连接mysql

db/index.js配置文件如下:

var mysql = require('mysql');
let config = {
    host     : 'localhost',
    user     : 'root',
    password : '123456',
    database : 'test',
    port:3306,
    multipleStatements: true//允许多条sql同时执行
};
let pool = mysql.createPool(config);
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.end()
                })
            }
        })
    })
};
module.exports = {
    query
}

其他配置

路由 Rouer

以上配置完成后,便可以开始写设计路由了。

引入相关配置

const router = require('koa-router')();
const Utils = require('../utils');
const Tips = require('../utils/tip');
const db = require('../db');

根据表设计路由

  • user表 -> user.js 管理员管理,可登录、查询登录态、退出登录
  • note表 -> note.js 笔记本管理,可添加、修改、删除,查询笔记本列表
  • blog表 -> blog.js 博客管理 可添加、修改、删除、查询博客列表。每篇博客必须关联对应的笔记本。可根据笔记本查询博客列表
  • img表 -> img.js 图片管理,可上传、删除、查询图片列表

具体路由部分实现内容

注意:所有的删除操作均为将表字段is_delete设置为1即可,方便恢复数据

  • user.js 管理员

    1. 登录
        router.post('/oa/login', async (ctx, next) => {
        let data = Utils.filter(ctx.request.body, ['name', 'password']);
        let res = Utils.formatData(data,[
            {key:'name',type:'string'},
            {key:'password',type:'string'}
        ]);
        if(!res) return ctx.body = Tips[1007];
        let { name, password } = data;
        let sql = 'SELECT uid FROM t_user WHERE name=? and password=? and is_delete=0', value = [name, md5(password)];
        await db.query(sql, value).then(res => {
            if (res && res.length > 0) {
                let val = res[0];
                let uid  = val['uid']
                ctx.session.uid = uid;
                ctx.cookies.set('uid', uid, {
                    maxAge:86400000,
                    httpOnly: true
                });
                ctx.body = {...Tips[0],data:{uid}};
            } else {
                ctx.body = Tips[1006];
            }
        }).catch(e => {
            ctx.body = Tips[1002];
        })
        
    });
    
    
    1. 查询登录信息
    router.get('/oa/user/auth', async (ctx, next) => {
        let uid = ctx.session.uid;
        let sql = 'SELECT name,uid,nick_name FROM t_user WHERE uid=? AND is_delete=0', value = [uid];
        await db.query(sql, value).then(res => {
            if (res && res.length > 0) {
                ctx.body = { ...Tips[0], data: res[0] };
            } else {
                ctx.body = Tips[1005];
            }
        }).catch(e => {
            ctx.body = Tips[1005];
        })
    });
    
  • note.js 笔记本管理

    1. 创建笔记本
    router.post('/oa/user/addNote',async (ctx,next)=>{
        let data = Utils.filter(ctx.request.body, ['name']);
        let {name} = data, uid = ctx.session.uid;
        let res = Utils.formatData(data, [
            {key: 'name', type: 'string'}
        ]);
        if (! res) return ctx.body = Tips[1007];
        let create_time = Utils.formatCurrentTime();
        let sql = `INSERT INTO t_note(name,uid,create_time) VALUES(?,?,?)`,
            value = [name, uid, create_time];
        await db.query(sql, value).then(res => {
            let {insertId: id} = res;
            if (id) {
                ctx.body = {
                    ...Tips[0],
                    data: {
                        id
                    }
                }
            } else {
                ctx.body = Tips[1002]
            }
        }).catch(e => {
            if(+e.errno === 1062){//笔记本不能重复
                ctx.body = {
                    code: 1010,
                    msg: '笔记本已存在!'
                };
            }else{
                ctx.body = Tips[1002]
            }
        })
    });
    
    1. (分页)查询笔记本列表
    router.get('/oa/user/myNote', async (ctx, next) => {
        let data = Utils.filter(ctx.request.query, ['pageSize', 'pageNum', 'type']), uid = ctx.session.uid;
        let res = Utils.formatData(data, [
            {key: 'type', type: 'number'},
        ]);
        if (! res) return ctx.body = Tips[1007];
        let {pageSize = 15, pageNum = 1, type = 0} = data;
        pageSize = Number(pageSize);
        pageNum = Number(pageNum);
        let offset = (pageNum - 1) * pageSize;
        let sql1 = `SELECT count(1) FROM  t_note WHERE uid=${uid} AND is_delete=0;`,
            sql= `SELECT name,id,create_time,update_time  FROM  t_note WHERE uid=${uid} AND is_delete=0 ORDER BY create_time DESC`;
        if(+type === 1){
            sql += ` limit ${offset},${pageSize};`
        }
        
        await db.query(sql1+sql).then(async result => {
            let res1 = result[0],res2 = result[1],total = 0,list = []
            if(res1 && res1.length >0 && res2 && res2.length >0){
                total = res1[0]['count(1)']
                list = res2
            }
            ctx.body = {
                ...Tips[0],
                data: {
                    list,
                    pageSize,
                    total
                }
            };
        }).catch(e => {
            ctx.body = Tips[1002];
        })
        
    });
    
  • blog.js 博客

    1. 创建博客
    router.post('/oa/user/addBlog', async (ctx, next) => {
        let data = Utils.filter(ctx.request.body, ['title', 'content', 'tag_id', 'note_id', 'brief', 'publish', 'create_time']),
            uid = ctx.session.uid;
        let res = Utils.formatData(data, [
            {key: 'note_id', type: 'number'},
            {key: 'title', type: 'string'},
            {key: 'brief', type: 'string'},
            {key: 'content', type: 'string'},
            {key: 'publish', type: 'number'}
        ]);
        if (! res) return ctx.body = Tips[1007];
        let {title = '无标题', content = '', note_id = '', brief = '', publish = 0, create_time = ''} = data;
        create_time = Utils.formatCurrentTime(create_time);
        let sql = `INSERT INTO t_blog(title,content,note_id,create_time,uid,brief,publish) VALUES (?,?,?,?,?,?,?)`,
            value = [title, content, note_id, create_time, uid, brief, publish];
        await db.query(sql, value).then(async res => {
            let {insertId: id} = res;
            ctx.body = {
                ...Tips[0],
                data: {id}
            }
            
        }).catch(e => {
            ctx.body = Tips[1002];
        });
        
    });
    
    1. 分页查询博客,与笔记本列表查询类似
  • img.js 图片管理

    1. 上传图片
    router.post('/oa/user/upFiles', async (ctx, next) => {
        try {
            let data = await asyncBusboy(ctx.req), uid = ctx.session.uid;
            let { files = [] } = data;
            if(files.length === 0) return ctx.body = Tips[1002];
            let file = files[0];
            let { mimeType = '', filename, path: filepath } = file;
            if(mimeType.indexOf('image') === -1) return ctx.body = Tips[1002];
            let name = Date.now() + '.' + filename.split('.').pop();
            let savePath = path.join(__dirname, `../../img/${name}`);
            try {
                let create_time = Utils.formatCurrentTime();
                let sql = 'INSERT INTO t_user_img(name,uid,create_time) VALUES (?,?,?)', value = [name, uid, create_time];
                await db.query(sql, value).then(res => {
                    let img = fs.readFileSync(filepath);
                    fs.writeFileSync(savePath, img);
                    fs.unlinkSync(filepath);//清除缓存文件
                    ctx.body = {
                        ...Tips[0], data: { name }
                    };
                }).catch(() => {
                    ctx.body = Tips[1002];
                })
                
            } catch (e) {
                ctx.body = Tips[1005];
            }
        } catch (e) {
            ctx.body = Tips[1002];
        }
    });
    

    2.删除图片

    router.post('/oa/user/removeImg', async (ctx, next) => {
        let data = Utils.filter(ctx.request.body, ['name']), uid = ctx.session.uid;
        let res = Utils.formatData(data, [
            { key: 'name', type: 'string' }
        ]);
        if (!res) return ctx.body = Tips[1007];
        let { name } = data;
        let sql = 'UPDATE t_user_img set is_delete=1 WHERE name=? AND uid=?;', value = [name, uid];
        await db.query(sql, value).then(res => {
            fs.unlinkSync(path.join(__dirname, `../../img/${name}`));//清除缓存文件
            ctx.body = Tips[0];
        }).catch(() => {
            ctx.body = Tips[1002];
        })
        
    });
    
    1. 图片列表查询同笔记本列表查询

路由统一管理

router/index.js将所有的路由集成并挂载至app.js

  • router/index.js

    const user = require('./user');
    const note = require('./note');
    const blog = require('./blog');
    const img = require('./img');
    module.exports = function(app){
        app.use(user.routes()).use(user.allowedMethods());
        app.use(note.routes()).use(note.allowedMethods());
        app.use(blog.routes()).use(blog.allowedMethods());
        app.use(img.routes()).use(img.allowedMethods());
    }
    
    
  • app.js(对需要登录的路由统一管理)

    const http = require('http');
    const koa = require('koa');
    const etag = require('koa-etag');
    const session = require('koa-session');
    const bodyParser = require('koa-bodyparser');
    const errorHandler = require('koa-error');
    const compress = require('koa-compress');
    const PORT = process.env.PORT || 8080;
    const koaBody = require('koa-body');
    const app = new koa();
    const Utils = require('./utils');
    const router = require('./router');
    app.keys = ['session@&'];
    
    app.use(session({
        key: 'abc::sess',
        maxAge: 86400000,
        overwrite: true,
        httpOnly: true,
        signed: true,
        rolling: false
    }, app));
    app.use(koaBody());
    app.use(async(ctx, next) => {
        let {url = ''} = ctx;
        if(url.indexOf('/oa/user/') >-1){//需要校验登录态
            let check = Utils.checkLogin(ctx);
            if(check.code != 0) return ctx.body = check;
        }
        await next();
        
    });
    app.use(errorHandler());
    app.use(bodyParser());
    
    app.use(etag());
    
    // compressor
    app.use(compress({
        filter: contentType => /text|javascript/i.test(contentType),
        threshold: 2048
    }));
    router(app);
    http.createServer(app.callback()).listen(PORT);
    log('server is running on port: %s', PORT);
    

以上便是后台搭建全过程,点此查看后台源码

阅读原文

前端项目源码