Node.jsでmysql2を扱う時の備忘録

## 冒頭の挨拶

MySQLをNode.jsで扱うライブラリはいくつか存在しており、
強力なORMを扱うようなライブラリも出てきています。

今回はシンプルな機能だけ提供している[mysql2](https://www.npmjs.com/package/mysql2)の取り扱い方の説明・備忘録です。

※ですます口調おわり

## ベストプラクティス

- Promise版を使う
- namedPlaceholders機能を有効にする

今どきNode.jsでPromise版を扱わないなんて信じられないが、
mysql2ライブラリのドキュメントの大半がコールバックで、まともにドキュメントとして機能していない

後者はmysql2で積極的に使うべき機能だが、
隠し機能みたいなレベルで具体的な使い方が載ってない
これを備忘録として残す為に記事を書いてるまである

### requireの仕方

関数の作り自体が違うので `mysql2/promise` という風に別ディレクトリを指定する

```node.js
const mysql = require("mysql2/promise");
```

## コネクションの張り方

`mysql.createPool`でプールを用意する

```node.js
const pool = mysql.createPool({
  host: "localhost",
  user: "root",
  password: "password",
  database: "database",
  connectionLimit: 3, // 接続を張り続けるコネクション数を指定
  namedPlaceholders: true, // 設定必須
});
```

`namedPlaceholders`はコネクション作る時のオプションとして指定
使い方の解説は後ほど

`mysql.createConnection`を使っても接続は作れるが
Node.jsらしくやるならプールを用意した方が良い
実運用でどちらか選択出来るならプール一択だし、`createConnection`が存在する意味がよくわからんまである

参考記事: [MySQL Connection Pooling と Persistent Connections はチョット違うという話 | mita2 database life](https://mita2db.hateblo.jp/entry/2020/08/02/162024)

---

この時に意味わからん仕様があって
`mysql.createConnection`で接続を準備する場合はawaitで待つ必要があるが……

> ```node.js
> const c = await mysql.createConnection({
>   port: 3306,
>   user: 'testuser',
>   namedPlaceholders: true,
>   password: 'testpassword'
> });
> ```

https://github.com/sidorares/node-mysql2/blob/fa47d0b0c46b3f5e79b7adae283bb78575298429/examples/promise-co-await/await.js#L6-L11

`mysql.createPool`で接続を準備する場合はawaitで待つ必要がない
お前予め複数接続張って待つのちゃうんか？

> ```node.js
> const pool = mysql.createPool({
>   port: 3306,
>   user: 'testuser',
>   namedPlaceholders: true,
>   password: 'testpassword'
> });
> ```

https://github.com/sidorares/node-mysql2/blob/fa47d0b0c46b3f5e79b7adae283bb78575298429/examples/promise-co-await/await.js#L40-L45

## クエリの発行

poolにqueryというメソッドが生えているので、
queryメソッドを使うだけで、勝手にプールからコネクションを1個借りてきてSQL文を発行、コネクションのリリースまでを受け持ってくれる便利

基本的な使い方はこれ

```node.js
// await構文を使うのでasync関数を定義した
const main = async () => {
  const [resultRows] = await pool.query(
    "SELECT * FROM users WHERE id = :id",
    {id: 123}
  );
  const user = resultRows[0];
  user.name = "Jiro";
  await pool.query(
    "UPDATE USERS SET :datum WHERE id = :id",
    {datum: user, id: user.id}
  )
}
main();
```

[.queryと.executeは何が違うねん](https://github.com/sidorares/node-mysql2/issues/553)という質問が乱立する程度には同じような動作をするので、
本記事は全て`pool.query`だし個人で使う時も全て`query`、使い分けなんていらん

上の例的なコードではqueryメソッドの第一引数で`?`ではなく`:id`を指定していたり、
第二引数が配列じゃなくてオブジェクトになっている
これはドキュメントやサンプルコードを読む限り全く違う使い方しているので到底動くとは思えない

https://www.npmjs.com/package/mysql2#first-query

### namedPlaceholders

mysql2にはnamedPlaceholdersという機能が存在する
これをONにすると、SQL文を発行する時のプレースホルダーが?や??から、オブジェクトとキー名の指定に切り替わる

特に何も指定しない場合

```node.js
  const [resultRows] = await pool.query(
    "SELECT * FROM users WHERE id = ?",
    [123]
  );
```

namedPlaceholdersを有効化した場合

```node.js
  const [resultRows] = await pool.query(
    "SELECT * FROM users WHERE id = :id",
    {id: 123}
  );
```

PHP知ってるならPDOも似たような書き方で懐かしくなるのではないだろうか？
`:id`みたいな書き方で秀逸、なんで最初からこうなってなかったんだよと思わずにはいられない
まぁNode.jsのmysqlライブラリ上でも使えるようになるのは素直に嬉しい

---

問題はここからで、この1枚ぺらのドキュメントしかない
https://github.com/sidorares/node-mysql2/blob/master/documentation/en/Extras.md

> `connection.config.namedPlaceholders = true;`

Promise版はこれを実行するとエラー、嘘だろ？
「mysql2 promise namedPlaceholders」でググったら2件出てきた
そりゃみんな備忘録にして残すわ、有能な人が居てくれて死なずに済んだ

https://qiita.com/Appseed246/items/49e2c47470e77206f806

https://tech-it.r-net.info/program/nodejs/306/

SET時のオブジェクト、元`??`のカラム名等への対応、二次元配列でのバルクインサート対応、これらも全て出来るらしい
有能過ぎないか？

有能だから乗り換える、そういう話

### Insert

`SET :datum`という書き方をして、datumキーをオブジェクトにすることで自動的に値を補完してくれる

```node.js
const sql = `
  INSERT INTO users
  SET :datum
`;
const datum = {
  name: "taro",
  age: 18,
  email: "taro@example.com",
};
await pool.query(sql, {datum});
```

### Update

メールアドレスがプライマリーキー想定

```node.js
const sql = `
  UPDATE users
  SET :datum
  WHERE email = :email
`;
const datum = {
  name: "taro",
  age: 18,
  email: "taro@example.com"
};
const {mail} = datum;
await pool.query(sql, {datum, email});
```

### Upsert

※データ重複でUPDATEを実行した場合でも、INSERT文が成功扱いとなりオートインクリメント値が進むのに注意

プライマリーキーが重複している場合はUpdateになる
都合の良いInsertがUpsert
mysqlの場合はInsert文の末尾に`ON DUPLICATE KEY UPDATE`をつける事で実現させる

https://dev.mysql.com/doc/refman/8.0/ja/insert-on-duplicate.html

> 注記:
> 新しい行とカラムを参照するための VALUES() の使用は、MySQL 8.0.20 以降非推奨になり、将来のバージョンの MySQL で削除される予定です。

元々`VALUES()`を使っていたらしいが、MySQL8流儀によると既に非推奨であり削除予定とのこと

> MySQL 8.0.19 以降では、VALUES または SET 句の後に AS キーワードを付けて、挿入する行のエイリアス (オプションでそのカラムの 1 つ以上) を使用できます。 行エイリアス new を使用すると、以前に VALUES() を使用して新しいカラム値にアクセスしていたステートメントを、次に示す形式で記述できます:

とのことで、VALUESやSETの後にASキーワードを付けて行エイリアスを使うのがベストプラクティスとなる
上記ドキュメントによるとカラムエイリアスというものも用意されているが、ただただ使いづらそうなので忘れて良い

メールアドレスがプライマリーキー想定

```node.js
const sql = `
  INSERT INTO users
  SET :datum AS new
  ON DUPLICATE KEY UPDATE
    name = new.name,
    age = new.age
`;
const datum = {
  name: "taro",
  age: 18,
  email: "taro@example.com"
};
await pool.query(sql, {datum});
```

### Bulk-insert

ドキュメントによると二次元配列は行けるらしいので
二次元配列を突っ込んで動作確認をおこなったところいけるようだ

VALUESの箇所で展開してくれる

```node.js
const sql = `
  INSERT INTO users (name, age, email)
  VALUES :rows
`;
const data = [
  {name: "taro", age: 18, email: "taro@example.com"},
  {name: "jiro", age: 17, email: "jiro@example.com"},
];
const rows = data.map(({name, age, email}) => [name, age, email]);
await pool.query(sql, {rows});
```

### Bulk-upsert

※データ重複でUPDATEを実行した場合でも、INSERT文が成功扱いとなりオートインクリメント値が進むのに注意

複数行のバルクアップサートは果たして出来るものか？
調べると可能であるようだ

こちらも行エイリアスがあるので少ない労力で簡単に書ける

```node.js
const sql = `
  INSERT INTO users (name, age, email)
  VALUES :rows AS new
  ON DUPLICATE KEY UPDATE
    name = new.name,
    age = new.age
`;
const data = [
  {name: "taro", age: 18, email: "taro@example.com"},
  {name: "jiro", age: 17, email: "jiro@example.com"},
];
const rows = data.map(({name, age, email}) => [name, age, email]);
await pool.query(sql, {rows});
```

### Select

文字列や数値も普通に展開してくれる

```node.js
const sql = `
  SELECT *
  FROM users
  WHERE email = :email
`;
const [rows] = await pool.query(sql, {email: "taro@example.com"});
console.log(rows);
```

await越しの返り値は必ず配列になるのが直感的ではないので注意

1. 検索結果
2. フィールド情報

フィールド情報はTypeScriptとかで型とか気にするなら使っても良いと思うけど
まぁ要らんと思うので、`const [rows] = await pool.query()`という感じで結果だけ受け取るのが分かりやすい

## おまけ

### 歴史的背景周りの話

mysql2はこの記事を書いている2023/10/29現在でもメンテされ続けている強力なライブラリです。
Node.jsというPC上で動作するスクリプト言語が登場した後、MySQLを扱う需要は多かったのでしょう。
すぐmysqlやnode-mysql、mysql-promise等のライブラリが乱立しました。

その後ECMAScriptのES2015、ES2016、ES2017という進化の流れで
Promiseやasync/await構文の追加により
mysqlのライブラリの作者はmysqlライブラリを捨て、別のライブラリの作者を巻き込んでmysql2ライブラリの開発を始めます。

この辺の歴史的背景があり
mysql2は基本的にはmysqlライブラリで使ってたコードのrequire先の手直しのみで動作しますし、
mysqlライブラリは既に6年以上メンテされておらずMySQL8には非対応だったりします。

まだmysql2ではなくmysqlをインストールさせようとする記事がメンテされずにしぶとく生き残ってますし、
mysql2のGitHubのドキュメントもmysql互換の記述が多く、Promise時代のベストプラクティスも無し。
Examplesも不足し過ぎでしょというレベルなので、ベストプラクティスだけを載せておこうと思って筆（？）を取りました。