发表Server / Egg13 分钟读完 (大约1977个字)

Eggjs实现通用化sql查询

背景

Egg官方推荐的mysql使用方法是类似orm的方式, 但是这种方式对于复杂查询或者表结构的调整来说不太友好, 于是试着开发一套直接基于sql语句的友好的开发方式, 类似Java的MyBatis

框架设计

sql统一管理

首先需要将SQL文件统一管理, 不能散落在各Service中, 否则极其不好维护.

然后通过node的动态require实现自动加载文件夹下的所有sql文件, 合并sql数据, 同时给每个查询分配唯一ID, 在启动时就能检测如果有重复ID直接启动报错, 这样保证线上运行不会有问题

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
// app/sql/index.js
const fs = require('fs');
const path = require('path');
const _ = require('lodash');

const sqls = {};

fs.readdirSync(__dirname).map((filename) => {
  if (filename == 'index.js') {
    return;
  }
  const filepath = path.join(__dirname, filename);
  const sql = require(filepath);
  if (!_.isObject(sql)) {
    throw new Error('Sql file need export an OBJECT of sql map');
  }
  for (const id in sql) {
    if (sqls[id]) {
      throw new Error('Duplicate sql id');
    }
  }
  Object.assign(sqls, sql);
});

module.exports = (id) => {
  if (sqls[id]) {
    return sqls[id];
  }
  throw new Error(`Not found sql: ${id}`);
};

实现sql-service

考虑到比较多简单查询或者列表通用查询等实际上是可以由页面直接发起的, 可以在做好参数校验的情况下直接提供查询入口, 但是对于修改数据的需求也是需要后端逻辑验证后才能执行, 所以在sql-service层提供不同的入口和方式, 以及在sql文件中定义好支持的类型

参考js的通用做法, 约定以_.开头的文件和sql是只有服务端内部才能调用的 sql, 其他的都可以由页面直接调用

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
// app/sql/index.js - 加上 private-sql 校验
const fs = require('fs');
const path = require('path');
const _ = require('lodash');

const sqls = {};

fs.readdirSync(__dirname).map((filename) => {
  if (filename == 'index.js') {
    return;
  }
  const fileIsPrivate = filename.startsWith('_.');
  const filepath = path.join(__dirname, filename);
  const sql = require(filepath);
  if (!_.isObject(sql)) {
    throw new Error('Sql file need export an OBJECT of sql map');
  }
  for (const id in sql) {
    if (sqls[id]) {
      throw new Error('Duplicate sql id');
    }
    if (id.startsWith('_.') !== fileIsPrivate) {
      throw new Error(`Private sql error, file: ${filename}, sql: ${id}`);
    }
  }
  Object.assign(sqls, sql);
});

module.exports = (id) => {
  if (sqls[id]) {
    return sqls[id];
  }
  throw new Error(`Not found sql: ${id}`);
};
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
// app/service/sql-base.js
'use strict';
const _ = require('lodash');
const Service = require('egg').Service;
const getSql = require('./../sql');

class SqlBaseService extends Service {
  getSql(sqlId, params, env, _private = false) {
    if (_.startsWith(sqlId, '_.') && !_private) {
      this.ctx.abort(404, 'Not Found');
    }
    const sql = getSql(sqlId);
    let result = null;
    if (_.isString(sql)) {
      result = sql;
    }
    if (_.isFunction(sql)) {
      result = sql(params, this.ctx);
    }
    if (!result) {
      throw new Error('sql need be string or function');
    }
    return result;
  }
  async queryBySql(sql, params) {
    return await this.app.mysql.query(sql, { ...params, ...this.env });
  }
  async query(sqlId, params, _private) {
    const sqlSpan = this.ctx.startSpan(sqlId);
    const result = await this.queryBySql(this.getSql(sqlId, params, this.env, _private), params);
    if (sqlSpan) {
      sqlSpan.setTag('status', 200);
      sqlSpan.finish();
    }
    return result;
  }
  // 对外直接提供的接口, 不支持查询私有sql
  async apiGetData() {
    const {
      ctx,
      ctx: { args },
    } = this;
    const { params, sqlId } = args;
    return [await this.query(sqlId, params)];
  }
}

上面两步完成之后, 就完成了主体框架的搭建, 后续的内容就是完善缺少的功能

补充功能

条件/参数化查询

最常见的需求: 某字段不传则不要作为where的过滤条件

在其他语言中, 这种往往通过模板字符串渲染来解决, 但是在es6中引入的模板字符串, 可以非常完美的解决这个问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
// app/service/sql-base.js
class SqlParams {
  constructor(params, env) {
    this.data = { ...params, ...env };
  }
  exist(key) {
    return _.has(this.data, key);
  }
  setIfExist(key, str, notExistStr) {
    const flag = _.isBoolean(key) ? key : this.exist(key);
    return flag ? str : notExistStr || '';
  }
}

class SqlBaseService extends Service {
  getSql(sqlId, params, env, _private = false) {
    // ...
    params = new SqlParams(params, env);
    result = sql(params, this.ctx);
    // ...
  }
}

这样在sql文件中的写法会非常优雅

1
2
3
4
5
6
7
8
9
10
11
12
13
// test.sql
module.exports = {
  'test_common.query': (params, ctx) => {
    return `
    SELECT
      name
    FROM
      table
    WHERE 1 = 1
    ${params.setIfExist('name', `AND name LIKE CONCAT('%', :name, '%')`)}
    `;
  },
};

环境变量注入

也是比较常见的需求, 需要在执行查询时候直接获取当前用户和项目或者其他配置等, 上面的代码中已经带上env变量了, 这里只写一下它的生成过程

这里的no_env是提供给如schedule或测试的时候用的参数 
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
// app/service/sql-base.js
class SqlBaseService extends Service {
  get env() {
    const { ctx } = this;
    return ctx.no_env
      ? {
          env_user: '_system',
          env_project: '_system',
        }
      : {
          env_user: ctx.user.name,
          env_project: ctx.user.project,
        };
  }
}

// test.sql
module.exports = {
  'test_common.query': (params, ctx) => {
    return `
    SELECT
      name
    FROM
      table
    WHERE 1 = 1
    AND create_user = :env_user
    ${params.setIfExist('name', `AND name LIKE CONCAT('%', :name, '%')`)}
    `;
  },
};

支持事务/切换数据库

这个也非常常见和简单, 我们将conn作为参数一路透传, 最终传入sql执行器即可

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
class SqlBaseService extends Service {
  async queryBySql(sql, params, conn) {
    conn = conn || this.app.mysql;
    return await conn.query(sql, { ...params, ...this.env });
  }
}
// 使用
const conn = await this.app.mysql.getConnection();
try {
  await conn.beginTransaction();

  await this.query(`_.test`, { name: 'test' }, true, conn);
  await this.query(`_.test.2`, { name: 'test' }, true, conn);

  await conn.commit();
  return { success: true };
} catch (err) {
  await conn.rollback();
  throw err;
} finally {
  await conn.release();
}

支持自动分页/排序接口

这个接口基本上是提供给前端列表页面使用的, 会稍微复杂点, 而且对于各种db需要一些定制化开发, 核心原理就是根据原始sql自动生成统计的sql

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
class SqlBaseService extends Service {
  async queryListByPage(sqlId, params, page, size, order, direct, _private, conn) {
    // page start from 0
    const realSql = this.getSql(sqlId, params, this.env, _private);
    const countSql = this.resolveCountSql(realSql);
    const pageSql = this.resolvePageSql(realSql, _.toSafeInteger(page), _.toSafeInteger(size), order, direct);
    const [list, count] = await Promise.all([this.queryBySql(pageSql, params, conn), this.queryBySql(countSql, params, conn)]);
    return {
      data: list,
      total: count[0].num,
    };
  }
  resolveCountSql(sql) {
    const SELECT = 'select';
    const FROM = 'from';
    let countSql = sql;
    if (sql.match(new RegExp(`\\s+${FROM}\\s+`, 'ig')).length === 1) {
      countSql = sql.replace(new RegExp(`^(\\s*${SELECT}\\s+?)(\\S[\\s\\S]+?\\S)(\\s+?${FROM}\\s+?)`, 'ig'), '$1 count(1) as num $3');
    } else {
      const selectMatch = sql.match(new RegExp(`^(\\s*${SELECT}\\s+?)`, 'ig'));
      let selectPos = selectMatch[0].length;
      let fromPos = 0;
      let level = 0;
      for (let i = selectPos; i < sql.length; i++) {
        const char = sql[i];
        if (char === '(') {
          level++;
        }
        if (char === ')') {
          level--;
        }
        if (level < 0) {
          throw new Error('error sql when parse count all: level less 0');
        }
        if (level === 0 && char.toLowerCase() === FROM[0] && sql.slice(i, i + 4).toLowerCase() === FROM && /\s\s/gi.test(sql[i - 1] + sql[i + 4])) {
          fromPos = i;
          break;
        }
      }
      if (!fromPos) {
        throw new Error('error sql when parse count all: not found FROM');
      }
      countSql = `${sql.slice(0, selectPos)} count(1) as num ${sql.slice(fromPos)}`;
    }
    return countSql;
  }
  resolvePageSql(sql, page, size, order, direct) {
    const start = size * (page - 1);
    let pageSql = sql.trimRight();
    if (pageSql.endsWith(';')) {
      pageSql = pageSql.substring(0, pageSql.length - 1);
    }
    if (/^[a-z0-9_]+$/i.test(order)) {
      direct = _.toLower(direct) === 'asc' ? 'asc' : 'desc';
    } else {
      order = null;
    }
    pageSql = `select * from (${pageSql}) t_auto_page ${order ? `order by ${order} ${direct}` : ``} limit ${start}, ${size};`;
    return pageSql;
  }
  // 提供给前端直接使用的api接口
  async apiGetDataByPages() {
    const {
      ctx,
      ctx: { args },
    } = this;
    const { page = 1, size = 10, order = '', direct = '' } = args.page || {};
    const { params, sqlId } = args;
    return await this.queryListByPage(sqlId, params, page, size, order, direct);
  }
}

支持前端批量查询和事务

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
class SqlBaseService extends Service {
  async batchQuery(sqlId, params, _private) {
    const conn = await this.app.mysql.getConnection();
    try {
      await conn.beginTransaction();
      const results = [];
      for (let i = 0; i < Math.max(_.isArray(sqlId) ? sqlId.length : 0, params.length); i++) {
        const _params = _.get(params, i);
        const _sqlId = _.isArray(sqlId) ? sqlId[i] : sqlId;
        const result = await this.query(_sqlId, _params, _private, conn);
        if (result.affectedRows && result.changedRows) {
          results.push(_.pick(result, ['affectedRows', 'changedRows', 'insertId']));
        } else {
          results.push(result);
        }
      }
      await conn.commit();
      return results;
    } catch (err) {
      await conn.rollback();
      throw err;
    } finally {
      await conn.release();
    }
  }
  async apiExecBatchQuery() {
    const {
      ctx,
      ctx: { args },
    } = this;
    const results = await this.batchQuery(args.sqlId, args.params);
    return [results];
  }
}

权限验证

直接基于ctx上提供的权限验证方法即可实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// test.sql
module.exports = {
  'test_common.insert': (params, ctx) => {
    ctx.validPermission('super_admin');
    return `
    INSERT INTO table (
      name,
      create_user
    )
    VALUES
      (
        :name,
        :env_user,
      );
    `;
  },
};

操作日志统一埋点

基于框架中之前开发的log中间件添加一下sql相关日志即可

这里有个比较巧妙的方法实现自动获取当前的sql_id, 是通过Error.prepareStackTrace来实现的 
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
// app/extend/context.js
module.exports = {
  callsites() {
    const _prepareStackTrace = Error.prepareStackTrace;
    Error.prepareStackTrace = (error, structuredStackTrace) => structuredStackTrace;
    const stack = new Error().stack.slice(1);
    Error.prepareStackTrace = _prepareStackTrace;
    return stack;
  },
  addSqlLog(action, params) {
    this.sqlLog = this.sqlLog || {};
    const stack = this.callsites();
    const sqlId = stack[1].getFunctionName();
    if (sqlId) {
      this.sqlLog[sqlId] = this.sqlLog[sqlId] || {
        action,
        params: [],
      };
      this.sqlLog[sqlId].params.push(params);
    }
  },
};
// test.sql
module.exports = {
  'test_common.insert': (params, ctx) => {
    ctx.validPermission('super_admin');
    ctx.addSqlLog(`table表新增内容`, `[name: ${params.data.name}]`);
    return `
    INSERT INTO table (
      name,
      create_user
    )
    VALUES
      (
        :name,
        :env_user,
      );
    `;
  },
};

小结

以上功能开发完之后, 前端开发就可以直接按需自己实现相关查询和操作了, 再也不会出现加一个字段同步修改半天这种困扰. 但是对于比较复杂或者有其他特殊功能的接口, 后端还是提供统一封装更好, 毕竟使用这套框架之后, 后端的查询也是个非常简单的事情了

Eggjs实现通用化sql查询

https://mosby-zhou.github.io/2019/01-29-egg-sql-query/

作者

Mosby

发布于

2019-01-29

许可协议

#

喜欢这篇文章？打赏一下作者吧

评论

关注我

分类

标签

css1
docker1
egg7
javascript20
others9
programming1
react40
react-native1
sql3
×