시퀄라이즈 (sequelize) 에서 쿼리에 주석 달기

조건: express 서버, sequelize 사용하여 DB 접속

 

서버 로직 테스트를 하던 도중 쿼리 로그는 보이는데 아래와 같이 쿼리가 여러개인 경우 어떤 위치에서 실행되는 쿼리인지 보기 어려운 상황이 발생했다.

Executing (default): SELECT `id`, `title`, `boardTypeCd`, `flex`, `color`, `startDate`, `endDate`, `createdAt`, JSON_LENGTH(replyJson) AS `replyCount`, (select name from users b where b.id = Board.CreaterId) AS `createrName`, (select name from users b where b.id = Board.UpdaterId) AS `updaterName` FROM `Boards` AS `Board` WHERE `Board`.`boardTypeCd` = 'notice' AND `Board`.`isDeleted` = false AND `Board`.`startDate` <= '2019-12-04' AND `Board`.`endDate` >= '2019-12-04' ORDER BY `Board`.`id` DESC LIMIT 5;
Executing (default): SELECT `id`, `title`, `boardTypeCd`, `flex`, `color`, `startDate`, `endDate`, `createdAt`, JSON_LENGTH(replyJson) AS `replyCount`, (select name from users b where b.id = Board.CreaterId) AS `createrName`, (select name from users b where b.id = Board.UpdaterId) AS `updaterName` FROM `Boards` AS `Board` WHERE `Board`.`boardTypeCd` = 'help' AND `Board`.`isDeleted` = false AND `Board`.`startDate` <= '2019-12-04' 
AND `Board`.`endDate` >= '2019-12-04' ORDER BY `Board`.`id` DESC LIMIT 5;
Executing (default): SELECT `UserEntity`.`id`, `UserEntity`.`userEntityStatusCd`, `UserEntity`.`inviteDate`, `UserEntity`.`createdAt`, `UserEntity`.`updatedAt`, `UserEntity`.`UserId`, `UserEntity`.`EntityId`, `UserEntity`.`DeptId`, `UserEntity`.`AuthorityId`, `Entity`.`id` AS `Entity.id`, `Entity`.`name` AS `Entity.name`, `Dept`.`id` AS `Dept.id`, `Dept`.`name` AS `Dept.name` FROM `UserEntities` AS `UserEntity` INNER JOIN `Entities` AS `Entity` ON `UserEntity`.`EntityId` = `Entity`.`id` AND `Entity`.`isDeleted` = 
false INNER JOIN `Depts` AS `Dept` ON `UserEntity`.`DeptId` = `Dept`.`id` AND `Dept`.`isDeleted` = false WHERE `UserEntity`.`UserId` = 6 AND `UserEntity`.`userEntityStatusCd` = 'confirmed';

어떻게 할까 고민하다 실행문 앞에 주석을 넣기로 결정하고 시퀄라이즈 모델 import 부분을 수정하여 사용자 function을 집어넣어 주었다.

 

* 원본

const fs = require('fs')
const path = require('path')
const Sequelize = require('sequelize')
const config = require('../config/config')
const db = {}
require('sequelize-hierarchy')(Sequelize)

const sequelize = new Sequelize(
  config.db.database,
  config.db.user,
  config.db.password,
  config.db.options
  // { operatorsAliases: false }
)

fs
  .readdirSync(path.join(__dirname, 'common'))
  .forEach((file) => {
    let model = sequelize.import(path.join(__dirname, 'common', file))
    db[model.name] = model
  })


Object.keys(db).forEach(function (modelName) {
  if ('associate' in db[modelName]) {
    db[modelName].associate(db)
  }
})

db.sequelize = sequelize
db.Sequelize = Sequelize

module.exports = db

* 수정본

const fs = require('fs')
const path = require('path')
const Sequelize = require('sequelize')
const config = require('../config/config')
const db = {}
require('sequelize-hierarchy')(Sequelize)

const sequelize = new Sequelize(
  config.db.database,
  config.db.user,
  config.db.password,
  config.db.options
  // { operatorsAliases: false }
)

function prepareComment (comment) {
  var sanitizedComment = String(comment)
    .replace(/[\r\n]+/g, ' ') // Strip new lines.
    .replace(/\/\*|\*\\/g, ' ') // Strip comments.
  return ('/* ' + sanitizedComment + ' */ ')
}

fs
  .readdirSync(path.join(__dirname, 'common'))
  .forEach((file) => {
    let model = sequelize.import(path.join(__dirname, 'common', file))
    db[model.name] = model
  })

