MySQL과 sequelize를 활용하여 데이터베이스 설계하기

hoon·2023년 6월 8일
Sequelizemysql데이터베이스프로젝트
1

이제 프로젝트의 회원가입과 로그인의 프론트엔드 파트가 어느정도 마무리 되었기 때문에 데이터베이스를 통해서 사용자의 정보를 저장할 필요가 있다. 따라서 이번에는 데이터베이스를 설계하는 방법에 대해서 알아보자

데이터베이스를 설계하는 과정은 크게 다음과 같다.

  1. 요구 분석: 개발하려는 시스템의 데이터 관리 요구사항을 이해하고 분석하는 단계이다. 이 과정에서 시스템이 어떤 데이터를 관리해야 하는지, 그리고 어떤 형태로 데이터를 저장하고 처리해야 하는지를 파악한다.
  2. 개념적 설계: 요구 분석의 결과를 기반으로 데이터베이스의 개념적인 구조를 설계하는 단계이다. 이 과정에서 일반적으로 ER(Entity-Relationship) 다이어그램을 사용하여 개체, 속성, 그리고 관계를 표현한다.
  3. 논리적 설계: 개념적 설계의 결과를 바탕으로 데이터베이스 관리 시스템(DBMS)이 이해할 수 있는 논리적인 데이터 모델로 변환하는 과정이다. 이 과정에서 테이블, 필드, 데이터 타입, 키, 관계 등을 정의하게 된다. 이때, Sequelize와 같은 ORM(Object-Relational Mapping) 도구를 사용하여 모델 정의와 관련된 마이그레이션 파일을 생성하는 것이 일반적이다.

이러한 과정을 거치면 데이터베이스는 시스템의 요구사항을 충족하면서도 효율적인 성능을 보장할 수 있는 구조를 가지게 된다. 또한, 이러한 설계 과정을 통해 데이터베이스의 정합성을 보장하고, 미래의 확장성을 고려한 설계를 할 수 있다.

1. 요구 분석

먼저, MySQL로 회원을 관리하기 위한 기본적인 데이터베이스 스키마를 설계해 보자

이를 위해 users라는 테이블을 만들고 일반적으로 회원 정보에는 아이디, 비밀번호, 이메일, 닉네임 등이 포함되므로 이를 고려해서 설계해 보자.

이 테이블은 다음과 같은 내용을 포함한다.

  • id: 각 사용자를 유일하게 식별하는 ID이다. 이 필드는 자동으로 증가하는 정수이다.
  • nickname: 사용자의 닉네임으로 이 필드는 20자 이내의 문자열이어야 한다.
  • email: 사용자의 이메일 주소이며, 이 필드는 50자 이내의 문자열이어야 한다. 또한, 이 필드는 테이블 내에서 유일해야 한다.
  • password: 사용자의 비밀번호이며, 이 필드는 해싱된 상태로 저장된다.
  • profileImage: 사용자의 프로필 이미지 URL이다. 이 필드는 필수 항목이 아니므로 NULL 값을 허용한다.
  • createdAt: 사용자가 만들어진 시각이며 기본값으로 생성 시각을 자동으로 저장한다.
  • updatedAt: 사용자 정보가 마지막으로 수정된 시각이며, 기본값으로 수정 시각을 자동으로 업데이트한다.
  • userType: 회원의 권한을 나타내는 필드로, 'admin'과 'user' 같은 문자열을 저장하거나, 권한의 복잡성에 따라 더 많은 옵션을 저장할 수 있다.
  • userStatus: 회원의 상태를 나타내는 필드로, 'active'와 'inactive', 'deleted'와 같은 상태를 저장하거나, 상황에 따라 더 많은 상태를 저장할 수 있다.

따라서 위에서 설명한 추가적인 필드들을 반영하여 테이블 스키마를 업데이트하면 다음과 같다

// models/User.js

