nodeJs 服務(wù)器以及中間層實踐（二）

二藕赞、服務(wù)器與數(shù)據(jù)庫

https://github.com/bartflyian/wx-front-with-server/tree/master/ 文件代碼地址当娱。
第一篇已經(jīng)使用koa對不同請求做不同的路由處理，如 GET /纯陨、POST /add坛芽；當(dāng)在客戶端頁面請求這些地址時，我們想其返回帶有數(shù)據(jù)庫數(shù)據(jù)的json翼抠。那么首先咙轩，需要建立數(shù)據(jù)庫連接：
1. 安裝mysql,數(shù)據(jù)庫視圖管理工具我用的是Navicat
2. 新建一個名為 koa 的數(shù)據(jù)庫，創(chuàng)建新的users表：

      use koa;

      create table users (

          id varchar(50) not null,

          name varchar(100) not null,

          gender bool not null,

          birth varchar(10) not null,

          createdAt bigint not null,

          updatedAt bigint not null,

          version bigint not null,

          primary key (id)

      ) engine=innodb;

服務(wù)器想要操作數(shù)據(jù)庫表阴颖，需要書寫對應(yīng)的sql語句活喊，在這里我用到Node的ORM框架Sequelize,即是創(chuàng)建與數(shù)據(jù)庫的映射，當(dāng)然也有自己的書寫規(guī)范量愧，所以還需要看看sequelize文檔了解一下钾菊，不過在這只用到基礎(chǔ)的增刪改查。
依賴包添加"sequelize": "3.24.1",或者直接npm install
(1) 創(chuàng)建 config文件夾偎肃，其下創(chuàng)建DBconfig.js:

image.png

(2) 創(chuàng)建 lib 文件夾煞烫，其下創(chuàng)建mysql.js,在此使用sequelize連接數(shù)據(jù)庫，并作數(shù)據(jù)表的映射sequelize先new一個Sequelize對象連接數(shù)據(jù)庫累颂，然后用sequelize.define對具體的表作映射處理滞详。在這里將這兩個過程封裝到mysql.js中：

const Sequelize = require('sequelize');

const config = require('../config/DBconfig');

console.log('init sequelize...');

var sequelize = new Sequelize(config.database, config.username, config.password, {

    host: config.host,

    dialect: 'mysql',

    pool: {

        max: 5,

        min: 0,

        idle: 30000

    }

});

const ID_TYPE = Sequelize.STRING(50);

function defineModel(name, attributes) {

    var attrs = {};

    for (let key in attributes) {

        let value = attributes[key];

        if (typeof value === 'object' && value['type']) {

            value.allowNull = value.allowNull || false;

            attrs[key] = value;

        } else {

            attrs[key] = {

                type: value,

                allowNull: false

            };

        }

    }

    attrs.id = {

        type: ID_TYPE,

        primaryKey: true

    };

    attrs.createdAt = {

        type: Sequelize.BIGINT,

        allowNull: false

    };

    attrs.updatedAt = {

        type: Sequelize.BIGINT,

        allowNull: false

    };

    attrs.version = {

        type: Sequelize.BIGINT,

        allowNull: false

    };

    return sequelize.define(name, attrs, {

        tableName: name,

        timestamps: false,

        hooks: {

            beforeValidate: function (obj) {

                let now = Date.now();

                if (obj.isNewRecord) {

                    if (!obj.id) {

                        obj.id = generateId();

                    }

                    obj.createdAt = now;

                    obj.updatedAt = now;

                    obj.version = 0;

                } else {

                    obj.updatedAt = Date.now();

                    obj.version++;

                }

            }

        }

    });

}

var db = {

    sync: () => {

        if (process.env.NODE_ENV !== 'production') {

            sequelize.sync({ force: true });

        } else {

            throw new Error('Cannot sync() when NODE_ENV is set to \'production\'.');

        }

    }

};

db.defineModel = defineModel;

const TYPES = ['STRING', 'INTEGER', 'BIGINT', 'TEXT', 'DOUBLE', 'DATEONLY', 'BOOLEAN'];

for (let type of TYPES) {

    db[type] = Sequelize[type];

}

db.ID = ID_TYPE;

module.exports = db;

(defineModel統(tǒng)一處理數(shù)據(jù)庫表中通用的字段如id...，最后return出一個sequelize.define方法紊馏，這樣我們在創(chuàng)建sequelize數(shù)據(jù)表映射時料饥，可以簡潔許多)。

(3) 創(chuàng)建 models 文件夾, 存放所有數(shù)據(jù)表映射朱监，我們先創(chuàng)建user.js: