0%

建立 GraphQL API (8)

發表於 更新於 分類於

使用 Oracle 資料庫

Oracle 還是目前我們使用的主要資料庫，以下會是使用 Oracle 資料庫的範例，還是使用 EMP 與 DEPT。

我們從 models 開始，這裡將會設定 Oracle 資料庫的連結與及資料表的 API。在專案目錄 models 中新增 oradb.config.js:

models/oradb.config.js
1
2
3
4
5
6
7
8
module.exports = {
  user: "xxxx",
  password: "xxxxxxx",
  connectString: "10.11.xx.xxx:1522/xxx.xxx.com.tw",
  poolMin: 0,
  poolMax: 2,
  poolIncrement: 1
};

這裡直接使用 Oracle connection pool，可依需求調整，這裡使用 demo SCHEMA。

接下來是資料庫的抽象層，oradb.database.js，這與之前 Node.js 教育訓練 REST API 的程式碼相同:

models/oradb.database.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
const oracledb = require('oracledb');
const dbConfig = require('./oradb.config');

async function initialize() {
  try {
    await oracledb.createPool(dbConfig);
  } catch(err) {
    console.log(err.message);
  }
}

async function close() {
  try {
    await oracledb.getPool().close(10);
  } catch (err) {
    console.error(err.message);
  }
}

function doExecute(statement, binds = [], opts = {}) {
  return new Promise(async (resolve, reject) => {
    let conn;

    opts.outFormat = oracledb.OBJECT;
    opts.autoCommit = true;

    try {
      conn = await oracledb.getConnection();
      const result = await conn.execute(statement, binds, opts);
      resolve(result);
    } catch (err) {
      reject(err);
    } finally {
      if (conn) {
        try {
          await conn.close();
        } catch (err) {
          console.log(err);
        }
      }
    }
  });
}

function doExecuteMany(statement, binds = [], opts = {}) {
  return new Promise(async (resolve, reject) => {
    let conn;

    opts.outFormat = oracledb.OBJECT;
    opts.autoCommit = true;
    opts.batchErrors = true;

    try {
      conn = await oracledb.getConnection();
      const result = await conn.executeMany(statement, binds, opts);
      resolve(result);
    } catch (err) {
      reject(err);
    } finally {
      if (conn) {
        try {
          await conn.close();
        } catch (err) {
          console.log(err);
        }
      }
    }
  });
}

module.exports = {
  initialize,
  close,
  doExecute,
  doExecuteMany
};

這抽象層可重複用在個別的資料表 API 中。

models/orademo.department.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
const oracledb = require("oracledb");
const oradb = require("./oradb.database");
const { setValue, toLowerCaseKeys } = require("./helpers/utils");

function findAll() {
  const statement = "select * from dept";

  return oradb
    .doExecute(statement)
    .then(result => toLowerCaseKeys(result))
    .catch(err => {
      throw err;
    });
}

function findByDeptno(deptno) {
  if (!deptno) {
    return Promise.reject(new Error("Invalid input"));
  }

  const binds = [];
  const statement = "select * from dept where deptno = :deptno";
  binds.push(deptno);

  return oradb
    .doExecute(statement, binds)
    .then(result => toLowerCaseKeys(result))
    .catch(err => {
      throw err;
    });
}

function add(data) {
  const statement = `
    INSERT INTO dept (deptno, dname, loc)
      VALUES(:deptno, :dname, :loc)
      RETURNING deptno INTO :id
  `;

  return new Promise(async (resolve, reject) => {
    if (typeof data !== "object") {
      reject(new Error("Input must be an valid object"));
    }

    let result;
    let binds = {
      deptno: setValue(data["deptno"]),
      dname: setValue(data["dname"]),
      loc: setValue(data["loc"]),
      id: {
        type: oracledb.STRING,
        dir: oracledb.BIND_OUT
      }
    };

    try {
      result = await oradb.doExecute(statement, binds);
      resolve(result);
    } catch (err) {
      reject(err);
    }
  });
}