"use strict";
const { Model, DataTypes } = require("sequelize");
module.exports = (sequelize) => {
  class User extends Model {
    static associate(models) {
      // Define associations here
    }
  }
  User.init(
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false,
      },
      nickname: {
        type: DataTypes.STRING(20),
        allowNull: false,
      },
      email: {
        type: DataTypes.STRING(50),
        allowNull: false,
        unique: true,
      },
      password: {
        type: DataTypes.STRING(255),
        allowNull: false,
      },
      profileImage: DataTypes.STRING(255),
      userType: {
        type: DataTypes.ENUM,
        values: ["admin", "user"],
        defaultValue: "user",
      },
      userStatus: {
        type: DataTypes.ENUM,
        values: ["active", "inactive", "deleted"],
        defaultValue: "active",
      },
    },
    {
      sequelize,
      modelName: "User",
      tableName: "users",
      timestamps: true,
      createdAt: "createdAt",
      updatedAt: "updatedAt",
    }
  );
  return User;
};

2. 개념적 설계

다음으로 MySQL Workbench를 사용해서 ER(Entity-Relationship) 다이어그램을 생성해 보자. ER 다이어 그램 생성 과정은 다음과 같다.

  1. MySQL Workbench 열기: 먼저 MySQL Workbench를 시작한다.
  2. 새로운 ER 모델 생성: 메뉴에서 "File" -> "New Model"을 선택하여 새 ER 모델을 생성한다.
  3. 다이어그램 추가: "Add Diagram" 버튼을 사용하여 추가 다이어그램을 생성할 수 있다.
  4. 테이블 추가: 도구 상자에서 "Table" 아이콘을 선택한 후 다이어그램 영역에 드래그하여 테이블을 추가한다.
  5. 테이블 수정: 테이블을 더블 클릭하여 테이블 구조를 수정한다. 여기에서 테이블 이름, 열(column), 인덱스, 외래키 등을 설정할 수 있다.
  6. 관계 설정: 도구 상자의 "1:1 Association", "1:n Non-Identifying Relationship", "1:n Identifying Relationship" 등의 아이콘을 사용하여 테이블 간의 관계를 설정할 수 있다. 관계를 설정하려면 해당 아이콘을 선택한 후, 관계를 설정하려는 두 테이블을 클릭하면 된다.
  7. 모델 저장: 모든 테이블과 관계가 다이어그램에 추가되었다면 "File" -> "Save Model As..."를 선택하여 ER 모델을 저장한다.
  8. 데이터베이스로 내보내기/동기화: 만들어진 ER 모델을 실제 MySQL 데이터베이스로 내보내거나 동기화하려면 "Database" 메뉴에서 "Forward Engineer..." 또는 "Synchronize Model..."를 선택한다.

users와 cafes테이블을 각각 만들고 이 테이블들을 연결하는 users_cafes 테이블을 다대다 관계로 연결하였다.

3. 논리적 설계 - Sequelize와 같은 ORM(Object-Relational Mapping) 도구를 사용하여 모델 정의와 관련된 마이그레이션 파일을 생성

Sequelize는 Node.js를 위한 ORM(Object-Relational Mapping)으로 JavaScript 객체와 데이터베이스 간의 관계를 매핑해주는 도구이다. ORM을 사용하는 이유는 코드의 가독성을 높이고 데이터베이스 스키마를 쉽게 변경하거나 업데이트할 수 있기 때문이다.

sequelize-cli를 통해 데이터베이스를 관리하는데 필요한 파일들에 대해서 알아보자

  1. config: 이 디렉터리에는 config.json 파일이 생성된다. 이 파일은 Sequelize에게 어떻게 데이터베이스에 연결할지에 대한 정보를 제공한다. 각 개발 환경(개발, 테스트, 배포)에 대한 데이터베이스 연결 설정을 별도로 할 수 있다.
  2. migrations: 이 디렉터리에는 데이터베이스 마이그레이션 파일들이 저장된다. 마이그레이션 파일은 데이터베이스의 스키마를 변경하는데 사용된다. 예를 들어, 테이블을 생성하거나 수정하거나 삭제하는 작업을 마이그레이션 파일로 작성할 수 있다.
  3. models: 이 디렉터리에는 Sequelize 모델 파일들이 저장된다. 각 파일은 데이터베이스의 테이블에 대응되며, 테이블의 스키마를 정의한다.
  4. seeders: 이 디렉터리에는 시더 파일들이 저장되며, 시더 파일은 데이터베이스에 초기 데이터를 삽입하는데 사용된다.

