MySql(MaraiaDb)과 연동하기
MySql(MaraiaDb)과 연동하기
Install
여기서는 mysql 이라는 package 에 대한 설명입니다.
npm i mysql
Connection
하나의 connection 만 생성 하여 전체 query 수행(transaction이 가능)
mysql.createConnection
기존 pool에서 connection을 제어 (속도가 빠르다. 추천)
mysql.createPool
mysql.createPool 에 대한 예제들
sample
var mysql = require('mysql');
var pool = mysql.createPool('mysql://localhost');
inTransaction(pool, function(db, next) {
db.query("DELETE * FROM stuff", function(err) {
if (err) return next(err);
db.query("INSERT INTO stuff VALUES (1,2,3)", function(err) {
return next(err);
});
});
}, function(err) {
console.log("All done, transaction ended and connection released");
});
/**
* Convenience wrapper for database connection in a transaction
*/
function inTransaction(pool, body, callback) {
withConnection(pool, function(db, done) {
db.beginTransaction(function(err) {
if (err) return done(err);
body(db, finished)
})
// Commit or rollback transaction, then proxy callback
function finished(err) {
var context = this;
var args = arguments;
if (err) {
if (err == 'rollback') {
args[0] = err = null;
}
db.rollback(function() { done.apply(context, args) });
} else {
db.commit(function(err) {
args[0] = err;
done.apply(context, args)
})
}
}
}, callback)
}
/**
* Convenience wrapper for database connection from pool
*/
function withConnection(pool, body, callback) {
pool.getConnection(function(err, db) {
if (err) return callback(err);
body(db, finished);
function finished() {
db.release();
callback.apply(this, arguments);
}
})
}
sample 1
require('dotenv').config();
const mysql = require('mysql');
const pool = mysql.createPool(
{
host: process.env.DB_HOST || '127.0.0.1',
user: process.env.DB_USER || 'db_user',
password: process.env.DB_PASSWORD || 'db_password',
database: process.env.DB_DATABASE || 'database',
multipleStatements: true
}); // [실서버용]
function query(sql, params, callback) {
if (typeof params === 'function') {
callback = params;
params = [];
}
pool.getConnection((err, connection) => {
if (err) {
return callback(err);
}
connection.query(sql, params, (err, results) => {
connection.release(); // always put connection back in pool after last query
if (err) {
return callback(err);
}
callback(null, results);
});
});
}
sample 2
require('dotenv').config();
const express = require('express');
const app = express();
const bodyParser = require('body-parser');
const http = require('http').createServer(app);
const path = require('path');
const mysql = require('mysql');
const pool = mysql.createPool(
{
host: process.env.DB_HOST || '127.0.0.1',
user: process.env.DB_USER || 'db_user',
password: process.env.DB_PASSWORD || 'db_password',
database: process.env.DB_DATABASE || 'database',
multipleStatements: true
}); // [실서버용]
function query(sql, params, callback) {
if (typeof params === 'function') {
callback = params;
params = [];
}
pool.getConnection((err, connection) => {
if (err) {
return callback(err);
}
connection.query(sql, params, (err, results) => {
connection.release(); // always put connection back in pool after last query
if (err) {
return callback(err);
}
callback(null, results);
});
});
}
// query('SELECT count(*) as cnt FROM withdrawals WHERE status=? AND deleted_at is null', ['R'], (err, result) => {
// if (err) { console.log(err); }
// console.log(result[0]);
// });
app.use(express.static(path.join(__dirname, '/public')));
app.use(bodyParser.urlencoded({extended: true}));
app.set('view engine', 'pug'); // jade engin 사용
app.set('views', './7-mysql/public') // public 경로 설정
app.route('/users')
.get((req, res) => {
query('SELECT * FROM users order by id desc ', [], (err, rows) => {
if (err) { console.log(err); }
console.log(rows);
console.log(rows.length);
// res.sendFile(path.join(__dirname, '/public/list.html'), result);
res.render('list', {rows});
// res.render(path.join(__dirname, '/public/list'), result);
});
});
app.route('/write')
.get((req, res) => {
res.sendFile(path.join(__dirname, '/public/write.html'));
})
.post((req, res, next) => {
const email = req.body.email;
const name = req.body.name;
const password = req.body.password;
query('INSERT INTO users (email, name, password) values (?, ?, ?) ', [email, name, password], (err, result) => {
if (err) { console.log(err); }
});
res.redirect('/users');
});
app.route('/view/:id')
.get((req, res) => {
const id = req.params.id;
query('SELECT * FROM users where id = ?', [id], (err, row) => {
if (err) { console.log(err); }
console.log(row);
res.render('view', {row: row[0]});
});
});
app.route('/update/:id')
.get((req, res) => {
const id = req.params.id;
query('SELECT * FROM users where id = ? ', [id], (err, rows) => {
if (err) { console.log(err); }
res.render('update', {row: rows[0]});
});
})
.post((req, res) => {
const id = req.params.id;
const email = req.body.email;
const name = req.body.name;
const password = req.body.password;
console.log(id, email, name, password);
query('UPDATE users set email = ?, name = ?, password = ? where id = ? ', [email, name, password, id], (err) => {
if (err) { console.log(err); }
res.redirect('/users');
});
});
app.route('/delete/:id')
.get((req, res) => {
const id = req.params.id;
query('DELETE FROM users where id = ?', [id], (err, row) => {
if (err) { console.log(err); }
res.redirect('/users');
});
})
.post((req, res) => {
});
http.listen(3000, () => {
console.log('listening on *:3000');
});
sample 3
require('dotenv').config();
const mysql = require('mysql');
const pool = mysql.createPool(
{
host: process.env.DB_HOST || '127.0.0.1',
user: process.env.DB_USER || 'db_user',
password: process.env.DB_PASSWORD || 'db_password',
database: process.env.DB_DATABASE || 'database',
multipleStatements: true
}); // [실서버용]
function query(sql, params, callback) {
if (typeof params === 'function') {
callback = params;
params = [];
}
pool.getConnection((err, connection) => {
if (err) {
return callback(err);
}
connection.query(sql, params, (err, results) => {
connection.release(); // always put connection back in pool after last query
if (err) {
return callback(err);
}
callback(null, results);
});
});
}
query('SELECT count(*) as cnt FROM bbs order by id desc ', [], (err, result) => {
if (err) { console.log(err); }
console.log(result[0]);
});
mysql.createConnection 에 대한 예제들
sample 1
var mysql = require('mysql');
var connection = mysql.createConnection(
{
host : 'localhost',
user : 'YOUR_USERNAME',
password : 'YOUR_PASSWORD',
database : 'DB_NAME'
}
);
connection.connect(function(err) {
if (err) {
console.error('error connecting: ' + err.stack);
return;
}
console.log('connected as id ' + connection.threadId);
});
/* Begin transaction */
connection.beginTransaction(function(err) {
if (err) { throw err; }
connection.query('INSERT INTO names SET name=?', "sameer", function(err, result) {
if (err) {
connection.rollback(function() {
throw err;
});
}
var log = result.insertId;
connection.query('INSERT INTO log SET logid=?', log, function(err, result) {
if (err) {
connection.rollback(function() {
throw err;
});
}
connection.commit(function(err) {
if (err) {
connection.rollback(function() {
throw err;
});
}
console.log('Transaction Complete.');
connection.end();
});
});
});
});
/* End transaction */
sample 2
function getClient(runner, callback) {
doIt();
function doIt() {
function rollback(err) {
con.rollback(function(){
console.log("rollbacked");
console.log(err);
});
//if (err.code === '40P01') {
// console.log('Warning: Retrying deadlocked transaction..');
// return doIt();
//}
callback(err);
}
con.beginTransaction(function(err) {
if (err){
return rollback(err);
}
runner(con, function (err, data) {
if (err){
return rollback(err);
}
con.commit(function (err) {
if (err){
return rollback(err);
};
//done();
callback(null, data);
});//con.commit(function (err) {
});//runner(con, function (err, data) {
});//con.beginTransaction(function(er) {
}//function doIt() {
}//function getClient(runner, callback) {