2.10.Promise-based ORM for Node.js

2.10.Promise-based ORM for Node.js

This section contain the following items:

  • 1.What is Sequelize?

  • 2.Installation

  • 3.Usage

1.Sequelize

  • Sequelize是Node.js的一個promise-based的ORM. 他支援PostgreSQL, MySQL, SQLite, MSSQL的"方言" (dialects)

2.Installation

    npm install sequelize
    npm install mysql

3.Model

  • Model相當於數據庫中table，有時它也會被稱為“模型”或“工廠“. Model不能通過構造函数創建, 而只能通過sequlize.define方法来定義或通過sequlize.import導入. 通過define定義一個Model, 就相當於定義了一種模型與數據表之間的映射關係, 通過模型可以實現對table紀錄的insert, delete, update, select等操作.

  • 在使用seqenlize的第一種方式進行query前, 必須先進行以下兩件事:

    • 1.建立schema

    • 2.在js檔中編寫對應每個table的model

  • 由於一開始我原本打算使用mysql模組在node.js上進行資料庫操作,因此在建立schema的部分我是直接用SQL語法建立, 以下以我的專案為例說明:

    • DROP DATABASE IF EXISTS e_test_reports;

CREATE DATABASE e_test_reports
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

USE e_test_reports;

CREATE TABLE tester(
  tester_name VARCHAR(50) primary key ,
  tester_mail VARCHAR(50)
);
CREATE TABLE model(
  model_name VARCHAR(50) primary key ,
  chipset VARCHAR(50),
  os_type VARCHAR(50)
);
CREATE TABLE testplan(
  testplan_id VARCHAR(50) primary key,
  model_name VARCHAR(50),
  tester_name VARCHAR(50),
  CONSTRAINT model_model_name_fk FOREIGN KEY (model_name) REFERENCES model (model_name),
  CONSTRAINT tester_tester_name_fk FOREIGN KEY (tester_name) REFERENCES tester (tester_name)
);
CREATE TABLE testplan_result(
  testplan_result_id VARCHAR(50) primary key,
  pass_rate FLOAT(5,3) UNSIGNED,
  iterate_time INT UNSIGNED,
  test_begin_datetime DATETIME,
  test_end_datetime DATETIME,
  comment VARCHAR(500),
  is_overall boolean,
  testplan_id VARCHAR(50),
  CONSTRAINT testplan_testplan_id_fk FOREIGN KEY (testplan_id) REFERENCES testplan (testplan_id)    
);
CREATE TABLE testitems(
  testitem_id VARCHAR(50) primary key,
  name VARCHAR(50),
  client_script VARCHAR(50),
  server_script VARCHAR(50)
);
CREATE TABLE testitem_result(
  testitem_id VARCHAR(50),
  testplan_result_id VARCHAR(50),
  primary key(testitem_id, testplan_result_id),
  testplan_result VARCHAR(50),
  testplan_log VARCHAR(400),
  INDEX(testitem_id),
  INDEX(testplan_result_id),
  FOREIGN KEY (testitem_id) REFERENCES testitems (testitem_id),    
  FOREIGN KEY (testplan_result_id) REFERENCES testplan_result (testplan_result_id)    
);

    • 2.在js檔中編寫對應每個table的model

      • 在建立好table們後必須針對每個table編寫相對應的model, 才能讓sequelize映射到tables, 如果有primary key的話也必須指定每個table的primary key; 另外為了在後續查詢時可以必須得知table間的關係 (如一對多, 多對多), 此階段也必須指定關聯性

      • (1)建立model

        • 初始化 var Sequelize = require('sequelize'); var sequelize = new Sequelize(settings.db_config.database, settings.db_config.user, settings.db_config.password, { host: settings.db_config.host, dialect: 'mysql', pool: { max: 5, min: 0, idle: 10000 } });

          //sequelize ORM
