Skip to main content / پرش به محتوای اصلی

Complete Guide to SQLAlchemy 2.0 and Alembic in Python (Modern Async ORM)

November 20262 min read۰ بازدید
Comprehensive tutorial on building modern Python database layers with SQLAlchemy 2.0 and Alembic: Declarative Mapped models, async sessions, pooling, and automated migrations.

In modern Python backend architectures, reliable database abstraction, declarative schema definitions, and migration workflows are cornerstones of maintainable systems. SQLAlchemy 2.0 represents a comprehensive paradigm shift: discarding legacy 1.x conventions in favor of strictly typed Mapped[...] constructs, unified select() syntax, and native first-class AsyncIO execution.

Paired with Alembic, Python developers gain an automated schema evolution pipeline that tracks model modifications safely without manual DDL scripting.

Short Answer: In SQLAlchemy 2.0, deprecated session.query() calls are eliminated in favor of select(). Declarative models use Python native types with Mapped[int] = mapped_column(), and asynchronous engines are powered by create_async_engine using high-speed async drivers like asyncpg.


Architectural Advantages of SQLAlchemy 2.0

  1. Deep Static Typing: Full compliance with PEP 484 type annotations, empowering IDE autocomplete and static type checkers like mypy.
  2. True AsyncIO Concurrency: Execute concurrent database statements without blocking Python's event loop via AsyncSession.
  3. Unified Query Construction: A singular, expressive API centered around select(), insert(), update(), and delete() across both Core and ORM.

1. Installation & Driver Setup

bash
pip install "sqlalchemy>=2.0.0" "alembic>=1.13.0" "asyncpg>=0.29.0" python-dotenv

2. Asynchronous Engine and Session Configuration (database.py)

python
import os
from collections.abc import AsyncGenerator
from dotenv import load_dotenv
from sqlalchemy.ext.asyncio import (
    AsyncSession,
    async_sessionmaker,
    create_async_engine,
)
from sqlalchemy.orm import DeclarativeBase

load_dotenv()

DATABASE_URL = os.getenv(
    "DATABASE_URL", 
    "postgresql+asyncpg://postgres:password@localhost:5432/my_project_db"
)

engine = create_async_engine(
    DATABASE_URL,
    echo=False,
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True,
)

AsyncSessionFactory = async_sessionmaker(
    bind=engine,
    class_=AsyncSession,
    expire_on_commit=False,
)

class Base(DeclarativeBase):
    pass

async def get_db_session() -> AsyncGenerator[AsyncSession, None]:
    async with AsyncSessionFactory() as session:
        try:
            yield session
        except Exception:
            await session.rollback()
            raise

3. Declarative Model Modeling

python
from datetime import datetime
from sqlalchemy import ForeignKey, String, Text, func
from sqlalchemy.orm import Mapped, mapped_column, relationship
from database import Base

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    username: Mapped[str] = mapped_column(String(50), unique=True, index=True, nullable=False)
    email: Mapped[str] = mapped_column(String(255), unique=True, index=True, nullable=False)
    is_active: Mapped[bool] = mapped_column(default=True)
    created_at: Mapped[datetime] = mapped_column(server_default=func.now())

    articles: Mapped[list["Article"]] = relationship(back_populates="author", cascade="all, delete-orphan")

class Article(Base):
    __tablename__ = "articles"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(250), nullable=False)
    slug: Mapped[str] = mapped_column(String(250), unique=True, index=True)
    body: Mapped[str] = mapped_column(Text, nullable=False)
    author_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))

    author: Mapped[User] = relationship(back_populates="articles")

4. Query Execution with select() and Eager Loading

python
from sqlalchemy import select
from sqlalchemy.orm import selectinload
from database import AsyncSessionFactory

async def fetch_user_data():
    async with AsyncSessionFactory() as session:
        # Single record retrieval
        stmt = select(User).where(User.username == "taha")
        result = await session.execute(stmt)
        user = result.scalar_one_or_none()

        # Relationship eager loading with selectinload
        stmt_with_articles = (
            select(User)
            .options(selectinload(User.articles))
            .where(User.is_active.is_(True))
        )
        active_users = (await session.scalars(stmt_with_articles)).all()

5. Automated Database Migrations with Alembic

Initialize async template in the repository:

bash
alembic init -t async alembic

In alembic/env.py, bind your metadata:

python
from database import Base
import models

target_metadata = Base.metadata

Generate and apply revisions:

bash
alembic revision --autogenerate -m "create initial tables"
alembic upgrade head

Frequently Asked Questions

Why is expire_on_commit=False mandatory in async SQLAlchemy?

By default, commits mark instance attributes expired. In an asynchronous context, evaluating attributes after committing would trigger implicit lazy synchronous I/O, leading to MissingGreenlet exceptions.

How does selectinload compare to joinedload?

selectinload generates a secondary WHERE IN (...) query, avoiding Cartesian explosion on one-to-many collections, making it the preferred strategy for async collection loading.

Tags for this article:

Enjoyed this article?

Share it with your friends and colleagues.

Share: