Node.jsでpromise-mysqlを扱ってみたかったので独自目線で纏めた

# はじめに

どうも、また私です。
今度はpromise-mysqlを扱ってみようと思います。
例の如く手探りです。

ここへ至る経緯はこっちから
[Node.jsでMySQLを扱いたかったので独自目線で纏めた](https://qiita.com/aya_akatsuki/items/ea9a8beba93367ef7209)

# 簡単な動作確認
## 設定
```javascript:setting.js
const mysql = require('/usr/local/lib/node_modules/promise-mysql');

mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
}).then(function(conn) {
    // do stuff with conn
    console.log('promise-mysql createConnection.');
    console.log(conn);
    conn.end();
});
```

## SELECT
```javascript:select1.js
const mysql = require('/usr/local/lib/node_modules/promise-mysql');

mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
}).then(function(conn) {
    // do stuff with conn
    console.log('promise-mysql createConnection.');
    // console.log(conn);

    // select
    const result = conn.query('SELECT * FROM node_mysql_test');
    conn.end();
    return result;
}).then(function(rows) {
    console.log(rows);
});
```

```javascript:select2.js
const mysql = require('/usr/local/lib/node_modules/promise-mysql');
let connection;

mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
}).then(function(conn) {
    // do stuff with conn
    console.log('promise-mysql createConnection.');
    // console.log(conn);
    connection = conn;
    // select
    const result = connection.query('SELECT * FROM node_mysql_test');
    connection.end();
    console.log(result);
    return result;
}).then(function(rows) {
    // result
    console.log('SELECT is Success!');
    console.log(rows);
}).catch(function(error) {
    if (connection && connection.end) connection.end();
    //logs out the error
    console.log('catch error.');
    console.log(error);
});
```

## INSERT
```javascript:insert.js
/**
 * promise-mysql module
 * @type       {Object}
 */
const mysql = require('/usr/local/lib/node_modules/promise-mysql');
/**
 * table name
 * @type       {string}
 */
const TABLE_NAME = 'node_mysql_test';
/**
 * connection
 * @type       {Object}
 */
let conn;
let connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
}).then(function(connect) {
    console.log('promise-mysql connect.');
    conn = connect;
    // insert
    const sql = 'INSERT INTO ' + TABLE_NAME + ' SET ?';
    const inserts = {name: '鷲尾雷'};
    const result = conn.query(sql, inserts);
    console.log(result);
    return result;
}).then(function(res) {
    console.log(res);
    // select
    const result = conn.query('SELECT * FROM node_mysql_test');
    conn.end();
    return result;
}).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
}).catch(function(error) {
    if (conn && conn.end) conn.end();
    //logs out the error
    console.log('catch error.');
    console.log(error);
});
```

## UPDATE
```javascript:update.js
/**
 * promise-mysql module
 * @type       {Object}
 */
const mysql = require('/usr/local/lib/node_modules/promise-mysql');
/**
 * table name
 * @type       {string}
 */
const TABLE_NAME = 'node_mysql_test';
/**
 * connection
 * @type       {Object}
 */
let conn;
let connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
}).then(function(connect) {
    console.log('promise-mysql connect.');
    conn = connect;
    // insert
    const sql = 'UPDATE ' + TABLE_NAME + ' SET age = ? WHERE name = ?';
    const inserts = [35, '氷室'];
    const result = conn.query(sql, inserts);
    console.log(result);
    return result;
}).then(function(res) {
    console.log(res);
    // select
    const result = conn.query('SELECT * FROM node_mysql_test');
    conn.end();
    return result;
}).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
}).catch(function(error) {
    if (conn && conn.end) conn.end();
    //logs out the error
    console.log('catch error.');
    console.log(error);
});
```
おめでとう！氷室さんの正しい年齢が記録されました！

## DELETE
```javascript:delete.js
/**
 * promise-mysql module
 * @type       {Object}
 */
const mysql = require('/usr/local/lib/node_modules/promise-mysql');
/**
 * table name
 * @type       {string}
 */
const TABLE_NAME = 'node_mysql_test';
/**
 * connection
 * @type       {Object}
 */
let conn;
let connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
}).then(function(connect) {
    console.log('promise-mysql connect.');
    conn = connect;
    // delete
    const sql = 'DELETE FROM ' + TABLE_NAME + ' WHERE name = ?';
    const inserts = ['葛城'];
    const result = conn.query(sql, inserts);
    console.log(result);
    return result;
}).then(function(res) {
    console.log(res);
    // select
    const result = conn.query('SELECT * FROM node_mysql_test');
    conn.end();
    return result;
}).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
}).catch(function(error) {
    if (conn && conn.end) conn.end();
    //logs out the error
    console.log('catch error.');
    console.log(error);
});
```

## SELECT書き方他にも
```javascript:select3.js
/**
 * promise-mysql module
 * @type       {Object}
 */
const mysql = require('/usr/local/lib/node_modules/promise-mysql');
/**
 * table name
 * @type       {string}
 */
const TABLE_NAME = 'node_mysql_test';
/**
 * pool
 * @type       {Object}
 */
let conn;
let pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
});

let sql = 'SELECT * FROM ' + TABLE_NAME;
pool.query(sql).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
}).catch(function(error) {
    //logs out the error
    console.log('catch error.');
    console.log(error);
});
```

```javascript:select4.js
/**
 * promise-mysql module
 * @type       {Object}
 */
const mysql = require('/usr/local/lib/node_modules/promise-mysql');
/**
 * table name
 * @type       {string}
 */
const TABLE_NAME = 'node_mysql_test';
/**
 * pool
 * @type       {Object}
 */
let conn;
let pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
});

pool.getConnection().then(function(connection) {
    let sql = 'SELECT * FROM ' + TABLE_NAME;
    connection.query(sql).then(function(rows) {
        // select is success
        console.log('SELECT is Success!');
        console.log(rows);
    });
}).catch(function(error) {
    //logs out the error
    console.log('catch error.');
    console.log(error);
    done(error);
});
```

# トランザクションを実装したい
node-mysqlのときより全然見やすいです。

```javascript:transaction.js
/**
 * promise-mysql module
 * @type       {Object}
 */
const mysql = require('/usr/local/lib/node_modules/promise-mysql');
/**
 * table name
 * @type       {string}
 */
const TABLE_NAME = 'node_mysql_test';
/**
 * connection
 * @type       {Object}
 */
let conn;
mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
}).then(function(connect) {
    console.log('promise-mysql connect.');
    conn = connect;
    // console.log(conn);
    const result = conn.beginTransaction();
    return result;
}).then(function(res) {
    console.log(res);
    let sql = 'SELECT * FROM ' + TABLE_NAME;
    return conn.query(sql);
}).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
    // insert data
    let sql = 'INSERT INTO ' + TABLE_NAME + ' SET ?';
    let inserts = {name: '桐生', age: 26};
    const result = conn.query(sql, inserts);
    return result;
}).then(function(res) {
    console.log('INSERT is Success!');
    console.log(res);
    // const result = conn.rollback();    // ここでロールバック発動すれば実行されずに終わる
    const result = conn.commit();
    return result;
}).then(function(res) {
    console.log('COMMIT Success!');
    console.log(res);
    // select data
    let sql = 'SELECT * FROM ' + TABLE_NAME;
    const result = conn.query(sql);
    return result;
}).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
    return Promise.resolve(1);    // 次のFunctionに行く引数
}).then(function(res) {
    console.log(res);
    // 最後に必ずコネクションを開放する
    if (conn)
    {
        conn.end();
    }
});
```

# コネクションプールについて

```javascript:pool.js
/**
 * promise-mysql module
 * @type       {Object}
 */
const mysql = require('/usr/local/lib/node_modules/promise-mysql');
/**
 * table name
 * @type       {string}
 */
const TABLE_NAME = 'node_mysql_test';
/**
 * connection
 * @type       {Object}
 */
let conn;
/**
 * pool
 * @type       {Object}
 */
let pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs',
    connectionLimit: 10,
})

pool.getConnection().then(function(connect) {
    console.log('promise-mysql connect.');
    conn = connect;
    // console.log(conn);
    const result = conn.beginTransaction();
    return result;
}).then(function(res) {
    console.log(res);
    let sql = 'SELECT * FROM ' + TABLE_NAME;
    return conn.query(sql);
}).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
    // insert data
    let sql = 'INSERT INTO ' + TABLE_NAME + ' SET ?';
    let inserts = {name: '最上', age: 52};
    const result = conn.query(sql, inserts);
    return result;
}).then(function(res) {
    console.log('INSERT is Success!');
    console.log(res);
    // const result = conn.rollback();    // ここでロールバック発動すれば実行されずに終わる
    const result = conn.commit();
    return result;
}).then(function(res) {
    console.log('COMMIT Success!');
    console.log(res);
    // select data
    let sql = 'SELECT * FROM ' + TABLE_NAME;
    const result = conn.query(sql);
    return result;
}).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
    return Promise.resolve(1);    // 次のFunctionに行く引数
}).then(function(res) {
    console.log(res);
    // 最後に必ずコネクションを開放する
    // console.log(conn);
    // if (conn && conn.end)
    // {
    //     conn.end();
    // }
    pool.releaseConnection(conn);
}).catch(function(err) {
    console.log('catch error.');
    done(err);
});

```

poolの場合はpool.getConnection()で始まりpool.releaseConnection(connection)で終わる。

# async/awaitで書けないの？

書けます。むしろこっちのほうがスマート。

```javascript:async_await.js
async mysqlSelect()
{
    let conn = await mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: '',
        database: 'nodejs'
    });

    let sql = 'SELECT * FROM ' + TABLE_NAME;
    const result = await conn.query(sql);
    console.log(result);
}
```


# おわりに
この記事はまだ更新予定です。
また、情報があればコメントにていただけますと幸いです。
よろしくお願いいたします。