Sequelize_사용법

공식 문서 참고

설치

npm install -g sequelize-cli
npm install sequelize --save
sequelize init # models, seeder, config 등 폴더 생성

데이터베이스 설정

// config/config.js
require('dotenv').config();

module.exports = {
  development: {
    username: process.env.USER_NAME,
    password: process.env.PASSWORD,
    database: process.env.DATABASE,
    host: process.env.HOST,
    dialect: 'mysql',
  },
  test: {
    username: process.env.USER_NAME,
    password: process.env.PASSWORD,
    database: process.env.DATABASE,
    host: process.env.HOST,
    dialect: 'mysql',
  },
  production: {
    username: process.env.USER_NAME,
    password: process.env.PASSWORD,
    database: process.env.DATABASE,
    host: process.env.HOST,
    dialect: 'mysql',
  },
};

User 스키마 생성

• 데이터 타입 : 참고

// models/user.js
module.exports = (sequelize, DataTypes) => {
  return sequelize.define(
    'user',
    {
      id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true,
      },
      userId: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true,
      },
      password: {
        type: DataTypes.STRING,
        allowNull: true,
      },
    },
    {
      timestamps: false,
      underscored: true,
    }
  );
};

모델 정의 & 관계 정의

• 1 : N 관계

  db.User.hasMany(db.Issue, { foriegnKey: 'user_id', sourceKeyL 'id' });
  db.Issue.belongsTo(db.User, { foriegnKey: 'user_id', targetKeyL 'id' });

• 1 : 1 관계

  db.User.hasOne(db.Issue, { foriegnKey: 'user_id', sourceKeyL 'id' });
  db.Issue.belongsTo(db.User, { foriegnKey: 'user_id', targetKeyL 'id' });

• N : M 관계

  db.User.belongsToMany(db.Issue, { through: db.IssueAssignee });
  db.Issue.belongsToMany(db.User, { through: db.IssueAssignee });

  • N : M 관계 테이블에서 데이터를 가져올 때

    const issues = Issue.findAll({
    	...
    });
    issues.forEach((issue) => {
    	const assignees = await issue.getUsers(); // getUsers() 함수 제공
    });

동기화

// app.js
const express = require('express');
const path = require('path');
const cookieParser = require('cookie-parser');
const logger = require('morgan');
const { sequelize } = require('./models');
const apiRouter = require('./routes/index');

const app = express();

sequelize.sync();

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, '../client/dist')));
app.use(passport.initialize());

app.use('/api', apiRouter);

module.exports = app;

CRUD

SELECT

User.findAll({
  attributes: ['id', 'user_id']
});
---------------------------------
User.findOne({
  attributes: ['id', 'user_id']
	where: {
		id: 5
	}
});

INSERT

User.create({
	user_id: 'id',
	password: 'password'
});

UPDATE

User.update({
	where: {
		id: 1
	},
	user_id: 'updatedId'
});

DELETE

User.destroy({
	where: {
		id: 1
	},
});