Posted inMySQL

Node.jsでmysql2を使ったMySQL接続：Connection Pooling、Prepared Statements、Transaction

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Node.jsでMySQL接続する際の実際の問題

Node.jsでバックエンド開発を始めたばかりの頃、最初に習ったMySQL接続の方法はこんな感じでした：

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '123456',
  database: 'myapp'
});

connection.connect();

app.get('/user/:id', (req, res) => {
  const id = req.params.id;
  connection.query(`SELECT * FROM users WHERE id = ${id}`, (err, results) => {
    res.json(results);
  });
});

コードは動いて、ローカルテストも問題なし。本番にプッシュした後、顧客からWebが遅い、たまにconnectionエラーが出るという報告が来ました。さらに深刻な問題も発見：URL /user/1 OR 1=1 がデータベース内の全ユーザーデータを返してしまっていたのです。

三つの問題が同時に発生していました：SQLインジェクション、Connection Poolingなし、そしてメンテナンスが止まった古いライブラリの使用。この記事ではその三つをすべて解決します。mysql2を使用します——ネイティブのPromise/async-awaitサポートと、元のmysqlパッケージより大幅に優れたパフォーマンスを持つモダンなバージョンです。

原因の分析

なぜ単一のconnectionでは不十分なのか？

Node.jsはevent loopでリクエストを処理します——複数のリクエストが同時に来ます。MySQL connectionが一つしかない場合、リクエストは互いに順番待ちをしなければなりません。さらに悪いことに、timeoutやMySQLサーバーの再起動でそのconnectionが切れると、アプリケーション全体が即座にエラーとなり、自動回復の仕組みがありません。

SQLインジェクションはどのように発生するか？

クエリに文字列を直接連結すると：

// 危険 — 絶対にやってはいけない
const query = `SELECT * FROM users WHERE username = '${username}'`;

攻撃者がusernameに ' OR '1'='1 を入力すると、クエリはこうなります：

SELECT * FROM users WHERE username = '' OR '1'='1'

このクエリは全ユーザーを返してしまいます。さらに危険なのは '; DROP TABLE users; -- でテーブルのデータを完全に削除できることです。

なぜ古いmysqlパッケージを使わないのか？

mysqlパッケージは長年にわたって積極的にメンテナンスされていません。mysql2はネイティブのPromiseをサポートし、パフォーマンスが優れ、旧APIとの完全な互換性があり、MySQL 8.0を完全にサポートしています。

解決方法

mysql2のインストール

npm install mysql2

方法1：Connection Pooling

一つのconnectionを使い続けるのではなく、poolを作成します——準備完了状態のconnectionの集まりです。リクエストが来たらpoolからconnectionを取り出して使用し、終わったら返却します：

// db.js — アプリケーション全体で共有するモジュール
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'appuser',
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME || 'myapp',
  waitForConnections: true,
  connectionLimit: 10,   // 最大10の同時connection
  queueLimit: 0          // キュー内の待機リクエスト数は無制限
});

module.exports = pool;

私の本番データベースはMySQL 8.0で約50GBのデータで動いており、connectionLimit: 10は妥当な出発点です——各connectionはMySQLサーバー側でもRAMを消費するため、高くしすぎるべきではありません。実際のメトリクスに基づいて調整するため、SHOW STATUSでThreads_connectedをモニタリングしています。

方法2：Prepared Statements — SQLインジェクションをブロック

Prepared statementはSQLコードとデータを分離します。MySQLエンジンは最初にSQL文を受け取ってコンパイルし、その後でデータを受け取ります——データがSQLコードとして解釈されることは絶対にありません：

const pool = require('./db');

async function getUserById(id) {
  // ?はプレースホルダー、mysql2が自動的に値をエスケープする
  const [rows] = await pool.query(
    'SELECT id, username, email FROM users WHERE id = ?',
    [id]
  );
  return rows[0] || null;
}

async function searchUsers(keyword) {
  const [rows] = await pool.query(
    'SELECT id, username FROM users WHERE username LIKE ? OR email LIKE ?',
    [`%${keyword}%`, `%${keyword}%`]
  );
  return rows;
}

攻撃者が ' OR '1'='1 を入力しても、mysql2がエスケープされた文字列リテラルに変換します——まったく無害で、クエリはその値だけを検索します。

方法3：execute() vs query() — どちらをいつ使うか？

mysql2にはプレースホルダー付きクエリの実行方法が二種類あります：

// query() — クライアント側でエスケープ、処理済みのSQL文を送信
const [rows1] = await pool.query('SELECT * FROM users WHERE id = ?', [id]);

