はじめに
どうも、また私です。
今度は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);
}
おわりに
この記事はまだ更新予定です。
また、情報があればコメントにていただけますと幸いです。
よろしくお願いいたします。