function update(deptno, data) {
  const statement = `
    UPDATE dept d
       SET d.dname = :dname,
           d.loc = :loc
     WHERE d.deptno = :deptno
    RETURNING ROWID INTO :rid
  `;

  return new Promise(async (resolve, reject) => {
    if (!deptno) {
      reject(new Error("Invalid deptno"));
    }
    if (typeof data !== "object") {
      reject(new Error("Input must be an valid object"));
    }

    let result;
    let binds = {
      deptno: setValue(deptno),
      dname: setValue(data["dname"]),
      loc: setValue(data["loc"]),
      rid: {
        type: oracledb.STRING,
        dir: oracledb.BIND_OUT
      }
    };

    try {
      result = await oradb.doExecute(statement, binds);
      resolve(result);
    } catch (err) {
      reject(err);
    }
  });
}

function remove(deptno) {
  const statement = `
    DELETE FROM dept
     WHERE deptno = :deptno
    RETURNING deptno INTO :id
  `;

  return new Promise(async (resolve, reject) => {
    if (!deptno) {
      reject(new Error("Invalid deptno"));
    }

    let result;
    let binds = {
      deptno: setValue(deptno),
      id: {
        type: oracledb.STRING,
        dir: oracledb.BIND_OUT
      }
    };

    try {
      result = await oradb.doExecute(statement, binds);
      resolve(result);
    } catch (err) {
      reject(err);
    }
  });
}

function upsert(data) {
  const statement = `
    MERGE INTO dept d
    USING (select :deptno as deptno,
                  :dname as dname,
                  :loc as loc
             from dual) p
    ON (d.deptno = p.deptno)
    WHEN MATCHED THEN
      UPDATE SET d.dname = p.dname,
                 d.loc = p.loc
    WHEN NOT MATCHED THEN
      INSERT (d.deptno, d.dname, d.loc)
      VALUES (p.deptno, p.dname, p.loc)
  `;

  return new Promise(async (resolve, reject) => {
    if (typeof data !== "object") {
      reject(new Error("Input must be an array"));
    }

    if (data instanceof Array && data.length > 0) {
      let result;
      let binds = [];

      data.forEach(value => {
        let item = {
          deptno: setValue(value["deptno"]),
          dname: setValue(value["dname"]),
          loc: setValue(value["loc"])
        };
        binds.push(item);
      });

      try {
        result = await oradb.doExecuteMany(statement, binds);
        resolve(result);
      } catch (err) {
        reject(err);
      }
    } else {
      reject(new Error("Input must be an array"));
    }
  });
}

module.exports = {
  findAll,
  findByDeptno,
  add,
  update,
  remove,
  upsert
};

Oracle 的 oracledb 驅動程式返回的 JSON 格式資料，屬性都會是大寫型態，這裡使用了一個 toLowerCaseKeys 函式將它全部轉為小寫，這個函式放在 helpers/utils 程式碼中，裡面還包含一些常會用到的小程式碼，在這裡大部份都不會用到，但可留著參考。

models/helpers/utils.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
const crypto = require("crypto");

exports.trim = (str) => str.replace(/^\s*|\s*$/g, '');

exports.setValue = (val) => val || null;

exports.isNull = isNull;

exports.isNotNull = negate(isNull);

exports.isUndefined = val => val === undefined;

exports.isEmpty = s => !s || !s.trim();

exports.isObject = (val) => val && typeof val === 'object';

exports.isArray = (val) => val && Array.isArray(val);

exports.isNumber = (val) => typeof val === 'number' && val === Number(val) && Number.isFinite(val);

exports.validNumber = val => !isNaN(parseFloat(val)) ? parseFloat(val) : null;

exports.normalize = (str) => str.replace(/\-/g, '');

exports.isValid = (str) => {
  if(str.length === 0) {
    return new Status(false, 'Invalid input. Expected non-empty value!');
  } else {
    return new Status(true, 'Success!');
  }
};

exports.toLowerCaseKeys = toLowerCaseKeys;

exports.checksum = checksum;

function toLowerCaseKeys (obj) {
  let newObj, value;

  if (obj instanceof Array) {
    return obj.map(value => {
      if (typeof value === "object") {
        value = toLowerCaseKeys(value);
      }
      return value;
    });
  } else {
    newObj = {};

    Object.keys(obj).forEach(origKey => {
      let newKey = (origKey.toLowerCase() || origKey).toString();
      value = obj[origKey];
      if (
        value instanceof Array ||
        (value !== null && value.constructor === Object)
      ) {
        value = toLowerCaseKeys(value);
      }
      newObj[newKey] = value;
    });
  }
  return newObj;
}

