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語法建立, 以下以我的專案為例說明:

  • 1.建立schema

    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));
          });

        • SQL code:

        • output result:

reference:

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