앞서 작성한 사용자의 스키마를 바탕으로 애플리케이션 코드를 작성해보자.

애플리케이션 코드는 애플리케이션의 비즈니스 로직을 구현하는 코드로 데이터베이스 모델을 사용하여 CRUD(Create, Read, Update, Delete) 연산을 수행하는 코드를 작성하는 것을 포함한다. controllers/userController.js파일에서 사용자의 CRUD 연산 코드를 작성해 보자.

// controllers/userController.js

const { User } = require("../models");

// 모든 사용자를 가져오는 함수
exports.getAllUsers = async (req, res) => {
  try {
    const users = await User.findAll();
    res.status(200).json(users);
    console.log(req.body);
  } catch (error) {
    console.error(error);
    res.status(400).json({ message: "사용자를 불러오는 중 오류가 발생했습니다" });
  }
};

// 특정 사용자를 가져오는 함수
exports.getUser = async (req, res) => {
  try {
    const user = await User.findByPk(req.params.id);
    if (!user) return res.status(404).json({ message: "사용자를 찾을 수 없습니다" });
    res.status(200).json(user);
    console.log(req.body);
  } catch (error) {
    console.error(error);
    res.status(400).json({ message: "사용자를 불러오는 중 오류가 발생했습니다" });
  }
};

// 새로운 사용자를 생성하는 함수
exports.createUser = async (req, res) => {
  try {
    const newUser = await User.create(req.body);
    res.status(201).json(newUser);
    console.log(req.body);
  } catch (error) {
    console.error(error);
    res.status(400).json({ message: "사용자를 생성하는 중 오류가 발생했습니다" });
  }
};

// 사용자 정보를 수정하는 함수
exports.updateUser = async (req, res) => {
  try {
    const user = await User.update(req.body, {
      where: { id: req.params.id },
    });
    if (!user) return res.status(404).json({ message: "사용자를 찾을 수 없습니다" });
    res.status(200).json({ message: "사용자가 성공적으로 업데이트되었습니다" });
    console.log(req.body);
  } catch (error) {
    res.status(400).json({ message: "사용자를 업데이트하는 중 오류가 발생했습니다" });
  }
};

// 사용자를 삭제하는 함수
exports.deleteUser = async (req, res) => {
  try {
    const user = await User.destroy({
      where: { id: req.params.id },
    });
    if (!user) return res.status(404).json({ message: "사용자를 찾을 수 없습니다" });
    res.status(200).json({ message: "사용자가 성공적으로 삭제되었습니다" });
    console.log(req.body);
  } catch (error) {
    console.error(error);
    res.status(400).json({ message: "사용자를 삭제하는 중 오류가 발생했습니다" });
  }
};

해당 코드는 사용자 데이터를 관리하기 위한 Controller의 일부를 나타낸다. 여기서는 User 모델에 대해 CRUD(Create, Read, Update, Delete) 연산을 수행하는 함수들이 정의되어 있다. 각 함수는 HTTP 요청을 받아 알맞은 데이터베이스 작업을 수행하고, 그 결과를 클라이언트에게 응답으로 보내준다.

이런 컨트롤러 함수를 정의한 후, 이 함수들을 Express 라우트와 연결하면 완성된다. 이제 라우팅 및 미들웨어 설정을 해보자

// routes/userRoutes.js

const express = require('express');
const userController = require('../controllers/userController');
const router = express.Router();

router
  .route('/')
  .get(userController.getAllUsers)
  .post(userController.createUser);

router
  .route('/:id')
  .get(userController.getUser)
  .put(userController.updateUser)
  .delete(userController.deleteUser);

module.exports = router;

CRUD 연산을 위한 라우트 정의는 다음과 같다.

  • router.route('/:id')는 URL에서 :id 부분에 동적인 값을 받을 수 있게 해준다. 예를 들어, http://localhost:3000/users/1와 같이 요청하면, :id 부분에 1이라는 값이 들어가게 되며, 이 값을 req.params.id로 가져올 수 있다.
  • get(userController.getUser)는 특정 사용자를 가져오는 요청을 처리한다.
  • put(userController.updateUser)는 특정 사용자의 정보를 업데이트하는 요청을 처리한다.
  • delete(userController.deleteUser)는 특정 사용자를 삭제하는 요청을 처리한다.

이렇게 각 컨트롤러는 해당하는 모델에 대한 CRUD 연산을 정의하고, 각 라우트는 해당 연산을 특정 URL 경로에 바인딩한다. 이렇게 하면 클라이언트가 특정 URL에 HTTP 요청을 보내면, 그 요청이 알맞은 함수에 의해 처리되어 적절한 응답을 보내게 된다.

이렇게 작성한 코드는 서버를 구동하는 app.js 파일에서 불러와 사용해야 한다. 이렇게 서버를 구동하면 사용자와 카페에 대한 API 엔드포인트가 생성되고, 이를 통해 데이터베이스와 상호작용할 수 있다.

// app.js

const express = require("express");
const app = express();
const userRoutes = require("./routes/userRoutes");
const cafeRoutes = require("./routes/cafeRoutes")
const PORT = process.env.PORT || 8000;

// JSON 데이터 파싱을 위한 미들웨어
app.use(express.json());

// 라우팅 설정
app.use("/", cafeRoutes);
app.use("/users", userRoutes);

app.listen(PORT, () => {
  console.log(`서버가 ${PORT} 포트에서 실행 중입니다.`);
});

module.exports = app;

마지막으로 sequelize-cli를 이용하여 앞서 작성한 스키마를 기반으로 실제 테이블을 생성해보자

마이그레이션 파일을 만들기 위해서는 Sequelize CLI를 사용하고 다음 명령어를 실행하면 된다.

npx sequelize-cli migration:generate --name create_users

생성된 마이그레이션 파일에 미리 설계한 스키마를 바탕으로 다음과 같이 작성한다.

'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      nickname: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING
      },
      password: {
        type: Sequelize.STRING
      },
      profileImage: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      userType: {
        type: Sequelize.ENUM('admin', 'user'),
        defaultValue: 'user'
      },
      userStatus: {
        type: Sequelize.ENUM('active', 'inactive', 'deleted'),
        defaultValue: 'active'
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};

마이그레이션 파일을 작성한 후, 다음 명령어를 실행하여 이 마이그레이션을 데이터베이스에 적용할 수 있다.

npx sequelize-cli db:migrate

이것으로 데이터베이스에 'Users' 테이블이 생성된다.

cafes 테이블과 users_cafes 테이블에 대해서도 동일하게 생성하면 MySQL 워크벤치에 다음과 같이 테이블이 생성된 모습을 볼 수 있다.

마지막으로 postman에서 내가 생성한 users 테이블이 정상적으로 HTTP 요청을 처리하고 있는지 테스트 해보자.

URL에http://localhost8000/users 로 데이터를 입력하고 POST 요청을 해보자.

MySQL 워크벤치에서 해당 데이터가 추가된 것을 확인할 수 있다.

profile
hoon
프론트엔드 학습 과정을 기록하고 있습니다.
이전 포스트

Redux Toolkit을 사용하여 모달 상태 관리하기

다음 포스트

로그인 및 회원가입 시스템 개요[사용자인증1]

0개의 댓글