function checksum(obj) {
  const string = Object.keys(obj)
    .sort()
    .map(prop => String(obj[prop]))
    .join("");

  return crypto
    .createHash("sha1")
    .update(string)
    .digest("hex");
}

function isNull(val) {
  return  val === null;
}

function negate(func) {
  return function() {
    return !func.apply(null, arguments);
  };
};

class Status {
  constructor(status, message) {
    this._status = status;
    this._message = messge;
  }

  get status() {
    return this._status;
  }

  get message() {
    return this._message;
  }
}

以下則是 EMP API:

models/orademo.employee.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
const oracledb = require('oracledb');
const oradb = require('./oradb.database');
const { setValue, toLowerCaseKeys } = require('./helpers/utils');

function findAll() {
  const statement = "select * from emp";

  return oradb.doExecute(statement)
    .then(result => toLowerCaseKeys(result))
    .catch((err) => {
      throw err;
    });
}

function findByEmpno (empno) {
  if (!empno) {
    return Promise.reject(new Error("Invalid input"));
  }

  const binds = [];
  const statement = "select * from emp where empno = :empno";
  binds.push(empno);

  return oradb.doExecute(statement, binds)
    .then((result) => toLowerCaseKeys(result))
    .catch((err) => {
      throw err
    });
}

function findByDeptno (deptno) {
  if (!deptno) {
    return Promise.reject(new Error("Invalid input"));
  }

  const binds = [];
  const statement = "select * from emp where deptno = :deptno";
  binds.push(deptno);

  return oradb.doExecute(statement, binds)
    .then((result) => toLowerCaseKeys(result))
    .catch((err) => {
      throw err
    });
}

function add(data) {
  const statement = `
    INSERT INTO emp
                (empno, ename, job, mgr, hiredate, sal, comm, deptno)
         VALUES (:empno, :ename, :job, :mgr, to_date(:hiredate,'yyyy-mm-dd"T"hh24:mi:ss'), :sal, :comm, :deptno)
    RETURNING empno INTO :id
  `;

  return new Promise(async (resolve, reject) => {
    if (typeof data !== "object") {
      reject(new Error("Input must be an valid object"));
    }

    let result;
    let binds = {
      empno: setValue(data["empno"]),
      ename: setValue(data["ename"]),
      job: setValue(data["job"]),
      mgr: setValue(data["mgr"]),
      hiredate: setValue(data["hiredate"]),
      sal: setValue(data["sal"]),
      comm: setValue(data["comm"]),
      deptno: setValue(data["deptno"]),
      id: {
        type: oracledb.STRING,
        dir: oracledb.BIND_OUT
      }
    };

    try {
      result = await oradb.doExecute(statement, binds);
      resolve(result);
    } catch (err) {
      reject(err);
    }
  });
}

function update(empno, data) {
  const statement = `
    UPDATE emp e
       SET e.ename = :ename,
           e.job = :job,
           e.mgr = :mgr,
           e.hiredate = to_date(:hiredate,'yyyy-mm-dd"T"hh24:mi:ss'),
           e.sal = :sal,
           e.comm = :comm,
           e.deptno = :deptno
     WHERE e.empno = :empno
    RETURNING ROWID INTO :rid
  `;

  return new Promise(async (resolve, reject) => {
    if (!empno) {
      reject(new Error("Invalid empno"));
    }
    if (typeof data !== "object") {
      reject(new Error("Input must be an valid object"));
    }

    let result;
    let binds = {
      empno: setValue(empno),
      ename: setValue(data["ename"]),
      job: setValue(data["job"]),
      mgr: setValue(data["mgr"]),
      hiredate: setValue(data["hiredate"]),
      sal: setValue(data["sal"]),
      comm: setValue(data["comm"]),
      deptno: setValue(data["deptno"]),
      rid: {
        type: oracledb.STRING,
        dir: oracledb.BIND_OUT
      }
    };

    try {
      result = await oradb.doExecute(statement, binds);
      resolve(result);
    } catch (err) {
      reject(err);
    }
  });
}

