Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

はじめに

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

ここへ至る経緯はこっちから
Node.jsでMySQLを扱いたかったので独自目線で纏めた

簡単な動作確認

設定

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

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);
});
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

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

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

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書き方他にも

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);
});
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のときより全然見やすいです。

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();
    }
});

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

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で書けないの?

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

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);
}

おわりに

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

aya_akatsuki
鯖屋と言う名の便利屋な気がする。たまに技術的問題にぶつかるとQiitaに現れます。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away