mysqlモジュールでDB操作

2022/11/26

mysqlモジュールを利用してDB操作する方法を確認します。「データ取得」「データ挿入」「コネクションプールの利用」「トランザクション処理」について動作確認します。

DockerでDB環境構築

フォルダ構成

DockerでDB環境を構築します。以下フォルダ構成で実行します。

.
├── initdb
│   ├── 1_create_node_mysql_test_db.sql     # 起動時にDB生成
│   ├── 2_create_tests_table.sql            # 起動時にテーブル生成
│   └── 3_insert_tests_table.sql            # 起動時にレコード挿入
└── docker-compose.yml

docker-compose.yml

version: '3'

services:

  db:
    image: mysql:5.7
    restart: always
    ports:
      - "13306:3306"
    volumes:
      # 永続データ
      # .data/dbは起動時に自動生成される
      - ./.data/db:/var/lib/mysql

      # 起動時のデータ初期化
      - ./initdb:/docker-entrypoint-initdb.d
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_USER: test
      MYSQL_PASSWORD: test
      TZ: "Asia/Tokyo"

DB初期化クエリ

1_create_node_mysql_test_db.sql

CREATE DATABASE IF NOT EXISTS node_mysql_test;

2_create_tests_table.sql

CREATE TABLE IF NOT EXISTS `node_mysql_test`.`tests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `integer` int(11) NOT NULL COMMENT '数値',
  `big_integer` bigint(20) NOT NULL DEFAULT '0',
  `unsigned_integer` int(10) unsigned NOT NULL,
  `float` double(8,2) NOT NULL,
  `double` double(15,8) NOT NULL,
  `string` varchar(255) DEFAULT NULL,
  `text` text NOT NULL,
  `enum` enum('DEBUG','INFO','NOTICE','WARNING','ERROR','CRITICAL','ALERT','EMERGENCY') NOT NULL,
  `geometry` geometry NOT NULL,
  `json` json NOT NULL,
  `date` date NOT NULL,
  `dateTime` datetime NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tests_integer_unique` (`integer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

3_insert_tests_table.sql

INSERT INTO `node_mysql_test`.`tests`
(`id`, `integer`, `big_integer`, `unsigned_integer`, `float`, `double`, `string`, `text`, `enum`, `geometry`, `json`, `date`, `dateTime`, `timestamp`)
VALUES
('1', '10', '20', '30', '10.5', '20.55', 'abc', 'xyz', 'INFO', GeomFromText('POINT(139.766247 35.681298)'), '{\"x\": 100, \"y\": 200}', '2012-01-01', '2000-01-01 10:20:30', '2000-01-01 02:12:22');

コンテナ立ち上げ

$ docker-compose up -d

mysqlコマンドで接続

$ mysql -uroot -p -h 127.0.0.1 --port 13306
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM `node_mysql_test`.`tests`\G;
*************************** 1. row ***************************
              id: 1
         integer: 10
     big_integer: 20
unsigned_integer: 30
           float: 10.50
          double: 20.55000000
          string: abc
            text: xyz
            enum: INFO
        geometry:        �m�xa@Dj��4�A@
            json: {"x": 100, "y": 200}
            date: 2012-01-01
        dateTime: 2000-01-01 10:20:30
       timestamp: 2000-01-01 02:12:22
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | JST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysqlモジュールをインストール

yarn add mysql

インストールできました。

$ yarn list | grep mysql
├─ mysql@2.16.0

mysqlモジュールでDB操作

接続・切断
( createConnection, connect, end )

const mysql = require('mysql')
const connection = mysql.createConnection({
  host: 'localhost',
  port: 13306,
  user: 'root',
  password: 'root',
  database: 'node_mysql_test'
})

connection.connect()

connection.query('SELECT "Hello World!" AS text', (error, results, fields) => {
  if (error) throw error
  console.log(results[0].text)
})

connection.end()

$ node app.js 
Hello World!

接続オプションは以下ページから確認できます。
https://github.com/mysqljs/mysql#connection-options

レコード取得

const mysql = require('mysql')
const connection = mysql.createConnection({
  host: 'localhost',
  port: 13306,
  user: 'root',
  password: 'root',
  database: 'node_mysql_test'
})

connection.connect()

connection.query('SELECT * FROM `tests`', (error, results, fields) => {
  if (error) throw error
  console.log(results[0])
  console.log(results[0].id)
  console.log(results[0].integer)
  console.log(results[0].big_integer)
  console.log(results[0].unsigned_integer)
  console.log(results[0].float)
  console.log(results[0].double)
  console.log(results[0].string)
  console.log(results[0].text)
  console.log(results[0].enum)
  console.log(results[0].geometry)
  console.log(results[0].json)
  console.log(results[0].date)
  console.log(results[0].dateTime)
  console.log(results[0].timestamp)
})

connection.end()

$ node app.js 
RowDataPacket {
  id: 1,
  integer: 10,
  big_integer: 20,
  unsigned_integer: 30,
  float: 10.5,
  double: 20.55,
  string: 'abc',
  text: 'xyz',
  enum: 'INFO',
  geometry: { x: 139.766247, y: 35.681298 },
  json: '{"x": 100, "y": 200}',
  date: 2011-12-31T15:00:00.000Z,
  dateTime: 2000-01-01T01:20:30.000Z,
  timestamp: 1999-12-31T17:12:22.000Z }
1
10
20
30
10.5
20.55
abc
xyz
INFO
{ x: 139.766247, y: 35.681298 }
{"x": 100, "y": 200}
2011-12-31T15:00:00.000Z
2000-01-01T01:20:30.000Z
1999-12-31T17:12:22.000Z

date dateTime timestamp の値が UTC表記 で取得されています。

JST で取得したい場合、 createConnection で timezone: 'jst' を追記します。

const mysql = require('mysql')
const connection = mysql.createConnection({
  host: 'localhost',
  port: 13306,
  user: 'root',
  password: 'root',
  database: 'node_mysql_test',
  timezone: 'jst'
})

connection.connect()

connection.query('SELECT * FROM `tests`', (error, results, fields) => {
  if (error) throw error
  console.log(results[0].date)
  console.log(results[0].dateTime)
  console.log(results[0].timestamp)
})

connection.end()

$ node app.js 
2012-01-01
2000-01-01 10:20:30
2000-01-01 02:12:22

レコード挿入

const mysql = require('mysql')
const connection = mysql.createConnection({
  host: 'localhost',
  port: 13306,
  user: 'root',
  password: 'root',
  database: 'node_mysql_test',
  timezone: 'jst'
})

connection.connect()

const test = {
  id: 2,
  integer: 100,
  big_integer: 200,
  unsigned_integer: 300,
  float: 1.4,
  double: 1.422,
  string: 'aaaaaaaaaa',
  text: 'bbbbbbbbbb',
  enum: 'NOTICE',
  json: JSON.stringify({ aaa: 123, bbb: 222 }),
  date: '2001-01-01',
  dateTime: '2002-01-01 10:20:30',
  timestamp: '2003-01-01 10:20:30'
}
const lat = 139.766247
const long = 35.681298
const query = connection.query(
  'INSERT INTO `tests` SET ?, geometry = POINT(?,?)',
  [test, lat, long],
  (error, results, fields) => {
    if (error) throw error

    console.log('=== success ===')
    console.log(results)
  }
)

console.log('=== show query ===')
console.log(query.sql)

connection.end()

$ node app.js 
=== show query ===
INSERT INTO `tests` SET `id` = 2, `integer` = 100, `big_integer` = 200, `unsigned_integer` = 300, `float` = 1.4, `double` = 1.422, `string` = 'aaaaaaaaaa', `text` = 'bbbbbbbbbb', `enum` = 'NOTICE', `json` = '{\"aaa\":123,\"bbb\":222}', `date` = '2001-01-01', `dateTime` = '2002-01-01 10:20:30', `timestamp` = '2003-01-01 10:20:30', geometry = POINT(139.766247,35.681298)
=== success ===
OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 2,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

以下のようにレコードが挿入されました。

mysql> SELECT * FROM `tests` WHERE `id`=2\G;
*************************** 1. row ***************************
              id: 2
         integer: 100
     big_integer: 200
unsigned_integer: 300
           float: 1.40
          double: 1.42200000
          string: aaaaaaaaaa
            text: bbbbbbbbbb
            enum: NOTICE
        geometry:        �m�xa@Dj��4�A@
            json: {"aaa": 123, "bbb": 222}
            date: 2001-01-01
        dateTime: 2002-01-01 10:20:30
       timestamp: 2003-01-01 10:20:30
1 row in set (0.00 sec)

ERROR: 
No query specified

コネクションプールの利用
( createPool, release )

DBへの接続・切断をアクセスのたびに行うと負荷が生じます。

接続状態を準備して、プール(蓄える)しておき、必要になったら貸し出す方法をとることで負荷軽減できます。

mysqlモジュールでは、 createPoolメソッド でコネクションプールを利用できます。

const mysql = require('mysql')

const pool = mysql.createPool({
  connectionLimit : 1,
  host: 'localhost',
  port: 13306,
  user: 'root',
  password: 'root',
  database: 'node_mysql_test',
  timezone: 'jst'
})

async function hello() {
  const connection = await new Promise((resolve, reject) => {
    pool.getConnection((error, connection) => {
      if (error) reject(error)
      resolve(connection)
    })
  })

  const results = await new Promise((resolve, reject) => {
    connection.query('SELECT "Hello World!" AS text', (error, results) => {
      if (error) reject(error)
      resolve(results)
    })
  })
  console.log(results[0].text)

  // connection.release()
}

(async () => {
  await hello()
  await hello()
  pool.end()
})()

コネクションは使い終わったら releaseメソッド を実行して解放してあげる必要があります。

上記コードでは、release処理を意図的にコメントアウトしています。connectionLimit : 1, としているので、実行すると2回目のhelloでコネクションを取得できない状態になり、処理が止まります。

$ node app.js 
Hello World!

release処理のコメントアウトを解除すると、以下のように処理が完了します。

$ node app.js 
Hello World!
Hello World!

トランザクション
( beginTransaction, commit, rollback )

トランザクションの動作確認をします。

const mysql = require('mysql')

const pool = mysql.createPool({
  connectionLimit : 1,
  host: 'localhost',
  port: 13306,
  user: 'root',
  password: 'root',
  database: 'node_mysql_test',
  timezone: 'jst'
})

async function updateAndDelete() {
  const connection = await new Promise((resolve, reject) => {
    pool.getConnection((error, connection) => {
      if (error) reject(error)
      resolve(connection)
    })
  })

  try {
    await new Promise((resolve, reject) => {
      connection.beginTransaction((error, results) => {
        if (error) reject(error)
        resolve(results)
      })
    })
    console.log('=== done beginTransaction ===')

    await new Promise((resolve, reject) => {
      connection.query('UPDATE `tests` SET `string`=? WHERE `id`=?', ['xxxx', 2], (error, results) => {
        if (error) reject(error)
        resolve(results)
      })
    })
    console.log('=== done update ===')

    await new Promise((resolve, reject) => {
      connection.query('DELETE FROM `tests` WHERE `id`=?', 1, (error, results) => {
        if (error) reject(error)
        resolve(results)
      })
    })
    console.log('=== done delete ===')

    await new Promise((resolve, reject) => {
      connection.commit((error, results) => {
        if (error) reject(error)
        resolve(results)
      })
    })
    console.log('=== done commit ===')
  } catch (err) {
    await new Promise((resolve, reject) => {
      connection.rollback((error, results) => {
        if (error) reject(error)
        resolve(results)
      })
    })
    console.log('=== done rollback ===')
  } finally {
    connection.release()
    console.log('=== done release ===')
  }
}

(async () => {
  console.log('=== before updateAndDelete ===')
  await updateAndDelete()
  console.log('=== after updateAndDelete ===')
  pool.end()
})()

$ node app.js 
=== before updateAndDelete ===
=== done beginTransaction ===
=== done update ===
=== done delete ===
=== done commit ===
=== done release ===
=== after updateAndDelete ===