# 2.10.Promise-based ORM for Node.js

## 2.10.Promise-based ORM for Node.js

![](https://3195929128-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M4M0G8GO2MkGtHfarAf%2F-M4M0IHoyFvfGKjf7P-T%2F-M4M0NqMCC-NY8qeSwZV%2F%E8%9E%A2%E5%B9%95%E5%BF%AB%E7%85%A7%202017-06-13%20%E4%B8%8B%E5%8D%887.50.04.png?generation=1586302922262048\&alt=media)

## 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 ![](https://3195929128-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M4M0G8GO2MkGtHfarAf%2F-M4M0IHoyFvfGKjf7P-T%2F-M4M0NqOzlcC2558P1_w%2F%E8%9E%A2%E5%B9%95%E5%BF%AB%E7%85%A7%202017-06-13%20%E4%B8%8B%E5%8D%887.50.47.png?generation=1586302922283904\&alt=media)

      ```
      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間的關係 (如一對多, 多對多), 此階段也必須指定關聯性&#x20;
    * (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](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: ![](https://3195929128-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M4M0G8GO2MkGtHfarAf%2F-M4M0IHoyFvfGKjf7P-T%2F-M4M0NqSzyL3SNSlg9do%2F%E8%9E%A2%E5%B9%95%E5%BF%AB%E7%85%A7%202017-06-13%20%E4%B8%8B%E5%8D%887.51.38.png?generation=1586302922428818\&alt=media)
        * output result: ![](https://3195929128-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M4M0G8GO2MkGtHfarAf%2F-M4M0IHoyFvfGKjf7P-T%2F-M4M0NqUig-N3H5rIaVC%2F%E8%9E%A2%E5%B9%95%E5%BF%AB%E7%85%A7%202017-06-13%20%E4%B8%8B%E5%8D%887.51.43.png?generation=1586302922336465\&alt=media)

### reference:

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