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) {