function remove(empno) {
  const statement = `
    DELETE FROM emp
     WHERE empno = :empno
    RETURNING empno INTO :id
  `;

  return new Promise(async (resolve, reject) => {
    if (!empno) {
      reject(new Error("Invalid empno"));
    }

    let result;
    let binds = {
      empno: setValue(empno),
      id: {
        type: oracledb.STRING,
        dir: oracledb.BIND_OUT
      }
    };

    try {
      result = await oradb.doExecute(statement, binds);
      resolve(result);
    } catch (err) {
      reject(err);
    }
  });
}

function upsert(data) {
  const statement = `
    MERGE INTO emp e
    USING (select :empno as empno,
                  :ename as ename,
                  :job as job,
                  :mgr as mgr,
                  to_date(:hiredate,'yyyy-mm-dd"T"hh24:mi:ss') as hiredate,
                  :sal as sal,
                  :comm as comm,
                  :deptno as deptno
             from dual) p
    ON (e.empno = p.empno)
    WHEN MATCHED THEN
      UPDATE SET e.ename = p.ename,
                 e.job = p.job,
                 e.mgr = p.mgr,
                 e.hiredate = p.hiredate,
                 e.sal = p.sal,
                 e.comm = p.comm,
                 e.deptno = p.deptno
    WHEN NOT MATCHED THEN
      INSERT (e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno)
      VALUES (p.empno, p.ename, p.job, p.mgr, p.hiredate, p.sal, p.comm, p.deptno)
  `;

  return new Promise(async (resolve, reject) => {
    if (typeof data !== "object") {
      reject(new Error("Input must be an array"));
    }

    if (data instanceof Array && data.length > 0) {
      let result;
      let binds = [];

      data.forEach(value => {
        let item = {
          empno: setValue(value["empno"]),
          ename: setValue(value["ename"]),
          job: setValue(value["job"]),
          mgr: setValue(value["mgr"]),
          hiredate: setValue(value["hiredate"]),
          sal: setValue(value["sal"]),
          comm: setValue(value["comm"]),
          deptno: setValue(value["deptno"]),
        }
        binds.push(item);
      });

      try {
        result = await oradb.doExecuteMany(statement, binds);
        resolve(result);
      } catch (err) {
        reject(err);
      }
    } else {
      reject(new Error("Input must be an array"));
    }
  });
}

module.exports = {
  findAll,
  findByEmpno,
  findByDeptno,
  add,
  update,
  remove,
  upsert
};

現在可以來測試看看了:

models/helpers/orademo-test.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
const oradb = require('../oradb.database');
const department = require('../orademo.department');

(async function() {
  try {
    await oradb.initialize();
    
    department.findAll()
      .then(result => console.log(result.rows))
      .catch(err => console.log(err));
  } catch (err) {
    console.log(err);
  } finally {
    await oradb.close();
  }
})();

第 6 行連結到資料庫，我們使用 Oracle Connection Pool，稍後我們會在啟動 GraphQL 伺服器時，馬上初始化資料庫的 Connection Pool。 測試順利的話應該會有如下的回應:

results
1
2
3
4
5
6
7
8
9
$ node orademo-test

[
  { deptno: 10, dname: '會計部', loc: '紐約' },
  { deptno: 20, dname: 'RESEARCH', loc: 'DALLAS' },
  { deptno: 30, dname: 'SALES', loc: 'CHICAGO' },
  { deptno: 40, dname: 'OPERATIONS', loc: 'BOSTON' },
  { deptno: 70, dname: '資訊部', loc: '永康 B4' }
]

現在我們使用一個程式碼將兩個 API 併接起來。

models/orademo.js
1
2
exports.employee = require('./orademo.employee');
exports.department = require('./orademo.department');

接著要修改 GraphQL 伺服器的啟動程式 index.js，初始化 Oracle 資料庫的 Connection Pool 連結。使用 Connection Pool 網路連結效能較佳，而且可以控制對資料庫的連結數量。初始化 Oracle Connection 的程式碼與之前用於 Node.js Express 相同，可參考 Node.js 教育訓練文件 第 287 頁。

