开源一个好用的nodejs访问mysql类库

一、背景问题

自nodejs诞生以来出现了一大批的web框架如express koa2 egg等等，前端可以不再依赖后端可以自己控制服务端的逻辑。原来的后端开发同学的阵地前端如今同样也写的风生水起，撸起袖子就是干几周一个项目前端、后端都自己搞定了，那叫一个效率。

虽然各框架都提供了一些自己的接口去简化CRUD操作，但是还是没有解决复杂条件查询、服务端分页等等问题，导致开发过程中很多开发者还是直接拼接SQL来访问数据库。于是我们就想如何让访问数据库变得简单易用。

二、类库设计

操作数据库都可以看作跟数据库做一次交互，交互传递的是数据+命令，我们希望能够用尽量简洁的代码来描述每一次访问数据库。所以可以对mysqljs对数据库操作进行封装，重新设计访问数据库的API。为nodejs访问mysql数据库提供强大流畅的api的工具类库，目标是希望访问数据库逻辑都能使用一行代码完成，让访问数据库变得更加简单优雅。

1. 初始化配置

初始化如下

const db = new DbClient({   host     : '127.0.0.1',   user     : 'root',   password : 'secret',   database : 'my_db' });

2. 构造查询

• 2.1 查询单个值

// 查询单个值，比如下面例子返回的是数字51，满足条件的数据条数 var result = await db   .select("count(1)")   .from("page")   .where("name", "测试", "like")   .queryValue();

• 2.2 查询单条数据

// 查询单条数据，返回的是 result = {id:12, name: '测试页面', ....} const result = await db   .select("*")   .from("page")   .where("id", 12) // id = 12   .queryRow();

• 2.3 查询多条数据

// 查询多条数据 返回的是 ressult = [{...}, {...}]; const result = await db   .select("*")   .from("page")   .where("name", "测试页面", 'like') // name like '%测试页面%'   .queryList();

• 2.4 服务端分页查询

// 查询多条数据（服务端分页） 返回的是 ressult = {total: 100, rows:[{...}, {...}]}; const result = await db   .select("*")   .from("page")   .where("id", 100, "lt") // id < 100   .queryListWithPaging(3, 20); //每页 20 条，取第 3 页

• 2.5 多表关联查询

// 多表关联查询 var result = await db   .select("a.page_id, a.saga_key")   .from("page_edit_content as a")   .join("left join page as b on b.id = a.page_id")   .where("b.id", 172)   .queryList();

• 2.6 查询除了支持各种多表join外，当然还支持groupby orderby having等复杂查询操作

const result = await db   .select("a1 as a, b1 as b, count(c) as count")   .from("table")   .where("date", db.literals.now, "lt") // date < now()   .where("creator", "huisheng.lhs")  // creator = 'huisheng.lhs"   .groupby("a1, b1")   .having("count(category) > 10")   .orderby("id desc")   .queryListWithPaging(2); //默认每页20条，取第2页

3. 构造插入

const task = {   action: "testA",   description: "desc1",   state: "123",   result: "result1" };  // 插入一条数据 const result = await db   .insert("task", task)   .execute();  // 也支持直接写字段，支持增加字段 const result = await db   .insert("task")   .column("action", "test")   .column("create_time", db.literals.now)   .execute();  // 插入多条数据 const tasks = [ task1, taks2, task3 ]; const result = await db   .insert("task", tasks)   .execute();  // 支持增加或覆盖字段 const result = await db   .insert("task", tasks)   .column('create_time', db.literals.now)  // 循环赋值给每一行数据   .column('create_user', 'huisheng.lhs')   .execute();

4. 构造更新

const task = {   action: "testA",   description: "desc1",   state: "123",   result: "updateResult" };  //更新数据 const result = await db   .update("task", task)   .where("id", 1)   .execute();  //更新数据，支持增加字段 const result = await db   .update("task")   .column("action", "test-id22")   .column("create_time", db.literals.now)   .where('id', 2)   .execute();

5. 构造删除

//删除id为1的数据 const result = await db   .delete("task")   .where("id", 1)   .execute();

6. 事务控制

