一、前言

nodejs诞生以来出现了一大批的web框架如express koa2 egg等等，前端可以不再依赖后端可以自己控制服务端的逻辑。今天我们就来说说前端在nodejs中如何操作mysql数据库。

二、操作数据库

直接使用mysqljs，比如查询一个字段，代码逻辑看起来是很清晰的，但是仅查询一个字段就需要这么多代码实在是过于麻烦：

var mysql      = require('mysql');
var connection = mysql.createConnection(mysqlConfig);

connection.connect();

connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

connection.end();

一些框架都提供了一些自己的接口去简化CRUD操作，比如egg中提供了egg-mysql:

const results = yield app.mysql.select('posts',{
  where: { status: 'draft' },
  orders: [['created_at','desc'], ['id','desc']],
  limit: 10,
  offset: 0
});

简单查询条件场景可以解决，但是我们的真实场景的查询条件中各种表关联、各种字段like、in、findinset拼接条件、各种子查询等等操作都满足不了，必须要自己写SQL文。

例如自己写SQL去实现一个服务端分页，实现起来也是比较麻烦的：

// 拼接各种条件
let whereSql = 'where online_version is not null and state <> 1';
if (scope == 'only') {
  whereSql += ' and use_scope like "%' + query.use_scope + '%"';
}
whereSql += handleIn(query) + handleEqual(query) + handleLike(query);

// 取得全部数据条数
const sqlTotal = 'select count(*) as total from component' + whereSql;
const resultTotal = yield this.app.mysql.query(sqlTotal, values);

// 取得当前页数据
let sqlSelect = 'select * from component'
sqlSelect += whereSql;
sqlSelect += ' order by modified_time desc, id desc limit ';
sqlSelect += (pageIndex - 1) * pageSize + ',' + pageSize;
const resultList = yield this.app.mysql.query(sqlSelect, values);

// 返回分页结果
const result = {
  list: resultList,
  total: resultTotal[0].total,
};
return result;

那有没有更简洁的方法去操作数据库呢，答案是肯定的社区有很多优秀的orm或sql builder的类库比如objection、sequelize、knexjs、squel等。

三、工具介绍

但在这里要向大家介绍一我们自己的内部的一个更加简洁易用的的nodejs操作mysql的工具类库 ali-mysql-client 它是一个sql builder思路的实现的工具，无需你额外再去定义数据模型更加轻量简洁。

先看一个查询示例，是不是看起来简洁易懂：

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

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

下面介绍下它的一些特点：

1. SQL Builder能力

提供了select insert update delete的强大的SQL Builder能力

// 构造查询 
const query = db
  .select("a.a1, b.b1, count(a.c) as count")
  .from("table as a")
  .join("table2 as b")
  .where("a.date", db.literals.now, "lt") // date < now()
  .where("a.creator", "huisheng.lhs")     // creator = 'huisheng.lhs"
  .groupby("a.a1, b.b1")
  .having("count(a.category) > 10")
  .orderby("a.id desc");
  
// 构造插入
const tasks = [ task1, taks2, task3 ];
const insert = db
  .insert("task", tasks)
  .column('create_time', db.literals.now)  // 循环赋值给每一行数据
  .column('create_user', 'huisheng.lhs');
  
// 构造更新
const update = db
  .update("task", task)
  .column("create_time", db.literals.now) //支持增加字段
  .where('id', 2)
  
// 构造删除
const delet = db
  .delete("task")
  .where("id", 1)

2. 丰富的Command

提供了丰富的数据库command更方便的访问数据库

// 查询command
const select = builderSelect();

// 查询一个字段值 value
const result1 = await select.queryValue();

// 查询单行数据 {id:12, name: '测试页面', ....}
const result2 = await select.queryRow();

// 查询数据列表 [{...}, {...}];
const result3 = await select.queryList();

// 服务端分页查询 {total: 100, rows:[{...}, {...}]};
const result4 = await select.queryListWithPaging();

// 执行插入更新删除
const result5 = await insert.execute();
const result6 = await update.execute();
const result7 = await delete.execute();

// 也支持直接传入sql
const result8 = await db.sql(sql, values);

3. 条件封装拓展

const result = await db
  .select("*")
  .from("page")
  .where("id", 100) // id = 100
  .where("name", 'test', "like") // name like '%test%'
  .queryList();

这里的第三个参数operator就是我们封装的条件逻辑，可传入字符串或函数，不传时默认是equal，

在类库中内置了以下操作符:

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)

支持自己拓展：

const config = db.config();
// 自定义operator
config.registerOperator('ne', ({ field, value }) => {
  return { sql: '?? <> ?', arg: [ field, value ] };
});

4. 动态条件

这个是我们根据我们自己的经验设计的一个参数，在社区目前还没看到过类似的，它的作用主要是用来简化代码，也就是当满足xx条件时则忽略该查询条件，设计的初衷是为了简化代码，比如以下代码是很常见的，界面上有输入值则查询，没有输入值时不做为查询条件

比如界面上有输入值时则当作查询条件，这是很常见的

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('ifNumber', ({ value }) => {
  return !isNaN(Number(value));
});

5. 事件支持

当然我们开发时需要查问题看看为什么查询出来的数据不对，所以支持了一些事件，在这些事件中你可以记录你的sql日志或做一些其它的事件

const config = db.config();

// 监听事件 执行前
config.onBeforeExecute(function({ sql }) {
  console.log(sql);
});

// 监听事件 执行后
config.onAfterExecute(function({ sql, result }) {
  console.log(result);
});

// 监听事件 执行出错
config.onExecuteError(function({ sql, error }) {
  console.log(error);
});

四、使用示例

在koa框架中完整的使用示例：

├── app

│ ├── controller

│ │ └── home.js

│ ├── router.js

│ └── service

│ ├── bar.js

│ └── foo.js

├── app.js

├── config.js

└── package.json

配置文件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 = '表信息' + 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;
};

更多示例

四、开源地址

ali-mysql-client 已经开源到了 github 上，目标是为nodejs访问mysql数据库提供强大流畅的api的工具类库，希望访问数据库逻辑都能使用一行代码完成，让访问数据库变得更加简单优雅，大家使用有问题欢迎大家在 github 反馈讨论。