index.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
const { createServer } = require('http');
const express = require("express");
const { ApolloServer, PubSub } = require("apollo-server-express");
const cors = require('cors');
const db = require('./models/dbs');
const orademo = require('./models/orademo');
const userService = require('./services/user.service');
const typeDefs = require('./schema');
const resolvers = require('./resolvers');

/* Initialize Oracle Database connection pool */
const oradb = require('./models/oradb.database');
const dbConfig = require('./models/oradb.config');
const defaultThreadPoolSize = 4;

process.env.UV_THREADPOOL_SIZE = dbConfig.poolMax + defaultThreadPoolSize;

dbInitialize();

const app = express();
app.use(cors());

const context = async ({ req, connection }) => {
  let currentUser = null;

  try {
    const token = req
      ? req.headers.authorization.split(" ")[1]
      : connection.context.Authorization.split(" ")[1];

    const payload = token && userService.jwtVerify(token);
    currentUser = payload && (await db.users.findOne(payload.appLogin));
  } catch (err) {
    currentUser = null;
  }

  return {
    db,
    orademo,
    userService,
    currentUser,
    pubsub
  };
};

const pubsub = new PubSub();

const server = new ApolloServer({
  typeDefs,
  resolvers,
  context
});

server.applyMiddleware({ app });

app.get("/", (req, res) => res.end("Welcome to the GraphQL Sample API"));

const httpServer = createServer(app);
server.installSubscriptionHandlers(httpServer);

httpServer.listen({ port: 4000 }, () => {
  console.log(
    `GraphQL Server running @ http://localhost:4000${server.graphqlPath}`
  );
  console.log(
    `GraphQL Subscriptions running @ ws://localhost:4000${server.subscriptionsPath}`
  );
});

process.on("SIGTERM", () => {
  console.log("Received SIGTERM");
  dbClose();
});
process.on("SIGINT", () => {
  console.log("Received SIGINT");
  dbClose();
});
process.on("uncaughtException", err => {
  console.log("Uncaught exception");
  console.log(err);
  dbClose(err);
});
process.on("unhandledRejection", (reason, promise) => {
  console.error("Unhandled Rejection at: ", promise, " reason: ", reason);
  dbClose(reason);
});

async function dbInitialize() {
  try {
    console.log("Initializing database");
    await oradb.initialize();
  } catch (err) {
    console.error(err);
    process.exit(1);
  }
}

async function dbClose(err) {
  let error = err;
  console.log("Close database connection pool");

  try {
    await oradb.close();
  } catch (err) {
    console.log("Encountered error", err);
    error = error || err;
  }

  if (error) {
    process.exit(1);
  } else {
    process.exit(0);
  }
}

  • 第 6 行加入 orademo API。

  • 第 12 ~ 18 行初始化 Oracle Database connection pool。UV_THREADPOOL_SIZE 必須在使用 threadpool 的第一個調用之前設置。 這是 因為 threadpool 是在第一次使用時創建的，一旦創建，它的大小是固定的。

  • 第 39 行將 orademo 加入 GraphQL context。這將會用在 GraphQL Resolvers 解析函式。

  • 第 70 行以後加上幾個 Signal 監聽器,當 Node.js process 收到這些信號時，將會發出 Signal Events，收到這些事件時關閉 Oracle Database connection pool。

現在可以開始設計 GraphQL Query 了，從 Schema 開始:

schema/department.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
const { gql } = require("apollo-server-express");

module.exports = gql`
  extend type Query {
    totalDepartments: Int
    allDepartments: [Department]
  }

type Department {
  deptno: ID!
  dname: String
  loc: String
  employees(job: String): [Employee!]!
}
`;

第 13 行這裡加入了一個邊(edge)的連結，employees 欄位是資料庫中沒有的欄位，而且他可以接受一個引數 job。返回的則是一個 Employee 型態的陣列，Employee 型態會在稍後定義。

schema/employee.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
const { gql } = require("apollo-server-express");

module.exports = gql`
  extend type Query {
    totalEmployees: Int!
    allEmployees: [Employee!]!
  }

  type Employee {
    empno: ID!
    ename: String
    job: String
    mgr: Int
    hiredate: DateTime
    sal: Float
    comm: Float
    income: Float
    department: Department
}
`;

