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

# 悩んでいたこと

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

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

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

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

```javascript
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句に複数条件

```javascript
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"というフォーマットを指定すると、配列がカンマ区切りで展開されます。

```javascript
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

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

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

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


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

```javascript
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 ('田中'), ('山田')
```


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

```javascript
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列があるとします。

```javascript
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メソッドを使えばもっと便利なことができます。

```javascript
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'の部分のことです。

```javascript
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`フォーマットを使用します。
以下で紹介している例はすべて同じ結果になります。

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

// SELECT name, age FROM user
```

```javascript:➁
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
```

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

// SELECT name, age FROM user
```

```javascript:➃
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列を更新するとします。

```javascript
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公式ヘルパーページ](https://vitaly-t.github.io/pg-promise/helpers.html)
[pg-promise公式フォーマットページ](https://vitaly-t.github.io/pg-promise/formatting.html)
[Multi-row insert with pg-promise](https://stackoverflow.com/questions/37300997/multi-row-insert-with-pg-promise)