node.js Sequelize

db.config.js

module.exports = {
  HOST: "localhost",
  USER: "testUser",
  PASSWORD: "testUser!1234",
  DB: "testUser",
  PORT: 3306,
};

 

sequelize.js

const Sequelize = require("sequelize");
const dbConfig = require("../config/db.config.js");

// initialze an instance of Sequelize
const sequelize = new Sequelize({
  host: dbConfig.HOST,
  database: dbConfig.DB,
  username: dbConfig.USER,
  password: dbConfig.PASSWORD,
  dialect: "mysql",
});

// check the databse connection
sequelize
  .authenticate()
  .then(() => console.log("Connection has been established successfully."))
  .catch((err) => console.error("Unable to connect to the database:", err));

module.exports = sequelize;

 

model.js - 1

const Sequelize = require("sequelize");
const sequelize = require("../sequelize.js");
const api = require("../../config/api.config");
const Op = Sequelize.Op;
const User = require("./user");
/*
  autoIncrement - 시퀀스 +1
  timestamps - createAt, modifyAt 컬럼 자동 생성 여부
  freezeTableName - 테이블명 복수형으로 치환 여부
                  ex) freezeTableName가 true이면, 테이블명이 user면 users로 생성됨
 */
const ChatRoom = sequelize.define("ROOM", {
  CR_SEQ: {
    type: Sequelize.BIGINT
    ,primaryKey: true
    ,autoIncrement: true
  }
  ,CR_NAME: {
    type: Sequelize.STRING
  }
  ,CR_TOPIC: {
    type: Sequelize.STRING
  }
  ,PUB_YN: {
    type: Sequelize.STRING
  }
  ,CR_PASSWORD: {
    type: Sequelize.STRING
  }
  ,REG_ID: {
    type: Sequelize.STRING
  }
  ,REG_DT: {
    type: Sequelize.DATE
    ,defaultValue: sequelize.NOW/* literal('now()')*/
  }
  ,LAST_DT: {
    type: Sequelize.DATE
  }
  ,MOD_DT: {
    type: Sequelize.DATE
  }
  ,CLOSE_DT: {
    type: Sequelize.DATE
  }
}
,{timestamps: false,freezeTableName: true}
);

const ChatUser = sequelize.define("USER", {
      USER_ID: {
        type: Sequelize.STRING
        ,primaryKey: true
      }
      ,TALK_TIME: {
        type: Sequelize.BIGINT
      }
      ,TALK_NO: {
        type: Sequelize.BIGINT
      }
      ,REG_DT: {
        type: Sequelize.DATE
        /*,defaultValue: sequelize.literal('now()')*/
        ,defaultValue: sequelize.NOW
      }
      ,LAST_DT: {
        type: Sequelize.DATE
      }
      ,CLOSE_DT: {
        type: Sequelize.DATE
      }
    }
    ,{timestamps: false ,freezeTableName: true}
);

/*
  hasMany - 1:N 관계정의
  belongsTo - 부
  as - 테이블 join시 alias 지정.
 */
ChatRoom.hasMany(ChatUser, {foreignKey:'CR_SEQ', id:'CR_SEQ', as: 'USER'});
ChatUser.belongsTo(ChatRoom, {foreignKey:'CR_SEQ'});
ChatUser.belongsTo(User, {foreignKey:'USER_ID'});

// create table with ChatRoom, ChatUser model
ChatRoom.sync()
  .then(() => console.log("ChatRoom table created successfully"))
  .catch((err) => console.log(err));
ChatUser.sync()
  .then(() => console.log("ChatUser table created successfully"))
  .catch((err) => console.log(err));

 

model.js - 2

