ExpressでMySQLと接続（Node.js その３）

「MySQL」は「RDB（リレーショナルデータベース）」です
「RDB」は表形式でデータを管理し、トランザクション（不可分な一連の処理）で、データの整合性が担保され、柔軟に検索できます

目次

CLIでMySQLを操作
ExpressからMySQLを操作する
コネクションプール
トランザクション処理

CLIでMySQLを操作

SQL文は、大文字と小文字の区別はないです（ごちゃまぜになっています）
MANPを使っているため、MySQLサーバー等インストールについては省略しています^^;

MySQLサーバー接続と終了

#接続
mysql -u ユーザー名 -D データベース名 -p

#MACのMANP
cd /Applications/MAMP/Library/bin/
$ ./mysql -u root -p
Enter password: ← パスワードを入力します

#終了
exit

一覧を表示する

#データベースの一覧を表示する
SHOW DATABASES;
#存在するテーブルの一覧を表示する
SHOW TABLES FROM データベース名;

データベースを新しく作成する

CREATE DATABASE データベース名 DEFAULT CHARACTER SET utf8;

データベースに、アクセスする
＊「 ; 」不要です

USE データベース名

テーブルを作成する（例）
CREATE TABLE テーブル名 (カラムの定義)
＊engine=InnoDB default charset=utf8はデフォルト値なので省略可
＊主キーは必須です（重複・変更しない値）
主キー用のID作成、CREATED作成日、MODIGIED更新日
unsigned：0 と正の数
primary key auto_increment ：主キーとして自動的1ずつ増加し連番をつける

CREATE TABLE テーブル名 (
id int unsigned primary key auto_increment,
列名１ データ型１,
列名２ データ型２,・・・,
created datetime,
modified timestamp
)engine=InnoDB default charset=utf8;

その他の制約

NOT NULL ：制約をつけると必ず値を設定しなければならない
UNIQUE ：一意（重複してはいけない）制約
FOREIGN KEY ：外部キー（他のテーブルと結合するためのキー）制約

複合主キー（主キーを複数カラムの組み合わせとして定義することもできます）
複数のカラムの組み合わせに対してPRIMARY KEY制約を設定する場合

CREATE TABLE テーブル名 (
key1 データ型 NOT NULL, 
key2 データ型 NOT NULL, ..., 
PRIMARY KEY(key1, key2, ...)
)

よく使うデータ型

数値	TINYINT・INT・BIGINT
日付	DATETIME・TIMESTAMP
文字列	CHAR（固定長）・VARCHAR（可変長の最大文字数）
バイナリデータを含むオブジェクト	BLOB

テーブルに値を挿入する
insert into テーブル名 (カラム名) values(値);
＊カラム名と値の順番は同じにすること

insert into テーブル名
  (created, 列名, ...)
  values
  (now(), '値', ...),
  ...
  (now(), '値', ...);

データベース・テーブル・レコードの削除

#データベースを削除する
DROP DATABASE テーブル名;
#テーブルを削除する
DROP TABLE テーブル名;
#レコード（行）を削除
delete from テーブル名 where 削除するレコードの条件

レコードの取得
「*」 : 全部のカラム
selectはテーブルを入力としてテーブルを出力しています
出力テーブルのカラム名には、asで別名をつけることができます
distinct : 重複レコードは省きます

select * from テーブル名;
select カラム名 from テーブル名;
select カラム名 as "別名" テーブル名;

where 条件句
＊true は1 false は0 になり、１のレコードだけ出力されます
条件句について