這裡第 17 與 18 行也是資料庫中沒有的欄位，income 在資料庫中不存在， department 則是個邊連結。這裡的 Schema 欄位都用小寫格式，從 Oracle API 中的資料屬性也都必須是小寫，否則預設的解析函式將無法解析。

記得要修改 schema/index.js 將新的 Schema 併接進來。

schema/index.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
...

const employeeSchema = require('./employee');
const departmentSchema = require('./department');

...

module.exports = [
  linkSchema, 
  userSchema, 
  employeeSchema, 
  departmentSchema, 
  photoSchema, 
  photoConnectionSchema, 
  subscriptionSchema
];

再來就是實作解析函式了:

resolvers/department.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
module.exports = {
  Query: {
    totalDepartments: (parent, args, { orademo }) =>
      orademo.department.findAll().then(result => result.rows.length ),
    allDepartments: (parent, args, { orademo }) => {
      return orademo.department.findAll()
      .then(result => result.rows );
    }  
  }, 
  Department: {
    employees: (parent, { job }, { orademo }) => {
      return orademo.employee
        .findByDeptno(parent.deptno)
        .then(result => { 
          if (typeof job === 'string') {
            return result.rows.filter(value => value.job === job);
          }
          return result.rows
        });
    }
  },
};

第 11 行 employees 欄位是資料庫中沒有的欄位，所以這裏要實作他的客製解析函式，他可選擇性的接受一個引數 job，可過濾職位型態。

resolvers/employee.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
module.exports = {
  Query: {
    totalEmployees: (parent, args, { orademo }) =>
      orademo.employee.findAll().then(result => result.rows.length),
    allEmployees: (parent, args, { orademo }) => {
      return orademo.employee.findAll()
        .then(result => result.rows );
    }  
  }, 
  Employee: {
    department: (parent, args, { orademo }) => {
      return orademo.department
        .findByDeptno(parent.deptno)
        .then(result => result.rows[0] );
    },
    income: (parent) => {
      let sal = !isNaN(parseFloat(parent.sal)) ? parseFloat(parent.sal) : 0;
      let comm = !isNaN(parseFloat(parent.comm)) ? parseFloat(parent.comm) : 0;
      return sal + comm;
    }
  },
};

這裡則客製了兩個解析函式 department 與 income。記得也要將新增的兩個 resolvers 併接起來:

resolvers/index.js
1
2
3
4
5
6
7
8
9
10
11
12
13
...
const employeeResolvers = require('./employee');
const departmentResolvers = require('./department');
...

module.exports = [
  userResolvers, 
  photoResolvers,
  employeeResolvers, 
  departmentResolvers,
  photoConnectionResolvers, 
  subscriptionResolvers
];

可以測試 Query 了。

query departments
1
2
3
4
5
6
7
8
9
10
11
12
13
query departments {
  totalDepartments
  allDepartments {
    dname
    deptno
    loc 
    employees
    {
      ename
      job
      income
    }
  }

可以在 employees 欄位選擇性的加入 job 引數:

query departments
1
2
3
4
5
6
7
8
9
10
11
12
13
14
query departments {
  totalDepartments
  allDepartments {
    dname
    deptno
    loc 
    employees(job: "MANAGER")
    {
      ename
      job
      income
    }
  }
}

這裡我只想知道主管是誰。

一樣可以測試一下 employee 查詢:

query employees
1
2
3
4
5
6
7
8
9
10
11
12
13
query Employees {
  totalEmployees
  allEmployees {
    empno
    ename
    hiredate
    income
    department {
      dname
      loc
    }
  }
}

這個 query 應該沒甚麼問題，他可以透過 department 欄位實作資料的連結，這在 GraphQL 中我們稱為 “邊” (Edges)，這與 SQL 裡的 join類似。在 SQL 中 join 越多，效能就會愈差，GraphQL 當然也不會有例外。GraphQL 也可以嵌套的連結，多層的嵌套可能會凍結伺服器的效能，要仔細評估一下。

接續下一篇 建立 GraphQL API (9)