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

用户1515472

发布于 2019-07-24 14:25:52

1.5K0

发布于 2019-07-24 14:25:52

文章被收录于专栏：忽如寄的前端周刊

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;