lumination

1:1 (일대일)

예: 유저(User)는 하나의 프로필(Profile)만 가짐

# user.py
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    username = Column(String)
    profile = relationship("Profile", back_populates="user", uselist=False)

# profile.py
class Profile(Base):
    __tablename__ = "profiles"
    id = Column(Integer, primary_key=True)
    bio = Column(String)
    user_id = Column(Integer, ForeignKey("users.id"))
    user = relationship("User", back_populates="profile")

• uselist=False → 관계가 리스트가 아니라 단일 객체임을 의미
• User.profile ↔ Profile.user 1:1 연결

1:N (일대다)

예: 유저(User)는 여러 개의 게시글(Post)을 작성할 수 있음

# user.py
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    username = Column(String)
    posts = relationship("Post", back_populates="owner")

# post.py
class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)
    title = Column(String)
    owner_id = Column(Integer, ForeignKey("users.id"))
    owner = relationship("User", back_populates="posts")

• User.posts → 게시글 목록 (list)
• Post.owner → 해당 글의 작성자 (1명)

M:N (다대다)

예: 학생(Student)은 여러 수업(Course)을 듣고,
수업(Course)은 여러 학생이 들을 수 있음

# 중간 테이블
association_table = Table(
    "student_course",
    Base.metadata,
    Column("student_id", ForeignKey("students.id"), primary_key=True),
    Column("course_id", ForeignKey("courses.id"), primary_key=True),
)

# student.py
class Student(Base):
    __tablename__ = "students"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    courses = relationship("Course", secondary=association_table, back_populates="students")

# course.py
class Course(Base):
    __tablename__ = "courses"
    id = Column(Integer, primary_key=True)
    title = Column(String)
    students = relationship("Student", secondary=association_table, back_populates="courses")

• secondary=association_table 로 중간 테이블 지정
• Student.courses → 수강 중인 수업 목록
• Course.students → 수업을 듣는 학생 목록

# 데이터 삽입 예시
    # 학생 생성
    student1 = Student(name="Alice")
    student2 = Student(name="Bob")

    # 수업 생성
    course1 = Course(title="Math")
    course2 = Course(title="Physics")

    # 관계 연결
    student1.courses.append(course1)     # Alice는 Math 듣고
    student1.courses.append(course2)     # Physics도 듣고
    student2.courses.append(course1)     # Bob은 Math만 들음

    # 세션에 추가
    session.add_all([student1, student2, course1, course2])
    session.commit()
    
# 데이터 조회 예시
	# 특정 학생이 듣는 수업들	
        alice = session.query(Student).filter_by(name="Alice").first()
        for course in alice.courses:
            print(course.title)  # Math, Physics

	# 특정 수업을 듣는 학생들
        math = session.query(Course).filter_by(title="Math").first()
        for student in math.students:
            print(student.name)  # Alice, Bob

# 관계 제거 (수업 삭제)
alice = session.query(Student).filter_by(name="Alice").first()
physics = session.query(Course).filter_by(title="Physics").first()

alice.courses.remove(physics)  # Alice가 Physics 수업 그만둠
session.commit()

1. SQLAlchemy + Pydantic 방식

• Pydantic과 함께 쓰면 API/DB 역할 분리가 명확함
• DB 데이터를 "객체"로 다룰 수 있음 (User, Post 등)
• relationship, ForeignKey로 테이블 간 관계 표현 가능

# 1. Pydantic 모델 (입출력용)
class UserCreate(BaseModel):
    username: str
    email: str

# 2. SQLAlchemy ORM 모델 (DB 테이블 대응)
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    username = Column(String)
    email = Column(String)

# 3. ORM 방식으로 DB에 객체 저장
def create_user(db: Session, user_data: UserCreate):
    db_user = User(**user_data.dict())  # 딕셔너리 언팩 → ORM 객체 생성
    db.add(db_user)                     # 세션에 추가
    db.commit()                         # DB에 반영
    db.refresh(db_user)                # 새로 생성된 id 등 갱신
    return db_user

• join, filter, subquery, order_by 등 복잡한 조건

from sqlalchemy.orm import aliased
from sqlalchemy import func, desc

def get_top_users(db: Session):
    # 최근 등록된 유저 중 이메일 있는 애들만, 이름 내림차순
    return db.query(User).filter(
        User.email != None,
        User.username.like("gildong%")
    ).order_by(desc(User.id)).limit(10).all()

• func, group_by, having 등 집계함수 활용

from sqlalchemy import func

def get_user_count_by_email_domain(db: Session):
    return db.query(
        func.substr(User.email, func.instr(User.email, '@') + 1).label("domain"),
        func.count(User.id).label("count")
    ).group_by("domain").having(func.count(User.id) > 5).all()

• 쿼리 힌트 사용 (with_hint, prefix_with, FOR UPDATE 등)

def get_user_with_hint(db: Session):
    # MySQL 인덱스 힌트 사용 예시 (DB 종류에 따라 다름)
    query = db.query(User).with_hint(User, "USE INDEX (idx_username)", dialect_name="mysql")
    return query.filter(User.username == "gildong").first()

• GET /users?skip=20&limit=10
  
  • skip: 건너뛸 row 수 (예: 0, 10, 20...)
  • limit: 한 번에 가져올 row 수

def get_users_paginated(db: Session, skip: int = 0, limit: int = 10):
    return db.query(User).offset(skip).limit(limit).all()

• 페이징 예시 - 응답 구조

from pydantic import BaseModel

class PaginatedUsers(BaseModel):
    total: int
    skip: int
    limit: int
    data: List[UserRead]

@router.get("/users", response_model=PaginatedUsers)
def list_users(skip: int = 0, limit: int = 10, db: Session = Depends(get_db)):
    total = db.query(User).count()
    users = db.query(User).offset(skip).limit(limit).all()
    return PaginatedUsers(
        total=total,
        skip=skip,
        limit=limit,
        data=users
    )

• 응답 구조
  • {
      "total": 100,
      "skip": 20,
      "limit": 10,
      "data": [
        { "id": 21, "username": "hong", ... },
        ...
      ]
    }

• 조인 (클래스 정의)

# user.py
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    username = Column(String)
    posts = relationship("Post", back_populates="owner")

# post.py
class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)
    title = Column(String)
    owner_id = Column(Integer, ForeignKey("users.id"))
    owner = relationship("User", back_populates="posts")

• 조인 사용 예시

# join(Post.owner) 은 내부적으로 posts.owner_id = users.id를 기준으로 조인
def get_posts_with_users(db: Session):
    return db.query(Post).join(Post.owner).all()
    
    
# 유저 정보 포함해서 가져오기 (query(Post, User) → 튜플로 결과 나옴)
def get_posts_with_user_info(db: Session):
    results = db.query(Post, User).join(User, Post.owner_id == User.id).all()

    for post, user in results:
        print(post.title, "-", user.username)
        
        
# outerjoin (LEFT JOIN) 예시
def get_all_users_with_or_without_posts(db: Session):
    return db.query(User).outerjoin(User.posts).all()
    
    
# Pydantic으로 응답 만들기
# joinedload() 써주면 N+1 문제 없이 User와 Post 같이 가져옴
class PostRead(BaseModel):
    id: int
    title: str

class UserReadWithPosts(BaseModel):
    id: int
    username: str
    posts: List[PostRead]

    class Config:
        orm_mode = True
        
@router.get("/users", response_model=List[UserReadWithPosts])
def list_users_with_posts(db: Session = Depends(get_db)):
    return db.query(User).options(joinedload(User.posts)).all()

2. SQLAlchemy + Raw SQL 방식 (가장 Low-level)

• SQL문을 직접 작성함
• text()로 감싸서 쿼리 실행
• 가장 직접적이지만, 유지보수가 어렵고 타입 지원도 없음

from sqlalchemy import text

def create_user_raw_sql(db, username: str, email: str):
    query = text("INSERT INTO users (username, email) VALUES (:username, :email)")
    db.execute(query, {"username": username, "email": email})
    db.commit()

3. SQLAlchemy Core 방식

• ORM 객체 없이 테이블을 직접 정의
• SQL을 Python 코드로 조립하는 방식
• 타입 지원이나 코드 자동화는 어느 정도 있음

from sqlalchemy import Table, Column, Integer, String, MetaData, insert

metadata = MetaData()

user_table = Table(
    "users", metadata,
    Column("id", Integer, primary_key=True),
    Column("username", String),
    Column("email", String)
)

def create_user_core(db, username: str, email: str):
    stmt = insert(user_table).values(username=username, email=email)
    db.execute(stmt)
    db.commit()

TL;DR - Install Mac, Ubuntu

1. Mac OS

brew tab mongodb/brew

brew install mongodb-community@7.0

brew services start mongodb-community@7.0

brew services list

Name                  Status  User File
mongodb-community@7.0 started nhn  ~/Library/LaunchAgents/homebrew.mxcl.mongodb-community@7.0.plist

mongosh

Current Mongosh Log ID:	67ff12bbd129c363bc1041e5
Connecting to:		mongodb://127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.5.0
Using MongoDB:		7.0.19
Using Mongosh:		2.5.0

For mongosh info see: https://www.mongodb.com/docs/mongodb-shell/


To help improve our products, anonymous usage data is collected and sent to MongoDB periodically (https://www.mongodb.com/legal/privacy-policy).
You can opt-out by running the disableTelemetry() command.

------
   The server generated these startup warnings when booting
   2025-04-16T11:15:00.990+09:00: Access control is not enabled for the database. Read and write access to data and configuration is unrestricted
------

2. Ubuntu 22.04

참고: https://jjeongil.tistory.com/2463

sudo apt update
sudo apt install gnupg wget apt-transport-https ca-certificates software-properties-commonsudo apt update
sudo apt install gnupg wget apt-transport-https ca-certificates software-properties-common

# MongoDB 리포지토리의 GPG 키
wget -qO- \
  https://pgp.mongodb.com/server-7.0.asc | \
  gpg --dearmor | \
  sudo tee /usr/share/keyrings/mongodb-server-7.0.gpg >/dev/null
  
# MongoDB 리포지토리를 추가
echo "deb [ arch=amd64,arm64 signed-by=/usr/share/keyrings/mongodb-server-7.0.gpg ] \
  https://repo.mongodb.org/apt/ubuntu $(lsb_release -cs)/mongodb-org/7.0 multiverse" | \
  sudo tee -a /etc/apt/sources.list.d/mongodb-org-7.0.list
  
sudo apt update
sudo apt install mongodb-org
sudo systemctl enable --now mongod

# 설치 확인
mongosh --eval 'db.runCommand({ connectionStatus: 1 })'