nodejs express mysql2 사용 예제

mariaDB에 접근하기위해서는 mysql 모듈을 사용하면 된다.

개인적으로 async/await 방식이 더 가독성이 좋다고 느껴져서 mysql2 모듈을 사용.

 

npm install mysql2 --save

mysql 모듈 : 콜백 기반, promise 지원 X

mysql2 모듈 : promise 지원, async/await 사용 가능

 

// mariadb.js

connection pool 사용, 쿼리 수행도중 에러 발생시 rollback.callback, callbackfail을 외부에서 주입받아 사용할 수 있게 코드 작성.

const mariaMyLoa = {
        host: '<hostName>',
        port: <portNumber>,
        user: '<DB UserName>',
        password: '<DB password>',
        database: '<DB Name>',
        dateStrings: 'date',
};

const maria = require('mysql2/promise');
const logger = require('../utils/Logger');
const poolMaria = maria.createPool(mariaMyLoa);

module.exports = {
	// single row insert, select 할때 사용.
    query: async (queryStr, pars, callback, callbackfail) => {
        let ret = {success: false};
        let conn = null;
        try{
            conn = await poolMaria.getConnection();
            await conn.beginTransaction();
            const [rows, fields] = await conn.query(queryStr, pars);
            ret = callback(rows);
            await conn.commit();
        } catch (error) {
            logger.log('error',`query err ${error}`);
            logger.log('error',`query queryStr ${JSON.stringify(queryStr)}`);
            logger.log('error',`query pars ${JSON.stringify(pars)}`);

            if(conn) await conn.rollback();
            if(callbackfail) ret = callbackfail();
            //throw err;
        } finally {
            if(conn) conn.release();
        }

        return ret;
    }, 
    ....
    // multi row insert 할때 사용.  ( 실제 DB에 파라미터 값을 배열의 형태로 전달해야함. )
    queryBulk: async (queryStr, pars, callback, callbackfail) => {
        let ret = {success: false};
        let conn = null;
        try{
            conn = await poolMaria.getConnection();
            await conn.beginTransaction();
            const [rows, fields] = await conn.query(queryStr, [pars], true);
            ret = callback(rows);
            await conn.commit();
        } catch (error) {
            logger.log('error',`queryBulk error ${error}`);
            logger.log('error',`queryBulk queryStr ${JSON.stringify(queryStr)}`);
            logger.log('error',`queryBulk pars ${JSON.stringify(pars)}`);

            if(conn) await conn.rollback();
            if(callbackfail) ret = callbackfail();
            //throw err;
        } finally {
            if(conn) conn.release();
        }

        return ret;
    }, 
    ...
}

사용 예시 - SELECT

 

const logger = require('../../../utils/Logger');
const mariaDB = require('../../../database/mariadb');

module.exports = {
    readMarketDBCategoryByCode: async (categoryCode) => {
        const queryStr = 'select * from market where CategoryCode=? order by RecentPrice desc';
        const pars = [categoryCode];
        let retQuery = {success: false, itemList: []};

        try { 
            retQuery = await mariaDB.query(queryStr, pars, (rows) => {
                let retCallback = {success: false, itemList: []};
                retCallback.success = true;
                if(rows.length > 0) retCallback.itemList = rows;

                return retCallback;
            }, null);
        } catch (error) {
            retQuery = {success: false, itemList: []};
            logger.log('error', `async readMarketDBCategoryByCode : ${error}`);
        }

        return retQuery;
    },
    ...
}

사용예시 - multi row insert

const logger = require('../../../utils/Logger');
const mariaDB = require('../../../database/mariadb');

module.exports = {
	saveCalendarContents: async (calendar) => {
        let pars = [];
        calendar.forEach((calendarContent) => {
            pars.push([
                calendarContent.CategoryName,
                calendarContent.ContentsName,
                calendarContent.ContentsIcon,
                calendarContent.MinItemLevel,
            ]);
        });

        let queryStr = "insert into calendarcontent(CategoryName, ContentsName, ContentsIcon, MinItemLevel)  VALUES?";
            queryStr += " on duplicate key update MinItemLevel=VALUES(MinItemLevel)";

        try {
            retData = await mariaDB.queryBulk(queryStr, pars, (rows) => {
                let retCallback = {success: false};
                if(rows.affectedRows > 0 || rows.insertId) {
                    retCallback.success = true;
                }
                return retCallback;
            }, null);
        } catch (error) {
            logger.log('error', `saverCalendarContents : ${error}`);
        } 
    },
}