開源一個阿里內部的nodejs訪問mysql類庫

蕭秦發表於2019-07-22
阿里NodeJSMySql

一、背景問題

自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;
};

更多示例

在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，目標是希望訪問資料庫邏輯都能使用一行程式碼完成，讓訪問資料庫變得更加簡單優雅。有任何意見或建議歡迎大家可以隨時反饋給我。

相關文章