Nodejs MySQL

MySQL

  • npm install mysql

Connection(์ปค๋„ฅ์…˜)

  • mysql.createConnection(DATABASE_CONFIG)
  • host,port : DBMS ์ฃผ์†Œ์™€ ํฌํŠธ(Default 3306)
  • user, password
  • database
  • multipleStatements : ๋™์‹œ์— ์—ฌ๋Ÿฌ SQL ์‹คํ–‰. ๋ณด์•ˆ์— ์ฃผ์˜
  • connectTimeout : DBMS ์—ฐ๊ฒฐ ํƒ€์ž„ ์•„์›ƒ ์‹œ๊ฐ„. Default 10,000 ms
  • connect : ์—ฐ๊ฒฐ
  • end : ์—ฐ๊ฒฐ ์ข…๋ฃŒ
  • query : SQL๋ฌธ ์‹คํ–‰์‹œ ์ปค๋„ฅ์…˜ ์ž๋™ ์—ฐ๊ฒฐ
connection.connect(function(err){
  if(err){
    console.error('error connecting'+err.stack);
    return;
  }
  connection.end();
})

Connection Pool

  • ๋‹ค์ˆ˜์˜ Connection ๊ด€๋ฆฌ ๊ธฐ๋ฒ•
  • Pool์—์„œ Connection ์–ป์–ด์„œ ์‚ฌ์šฉํ•˜๊ณ  Pool์— ๋ฐ˜๋‚ฉ
  • mysql.createPool(option)
  • waitForConnections : ํ’€์— ์—ฌ์œ  ์ปค๋„ฅ์…˜์ด ์—†๋Š” ๊ฒฝ์šฐ ๋Œ€๊ธฐ ์—ฌ๋ถ€
  • connectionLimit : ์ตœ๋Œ€ ์ปค๋„ฅ์…˜ ๊ฐœ์ˆ˜, ๊ธฐ๋ณธ 10๊ฐœ
var mysql = require('mysql');
 
var dbConfig = {
   host: 'localhost',
   user: 'root',
   password: '1234',
   port: 3306,
   database: 'nelp'
   connectionLimit : 50
};
 
var pool = mysql.createPool(dbConfig);
// Get Connection in Pool
pool.getConnection(function(err,connection){
  if(!err){
    //connected!
  }
  // ์ปค๋„ฅ์…˜์„ ํ’€์— ๋ฐ˜ํ™˜
  connection.release();
});

SQL ์‹คํ–‰

  • connection.query(sql,callback);
  • affectedRow : ์˜ํ–ฅ์„ ๋ฐ›์€ ์—ด์˜ ๊ฐœ์ˆ˜
  • insertID : ์ƒˆ๋กœ ์ถ”๊ฐ€ํ•œ ๊ฒฝ์šฐ Primary Key
  • changedRow : ๋ณ€๊ฒฝ๋œ ์—ด์˜ ์ˆ˜
  • placeholder

SQL Injection ๋ฐฉ์ง€

  • mysql.escape()
  • placeholder ์‚ฌ์šฉํ•˜๊ธฐ

Transcation

  • conn.beginTransaction(CB);
  • conn.commit(); : Transcation ๋ณ€๊ฒฝ ํ™•์ •
  • conn.rollback(); : Transcation ๋ณต๊ตฌ

Sequelize

  • ORM : ๊ฐ์ฒด์™€ ๋ชจ๋ธ์˜ Mapping
  • Promise ๊ธฐ๋ฐ˜
  • npm install sequelize

์‚ฌ์šฉํ•˜๊ธฐ

  • ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์—ฐ๊ฒฐ ์„ค์ •
  • ๋ชจ๋ธ ์„ค์ •
  • ๋ชจ๋ธ์„ ์ด์šฉํ•ด์„œ ๋ฐ์ดํ„ฐ ์ €์žฅ
  • ๋ชจ๋ธ์—์„œ ๋ฐ์ดํ„ฐ ์–ป์–ด์˜ค๊ธฐ
  • ๋ชจ๋ธ์„ ์ด์šฉํ•ด์„œ ๋ฐ์ดํ„ฐ ์ˆ˜์ •/์‚ญ์ œ

์—ฐ๊ฒฐ์„ค์ •

  • new Sequelize(uri, option)
  • dialect : ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ข…๋ฅ˜
  • host,port
  • pool : ์ปค๋„ฅ์…˜ ํ’€ ์„ค์ •

Local Connection

var Sequelize = require('sequelize');
var sequelize = new Sequelize('nelp', 'root', 'csedbadmin');

Remote Host Connection Pool

var Sequelize = require('sequelize');
var sequelize = new Sequelize('nelp', 'root', 'csedbadmin',{
  dialect:'mysql',
  host:'RDB_ADDRESS',
  port:3306,
 
  pool:{
    max:10,
    min:0,
    idle:10000
  }
});

Model Define

  • sequelize.define('name',{attributes}, {options})
var User = sequelize.define('user', {
        sso_id: {type: Sequelize.STRING, primaryKey: true},
        account_bank: {type: Sequelize.STRING},
        account_number: {type: Sequelize.STRING},
        name: {type: Sequelize.STRING},
        password: {type: Sequelize.STRING}
    }, {
        freezeTableName: true, // Table์ด๋ฆ„์ด ๋ณ€๊ฒฝ๋œ๋‹ค๋ฉด ์ด ์˜ต์…˜์„ ์ฃผ๋ฉด ํ•ด๊ฒฐํ•  ์ˆ˜ ์žˆ๋‹ค.
        timestamps: false,  //  timestamp Default Value๊ฐ€ true์ด๋ฏ€๋กœ ์ž๋™์œผ๋กœ ์ž…๋ ฅ๋˜๋Š” ๊ฒฝ์šฐ๊ฐ€ ์žˆ๋Š”๋ฐ false๋ฅผ ํ†ตํ•ด ์ œ๊ฑฐ ํ•  ์ˆ˜ ์žˆ๋‹ค.
    }
);

Model Create

User.create({
       sso_id: 'testNodejs',
       account_bank: 'testNodejs',
       account_number: 'testNodejs',
       name:'testNodejs',
       password:'testNodejs'
}).then(resolved,rejected);
  • ๊ฒฐ๊ณผ ํ™”๋ฉด

Model Read

User.findAll({
      attributes: ['sso_id','account_bank'],
      where:{
          sso_id:'testNodejs'
      }
 
}).then(resolved,rejected);

Model Update

User.update({
        sso_id: 'testNodejsUpdate',
    },{
        where:{
            sso_id:'testNodejs'
        }
}).then(resolved,rejected);

Model DELETE

User.destroy({
       where:{
           sso_id:'testNodejsUpdate'
       }
   }).then(resolved,rejected);