const trans = await db.useTransaction();  try {   // 数据库操作   // await trans.insert(...)   // await trans.update(...)   await trans.commit(); } catch (e) {   await trans.rollback(); }

7. 复杂条件查询设计

7.1 查询条件所有参数说明

// 查询条件所有参数 const result = await db   .where(field, value, operator, ignore, join) // 支持的所有参数   .where({field, value, operator, ignore, join}) //支持对象参数   .queryList();    // 复杂查询条件 const result = await db   .select("*")   .from("page")   .where("id", 100, "gt") // id > 100   .where("tags", "test", "like") //name like '%test%'   .where("tech", tech, "eq", "ifHave") // tech='tech_value' 当 tech 为空时，不做为查询条件   .where("tags", tags, "findinset", "ifHave", "or")   .queryList();

• field 字段名
• value 传入值
• operator 操作符，默认equal4
• ignore 是否加为条件，返回false时则忽略该条件
• join 连接符号(and or)，默认为and

7.2 操作逻辑定义operator

该参数很好理解，默认值为equal，支持传字符串或传入函数，传入字符串则会匹配到已定义的逻辑，

const result = await db   .select("*")   .from("page");   .where("id", 100, "lt")  // id < 100   .where("group_code", "dacu") // group_code = "dacu"   .queryList();

大家能理解operator是为拼接查询条件使用的逻辑封装，复杂条件的拓展能力都可以靠自定义的operator来完成。其函数的形式如下：

const customOperator =  ({ field, value }) => {   if (condition) {     return {       sql: '?? = ?',       arg: [ field, value ],     };   } else {     return {       sql: '?? > ?',       arg: [ field, value ],     };    } };  // 可直接使用也可注册到全局 const config = db.config(); config.registerOperator("customOperator", customOperator);

7.3 是否加为条件ignore

这个需要解释下，当满足xx条件时则忽略该查询条件，ignore设计的初衷是为了简化代码，比如以下代码是很常见的，界面上有输入值则查询，没有输入值时不做为查询条件：

const query = db   .select("*")   .from("page");   .where("id", 100, "lt");  if (name){     query.where("name", name, 'like'); }  if (isNumber(source_id)){     query.where('source_id', source_id) }  const result = await query.queryList();

上面的代码使用ignore时则可简化为：

const result = await db   .select("*")   .from("page")   .where("id", 100, "lt")   .where("name", name, "like", "ifHave") //使用内置 ifHave，如果name为非空值时才加为条件   .where("source_id", tech, "eq", "ifNumber") //使用内置 ifNumber   .queryList();

支持传字符串或传入函数，传入字符串则会匹配到已定义的逻辑，其函数的形式如下：

const customIgnore = ({field, value}) => {     if (...){         return false;     }          return true; };  //也可以注册到全局使用 const config = db.config(); config.registerIgnore("customIgnore", customIgnore);

7.4 查询条件优先级支持

// where a = 1 and (b = 1 or c < 1) and d = 1 const result = await db.select('*')   .from('table')   .where('a', 1)   .where([     {field: 'b', value: '1', operator:'eq'},     {field: 'c', value: '1', operator:'lt', join: 'or'},   ])   .where('d', 1)   .queryList();

7.5 真实场景中的复杂查询示例

// 复杂查询，真实场景示例，项目中拓展了keyword、setinset等operator及ignore const result = await app.db   .select('a.*, b.id as fav_id, c.name as biz_name, d.group_name')   .from('rocms_page as a')   .join(`left join favorite as b on b.object_id = a.id and b.object_type = "rocms_page" and b.create_user = "${this.ctx.user.userid}"`)   .join('left join rocms_biz as c on c.biz = a.biz')   .join('left join rocms_biz_group as d on d.biz = a.biz and d.group_code = a.biz_group')   // 关键字模糊查询   .where('a.name,a.biz,a.biz_group,a.support_clients,a.owner,a.status', query.keywords, 'keywords', 'ifHasValueNotNumber') // 关键字在这些字段中模糊查询   .where('a.id', query.keywords, 'eq', 'ifNumber') // 关键字中输入了数字时当作id查询   // 精确查询   .where('a.id', query.id, 'eq', 'ifHave')   .where('a.name', query.name, 'like', 'ifHave')   .where('a.biz', query.biz, 'eq', 'ifHave')   .where('a.biz_group', query.biz_group, 'eq', 'ifHave')   .where('a.support_clients', query.support_clients, 'setinset', 'ifHave')   .where('a.status', query.status, 'insetfind', 'ifHave')   .where('a.owner', query.owner, 'eq', 'ifHave')   .where('a.offline_time', query.owner, 'eq', 'ifHave')   // TAB类型 我的页面own、我的收藏fav、所有页面all   .where('a.owner', this.ctx.user.userid, 'eq', () => query.queryType === 'own')   .where('b.id', 0, 'isnotnull', () => query.queryType === 'fav')   // 分页查询   .orderby('a.update_time desc, a.id desc')   .queryListWithPaging(query.pageIndex, query.pageSize);

4. 自定义配置

const config = db.config();  // 自定义operator config.registerOperator('ne', ({ field, value }) => {   return { sql: '?? <> ?', arg: [ field, value ] }; });  // 自定义ignore config.registerIgnore('ifNumber', ({ value }) => {   return !isNaN(Number(value)); });  // 监听事件 执行前 config.onBeforeExecute(function({ sql }) {   console.log(sql); });  // 监听事件 执行后 config.onAfterExecute(function({ sql, result }) {   console.log(result); });  // 监听事件 执行出错 config.onExecuteError(function({ sql, error }) {   console.log(error); });

5. 内置的operator及ignore

• 内置的默认operator
  • eq (equal)
  • ne (not equal)
  • in (in)
  • gt (greater than)
  • ge (greater than or equal)
  • lt (less than)
  • le (less than or equal）
  • isnull (is null)
  • isnotnull (is not null)
  • like (like)
  • startwith (start with)
  • endwith (end with)
  • between (between)
  • findinset (find_in_set(value, field))
  • insetfind (find_in_set(field, value))
  • sql (custom sql)
  • keywords (keywords query)
• 内置的默认ignore
  • ifHave (如果有值则加为条件）
  • ifNumber (如果是数值则加为条件）

三、使用示例

下面是一个在koa框架中完整的使用示例：

config.js

'use strict';  module.exports = {   port: 7001,   mysqlClient: {     mysql: { // 数据库存连接配置       // host       host: '127.0.0.1',       // 端口号       port: '3306',       // 用户名       user: 'root',       // 密码       password: 'mypassword',       // 数据库名       database: 'information_schema',     },     config: config => { // 数据库工具配置       // 自定义operator       config.registerOperator('ne', ({ field, value }) => {         return { sql: '?? <> ?', arg: [ field, value ] };       });        // 自定义ignore       config.registerIgnore('ifNumber', ({ value }) => {         return !isNaN(Number(value));       });        // 监听事件 执行前       config.onBeforeExecute(function({ sql }) {         console.log(sql);       });        // 监听事件 执行后       config.onAfterExecute(function({ sql, result }) {         console.log(result);       });        // 监听事件 执行出错       config.onExecuteError(function({ sql, error }) {         console.log(error);       });     },   }, };

app.js

'use strict';  const Koa = require('koa'); const app = module.exports = new Koa();  // 加载控制器 const HomeController = require('./app/controller/home')(app); app.controller = {   home: new HomeController(), };  // 加载服务 const FooService = require('./app/service/foo')(app); const BarService = require('./app/service/bar')(app); app.service = {   foo: new FooService(),   bar: new BarService(), };  // 初始化路由 app.router = require('./app/router')(app); app.use(app.router.routes());  // 获取配置信息 const config = app.config = require('./config'); const { mysqlClient, port } = config;  // 初始化数据库 const DbClient = require('ali-mysql-client'); app.db = new DbClient(mysqlClient);  // 启动服务 if (!module.parent) {   app.listen(port);   console.log('$ open http://127.0.0.1:' + port); }

router.js

'use strict';  const Router = require('koa-router');  module.exports = app => {   const router = new Router();   router.get('/', app.controller.home.index);   router.get('/foo', app.controller.home.foo);    return router; };

controller/home.js

'use strict';  module.exports = app => {   class HomeController {     async index(ctx, next) {       const result = await app.service.foo.getDetail();       ctx.body = 'tables表信息' + JSON.stringify(result);     }      async foo(ctx, next) {       const result = await app.service.foo.getCount();       ctx.body = '表数量：' + result;     }   }    return HomeController; };

service/foo.js

'use strict';  module.exports = app => {   class FooService {        async getDetail() {       const result = await app.db         .select('*')         .from('tables')         .where('table_name', 'tables')         .queryRow();        return result;     }      async getCount() {       const result = await app.db         .select('count(*)')         .from('tables')         .queryValue();        return result;     }   }    return FooService; };

更多示例

• demo-egg
• demo-koa
• demo-express

在eggjs中可以和egg-mysql一起使用，初始化时支持直接传入egg-mysql或ali-rds对象，避免重复创建连接池。

// this.app.mysql 为egg-mysql对象 const db = new DbClient(this.app.mysql)

四、项目开源

该项目最初是在自己项目内部使用只发布在内网中，最近整理去除了内网依赖开源到github上，ali-mysql-client：https://github.com/liuhuisheng/ali-mysql-client

该类库旨在为nodejs访问mysql数据库提供强大流畅的api，目标是希望访问数据库逻辑都能使用一行代码完成，让访问数据库变得更加简单优雅。有任何意见或建议欢迎大家可以随时反馈给我。