sequelize
.authenticate()
.then(function(err) {
  console.log('Connection has been established successfully.');
})
.catch(function(err) {
  console.log('Unable to connect to the database:', err);
});

        • 編寫model

          • 以上圖中的tester為例, 需要填寫每個對應的欄位即對應的data type, data可參考官網:http://docs.sequelizejs.com/en/latest/docs/models-definition/#data-types; 這邊還需要指定primary key及tableName, tableName就是實體table的名稱為何.

            var tester = sequelize.define('tester', {
  tester_name: {
    type: Sequelize.STRING(50),
    primaryKey: true
  },
  tester_mail: Sequelize.STRING(50)
}, {
tableName: 'tester',
timestamps: false
});

      • (2)描述關聯性: 為了後續join之用

        • 一對多

          • 以上圖為例, 一個model可對應到多個testplan, 則可以做以下設定, 但是在實體的table也要記得加入foreign key (及constraint)

            model.hasMany(testplan, {
  foreignKey: 'model_name'
});
testplan.belongsTo(model, {
  foreignKey: 'model_name'
});

        • 多對多

          • 以上圖為例, 一個testplan_result以testitem_result作為junction table, 可以對應到多個testitems

            testplan_result.belongsToMany(testitems, {Cr
    through: 'testitem_result',
    foreignKey: 'testplan_result_id'
});
testitems.belongsToMany(testplan_result, {
  through: 'testitem_result',
  foreignKey: 'testitem_id'
});

4.Query

  • Sequelize中有兩種查询：使用Model（模型）中的方法查詢和使用sequelize.query()進行基於SQL語句的原始查詢.

  • 1.以下是我目前所用過的query:

    • findAll: 查詢並回傳所有結果

      db.db.tester.findAll().then(function(info) {
  console.log(info);
  callback(null, JSON.stringify(info));
}, function(error) {
  callback(null, JSON.stringify(error));
});

    • findOrCreate：確認資料庫中是否有該查詢結果, 若沒有則建立一筆資料

      db.db.tester.findOrCreate({
    where: {
        tester_name: tester_name
    },
    defaults: {
        tester_mail: tester_mail
    }
})
.spread(function(user, created) {
    console.log(user.get({
        plain: true
    }));
    callback(null, JSON.stringify(user));
}).catch(function(error) {
    callback(JSON.stringify(error));
});

  • 2.Outer join:

    • Join基本可分為Inner join, Outer join, Natural join三種

    • 目前測試, sequelize可以做到Inner join (findAll + where)及Outer join (需建立好primary key, foreign key)兩種

      • Inner join, Outer join, Natural join的差異

        • 1.Inner join: 利用條件式裡的比較運算子結合兩份資料表

            SELECT a.*, b.* FROM `test1` as a, `test2` as b where a.id = b.id

        • 2.Natural join:只有連結的資料欄在兩邊的資料表名稱都相同時才會有用

            SELECT a.*, b.* FROM `test1` as a NATURAL JOIN `test2` as b

        • 3.Outer join: 又可分為 LEFT, RIGHT, FULL join

          • LEFT是顯示左欄所有資料與匹配結果

          • RIGHT是顯示右欄所有資料與匹配結果

          • FULL是LEFT與RIGHT的連集

            SELECT a.*, b.* FROM `test1` as a LEFT JOIN `test2` as b on a.id = b.id

      • example:

        • 以上圖為例, 查詢與testplan_result相關的testplan, testitems, testitem_result:

          • sequelize code:

            db.db.testplan_result.findAll({
  include: [db.db.testplan, db.db.testitems]
})
.then(function(info) {
  callback(null, JSON.stringify(info));
}, function(error) {
    callback(null, JSON.stringify(error));
});

reference:

1.http://itbilu.com/nodejs/npm/VJIR1CjMb.html 2.http://docs.sequelizejs.com/en/latest/docs/models-definition/#data-types

Previous2.9.Node Swagger mock server

Last updated