/*
    전체 검색
    Param : offset, limit
*/
ChatRoom.getListRoom = async (obj) => {
  try{
    if(isEmpty(obj))
    {
      return api.parameterNull;
    }
    else
    {
      let listQuery = await getChatRoomListQuery(obj);
      let countQuery = await getChatRoomListCntQuery(obj);
      let chatRoom = await sequelize.query(listQuery, {type: Sequelize.QueryTypes.SELECT, nest: true});
          chatRoom = await convertChatRoomListData(chatRoom);
      let chatRoomListCount = await sequelize.query(countQuery, {type: Sequelize.QueryTypes.SELECT, nest: true});
      let pagingObj = {"total_count":chatRoomListCount[0].COUNT, "page_size":obj.page_size, "page_num":obj.page_num}
      return objectAssign(pagingObj, objectAssign(api.success, {"items":chatRoom}));

      /*let limit = isEmpty(obj.page_size) ? 1 : obj.page_size;
      let offset = ((isEmpty(obj.page_num) ? 1 : obj.page_num)-1)*limit;
      const chatRoom2 = await ChatRoom.findAll (
          {
            where: getChatListParamSet(obj)
            ,offset:offset
            , limit:limit
            , order:[['REG_DT','DESC']]
            , include:[{model: ChatUser, as:'AIAB_CR_USER', required: false}]
          }
      );
      let pagingObj = {"total_count":await ChatRoom.count({where: getChatListParamSet(obj)}), "page_size":obj.page_size, "page_num":obj.page_num}
      return objectAssign(pagingObj, objectAssign(api.success, {"items":chatRoom}));*/

    }
  } catch (e) {
    return objectAssign({"error":e.toString()}, api.fail) ;
  }
};

/*
    사용자 Leave
    Param : CR_SEQ, USER_ID 포함된 json object
*/
ChatRoom.outRoom = async (obj) => {
  try {
    if(isEmpty(obj)){
      return api.parameterNull;
    }else{
      const chatUser = await ChatUser.update({"CLOSE_DT": sequelize.fn('NOW')},{ where: {CR_SEQ: obj.cr_seq, "USER_ID": obj.user_id} });
      let chatRoomUser = await ChatUser.findAndCountAll({where:{CR_SEQ: obj.cr_seq, CLOSE_DT: null}});
      if(chatRoomUser.count == 0) {
        ChatRoom.update({"CLOSE_DT":sequelize.fn('NOW')},{ where: {CR_SEQ: obj.cr_seq} });
      }
      if(chatUser == 1){
        let userObj = {"user_id": obj.user_id};
        return objectAssign(userObj, api.success);
      }else{
        return objectAssign({"error": e.toString()}, api.fail) ;
      }
    }
  } catch (e) {
    return objectAssign({"error": e.toString()}, api.fail) ;
  }
};

/*
    사용자 LAST_DT UPDATE
    Param : CR_SEQ, USER_ID 포함된 json object
*/
ChatRoom.updateLastDt = async (obj) => {
  try {
    if(isEmpty(obj)){
      return api.parameterNull;
    }else{
      const chatUser = await ChatUser.update({"LAST_DT": sequelize.fn('NOW')
                                            , "TALK_NO": sequelize.literal("TALK_NO+1")
                                            , "TALK_TIME": sequelize.literal("TALK_TIME "+obj.running_time)}
                                           , { where: {CR_SEQ: obj.cr_seq, "USER_ID": obj.user_id} }).then((chatUser) => {
        let updateResult = ChatRoom.update({"LAST_DT": sequelize.fn('NOW')},{ where: {CR_SEQ: obj.cr_seq} });
        return updateResult;
      });
      if(chatUser == 1){
        let param = {"cr_seq":obj.cr_seq, "user_id": obj.user_id};
        return objectAssign(param, api.success);
      }else{
        return objectAssign({"error": e.toString()}, api.fail) ;
      }
    }
  } catch (e) {
    return objectAssign({"error": e.toString()}, api.fail) ;
  }
};

/*
    사용자 누석 시간 조회
    Param : CR_SEQ, USER_ID 포함된 json object
*/
ChatRoom.getUserTotalTime = async (obj) => {
    try{
        if(isEmpty(obj)){
            return api.parameterNull;
        }else{
            const timeInfo = await ChatUser.findAll(
                {
                    attributes: [[sequelize.fn('sum', sequelize.col('TALK_NO')), 'TALK_NO_SUM']
                                ,[sequelize.fn('sum', sequelize.col('TALK_TIME')), 'TALK_TIME_SUM']]
                    ,raw: true
                    ,where: paramNullCheck(obj)
                }
            );
            return objectAssign(api.success, {"items":timeInfo[0]});
        }
    } catch (e) {
        return objectAssign({"error":e.toString()}, api.fail) ;
    }
};

/*Parameter Null Check*/
const paramNullCheck = (obj) => {
  let returnObj = {};
  for(var key in obj){
    let value = obj[key];
    if(value != null && value != ""){
      returnObj[key.toUpperCase()] = value;
    }
  }
  return returnObj;
}