Database Architecture & SQLAlchemy 2.0
Master PostgreSQL SQL queries, SQLAlchemy 2.0 ORM mapping, Alembic schema migrations, connection pooling, and strict layer separation (Model vs Schema vs Repository vs Service).
Fat ORM "God Model" vs 4-Layer Responsibility Architecture
Never mix database table mapping with password hashing, HTTP validation, or email sending. Separate responsibilities cleanly.
# ❌ Model does hashing, DB queries & email sending!
class User(Base):
def register(self):
self.password = hash_pwd(self.password)
db.session.add(self)
send_welcome_email(self.email)# ✅ Model = DB Entity | Schema = DTO | Repo = Queries | Service = Rules
class UserModel(Base): ... # Entity
class UserCreate(BaseModel): ... # DTO
class UserRepository: ... # SQL
class UserService: ... # Logic🧱 Data Architecture 4-Layer Matrix
Maps directly to relational SQL table schema, primary keys, and indexes.
Validates incoming HTTP payload fields and serializes response JSON.
Encapsulates raw SQL queries, filtering, and database transaction commits.
Orchestrates domain validation rules, password hashing, and external APIs.
💻 Production Blueprint: SQLAlchemy 2.0 & Repository Pattern
Notice how `UserModel` uses SQLAlchemy 2.0 `Mapped` type annotations and `UserRepository` encapsulates SQL execution.
# ─── 1. SQLALCHEMY 2.0 ORM ENTITY (models/user.py) ─────────────────────────
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import String
class UserModel(DeclarativeBase):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
email: Mapped[str] = mapped_column(String(255), unique=True, index=True)
# ─── 2. DATA ACCESS REPOSITORY (repositories/user_repo.py) ────────────────
from sqlalchemy import select
class UserRepository:
def __init__(self, session): self.session = session
def get_by_email(self, email: str) -> UserModel | None:
stmt = select(UserModel).where(UserModel.email == email)
return self.session.execute(stmt).scalars().first()