Knex.js で日付範囲指定の検索したり・バッチ処理で更新や削除したり

最近 (今年の2月ぐらいに) 興味深い記事を読みました。

クエリビルダを利用する明確な利点は存在しないという内容です。ここ3年ぐらいの間に割と聞かれるようになったやつです※。

Stop using Knex.js — Using SQL query builder is an anti-pattern
https://medium.com/@gajus/stop-using-knex-js-and-earn-30-bf410349856c

その趣旨を一言で述べると、動的に作成される必要があるクエリにのみ例外的に Knex.js を使用して、それ以外は直に SQL を書いたほうが良いというもの。

Knex.js というのは node.js において最もよく使われている SQL クエリビルダで、私も手を抜くときに使っています。

MySQL にも PostgreSQL にも SQLite3 にも (不完全ながら DB2 にも) 対応しており、SQL を知っている人にとっては直感的に使えます。

何が良いというわけではないのですけれども、無意識に自然と使用していることが多いです。 SQL を直書きするときでさえ、気がつくと knex.raw() 関数を使っています。

しかし、データベースの一部の機能が使用できない、あるいは使用できないという誤解に基づき酷評されている場面を見たことがあります。

ここからが上の記事の内容なのですが、SQL で実現可能なことをクエリビルダで実現するために二度の学習が必要となり、knex.js に詳しい人も (SQLに詳しい人に比べれば) 少ないので問題が起こった際に調べる手間が大きいことが難点です。

そして、バッチ更新のように利用頻度が高いものが、公式ドキュメントに書かれていない (いま再確認したら batchInsert は書かれていましたけど) ので、それも誤解の一因になっているのかと思いました。

Knex.js – A SQL Query Builder for Javascript / Latest Release: 0.16.3
https://knexjs.org/

ドキュメントに書かれていないので、誤解されても仕方がないですが、データの更新 (UPDATE) や削除 (DELETE) を knex.js でバッチ処理で実行することは可能です。

もとのソースコードの SQL 文の生成あたりを読んでいて、トランザクションの中にクエリ (Array objects) を入れてみたら実行できるような気がしたので、ためしに入れてみたところ… 実際に動作することを確認できました。

できる根拠を尋ねられても、もう当時の考えを覚えてないので困ってしまうのですけれども、やってみたら実行できたのですよ。

const express = require('express');
const router = express.Router();
const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: "./myname.sqlite"
  }
});

/* Connect to (MySQL|PSQL|SQLite) */
router.get('/', (req, res) => {
  let tableName = 'name';
  let query = [{
      id: 1,
      name: 'Janie Doe'
    },
    {
      id: 2,
      name: 'Richard Roe'
    },
    {
      id: 3,
      name: 'John Schmoe'
    }];
  knex.transaction(trx => {
      let built = query.map(elem => {
        return knex(tableName)
          .where('id', elem.id)
          .update(elem)
          .transacting(trx);
      });
      return Promise.all(built)
        .then(trx.commit)
        .catch(trx.rollback);
    })
    .then()
    .catch();
});
module.exports = router;

このソースコードは myname という DB に name というテーブルを作成して、 hoge とか huga とか piyo とか foo とか waldo みたいな適当な初期値を入れておけば実行できます。

削除したい場合には、update(elem) の部分を del() に書き換えると同様に実行できます。もちろん、insert() に書き換えても動きます。

実行環境は以下のとおりです。

$ uname -a
Linux Ganymede 4.15.0-50-generic #54-Ubuntu SMP Mon May 6 18:46:08 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

$ node -v
v12.2.0

$ grep -i knex package.json 
    "knex": "^0.16.7",

$ sqlite3 --version
3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d

同じように公式ドキュメントには書かれていないのですけれども、日付の範囲指定なども whereBetween() 関数の中に入りそうに見えたので、入れてしまうとやっぱり動きます。

こうすると簡単かつ柔軟に範囲を指定できるようになって便利なんですよね。

const express = require('express');
const router = express.Router();
const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: "./mylog.sqlite"
  }
});

router.get('/', (req, res) => {
  let tableName = 'access';

  const offset = 8; // UTC time offset HKT 
  //const offset = 9;  
  let currentDate = new Date();
  currentDate.setTime(currentDate.getTime() + 60 * 60 * 1000 * offset);

  let nextDate = new Date();
  nextDate.setTime(nextDate.getTime() + 60 * 60 * 1000 * offset);
  nextDate.setDate(nextDate.getDate() + 1);

  let cd = currentDate.toISOString().split('T')[0];
  let nd = nextDate.toISOString().split('T')[0];

  knex(tableName)
    .select()
    .whereBetween('date', [cd, nd])
    .orderBy('date', 'desc')
    .then(rows => {rows.forEach(elem => {console.log(elem);});})
    .catch();
});
module.exports = router;

このように knex.js には、公式には謳われていなくても、既知の関数の組み合わせで実現できることがたくさんあります。

SQL で実行できることは、何かしらの方法で実行できることが多いです。

できそうに思えたのに実行できなかったのは OrderBy() の中に Array や連想配列を入れて、複数の条件で並び替えを行うことです (Knex.js ver 0.16.7 までの話で、今後は対応されるかもしれません)。

この場合はデータベース内に VIEW を作成しておいて tableName に VIEW の名前を代入してやると思い通りの結果を得ることができます。

SELECTの中で条件分岐したい場合などにも、あらかじめ VIEW を作成しておいて tableName に VIEW の名前を代入してやると簡単です (これもドキュメントに書いてあったかどうか…) 。

でも、それは「SQL で同じことができることを経験的に知っているからではないか」と言われてしまうと、冒頭の話題を考えざるを得なくなるわけです。

SQLで直にクエリを書くことを考えると、どちらが良いのでしょうね。

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)