设计一个基于 GraphQL 的 Node.js 工单系统

• MySQL 数据库存储，Redis 缓存
• OAuth 鉴权
• Dataloader 数据查询优化
• GraphQL 底层接口数据引擎

表结构

数据库采用 MySQL，核心两张表，分别是 工单 和 回复。

CREATE TABLE IF NOT EXISTS `xibang`.`d_ticket` (
  `tid` varchar(40) NOT NULL DEFAULT '' COMMENT '工单id',
  `uid` int(11) unsigned NOT NULL COMMENT '提交用户id',
  `status` enum('open','closed') NOT NULL DEFAULT 'open' COMMENT '开闭状态',
  `reply` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '回复状态',
  `type` varchar(32) NOT NULL DEFAULT 'bug' COMMENT '类型',
  `notify` enum('mobile','email','both','none') NOT NULL DEFAULT 'email' COMMENT '通知方式',
  `title` varchar(255) NOT NULL DEFAULT '' COMMENT '标题',
  `body` blob NOT NULL COMMENT '描述',
  `createdAt` int(10) unsigned NOT NULL COMMENT '创建时间',
  `updatedAt` int(10) unsigned NOT NULL COMMENT '操作时间',
  PRIMARY KEY (`tid`),
  KEY `uid` (`uid`),
  KEY `createdAt` (`createdAt`),
  KEY `status` (`status`),
  KEY `type` (`type`),
  KEY `reply` (`reply`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

工单状态分两种：

• 打开
• 关闭

回复状态也分两种：

• 0： 未回复
• 1： 回复

CREATE TABLE IF NOT EXISTS `xibang`.`d_ticketreply` (
  `tid` varchar(40) NOT NULL DEFAULT '' COMMENT '工单id',
  `uid` int(11) unsigned NOT NULL COMMENT '回复人用户id',
  `body` blob NOT NULL COMMENT '回复内容',
  `createdAt` int(10) unsigned NOT NULL COMMENT '回复时间',
  `updatedAt` int(10) unsigned NOT NULL COMMENT '最后修改时间',
  KEY `tid` (`tid`),
  KEY `createdAt` (`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8
9

GraphQL Schema

打印脚本：

const { printSchema } = require("graphql");
const schema = require("../src/graphql");

console.log(printSchema(schema));
1
2
3
4

完整的 GraphQL 结构：

"""
Root mutation object
"""
type Mutation {
  createTicket(input: TicketCreateInput!): Ticket
  updateTicket(input: TicketUpdateInput!): Ticket
  createReply(input: ReplyCreateInput!): TicketReply
  updateReply(input: ReplyUpdateInput!): TicketReply
}

"""
An object with an ID
"""
interface Node {
  """
  The id of the object.
  """
  id: ID!
}

type Owner implements Node {
  """
  The ID of an object
  """
  id: ID!
  uid: Int!
  oid: Int!
  username: String!
  mobile: String!
  email: String!
  createdAt: Int!
  avatar: String!
  verified: Boolean!
  isAdmin: Boolean!
}

"""
Information about pagination in a connection.
"""
type PageInfo {
  """
  When paginating forwards, are there more items?
  """
  hasNextPage: Boolean!

  """
  When paginating backwards, are there more items?
  """
  hasPreviousPage: Boolean!

  """
  When paginating backwards, the cursor to continue.
  """
  startCursor: String

  """
  When paginating forwards, the cursor to continue.
  """
  endCursor: String
}

"""
Root query object
"""
type Query {
  viewer: User
  ticket(
    """
    Ticket ID
    """
    tid: String!
  ): Ticket
  tickets(
    """
    Ticket Owner User ID
    """
    uid: Int

    """
    Ticket Open Status
    """
    status: TicketStatus

    """
    Ticket Type
    """
    type: TicketNotify

    """
    Ticket Reply Status
    """
    reply: Boolean
    after: String
    first: Int
    before: String
    last: Int
  ): TicketsConnection
}

"""
A connection to a list of items.
"""
type RepliesConnection {
  """
  Information to aid in pagination.
  """
  pageInfo: PageInfo!

  """
  A list of edges.
  """
  edges: [RepliesEdge]

  """
  A count of the total number of objects in this connection, ignoring pagination.
  """
  totalCount: Int

  """
  A list of all of the objects returned in the connection.
  """
  replies: [TicketReply]
}

"""
An edge in a connection.
"""
type RepliesEdge {
  """
  The item at the end of the edge
  """
  node: TicketReply

  """
  A cursor for use in pagination
  """
  cursor: String!
}

"""
Input reply payload
"""
input ReplyCreateInput {
  """
  Ticket ID
  """
  tid: String!

  """
  Reply Content
  """
  body: String!
}

"""
Input reply payload
"""
input ReplyUpdateInput {
  """
  Ticket ID
  """
  tid: String!

  """
  Reply Content
  """
  body: String!

  """
  Reply createdAt
  """
  createdAt: Int!
}

type Ticket implements Node {
  """
  The ID of an object
  """
  id: ID!
  tid: String!
  uid: Int!
  status: TicketStatus!
  reply: Boolean!
  type: String!
  notify: TicketNotify!
  title: String!
  body: String!
  createdAt: Int!
  updatedAt: Int!
  replies(
    after: String
    first: Int
    before: String
    last: Int
  ): RepliesConnection
  owner: Owner
}

"""
Input ticket payload
"""
input TicketCreateInput {
  """
  Ticket Type
  """
  type: String!

  """
  Ticket Notification Type
  """
  notify: TicketNotify!

  """
  Ticket Title
  """
  title: String!

  """
  Ticket Content
  """
  body: String!
}

enum TicketNotify {
  mobile
  email
  both
  none
}

type TicketReply implements Node {
  """
  The ID of an object
  """
  id: ID!
  tid: String!
  uid: Int!
  body: String!
  createdAt: Int!
  updatedAt: Int!
  owner: Owner
}

"""
A connection to a list of items.
"""
type TicketsConnection {
  """
  Information to aid in pagination.
  """
  pageInfo: PageInfo!

  """
  A list of edges.
  """
  edges: [TicketsEdge]

  """
  A count of the total number of objects in this connection, ignoring pagination.
  """
  totalCount: Int

  """
  A list of all of the objects returned in the connection.
  """
  tickets: [Ticket]
}

"""
An edge in a connection.
"""
type TicketsEdge {
  """
  The item at the end of the edge
  """
  node: Ticket

  """
  A cursor for use in pagination
  """
  cursor: String!
}

enum TicketStatus {
  open
  closed
}

"""
Input ticket payload
"""
input TicketUpdateInput {
  """
  TicketID
  """
  tid: String!

  """
  Ticket Open Status
  """
  status: TicketStatus

  """
  Ticket Type
  """
  type: String

  """
  Ticket Notify Status
  """
  notify: TicketNotify

  """
  Ticket Title
  """
  title: String

  """
  Ticket Body
  """
  body: String
}

type User implements Node {
  """
  The ID of an object
  """
  id: ID!
  uid: Int!
  oid: Int!
  username: String!
  mobile: String!
  email: String!
  createdAt: Int!
  avatar: String!
  verified: Boolean!
  isAdmin: Boolean!
  tickets(
    """
    Ticket Owner User ID
    """
    uid: Int

    """
    Ticket Open Status
    """
    status: TicketStatus

    """
    Ticket Type
    """
    type: TicketNotify

    """
    Ticket Reply Status
    """
    reply: Boolean
    after: String
    first: Int
    before: String
    last: Int
  ): TicketsConnection
}
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
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363

权限设置

Query 部分：

• Viewer
  • 用户查询自己的信息
  • 查询自己的工单
• Ticket
  • 管理员查询所有工单
  • 用户查询自己的工单
  • 查询工单回复
• Tickets
  • 用户无权限，仅限管理员查询所有工单

Mutation 部分：

• 创建工单
• 更新工单：用户操作自己的，管理员操作（关闭、重新打开）所有
• 添加回复
• 更新回复

{
  Query: {
    viewer: {
      // 用户（管理员）查询自己的
      tickets: {
        // 用户查询自己的工单
      }
    },
    ticket: {
      // 用户查询自己的，管理员查询所有
      replies: {

      }
    },
    tickets: {
      // 用户无权限，管理员查询所有
      // 用户查询自己的工单从 viewer 下进行
    }
  },
  Mutation: {
    addTicket: '用户',
    updateTicket: '用户操作自己的，管理员操作（关闭、重新打开）所有',
    addReply: '用户',
    updateReply: '用户（管理员）操作自己的'
  }
}
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

代码实现

在 Root 中进行鉴权。

Query 部分

const {
  GraphQLObjectType, GraphQLNonNull, GraphQLString
} = require('graphql');
const { type: UserType } = require('./types/user');
const { type: TicketType, args: TicketArgs } = require('./types/ticket');
const connection = require('./interfaces/connection');
const { getObject } = require('./loaders');

module.exports = new GraphQLObjectType({
  name: 'Query',
  description: 'Root query object',
  fields: {
    viewer: {
      type: UserType,
      resolve: (_, args, ctx) => {
        const { uid } = ctx.session;
        return getObject({ type: 'user', id: uid });
      }
    },
    ticket: {
      type: TicketType,
      args: {
        tid: {
          description: 'Ticket ID',
          type: new GraphQLNonNull(GraphQLString)
        }
      },
      resolve: (_, args, ctx) => getObject({ id: args.tid, type: 'ticket' }).then((data) => {
        const { uid } = ctx.session;
        // TODO: Admin Auth Check
        // data.uid !== uid && user is not admin
        if (data.uid !== uid) {
          return null;
        }
        return data;
      })
    },
    tickets: connection('Tickets', TicketType, TicketArgs)
  }
});
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

权限的校验在此处进行。可以通过用户 uid 判断是否为自己的工单，也可以在此处去做管理员的校验。

Mutation 部分

const { GraphQLObjectType } = require('graphql');
const { type: TicketType, input: TicketInputArgs, inputOperation: TicketUpdateInputArgs } = require('./types/ticket');
const { type: ReplyType, input: ReplyInputArgs, inputUpdate: ReplyUpdateInputArgs } = require('./types/reply');
const { TicketCreate, TicketUpdate } = require('./mutations/ticket');
const { ReplyCreate, ReplyUpdate } = require('./mutations/reply');

module.exports = new GraphQLObjectType({
  name: 'Mutation',
  description: 'Root mutation object',
  fields: {
    createTicket: {
      type: TicketType,
      args: TicketInputArgs,
      resolve: (_, { input }, ctx) => {
        const { uid } = ctx.session;
        return TicketCreate(uid, input);
      }
    },
    updateTicket: {
      type: TicketType,
      args: TicketUpdateInputArgs,
      resolve: (_, { input }, ctx) => {
        const { uid } = ctx.session;
        const { tid, ...args } = input;
        return TicketUpdate(tid, args, uid);
      }
    },
    createReply: {
      type: ReplyType,
      args: ReplyInputArgs,
      resolve: (_, { input }, ctx) => {
        const { uid } = ctx.session;
        return ReplyCreate(uid, input);
      }
    },
    updateReply: {
      type: ReplyType,
      args: ReplyUpdateInputArgs,
      resolve: (_, { input }, ctx) => {
        const { uid } = ctx.session;
        return ReplyUpdate(uid, input);
      }
    }
  }
});
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

Mutation 中不需要进行用户的 UID 校验了，因为有 Session 的校验在前面了。

DataLoader 引入查询

DataLoader 中文文档翻译： https://dataloader.js.cool/

const DataLoader = require("dataloader");
const { query, format } = require("../db");
const { CountLoader } = require("./connection");

const TICKETTABLE = "xibang.d_ticket";

/**
 * TicketLoader
 * ref: UserLoader
 */
exports.TicketLoader = new DataLoader((tids) => {
  const sql = format("SELECT * FROM ?? WHERE tid in (?)", [TICKETTABLE, tids]);
  return query(sql).then((rows) =>
    tids.map(
      (tid) =>
        rows.find((row) => row.tid === tid) ||
        new Error(`Row not found: ${tid}`)
    )
  );
});

/**
 * TicketsLoader
 * Each arg:
 * {  time: {before, after}, // Int, Int
 *    where, // obj: {1:1, type:'xxx'}
 *    order, // 'DESC' / 'ASC'
 *    limit // Int
 * }
 */
exports.TicketsLoader = new DataLoader((args) => {
  const result = args.map(
    ({ time: { before, after }, where, order, limit }) => {
      let time = [];
      if (before) {
        time.push(format("createdAt > ?", [before]));
      }
      if (after) {
        time.push(format("createdAt < ?", [after]));
      }
      if (time.length > 0) {
        time = ` AND ${time.join(" AND ")}`;
      } else {
        time = "";
      }
      let sql;
      if (where) {
        sql = format(
          `SELECT * from ?? WHERE ?${time} ORDER BY createdAt ${order} LIMIT ?`,
          [TICKETTABLE, where, limit]
        );
      } else {
        sql = format(
          `SELECT * from ?? WHERE 1=1${time} ORDER BY createdAt ${order} LIMIT ?`,
          [TICKETTABLE, limit]
        );
      }
      return query(sql);
    }
  );
  return Promise.all(result);
});

/**
 * TicketsCountLoader
 * @param {obj} where where args
 * @return {DataLoader} CountLoader
 */
exports.TicketsCounter = (where) => CountLoader.load([TICKETTABLE, where]);
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

Facebook 的 Dataloader 框架可以帮助代码中减少查询次数，提升查询的效率。

GraphQL Edge 分页实现

使用 Cursor 分页，由于 MySQL 不支持 Cursor 游标，所以通过代码来实现。

const { parseArgs, fromConnectionCursor, toConnectionCursor } = require('../lib');
const { TicketsLoader } = require('./ticket');
const { RepliesLoader } = require('./reply');

/**
 * Switch DataLoader by Type
 * @param {string} type Ticket or TicketReply
 * @returns {function} DataLoader
 */
const TypeLoader = (type) => {
  if (type === 'Ticket') {
    return TicketsLoader;
  }
  return RepliesLoader;
};

/**
 * Filter Limit Args
 * @param {string} arg first or last
 * @param {int} v value
 * @returns {int} limit or undefined
 */
const filterLimitArg = (arg, v) => {
  if (typeof v === 'number') {
    if (v < 0) {
      throw new Error(`Argument "${arg}" must be a non-negative integer`);
    } else if (v > 1000) {
      return 1000;
    }
    return v;
  }
  return undefined;
};

/**
 * Connection Edges Loader
 * @param {string} type Type Name
 * @param {obj} args Args like: {first: 10, after: "xxx"}
 * @param {int} totalCount totalCount
 * @param {obj} obj parent node object
 * @returns {Promise} {edges, pageInfo: {startCursor, endCursor, hasNextPage, hasPreviousPage}}
 */
exports.NodesLoader = (type, args, totalCount, obj = {}) => {
  // 分页查询 limit 字段
  let { first, last } = args;
  first = filterLimitArg('first', first);
  last = filterLimitArg('last', last);
  const [limit, order] = last === undefined ? [first, 'DESC'] : [last, 'ASC'];

  // 删除查询参数中的 first, last, before, after 无关条件
  // 保留剩余的，如 { type: 'issue' }
  const { after, before } = args;
  let where = parseArgs(args);
  if (type === 'Ticket') {
    if (obj.uid) {
      where.uid = obj.uid;
    }
  } else {
    where = {
      tid: obj.tid
    };
  }

  // 从 before, after 中获取 createdAt 和 index
  const [beforeTime, beforeIndex = totalCount] = fromConnectionCursor(before);
  const [afterTime, afterIndex = -1] = fromConnectionCursor(after);

  const loader = TypeLoader(type);
  return loader.load({
    time: {
      before: beforeTime,
      after: afterTime
    },
    where,
    order,
    limit
  }).then((nodes) => {
    const edges = nodes.map((v, i) => ({
      cursor: toConnectionCursor(v.createdAt, order === 'DESC' ? (afterIndex + i + 1) : (totalCount - beforeIndex - i - 1)),
      node: v
    }));
    const firstEdge = edges[0];
    const lastEdge = edges[edges.length - 1];

    return {
      edges,
      totalCount,
      pageInfo: {
        startCursor: firstEdge ? firstEdge.cursor : null,
        endCursor: lastEdge ? lastEdge.cursor : null,
        hasPreviousPage:
          typeof last === 'number' ? (totalCount - beforeIndex - limit) > 0 : false,
        hasNextPage:
          typeof first === 'number' ? (afterIndex + limit) < totalCount : false
      }
    };
  });
};
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

需要注意一下：cursor 是 base64 编码的。

OAuth 鉴权

const { getAccessToken } = require('./model');

const e403 = (ctx) => {
  // 失败
  ctx.status = 403;
  ctx.body = {
    data: {},
    errors: [{
      message: 'You need signin first.',
      type: 'FORBIDDEN'
    }]
  };
};

module.exports = () => (ctx, next) => {
  const { access_token: accessTokenQuery = '' } = ctx.query;
  const { authorization = '' } = ctx.header;
  const accessToken = authorization.startsWith('Bearer ') ? authorization.replace('Bearer ', '') : accessTokenQuery;

  if (accessToken === '') {
    return e403(ctx);
  }
  // 检查 Token 合法性
  return getAccessToken(accessToken)
    .then((data) => {
      if (!data) {
        return e403(ctx);
      }
      ctx.session = data.user;
      return next();
    });
};
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

这部分比较简单，可以通过 Query 或者 Header 传递鉴权信息。

---

该项目完整实现代码下载： https://download.csdn.net/download/jslygwx/88188235