= （一致）!=（一致しない）
> < >= <= 数値の比較
and or
in （いずれかの値に一致）や not in（いずれかの値に一致しない）
文字列の曖昧検索 like % は0文字以上ワイルドカード _は1文字のワイルドカード
order by (descをつけると大き順）に並べ替え
limit 数値：検索件数を絞る

値の更新（UPDATE）

update テーブル名
set カラム名 = 値, ...
where 更新するレコードの条件;

集計する
sum(カラム名)：カラムの合計値
avg(カラム名)：カラムの平均値
count(カラム名) ：レコードの数を数える
＊ group by カラム名でカラム名ごとに集計できます

select sum(カラム名) from テーブル名;

テーブルを結合して等しいカラムの値で紐付けした新しいテーブルを作ります（外部結合）
left join : 左側に指定された表のすべての行が表示されます

select * from テーブル名1 join テーブル名2 on テーブル名1.カラム名 = テーブル名2.カラム名;

テーブルを結合して等しいカラムだけの新しいテーブルを作ります（内部結合）

select * from テーブル名1 inner join テーブル名2 on テーブル名1.カラム名 = テーブル名2.カラム名;

結合するときは、カラム名が重複することがあるので、必ずテーブル名とセットにする必要があります
別名を利用することで、省略して書くことができます

ExpressからMySQLを操作する

＊Expressサーバーは起動している状態です

Expressフレームワークの基本的な使い方（Node.jsその２）

Node.jsでMySQLに接続するには「mysql モジュール（ドライバー）」を使います

npm mysql

$ npm install mysql

mysql.createConnectionの戻り値 Connectionインスタンスを使って、データベースを操作します
connection.connect(callback) : 接続

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: '・・・',
  password: '・・・'
});
connection.connect((err) =>{
  if (err) throw err;
  console.log('接続!!');
});

connection.query(query, callback) : データベースに対してクエリを実行
connection.end(callback) :接続を終了

const mysql = require('mysql');

const connection = mysql.createConnection({
    host: 'localhost',
    port: '3306'
    database: 'my_database',
    user: '...',
    password: '...',
});

//接続　callbackは　接続成功/失敗した時に呼び出される関数
connection.connect((err)=> {
  if (err) throw err;
  console.log('接続');
   //データベースに対してクエリを実行し、結果のデータを受け取るなどができる
   //第3引数fieldsはカラムの情報
  connection.query('実行したいクエリ文字列', (err, result, fields)=> {
  if (err) throw err; 
    console.log(result);
  });
});
//データベースと接続を終了
connection.end();

設定は本番環境を考え環境変数からも与えられるようにします

//設定ファイル
module.exports = {
  HOST: process.env.MYSQL_HOST || "127.0.0.1",
  PORT: process.env.MYSQL_PORT || "3306",
  USERNAME: process.env.MYSQL_USERNAME || "・・・",
  PASSWORD: process.env.MYSQL_PASSWORD || "・・・",
  DATABASE: process.env.MYSQL_DATABASE || "・・・"
};

コールバックだとネストが複雑になるので、Promise化して、別ファイルに切り出しました

//Promise化するため
const { promisify } = require("util");
const config = require('../mysql.config');
const mysql = require("mysql");
const con = mysql.createConnection({
  host: config.HOST,
  port: config.PORT,
  user: config.USERNAME,
  password: config.PASSWORD,
  database: config.DATABASE
});
//Promise化してasync/awaitを使う
const Client = {
  connect: promisify(con.connect).bind(con),
  query: promisify(con.query).bind(con),
  end: promisify(con.end).bind(con)
};

module.exports = {
  Client,
};

// /testにアクセスしたときにselect文の結果をコンソールに出力
app.get("/test", async (req, res, next) => {
  const { Client } = require("./client.js");
  try {
    await Client.connect();
    const data = await Client.query('select * from users')
    console.log(data);
  } catch (err) {
    next(err);
  } finally {
    await Client.end();
  }
  res.end("OK");
});

コネクションプール

アクセスがあるたびにDBへの接続・切断をすると負荷がかかります
アプリケーションサーバーからDBサーバーへの接続を一定量プールして、接続情報を使い回し、プールされた接続が利用できない場合にのみ新しい接続を確立することで、負荷を軽減します

MySQLへの接続は、mysql.createPool(option)の戻り値、Poolインスタンスを利用します
optionのconnectionLimitは同時接続できる上限です（デフォルトは10）
queryメソッドはコネクションを取得し、クエリを実行して、プールにコネクションを戻す処理を一括で処理します
pool.query(query, callback)

