本文將詳細講解使用node+koa2+mysql搭建部落格後臺的全過程。
開發環境
- node 8.3.0及以上
- npm 5.3.0及以上
- mysql 5.7.21
具體的環境配置可檢視我的上一篇文章
準備工作
- npm下載pm2(程式守護),並設定全域性變數
- 建立部落格需要的資料庫與表
- 開啟mysql並建立資料庫test:
create database test;
- 切換到資料庫test
use tests;
,輸入命令建立以下資料表:
- 開啟mysql並建立資料庫test:
//系統管理員表 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
。 - 在專案下建立以下資料夾和檔案
- 安裝以下依賴包:
- koa node框架
- koa-bodyparser 表單解析中介軟體
- koa-router 路由框架
- koa-session 基於koa的session模組
- mysql 資料庫
- md5 md5加密
- async-busboy 帶檔案的表單解析模組
- 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
}
複製程式碼
其他配置
- 框架公用方法,包括引數校驗、登入態校驗等。config/index.js
- 框架介面返回碼規範 config/tip.js
路由 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 管理員
- 登入
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]; }) }); 複製程式碼
- 查詢登入資訊
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 筆記本管理
- 建立筆記本
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] } }) }); 複製程式碼
- (分頁)查詢筆記本列表
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 部落格
- 建立部落格
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]; }); }); 複製程式碼
- 分頁查詢部落格,與筆記本列表查詢類似
-
img.js 圖片管理
- 上傳圖片
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]; }) }); 複製程式碼
- 圖片列表查詢同筆記本列表查詢
路由統一管理
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); 複製程式碼
以上便是後臺搭建全過程,點此檢視後臺原始碼。