一、背景问题
自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,目标是希望访问数据库逻辑都能使用一行代码完成,让访问数据库变得更加简单优雅。有任何意见或建议欢迎大家可以随时反馈给我。