const pool = mysql.createPool({
    connectionLimit : 10,
    host: 'localhost',
    database: '・・・',
    user: '・・・',
    password: '・・・',
});
//第3引数のfieldsはカラムの情報
pool.query('select・・・・', (err, results, fields) => {  
  if (err) { throw err;}
　　　　console.log(results)
});

getConnectionメソッド
pool.getConnection(callback)：プールの中からコネクションを取得
pool.end(callback)：プール内のすべての接続を閉じる
＊connection.release()でコネクションを開放します

const pool = mysql.createPool({
    connectionLimit : 10,
    host: 'localhost',
    database: '・・・',
    user: '・・・',
    password: '・・・',
});
//プールの中からコネクションを取得
pool.getConnection((err, connection) => {
    if (err) throw err;
　　//クエリを実行
    connection.query('select・・・・', (err, results, fields) => {
        //失敗したら　
        if (err) {
　　　　　//コネクションを開放
         connection.release();
         throw err;
        }
　　　　　console.log(results)
        connection.release();
    });
});

getConnectionメソッドは、後続のクエリの接続状態を共有するのに役立ちます
queryメソッドは2つの異なる接続を使用し、並行して実行される可能性があります

モジュールにして別ファイルに切り出す

const { promisify } = require("util");
const { host, port, user, password, database } = require('../mysql.config');
const mysql = require("mysql");

const cofObj = {
    host,
    port,
    user,
    password,
    database
}
//Poolインスタンス
const pool = mysql.createPool({ ...cofObj, connectionLimit: 10 });

//pool.queryをプロミス化、
const poolQuery = promisify(pool.query).bind(pool)
//引数にクエリとバインド用valuesを取れるようにして、結果を受け取る
const executeQuery = async (query, values) => {
    const results = await poolQuery(query, values);
    return results;
}
module.exports = {
  executeQuery,
};

// /testにアクセスしたときにselect文の結果をコンソールに出力
app.get("/test", async (req, res, next) => {
  const { executeQuery } = require("./database/pool.js");
  try {
    const data = await executeQuery('select * from users')
    console.log(data);
  } catch (err) {
    next(err);
  }
//pool.queryを使っているので、開放は不要
  res.end("OK");
});

重要

ユーザーから取得した値を変数に格納して使う場合など、必ず変数には、バインド（sql文の変数は？を使う）を利用します（SQLインジェクション対策）
valuesは変数？に与える値の配列
connection.query(query, values, callback)
pool.query(query, values, callback)

トランザクション処理

トランザクションを活用することで、中途半端な状態でデータが更新されることを防ぐことができます
イメージは銀行の振り込み時の入出金
一連のSQLの処理が成功したらCommit
もし失敗したらRollback

該当のコネクションに対してトランザクションの機能を実装します
connection.beginTransaction(callback)

connection.beginTransaction((err) => {
  if (err) { throw err; }
　//１っ目のクエリ　バインドを利用
  connection.query('INSERT INTO ... VALUES (?)', ['値'], (err, results, fields) => {
    //失敗したらrollback
   if (err) {connection.rollback(() => { throw error; });}
　　　//２っ目のクエリ　バインドを利用
    connection.query('INSERT INTO ... VALUES (?)', ['値'], (error, results, fields) => {
       //失敗したらrollback
　　　　if (error) { connection.rollback(() => {throw error;});}
　　　　//コミットする
      　connection.commit((err) => {
        if (err) {connection.rollback(() => {throw error});}
　　　　　//失敗したらrollback
        console.log('success!');
      });
    });
  });
});

再利用を考えたファイル分割をしてみる

pool.js
pool.getConnectionとpool.queryをプロミス化しておく

const { promisify } = require("util");
const { host, port, user, password, database } = require('../mysql.config');
const mysql = require("mysql");

const cofObj = {
    host,
    port,
    user,
    password,
    database
}
//Poolインスタンス
const pool = mysql.createPool({ ...cofObj, connectionLimit: 10 });
//pool.getConnectionプロミス化
const getConnection = promisify(pool.getConnection).bind(pool);
//pool.queryプロミス化
const poolQuery = promisify(pool.query).bind(pool)

module.exports = {
getConnection,
poolQuery
};

