前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >使用Koa + mysql一小时搭建个人博客

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

作者头像
用户1515472
发布2019-07-24 14:25:52
1.5K0
发布2019-07-24 14:25:52
举报
文章被收录于专栏:忽如寄的前端周刊

koa插件选择

路由:koa-router 表单解析:koa-bodyparser 视图:koa-viewsejs session: koa-session-minimalkoa-mysql-session 数据库引擎: mysql

数据库设计

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

数据库初始化

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

代码语言:javascript
复制
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
};

入口文件设置:

代码语言:javascript
复制
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文件中,如下:

代码语言:javascript
复制
const config = {
  port: 3000,
  database: {
    DATABASE: 'koablog',
    USERNAME: 'root',
    PASSWORD: '123456',
    PORT: '3306',
    HOST: 'localhost'
  }
};

module.exports = config;

路由处理

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

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • koa插件选择
  • 数据库设计
  • 数据库初始化
  • 入口文件设置:
  • 路由处理
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档