【Node.js + PostgreSQL】特殊なクエリ記法

悩んでいたこと

• IN句で複数の条件を指定したい
• INSERT文で複数レコード登録したい

これらはただSQL文を書くのは簡単ですが、プレースホルダーを使った上での書き方が難しく、日本語の情報が全くなかったので、まとめました。
上記以外に使えそうな記法もまとめてあります。

前提

pg-promiseパッケージを使用し、databaseとの接続などはできていることとします。
載せているサンプルコードではanyメソッドを使っていますが、非推奨なので、oneやmanyを適宜使いましょう。

普通のSELECT文

とりあえず、例としてただのSELECT文

const userName = '田中';
const query = 'SELECT * FROM user WHERE name = $1';
const res = await db.any(query, [userName]);

// SELECT * FROM user WHERE name = '田中'

クエリ内の"$1"がanyメソッドの第二引数の値で置き換わる感じです。

IN句に複数条件

const userName = ['田中', '山田'];
const query = 'SELECT * FROM user WHERE name IN ($1:csv)';
const res = await db.any(query, [userName]);

// SELECT * FROM user WHERE name IN ('田中', '山田')

":csv"というフォーマットを指定すると、配列がカンマ区切りで展開されます。

const userName = ['田中', '山田'];
const age = 20;
const query = 'SELECT * FROM user WHERE name IN ($1:csv) AND age = $2';
const res = await db.any(query, [userName, age]);

// SELECT * FROM user WHERE name IN ('田中', '山田') AND age = 20

他にもプレースホルダーを使いたい場合もそのまま書けます。

複数レコードINSERT

const userName = ['田中', '山田'];
const query = pgp.helpers.insert(userName, ['name'], 'user');
const res = await db.any(query);

// INSERT INTO user(name) VALUES ('田中'), ('山田')

pgpヘルパーを利用して解決しました。

以下のような書き方もできます。
ColumnSetインスタンスは再利用可能であり、パフォーマンス面からも推奨されます。

const userName = ['田中', '山田'];
const cs = new pgp.helpers.ColumnSet(['name'], {table: 'user'});
const query = pgp.helpers.insert(userName, cs);
const res = await db.any(query);

// INSERT INTO user(name) VALUES ('田中'), ('山田')

通常、カラムは複数あることが多いと思います。
その場合は、オブジェクトの配列にします。

const userInfo = [{name: '田中', age: 24}, {name: '山田', age: 26}];
const cs = new pgp.helpers.ColumnSet(['name', 'age']);
const query = pgp.helpers.insert(userInfo, cs, 'user');
const res = await db.any(query);

// INSERT INTO user(name, age) VALUES ('田中', 24), ('山田', 26)

テーブル名はColumnSetではなく、insert関数に直接指定することもできます。

INSERTしたレコードのIDを取得する

userテーブルには自動採番されるid列があるとします。

const userName = ['田中', '山田'];
const query = pgp.helpers.insert(userName, ['name'], 'user') + 'RETURNING id';
const res = await db.any(query);

// INSERT INTO user(name) VALUES ('田中'), ('山田')

resには[{id: '1'}, {id: '2'}]が入っています。

mapメソッドを使えばもっと便利なことができます。

const userName = ['田中', '山田'];
const query = pgp.helpers.insert(userName, ['name'], 'user') + 'RETURNING id';
const res = await db.map(query, [], r => +r.id);

resには[1, 2]が入っています。
返ってくる値は文字型なので、'+'を使って整数に変換しています。

置換のために指定している配列を連想配列で指定したい(追記：6/17)

const res = await db.any(query, [userName]);でいう、'userName'の部分のことです。

const param = {
    "userName": "田中",
    "country": "日本"
};
const query = 'SELECT * FROM user WHERE name = $/userName/ AND country = $/country/';
const res = await db.any(query, param);

// SELECT * FROM user WHERE name = '田中' AND country = '日本'

//で括ったクエリ内のパラメータが連想配列のキーと紐づきます。
//以外にも<>, [], {}, ()が使えます。

SELECT句やFROM句を可変にしたい(追記：6/17)

今まで紹介した方法だとSELECT句とFROM句での置換はうまくいきません。
SELECT句、FROM句では:nameフォーマットを使用します。
以下で紹介している例はすべて同じ結果になります。

➀

const query = 'SELECT $1:name, $2:name FROM $3:name';
const res = await db.any(query, ['name', 'age', 'user']);

// SELECT name, age FROM user

➁

const param = {
    'column1': 'name',
    'column2': 'age',
    'table': 'user'
};
const query = 'SELECT $/column1:name/, $/column2:name/ FROM $/table:name/';
const res = await db.any(query, param);

// SELECT name, age FROM user

➂

const query = 'SELECT $1:name FROM $2:name';
const res = await db.any(query, [['name', 'age'], 'user']);

// SELECT name, age FROM user

➃

const param = {
    'column': ['name', 'age'],
    'table': 'user'
};
const query = 'SELECT $/column:name/ FROM $/table:name/';
const res = await db.any(query, param);

// SELECT name, age FROM user

➂、➃のようにカラムはカラムでまとめると、数も可変にできるので、おすすめです。

複数レコードUPDATE(追記：10/13)

UPDATEは少し難しいです。
name列をキーにして、age列を更新するとします。

const userInfo = [{name: '田中', age: 34}, {name: '山田', age: 36}];
const cs = new pgp.helpers.ColumnSet(['?name', 'age'], {table: 'user'});
const query = pgp.helpers.update(userInfo, cs) + ' WHERE v.name = t.name';
const res = await db.any(query);

// UPDATE user AS t SET age=v.age FROM (VALUES('田中',34),('山田',36)) AS v(name,age) WHERE v.name = t.name

name列の '?' はその列がWHERE句によって条件として使用される（更新されない）ということを意味します。
'v' と 't' はそれぞれ元テーブル、更新する値のデフォルトのエイリアスになります。

まとめ

他にもいろいろ便利なものがありそうですが、とりあえず困っていることは解決できたので、ここまでにします。

参考

pg-promise公式ヘルパーページ
pg-promise公式フォーマットページ
Multi-row insert with pg-promise