Edited at

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



おわりに

この記事はまだ更新予定です。

また、情報があればコメントにていただけますと幸いです。

よろしくお願いいたします。