knex.jsで発生した問題と対処方法（Invalid columnエラー、returningで取得できない）

• facebook

オフィス狛 技術部のJoeです。

弊社ではknex.jsを一部のプロジェクトで使用しているのですが、
これまで開発中にハマってしまった事象と対処方法を２点ご紹介したいと思います。

【環境】
・node.js：12.14.0
・knex.js：0.15.2
・SQL Server 2017

１．「Invalid column name」エラー

内部結合でand条件を追加するため、マニュアルを参考に下記のように記述したのですが、エラーが発生しました。

  const number = '1234';
  return await knex
    .select('id')
    .from('tbl_1')
    .innerJoin('tbl_2', function cond() {
      this.on('tbl_2.id', 'tbl_1.id');
      this.andOn('tbl_2.number', number);
    });

エラー内容）

Invalid column name '1234'.

function内で変数を使用すると、カラム名として扱われてしまいまいた。
対処として、プレースホルダを使用すると、値として正しく扱うことができました。

対処方法：プレースホルダを使用する）

  const number = '1234';
  return await knex
    .select('id')
    .from('tbl_1')
    .innerJoin('tbl_2', function cond() {
      this.on('tbl_2.id', 'tbl_1.id');
      this.andOn(knex.raw('tbl_2.number = ?', [number]),
      );
    });

２．insertした値を「returning」で取得できない

「returning」を使うとinsertした行の、指定したカラムの値を取得することができます。
（マニュアルを見ると、PostgreSQL、MSSQL、およびOracleで使用できるようです）

通常は以下のように記述します。この場合、insertしたデータのidの値「2」を取得できます。
（insertする値を取得するというちょっと意味のないことをしていますが、本来はオートインクリメントの値などを取得したい時などに使用します）

  return await knex
    .insert({ id: 2, memo: 'メモ' })
    .into('memo_tbl')
    .returning('id');

次に、insert句にknex.rawを使用して記述してみると、値が取得できませんでした。

ＮＧ：値が取得できない）

  return await knex
    .insert(knex.raw("(id, memo) VALUES (2, 'メモ')"))
    .into('memo_tbl')
    .returning('id');

対処として、returningは使用せず、SQL Serverで同じく操作した行の値を取得するOUTPUT句をそのまま記述することで値を取得することができました。
（OUTPUT句はSQL Serverで使用可能です。他のデータベースは異なります）

対処方法：OUTPUT句をそのまま記述する）

  return await knex
    .insert(knex.raw("(id, memo) OUTPUT INSERTED.id VALUES (2, 'メモ')"))
    .into('memo_tbl');

今回ご紹介した事象のように、一見すると正しい記述のようですが、
思わぬところで期待通りに動作しないことがありますので、ご注意ください。


knex.js , Node.js