Node.js/Sequelize

[ORM] ๐Ÿ“š ์‹œํ€„๋ผ์ด์ฆˆ - ๋ชจ๋ธ INDEX / FULLTEXT ์„ค์ •ํ•˜๊ธฐ

์ธํŒŒ_ 2022. 7. 19. 06:00

sequelize-index-fulltext

Sequelize ์ธ๋ฑ์Šค ์„ค์ •ํ•˜๊ธฐ

์‹œํ€„๋ผ์ด์ฆˆ ๋ชจ๋ธ์˜ ํ…Œ์ด๋ธ” ์˜ต์…˜ ์ •ํ•˜๋Š” ๊ฐ์ฒด ๋ธ”๋ก์— indexes ๋กœ ๋ฐฐ์—ด์„ ์„ ์–ธํ•ด์ฃผ๋ฉด ๋œ๋‹ค.

const Sequelize = require('sequelize');

module.exports = class User extends Sequelize.Model {
   //* ํ…Œ์ด๋ธ” ํ•„๋“œ ์„ค์ •
   static init(sequelize) {
      return super.init(
         {
            //& ์‹œํ€„๋ผ์ด์ฆˆ๋Š” id ์ž๋™ ์ƒ์„ฑ (auto_increament + Primary ์ธ๋ฑ์Šค)
            // id : { ... }

            email: {
               type: Sequelize.STRING(40),
               allowNull: true, 
               unique: true, // ์œ ๋‹ˆํฌ ๋ณด์กฐ ์ธ๋ฑ์Šค ์ƒ์„ฑ
            },

            nick: {
               type: Sequelize.STRING(15),
               allowNull: false,
            },

            password: {
               type: Sequelize.STRING(100), 
            },
         },
         {
            sequelize, 
            timestamps: true, // createdAt, updatedAt ์ž๋™ ์ƒ์„ฑ
            underscored: false, // camel ์Šคํƒ€์ผ๋กœ
            modelName: 'User', // ๋ชจ๋ธ๋ช…
            tableName: 'users', // ํ…Œ์ด๋ธ”๋ช…
            paranoid: true, // deletedAt ์ž๋™ ์ƒ์„ฑ
            charset: 'utf8', // ํ•œ๊ธ€ ์ธ์ฝ”๋”ฉ ์„ค์ •
            collate: 'utf8_general_ci',
            indexes: [ // ์ธ๋ฑ์Šค ์„ค์ •
               {
                  name: 'nick_index', // ์ด๋ฆ„์„ ์ง€์ •ํ•ด์ฃผ์ง€ ์•Š์œผ๋ฉด ๊ธฐ๋ณธ ์ธ๋ฑ์Šค๋ช…์€ [table]_[fields]
                  fields: ['nick'], // nick์— ์ผ๋ฐ˜ ๋ณด์กฐ ์ธ๋ฑ์Šค ์ƒ์„ฑ
               },
            ],
         },
      );
   }

   //* ํ…Œ์ด๋ธ” ๊ด€๊ณ„ ์„ค์ •
   static associate(db) {
     // ...
   }
};

 

์ธ๋ฑ์Šค ์ •๋ณด๋ฅผ ๋ณด๋ฉด, users ํ…Œ์ด๋ธ”์— ์ด 3๊ฐœ์˜ ์ธ๋ฑ์Šค๊ฐ€ ๋“ฑ๋ก๋จ์„ ํ™•์ธ ํ•  ์ˆ˜ ์žˆ๋‹ค.

show index from users;

sequelize-index

 

์ด์™ธ์˜ ์‹œํ€„๋ผ์ด์ฆˆ ์ธ๋ฑ์Šค ์˜ต์…˜๋“ค์€ ๋‹ค์Œ๊ณผ ๊ฐ™๋‹ค.

๋‹ค์ค‘ ์ธ๋ฑ์Šค๋ฅผ ์„ค์ •ํ•  ์ˆ˜ ์žˆ์œผ๋ฉฐ, ์ธ๋ฑ์Šค ์•Œ๊ณ ๋ฆฌ์ฆ˜๋„ ์ •ํ• ์ˆ˜๋„ ์žˆ๋‹ค.

sequelize-index


Sequelize FULLTEXT ์„ค์ •ํ•˜๊ธฐ

ํ’€ ํ…์ŠคํŠธ ์ธ๋ฑ์Šค ์„ค์ •์€ ์œ„์˜ ์ธ๋ฑ์Šค ๋ฌธ๋ฒ•์—์„œ type: 'FULLTEXT' ๋ฅผ ์ง€์ •ํ•˜๋ฉด ๋œ๋‹ค.

const Sequelize = require('sequelize');

module.exports = class Post extends Sequelize.Model {
   static init(sequelize) {
      return super.init(
         {
            content: {
               type: Sequelize.STRING(200),
               allowNull: false,
            },

            img: {
               type: Sequelize.STRING(500),
               allowNull: true,
            },
         },
         {
            sequelize,
            timestamps: true,
            underscored: false,
            modelName: 'Post',
            tableName: 'posts',
            paranoid: false, 
            charset: 'utf8mb4', 
            collate: 'utf8mb4_general_ci',
            indexes: [ // ์ธ๋ฑ์Šค ์„ค์ •
               {
               	  // ์ธ๋ฑ์Šค ์ด๋ฆ„์€ ๋”ฐ๋กœ ์ง€์ •ํ•ด์ฃผ์ง€ ์•Š์•„, ์ธ๋ฑ์Šค๋ช…์€ [table]_[fields]
                  type: 'FULLTEXT', // ํ’€ํ…์ŠคํŠธ ์ธ๋ฑ์Šค ์„ค์ •
                  fields: ['content'],
               },
            ],
         },
      );
   }

   static associate(db) {
      //* Post(N) : User(1)
      //? db.Post.belongsto(db.User, { foreignKey: 'Userid', targetKey: 'id' })
      db.Post.belongsTo(db.User);
   }
};
show index from posts;

Sequelize FULLTEXT