mysql2 - Node.js

mysql2 とは

データベースを操作するためのパッケージ

インストール

npm install mysql2

node-mysql2 - GitHub

基本的な使い方

const mysql = require('mysql2');

const mysql_conf = {
    host : 'localhost',
    user : 'root',
    password : 'password',
    database: 'sample'
};

const connection = mysql.createConnection(mysql_conf);

connection.query('SELECT * FROM users', function(err, results, fields){
    console.log(results);
});

connection.end();

複数のカラムをオブジェクトで指定する

const mysql = require('mysql2');

const mysql_conf = {
    host : 'localhost',
    user : 'root',
    password : 'password',
    database: 'sample'
};

let data = {name:"Taro", age:20};

const connection = mysql.createConnection(mysql_conf);

connection.query('UPDATE users SET ? WHERE id = 1', [data],  function(err, results, fields){
    console.log(results);
});

connection.end();

フィールド名にもプレイスホルダーを使う

execute では利用できません。

const mysql = require('mysql2');

const mysql_conf = {
    host : 'localhost',
    user : 'root',
    password : 'password',
    database: 'sample'
};

const connection = mysql.createConnection(mysql_conf);

connection.query('SELECT ?? FROM ?? WHERE id = ?', ['name', 'users', 1], (err, result) =>  {
  console.log(result)
});
connection.end();

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

複数挿入した場合は一番最後の ID が返ります。

const mysql = require('mysql2');

const mysql_conf = {
    host : 'localhost',
    user : 'root',
    password : 'password',
    database: 'sample'
};

const connection = mysql.createConnection(mysql_conf);

connection.execute("INSERT INTO users SET name = ? , age = ?", ["太郎", 35], (err, result) =>  {
  console.log("insert id :" + result["insertId"])
});
connection.end();

複数のクエリを実行する

複数のクエリを実行するには、オプションに「 multipleStatements: true 」を設定します。

multipleStatements オプションは、execute() では利用できません。

const mysql = require('mysql2');

const mysql_conf = {
    host : 'localhost',
    user : 'root',
    password : 'password',
    database: 'sample',
    multipleStatements: true
};

const connection = mysql.createConnection(mysql_conf);

connection.query('SELECT * FROM users;SELECT * FROM fruits', function(err, results, fields){
    console.log(results);
});

connection.end();

[
  [ { id: 1, name: 'Taro', age: 28 }, { id: 2, name: '花子', age: 18 } ],
  [ { id: 1, name: 'りんご', price: 100 } ]
]

プリペアドステートメントを使う

プリペアドステートメントは、SQL 文を予め解析しておいて、後は変数をセットするだけで実行出来るようにしておくテンプレートのようなものです。

プレースホルダを使って指定しておけば、その部分はあくまで「値」として処理され、万が一不正な値が入力されてもエスケープ処理されます。

mysql2 では、execute() を使うことで自動的に prepared statement になります。

Node.jsのMySQLパッケージにおけるエスケープ処理だけでは防げない「隠れた」SQLインジェクション

const mysql = require('mysql2');

const mysql_conf = {
    host : 'localhost',
    user : 'root',
    password : 'password',
    database: 'sample'
};

const connection = mysql.createConnection(mysql_conf);

connection.execute('SELECT * FROM users WHERE name = ? AND age = ?', ["Taro", 28], function(err, results, fields){
    console.log(results);
});

connection.end();

createConnection() と createPool() の違い

createConnection() と createPool() は、どちらもMySQLへの接続を作成するためのメソッドです。

createConnection()

クエリ実行後は connection.end() で接続を閉じる必要がある
短期間のスクリプトや単発のクエリに適している
query() を実行するたびに新しい接続が確立されるため、頻繁にクエリを実行するとオーバーヘッドが発生する

メリット

シンプルにデータベースへ接続・クエリ実行ができる
短期間のスクリプトやテストに最適

デメリット

クエリごとに新しい接続を作るため、複数のリクエストが発生すると非効率
多くの接続を作成するとデータベースの負荷が増える

createPool()

接続をプール（複数の接続を保持するリスト）として管理する
クエリ実行後は接続を閉じるのではなく再利用できる (getConnection() を使えば手動で接続を取得可能)
多数のリクエストに対して効率よく接続を分配できるため、高負荷のアプリケーションに向いている

メリット

接続をプールすることで効率的な管理ができる
高負荷時でも接続数を制限しながらリクエストを処理できる
query() を使えばプール接続を取得・解放する手間が不要

デメリット

短期間のスクリプトやテストではオーバーヘッドが大きい
connection.release() を忘れると、プール接続が枯渇しアプリがクラッシュする可能性がある

Sample : createConnection

const mysql = require('mysql2/promise');

const runQuery = async () => {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydb'
  });

  const [rows] = await connection.query('SELECT * FROM users');
  console.log(rows);

  await connection.end(); // 接続を閉じる
};

runQuery();

Sample : createPool

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  waitForConnections: true,
  connectionLimit: 10, // 最大接続数
  queueLimit: 0
});

// クエリを実行する関数
const getUsers = async () => {
  const [rows] = await pool.query('SELECT * FROM users');
  return rows;
};

getUsers().then(users => console.log(users));

query() と getConnection() の違い

query() と getConnection() は、どちらも MySQL データベースとの通信に使用されますが、用途や動作が異なります。

query

単純なクエリ実行用
pool.query(sql, params) を使用すると、内部的に接続を取得し、クエリを実行した後、自動で接続を解放します。
クエリのたびに接続が管理されるため、手動で管理する必要がない。

メリット

シンプルにクエリを実行できる
接続管理を気にせずに済む

デメリット

トランザクション処理には向かない
複数のクエリを連続で実行する場合、毎回新しい接続を取得するので効率が悪い

getConnection

明示的に接続を取得して管理する。
pool.getConnection() を使用すると、データベースとの接続を手動で確保し、クエリ実行後に connection.release() で解放する必要がある。
トランザクションの管理や複数クエリをまとめて処理するときに便利。

メリット

複数のクエリを同じ接続で処理できる
トランザクションを使って安全にデータを更新できる

デメリット

connection.release() を忘れると接続が枯渇する
単発のクエリには向かない（query() の方が簡単）

Sample : query

const [rows] = await pool.query('SELECT * FROM users WHERE id = ?', [userId]);
console.log(rows);

Sample : getConnection

const connection = await pool.getConnection();
try {
  await connection.beginTransaction();
  
  await connection.query('UPDATE users SET name = ? WHERE id = ?', ['New Name', userId]);
  await connection.query('INSERT INTO logs (message) VALUES (?)', [`User ${userId} updated`]);

  await connection.commit();
  console.log('トランザクション成功');
} catch (error) {
  await connection.rollback();
  console.error('エラー発生、ロールバックしました:', error);
} finally {
  connection.release(); // 接続を解放
}

まとめると以下のような内容になります。

query()	単発のクエリ実行（シンプルなデータ取得・更新）
getConnection()	手動で接続を管理（トランザクション・複数クエリの実行）

もし簡単な SELECT や UPDATE なら query() を使い、トランザクションや複数クエリをまとめて処理するなら getConnection() を使うのがベスト

connection.end() と connection.release() の違い

connection.end()（接続の終了）

データベースへの接続を完全に閉じる
end() を呼び出すと、その接続は再利用できなくなる
一度閉じた接続は、新たに createConnection() や getConnection() で作り直す必要がある

注意点

end() を使うと接続が閉じられるため、頻繁に createConnection() するのは非効率
高負荷のアプリではプール接続 (createPool()) を使う方が適している

connection.release()（接続の解放）

接続を閉じずにプールへ戻して、再利用できるようにする
createPool() を使っている場合にのみ使用可能
release() を呼び出しても接続は完全に閉じず、他のリクエストで再利用される

注意点

release() を忘れると接続が枯渇してしまい、アプリがクラッシュする可能性がある
pool.query() なら自動的に接続が管理されるので、手動で release() する必要がない

cratePool() の設定値

createPool()の設定値には、以下の表なものがあります。

設定値	内容	例
host	データベースのホスト名	'localhost'
user	MySQL のユーザー名	'root'
password	MySQL のパスワード	'password'
database	使用するデータベース名	'mydb'
port	MySQL のポート番号	3306（デフォルト）
waitForConnections	接続プールがいっぱいの場合、待機するかどうか	true
connectionLimit	プールの最大接続数	10
queueLimit	接続待機キューの最大長（0なら制限なし）	0
multipleStatements	複数のSQL文を1つのクエリで実行可能にする	true
namedPlaceholders	プレースホルダーを有効	true
charset	データベースの文字セット	'utf8mb4'
connectTimeout	接続のタイムアウト時間（ミリ秒）	10000

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  multipleStatements: true,
  namedPlaceholders: true,
  charset: 'utf8mb4',
  connectTimeout: 10000
});

module.exports = pool;

multipleStatementsを true にすると、SELECT * FROM users; SELECT * FROM orders;のように 1回のクエリで複数の処理が可能。

namedPlaceholdersは、:param 形式を使用できるようになります。query('SELECT * FROM users WHERE name = :name', { name: 'Taro' })

connectionLimitは、接続プールで同時に確保できる最大接続数を指定するものですが、高く設定しても、MySQL の max_connections を超えることはできません。MySQLではデフォルトで151に設定されています。

現在のmax_connectionsの設定値を核にするには、以下の SQL を実行します。

SHOW VARIABLES LIKE 'max_connections';

以下の表は、max_connectionsの設定値の例です。

種類	max_connections の設定値
小規模なアプリ	100 ～ 500
高負荷なアプリ	1000 ～ 5000
大規模システム	10,000 以上

MySQL の max_connectionsを変更したい場合、my.cnfで変更できます。

my.cnf

[mysqld]
max_connections=500

createConnection() の設定値

createConnection()の設定値には、以下の表なものがあります。

設定値	内容	例
host	データベースのホスト名	'localhost'
user	MySQL のユーザー名	'root'
password	MySQL のパスワード	'password'
database	使用するデータベース名	'mydb'
port	MySQL のポート番号	3306（デフォルト）
multipleStatements	複数のSQL文を1つのクエリで実行可能にする	true
namedPlaceholders	プレースホルダーを有効	true
charset	データベースの文字セット	'utf8mb4'
connectTimeout	接続のタイムアウト時間（ミリ秒）	10000
debug	デバッグモードを有効化	true