使用Koa + mysql一小时搭建个人博客

koa插件选择

路由：koa-router 表单解析：koa-bodyparser 视图：koa-views、ejs session: koa-session-minimal、koa-mysql-session 数据库引擎: mysql

数据库设计

用户表(users)：id、name、pass 文章表(posts)：id、name、title、content、uic、moment、comments、pv 评论表(comment)：id、name、content、postid

数据库初始化

使用连接池连接数据库，每次查询完毕之后释放链接，可以将数据表的建立在mysql.js中完成，为每一次query创建一个公共函数，并且每次查询都封装为一个方法，如下：

const mysql = require('mysql');
const config = require('./../config/default');

const pool = mysql.createPool({
  host: config.database.HOST,
  port: config.database.PORT,
  user: config.database.USERNAME,
  password: config.database.PASSWORD,
  database: config.database.DATABASE
});

let query = function(sql, values) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function(err, connection) {
      if(err) {
        resolve(err);
      } else {
        connection.query(sql, values, (err, rows) => {
          if(err) {
            reject(err);
          } else {
            resolve(rows)
          }
          connection.release();
        })
      }
    })
  })
};

let users = `create table if not exists users(
 id INT NOT NULL AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL,
 pass VARCHAR(40) NOT NULL,
 PRIMARY KEY ( id )
);`;

let posts = `create table if not exists posts(
 id INT NOT NULL AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL,
 title VARCHAR(40) NOT NULL,
 content  VARCHAR(40) NOT NULL,
 uid  VARCHAR(40) NOT NULL,
 moment  VARCHAR(40) NOT NULL,
 comments  VARCHAR(40) NOT NULL DEFAULT '0',
 pv  VARCHAR(40) NOT NULL DEFAULT '0',
 PRIMARY KEY ( id )
);`;

let comment = `create table if not exists comment(
 id INT NOT NULL AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL,
 content VARCHAR(40) NOT NULL,
 postid VARCHAR(40) NOT NULL,
 PRIMARY KEY ( id )
);`;

let createTable = function(sql) {
  return query(sql, []);
};

createTable(users);
createTable(posts);
createTable(comment);

let insertData = function(value) {
  let _sql = "insert into users(name,pass) values(?,?);";
  return query(_sql, value);
};

let insertPost = function(value) {
  let _sql = "insert into posts(name, title, content, uid, moment) values(?,?,?,?,?);";
  return query(_sql, value);
};

let updatePostComment = function(value) {
  let _sql = "update posts set comment=? where id=?";
  return query(_sql, value);
};

let updatePostPv = function(value) {
  let _sql = "update posts set pv=? where id=? ";
  return query(_sql, value);
};

let insertComment = function(value) {
  let _sql = "insert into comment(name, content, postid) values(?,?,?);";
  return query(_sql, value);
};

let findDataByName = function(name) {
  let _sql =`select * from users where name="${name}"`;
  return query(_sql);
};

let findDataByUser = function(name) {
  let _sql = `select * from posts where name="${name}"`;
  return query(_sql);
}

let findDataById = function(id) {
  let _sql = `select * from posts where id="${id}"`;
  return query(_sql);
}

let findCommentById = function(id) {
  let _sql = `select * from comment where postid="${id}"`;
  return query(_sql);
}

let findAllPost = function() {
  let _sql = `select * from posts`;
  return query(_sql);
}

let updatePost = function(values) {
  let _sql = `update posts set title=?,content=? where id=?`;
  return query(_sql, values);
}

let deletePost = function(id) {
  let _sql = `delete from posts where id=?`;
  return query(_sql);
}

let deleteComment = function(id) {
  let _sql = `delete from comment where id = ${id}`;
  return query(_sql);
}

let deleteAllPostComment = function (id) {
  let _sql = `delete from comment where postid = ?`;
  return query(_sql);
}

let findCommentLength = function(id) {
  let _sql = `select content from comment where postid in (select id from posts where id=${id})`;
  return query(_sql);
}

module.exports = {
  query,
  createTable,
  insertData,
  findDataByName,
  findDataById,
  findDataByUser,
  insertPost,
  findAllPost,
  insertComment,
  findCommentById,
  updatePost,
  deletePost,
  deleteComment,
  findCommentLength,
  updatePostComment,
  deleteAllPostComment,
  updatePostPv
};

入口文件设置：

const Koa = require('koa');
const config = require('./config/default');
const path = require('path');
const ejs = require('ejs');
const router = require('koa-router');
const koaStatic = require('koa-static');
const views = require('koa-views');
const bodyParser = require('koa-bodyparser');
const session = require('koa-session-minimal');
const MysqlStore = require('koa-mysql-session');

const app = new Koa();

const sessionMysqlConfig = {
  user: config.database.USERNAME,
  password: config.database.PASSWORD,
  database: config.database.DATABASE,
  host: config.database.HOST
};

app.use(session({
  key:'USER_SID',
  store: new MysqlStore(sessionMysqlConfig)
}))

app.use(koaStatic(path.join(path.join(__dirname, 'public'))));

app.use(views(path.join(__dirname,'views'),{
  extension: 'ejs'
}));

app.use(bodyParser());

app.use(require('./routes/singup.js').routes());

app.use(require('./routes/signin.js').routes());

app.use(require('./routes/posts').routes());

app.use(require('./routes/signout').routes());

app.listen(config.port);

我们应该将数据库等配置放置在一个公共的config文件中，如下:

const config = {
  port: 3000,
  database: {
    DATABASE: 'koablog',
    USERNAME: 'root',
    PASSWORD: '123456',
    PORT: '3306',
    HOST: 'localhost'
  }
};

module.exports = config;

路由处理

前后端未分离中，存在的问题就是，数据判断逻辑的放置，比如根据用户是否登录显示不同的header，这个可以在视图中判断session.name是否存在，同样也可以在route中先判断，在给传值为logined:false，个人偏向后一种，毕竟我认为视图中应该尽量少出现逻辑。

完整的教程在这里：http://www.wclimb.site/2017/07/12/Node-Koa2-Mysql-%E6%90%AD%E5%BB%BA%E7%AE%80%E6%98%93%E5%8D%9A%E5%AE%A2/

huruji / blog

使用Koa + mysql一小时搭建个人博客 #11

koa插件选择

数据库设计

数据库初始化

入口文件设置：

路由处理