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

冒頭の挨拶

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

今回はシンプルな機能だけ提供している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

---

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

node.js

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

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

node.js

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

クエリの発行

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は何が違うねんという質問が乱立する程度には同じような動作をするので、
本記事は全てpool.queryだし個人で使う時も全てquery、使い分けなんていらん

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

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件出てきた
そりゃみんな備忘録にして残すわ、有能な人が居てくれて死なずに済んだ

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

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

Insert

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

node.js

const sql = `
  INSERT INTO users
  SET :datum
`;
const datum = {
  name: "taro",
  age: 18,
  email: "[e-mail]",
};
await pool.query(sql, {datum});

Update

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

node.js

const sql = `
  UPDATE users
  SET :datum
  WHERE email = :email
`;
const datum = {
  name: "taro",
  age: 18,
  email: "[e-mail]"
};
const {mail} = datum;
await pool.query(sql, {datum, email});

Upsert

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

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

注記:
新しい行とカラムを参照するための 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: "[e-mail]"
};
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: "[e-mail]"},
  {name: "jiro", age: 17, email: "[e-mail]"},
];
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: "[e-mail]"},
  {name: "jiro", age: 17, email: "[e-mail]"},
];
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: "[e-mail]"});
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も不足し過ぎでしょというレベルなので、ベストプラクティスだけを載せておこうと思って筆（？）を取りました。