fs
  .readdirSync(path.join(__dirname, 'finance'))
  .forEach((file) => {
    let model = sequelize.import(path.join(__dirname, 'finance', file))
    db[model.name] = model
  })

fs
  .readdirSync(path.join(__dirname, 'treasure'))
  .forEach((file) => {
    let model = sequelize.import(path.join(__dirname, 'treasure', file))
    db[model.name] = model
  })

fs
  .readdirSync(path.join(__dirname, 'tax'))
  .forEach((file) => {
    let model = sequelize.import(path.join(__dirname, 'tax', file))
    db[model.name] = model
  })

Object.keys(db).forEach(function (modelName) {
  if ('associate' in db[modelName]) {
    db[modelName].associate(db)
  }
})

const queryGenerator = sequelize.getQueryInterface().QueryGenerator
const oldSelectFromTableFragment = queryGenerator.selectFromTableFragment
queryGenerator.selectFromTableFragment = function (options, model, attributes, tables, mainTableAs) {
  var baseFragment = oldSelectFromTableFragment.apply(this, arguments)
  // Prepend SQL comment if option is present.
  var fragment = options.comment ? (prepareComment(options.comment) + baseFragment) : baseFragment
  return (fragment)
}

db.sequelize = sequelize
db.Sequelize = Sequelize

module.exports = db

수정본에서 보면 prepareComment 라는 function을 추가하고 시퀄라이즈가 가지고 있던 QueryGernerator를 수정하여 comment 속성이 있으면 시퀄라이즈가 만든 쿼리문 앞에 prepareComment를 사용하여 주석을 추가하도록 하였다.

 

그리고 쿼리를 정의한 부분에 가서 실제 사용할 comment를 추가해 주었다.

const { Board } = require('../models')

module.exports = {
  async getBoardTitles (req, res, next) {
    try {
      res.send(await Board.findAll({
        attributes: 속성값 array,
        where: 조건값 object,
        comment: 'BoardController/getBoardTitles'
      }))
    } catch (e) {
      에러처리
    }
  }
}

이렇게 처리하고 난뒤 아래와 같이 보이게 되었다.

 

Executing (default): /* BoardController/getBoardTitles */ SELECT `id`, `title`, `boardTypeCd`, `flex`, `color`, `startDate`, `endDate`, `createdAt`, JSON_LENGTH(replyJson) AS `replyCount`, (select name from users b where b.id = Board.CreaterId) AS `createrName`, (select name from users b where b.id = Board.UpdaterId) AS `updaterName` FROM `Boards` AS `Board` WHERE `Board`.`boardTypeCd` = 'notice' AND `Board`.`isDeleted` = false AND `Board`.`startDate` <= '2019-12-04' AND `Board`.`endDate` >= '2019-12-04' ORDER BY `Board`.`id` DESC LIMIT 5;
Executing (default): /* BoardController/getBoardTitles */ SELECT `id`, `title`, `boardTypeCd`, `flex`, `color`, `startDate`, `endDate`, `createdAt`, JSON_LENGTH(replyJson) AS `replyCount`, (select name from users b where b.id = Board.CreaterId) AS `createrName`, (select name from users b where b.id = Board.UpdaterId) AS `updaterName` FROM `Boards` AS `Board` WHERE `Board`.`boardTypeCd` = 'help' AND `Board`.`isDeleted` = false AND `Board`.`startDate` <= '2019-12-04' 
AND `Board`.`endDate` >= '2019-12-04' ORDER BY `Board`.`id` DESC LIMIT 5;
Executing (default): /* AuthenticationController/getUserEntity */ SELECT `UserEntity`.`id`, `UserEntity`.`userEntityStatusCd`, `UserEntity`.`inviteDate`, `UserEntity`.`createdAt`, `UserEntity`.`updatedAt`, `UserEntity`.`UserId`, `UserEntity`.`EntityId`, `UserEntity`.`DeptId`, `UserEntity`.`AuthorityId`, `Entity`.`id` AS `Entity.id`, `Entity`.`name` AS `Entity.name`, `Dept`.`id` AS `Dept.id`, `Dept`.`name` AS `Dept.name` FROM `UserEntities` AS `UserEntity` INNER JOIN `Entities` AS `Entity` ON `UserEntity`.`EntityId` = `Entity`.`id` AND `Entity`.`isDeleted` = 
false INNER JOIN `Depts` AS `Dept` ON `UserEntity`.`DeptId` = `Dept`.`id` AND `Dept`.`isDeleted` = false WHERE `UserEntity`.`UserId` = 6 AND `UserEntity`.`userEntityStatusCd` = 'confirmed';