[가계부 만들기] Backend - Database #2

이전 포스팅에서 connection.py 파일을 통해서 mysql 서버에 접근하는 코드를 추가하였다.

이제 ORM을 사용하기 위해서 models.py 파일을 작성할 것이다. ORM에 대한 내용은 아래 링크에서 확인할 수 있다.

ORM(Object Relational Mapping)이 뭘까? 🤔

Fastapi에서는 sqlalchemy 라이브러리를 사용하고 있고 여기서 지원해주는 ORM 기능을 통해서 데이터베이스와 연결한다.

sqlalchemy는 기본적인 ORM을 제공해줌과 동시에 각 RDBMS 별 특성에 맞는 키워드나 기능을 추가로 지원해준다.

여기서는 mysql을 사용할 것이기 때문에 Column 형에서 mysql에서만 사용할 수 있는 타입을 사용하게 된다. 예를 들어서 unsigned smallint는 mysql에서만 사용하는 타입이므로 다른 DBMS에서는 지원하지 않는다. 여기서는 이 타입을 사용하는데 이걸 사용하기 위해서 sqlalchemy에서는 mysql용 column 형을 제공해준다.

먼저 상대에 Column 형으로 사용할 타입과 라이브러리 또는 DB 연결에서 사용할 변수 등을 import 하도록 한다.

from sqlalchemy import (
    Column,
    VARCHAR,
    DATETIME,
    DATE,
    CHAR,
    PrimaryKeyConstraint,
    UniqueConstraint,
    ForeignKeyConstraint,
)
from sqlalchemy.dialects.mysql import (
    TINYINT,
    SMALLINT,
    INTEGER,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func
from .connection import engine

Base = declarative_base()

앞의 포스팅에서 적용한 연결에 사용한 engine 변수를 import 하여서 해당 파일에서 사용하도록 한다.

그리고 각 ORM Class에서 넘겨줄 Base를 선언하도록 한다.

먼저 tb_members 테이블에 대해서 정의한다.

class Members(Base):
    __tablename__ = "tb_members"

    member_no = Column(SMALLINT(unsigned=True), nullable=False, autoincrement=True, comment="멤버번호")
    member_id = Column(VARCHAR(length=30), nullable=False, comment="사용자 아이디")
    member_pw = Column(VARCHAR(length=128), nullable=False, comment="사용자 패스워드")
    member_name = Column(VARCHAR(length=20), nullable=False, comment="사용자 이름")
    member_email = Column(VARCHAR(length=50), nullable=False, comment="이메일 주소")
    reg_dt = Column(DATETIME(timezone=False), nullable=False, server_default=func.now(), comment="생성일시")
    upd_dt = Column(DATETIME(timezone=False), nullable=False, server_default=func.now(), onupdate=func.now(), comment="수정일시")
    is_deleted = Column(CHAR(length=1), default="F", server_default="F", nullable=False, comment="삭제여부(T|F)")
    del_dt = Column(DATETIME(timezone=False), nullable=True, comment="삭제일시")

    __table_args__ = (
        PrimaryKeyConstraint(member_no, name="pk_members"),
        UniqueConstraint(member_id, name="ixn_members__member_id"),
        {
            "comment": "사용자 정보"
        }
    )

테이블의 이름을 지정하고 각 Column에서 대해서 기술하였다. 그리고 마지막에 해당 테이블에 대한 특성을 기술하였다.

해당 테이블에 대해서 primary key를 선언하는 방법은 두 가지가 있다. 우선 위의 코드처럼 PrimaryKeyConstraint를 통해서 컬럼과 인덱스 이름을 지정하는 방법이 있다. mysql에서는 primary key의 이름을 지정하여서 사용하지 않기 때문에 name을 쓰지 않아도 된다.

다른 방법은 primary key로 사용할 컬럼에서 primary_key=True와 같이 사용하는 것이다. 아래 코드를 참조하면 된다.

member_no = Column(SMALLINT(unsigned=True), nullable=False, autoincrement=True, primary_key=True, comment="멤버번호")

이렇게 작성하면 create table 구문이 위의 방식과 약간 다르게 생성된다. 하지만 실제 mysql 내부에서는 이 두 가지 방법을 모두 동일하게 처리한다.

이제 다음으로 tb_category 테이블에 대한 Class 정의를 한다. 다음 코드와 같다.

class Category(Base):
    __tablename__ = "tb_category"

    category_no = Column(INTEGER(unsigned=True), nullable=False, autoincrement=True, comment="카테고리 번호")
    member_no = Column(SMALLINT(unsigned=True), nullable=True, comment="카테고리 생성자 번호(기본 카테고리일 경우 null)")
    category_name = Column(VARCHAR(length=50), nullable=False, comment="카테고리 이름")
    has_children = Column(CHAR(length=1), nullable=False, default="F", comment="자식을 가지고 있는지 여부(T|F)")
    parent_no = Column(INTEGER(unsigned=True), nullable=True, comment="부모 카테고리 번호")
    class_name = Column(VARCHAR(length=30), nullable=True, comment="아이콘 클래스")
    is_system = Column(CHAR(length=1), default="F", comment="시스템 기본 카테고리")
    reg_dt = Column(DATETIME(timezone=False), nullable=False, server_default=func.now(), comment="생성일시")
    upd_dt = Column(DATETIME(timezone=False), nullable=False, server_default=func.now(), onupdate=func.now(), comment="수정일시")
    is_deleted = Column(CHAR(length=1), default="F", server_default="F", nullable=False, comment="삭제여부(T|F)")
    del_dt = Column(DATETIME(timezone=False), nullable=True, comment="삭제일시")

    __table_args__ = (
        PrimaryKeyConstraint(category_no, name="pk_category"),
        ForeignKeyConstraint(
            ["member_no"],
            ["tb_members.member_no"],
            name="fk_category__member_no",
            onupdate="NO ACTION",
            ondelete="NO ACTION",
        ),
        {
            "comment": "카테고리 정보"
        }
    )

Category 테이블에서는 tb_members 테이블의 member_no 컬럼을 foreign key로 사용한다. 이에 대해서 위의 코드와 같이 정리하였다.

다른 두 테이블에 대한 정의는 아래 같이 정리한다.

class AccountLog(Base):
    __tablename__ = "tb_account_log"

    account_log_no = Column(INTEGER(unsigned=True), nullable=False, autoincrement=True, comment="내역번호")
    member_no = Column(SMALLINT(unsigned=True), nullable=False, comment="사용자번호")
    std_date = Column(DATE, nullable=False, comment="날짜")
    opponent_name = Column(VARCHAR(length=150), nullable=False, comment="메인거래처")
    reg_dt = Column(DATETIME(timezone=False), nullable=False, server_default=func.now(), comment="생성일시")
    upd_dt = Column(DATETIME(timezone=False), nullable=False, server_default=func.now(), onupdate=func.now(), comment="수정일시")
    is_deleted = Column(CHAR(length=1), default="F", server_default="F", nullable=False, comment="삭제여부(T|F)")
    del_dt = Column(DATETIME(timezone=False), nullable=True, comment="삭제일시")

    __table_args__ = (
        PrimaryKeyConstraint(account_log_no, name="pk_account_log"),
        ForeignKeyConstraint(
            ["member_no"],
            ["tb_members.member_no"],
            name="fk_account_log__member_no",
            onupdate="NO ACTION",
            ondelete="NO ACTION",
        ),
        {
            "comment": "가계부 메인 내역"
        }
    )

class LogDefault(Base):
    __tablename__ = "tb_log_detail"

    log_detail_no = Column(INTEGER(unsigned=True), nullable=False, autoincrement=True, comment="사용내역 상세 번호")
    account_log_no = Column(INTEGER(unsigned=True), nullable=False, comment="내역번호")
    detail_contents = Column(VARCHAR(length=300), nullable=False, comment="상세내역정보")
    amounts = Column(INTEGER(unsigned=True), nullable=False, comment="금액")
    io_type = Column(CHAR(length=1), nullable=False, comment="수입/지출 구분(I: 수입, O: 지출)")
    category_no = Column(INTEGER(unsigned=True), nullable=True, comment="카테고리 번호")
    important = Column(TINYINT(unsigned=True), nullable=False, default=1, comment="중요도(지출일 경우만)")
    is_fixed_cost = Column(CHAR(length=1), nullable=False, default="F", comment="고정비여부(지출일 경우만, T|F)")
    reg_dt = Column(DATETIME(timezone=False), nullable=False, server_default=func.now(), comment="생성일시")
    upd_dt = Column(DATETIME(timezone=False), nullable=False, server_default=func.now(), onupdate=func.now(), comment="수정일시")
    is_deleted = Column(CHAR(length=1), default="F", server_default="F", nullable=False, comment="삭제여부(T|F)")
    del_dt = Column(DATETIME(timezone=False), nullable=True, comment="삭제일시")

    __table_args__ = (
        PrimaryKeyConstraint(log_detail_no, name="pk_log_detail"),
        ForeignKeyConstraint(
            ["account_log_no"],
            ["tb_account_log.account_log_no"],
            name="fk_log_detail__account_log_no",
            onupdate="NO ACTION",
            ondelete="NO ACTION",
        ),
        ForeignKeyConstraint(
            ["category_no"],
            ["tb_category.category_no"],
            name="fk_log_detail__category_no",
            onupdate="NO ACTION",
            ondelete="NO ACTION",
        ),
        {
            "comment": "가계부 상세 내역"
        }
    )

이제 마지막으로 다음 코드를 추가한다.

with engine.connect() as conn:
    Base.metadata.create_all(conn)

해당 코드를 입력하면 프로그램이 구동될 때 ORM 정의에서 현재 생성되지 않은 테이블이 있으면 비교해서 자동으로 테이블을 생성해준다.

ORM을 작성하면서 실제로 생성되는 테이블 스키마를 확인할 수 있는데 이를 보면서 코드를 변경하면서 개발하면 개발을 용이하게 할 수 있다.

더불어 connection.py에서 create_engine 함수를 호출하면서 echo 옵션을 True로 선언하면 실제로 sqlalchemy에서 사용하는 쿼리를 확인할 수 있다. 개발 단계에서는 이렇게 쿼리를 확인하면서 하는 것이 많은 도움을 준다.

engine = create_engine(
    DB_URL, encoding = 'utf-8',
    echo=True,
    future=True,
)

위의 코드를 통해서 생성된 테이블의 DDL은 다음 이미지들과 같다.

다음으로는 models를 기반으로 schemes.py를 만들어야 하는데, 해당 파일을 route를 만들면서 request parameter와 response body를 어떻게 만들건지 정의하면서 계속 수정해나가야 한다.

다음 포스팅에서는 로그인을 하기 위한 scheme 정의와 login 하기 위한 코드 작성을 진행하도록 하겠다.