transaction.js
Transactionクラスを作り、Transactionクラスにbegin（トランザクション開始）・query（クエリの実行）・commit（コミット）・rollback（ロールバック）メソッドを実装します

const pool = require("./pool.js");

const Transaction = class {
  constructor(connection) {
    this.connection = connection;
  }
  async begin() {
 //コネクションの有無確認
      if (this.connection) {
        //コネクションがあれば開放
      this.connection.release();
    }
//コネクションがなければ、poolからコネクションを取得
    this.connection = await pool.getConnection();
//トランザクション開始
    this.connection.beginTransaction();
  }
 //クエリの実行 options = {}はfields
  async query(query, values, options = {}) {
    options = {
      fields: options.fields || false
    };
     //プロミスでラップ
    return new Promise((resolve, reject) => {
        //クエリを実行
      this.connection.query(query, values, (err, results, fields) => {
          if (!err) {
            //fieldsがあれば、fieldsの情報も含めて返却
          resolve(!options.fields ? results : { results, fields });
        } else {
          reject(err);
        }
      });
    });
  }
  async commit() {
    return new Promise((resolve, reject) => {
     //コミットを実行
        this.connection.commit((err) => {
        if (!err) {
         //エラーでなければコネクションを開放
          this.connection.release();
          this.connection = null;
          resolve();
        } else {
          reject(err);
        }
      });
    });
  }
  async rollback() {
    return new Promise((resolve) => {
    //ロールバックを実行
        this.connection.rollback(() => {
     //コネクションを開放
        this.connection.release();
        this.connection = null;
        resolve();
      });
    });
  }
};

module.exports = Transaction;

mysql_client.js
通常のsql処理（値を取得する時など）とトランザクションを開始する処理を関数化しておく

const { poolQuery } = require('./pool.js');
const Transaction = require("./transaction.js");

//引数にクエリとバインド用valuesを取れるようにして、結果を受け取る
const executeQuery = async (query, values) => {
    const results = await poolQuery(query, values);
    return results;
}
//トランザクションを開始
const beginTran = async () => {
    const  tran = new Transaction();
    await tran.begin();
    return tran;
  }

module.exports = {
    executeQuery,
    beginTran
};

app.js の実装例
/updateでは、トランザクション処理で値を更新 💦
/testは通常のsql処理でテーブルの値を取得

app.get("/update", async (req, res, next) => {
    const { beginTran } = require("./database/mysql_client.js");
    const tran = await beginTran();
    try {
    await tran.query(
      "UPDATE users SET name=? WHERE id=?",
      ['hoge', 1]
    );
    // throw new Error("エラーテスト");
    await tran.commit();
    res.end("OK");
  } catch (err) {
  //  await tran.rollback();
    next(err);
  }
});

app.get("/test", async (req, res, next) => {
  const { executeQuery } = require("./database/mysql_client.js");
  try {
    const data = await executeQuery('select * from users')
    console.log(data);
  } catch (err) {
    next(err);
  }
  res.end("OK");
});

排他制御について
排他制御は、トランザクション実行時に複数のユーザーが同じレコードを同時に利用した場合でもデータの矛盾を発生させないための仕組みです

悲観ロック（同じデータが頻繁に更新されるという悲観的な考え方の排他制御で、データを読み取り時ロックして、更新したらロックを解除します）
楽観ロック（同時アクセスはあまり起きないという楽観的な考え方の排他制御で、テーブルにバージョン番号用カラムが付与し、更新時にテーブルのバージョンがカウントアップ、データを読み取り時と更新時のテーブルのバージョンが異なるとエラーになります）
不整合を発生させないためだけなら、悲観ロックだけで良いのですが、参照のみのユーザーまでロック解除されるまでデータ参照を待たされてしまいます

＊select 文に対して for update をつけると対象に排他ロックがかかり、更新するとロックが解除されます
対象に行のみロックするには、検索対象カラム（where ・・・）がユニーク制約かインデックスが貼られている必要があります
＊悲観ロックではテーブルの更新順などでデッドロック（それぞれがロック解除待ちになり、処理が完了しない状態）になるケースがあり要注意