// execute() — 真のprepared statement、MySQLサーバーが個別にコンパイル
const [rows2] = await pool.execute('SELECT * FROM users WHERE id = ?', [id]);
  • execute()は繰り返し実行するクエリに使用——MySQLがexecution planをキャッシュするため、高負荷時に大幅な速度向上
  • query()は一度しか実行しないクエリや、prepared statementが使えない複雑なクエリに使用

方法4：データの整合性を保証するTransaction

Transactionは「all or nothing」を保証します——すべての操作が成功するか、初期状態にrollbackするかのどちらかです。例として、二つのアカウント間の送金を示します：

async function transferMoney(fromId, toId, amount) {
  const connection = await pool.getConnection();

  try {
    await connection.beginTransaction();

    // 送金元口座から差し引く（同じクエリで残高確認）
    const [result] = await connection.execute(
      'UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?',
      [amount, fromId, amount]
    );

    if (result.affectedRows === 0) {
      throw new Error('残高不足またはアカウントが存在しません');
    }

    // 送金先口座に加算
    await connection.execute(
      'UPDATE accounts SET balance = balance + ? WHERE id = ?',
      [amount, toId]
    );

    // 取引ログを記録
    await connection.execute(
      'INSERT INTO transactions (from_id, to_id, amount, created_at) VALUES (?, ?, ?, NOW())',
      [fromId, toId, amount]
    );

    await connection.commit();
    return { success: true };

  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    // 重要：成功・失敗にかかわらず常にpoolに返却する
    connection.release();
  }
}

finally内にconnection.release()を置くtry/catch/finallyパターンは必須です。解放を忘れると、connectionがpoolから「リーク」します——数時間動かすとconnectionが枯渇し、理由不明のまま全リクエストがハングします。

ベストプラクティス — 三つのテクニックの組み合わせ

最も効果的な構成は、データベースモジュールを分離し、各タスクにwrapper関数を書くことだと感じています：

// models/user.js
const pool = require('../db');

class UserModel {
  async findById(id) {
    const [rows] = await pool.execute(
      'SELECT id, username, email, created_at FROM users WHERE id = ?',
      [id]
    );
    return rows[0] || null;
  }

  async create(username, email, hashedPassword) {
    const [result] = await pool.execute(
      'INSERT INTO users (username, email, password, created_at) VALUES (?, ?, ?, NOW())',
      [username, email, hashedPassword]
    );
    return result.insertId;
  }

  // Transaction：userとprofileを一つのatomic operationで作成
  async registerWithProfile(userData) {
    const conn = await pool.getConnection();
    try {
      await conn.beginTransaction();

      const [userResult] = await conn.execute(
        'INSERT INTO users (username, email, password) VALUES (?, ?, ?)',
        [userData.username, userData.email, userData.password]
      );
      const userId = userResult.insertId;

      await conn.execute(
        'INSERT INTO profiles (user_id, display_name, bio) VALUES (?, ?, ?)',
        [userId, userData.displayName, userData.bio || '']
      );

      await conn.commit();
      return userId;
    } catch (err) {
      await conn.rollback();
      throw err;
    } finally {
      conn.release();
    }
  }
}

module.exports = new UserModel();

// server.js — 起動時にデータベース接続を確認
const pool = require('./db');

async function startServer() {
  try {
    const [rows] = await pool.query('SELECT 1 AS health');
    console.log('Database:', rows[0].health === 1 ? 'Connected OK' : 'FAIL');
  } catch (err) {
    console.error('Cannot connect to database:', err.message);
    process.exit(1);
  }

  const app = require('./app');
  app.listen(3000, () => console.log('Server running on port 3000'));
}

startServer();

コードをリリースする前のチェックリスト

  • SQLに文字列を連結しない — 常にプレースホルダー?を使用、例外なし
  • Poolはシングルトン — db.jsで一度だけ初期化し、必要な場所でimport
  • 常にconnectionを解放する — transactionの後はtryではなくfinallyブロックで
  • 認証情報は環境変数から — ソースコードにハードコードせず、.envをコミットしない
  • 繰り返しクエリにはexecute()、一度だけ実行するクエリにはquery()

このパターンに移行してから、システムは格段に安定しました——トラフィックが高い時にconnectionが枯渇することもなくなり、SQLインジェクションを心配する必要もなくなりました。MySQLが繰り返し呼ばれるprepared statementのexecution planをキャッシュするため、これらの最適化によりクエリが大幅に高速化されました。特に毎分数百回クエリが実行される場合に顕著な効果があります。

Share:
Tags: