PostgreSQL + SQLAlchemy ORM: Database Design and Migrations

Database Design

This post covers the PostgreSQL database and SQLAlchemy ORM setup for hr_blog2.0.

ER Diagram

erDiagram
    POST ||--o{ POST_TAG : has
    POST }o--|| CATEGORY : belongs_to
    TAG ||--o{ POST_TAG : has
    POST ||--o{ IMAGE : has
    POST ||--o{ POST_CONNECTION : source
    POST ||--o{ POST_CONNECTION : target

    POST {
        int id PK
        string slug UK
        string title
        text content
        int category_id FK
        int read_time
        boolean published
        datetime created_at
        datetime updated_at
    }

    CATEGORY {
        int id PK
        string name
        string slug
        int parent_id FK
    }

    TAG {
        int id PK
        string name UK
    }

    IMAGE {
        int id PK
        string filename
        string url
        int post_id FK
    }

SQLAlchemy Models

from sqlalchemy import Column, Integer, String, Text, Boolean, ForeignKey, DateTime
from sqlalchemy.orm import relationship
from .base import Base

class Post(Base):
    __tablename__ = "posts"

    id = Column(Integer, primary_key=True)
    slug = Column(String(255), unique=True, nullable=False)
    title = Column(String(500), nullable=False)
    content = Column(Text, nullable=False)
    category_id = Column(Integer, ForeignKey("categories.id"))
    read_time = Column(Integer, default=5)
    published = Column(Boolean, default=False)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, onupdate=func.now())

    category = relationship("Category", back_populates="posts")
    tags = relationship("Tag", secondary=post_tags)
    images = relationship("Image", back_populates="post")

Automatic Migrations

hr_blog2.0's migrate.py:

from sqlalchemy import create_engine
from models.base import Base

def auto_migrate():
    engine = create_engine(DATABASE_URL)
    Base.metadata.create_all(engine)
    print("마이그레이션 완료")

Performance Optimization

1. Indexes: Add indexes on slug and category_id
2. Eager Loading: Pre-load relational data
3. Connection Pool: Configure the SQLAlchemy connection pool
4. Query Optimization: Prevent N+1 query problems