fastapi-database-setup

name	fastapi-database-setup
description	Configure async SQLAlchemy 2.0 engine, session factory, and database dependency for FastAPI

FastAPI Database Setup

Overview

This skill covers setting up async SQLAlchemy 2.0 with PostgreSQL using asyncpg driver.

Create database.py

Create src/app/database.py:

from collections.abc import AsyncGenerator

from sqlalchemy.ext.asyncio import (
    AsyncSession,
    async_sessionmaker,
    create_async_engine,
)

from app.config import settings

# Create async engine
engine = create_async_engine(
    str(settings.database_url),
    echo=settings.database_echo,
    pool_size=settings.database_pool_size,
    max_overflow=settings.database_max_overflow,
    pool_pre_ping=True,  # Verify connections before use
)

# Create async session factory
async_session_factory = async_sessionmaker(
    bind=engine,
    class_=AsyncSession,
    expire_on_commit=False,  # Don't expire objects after commit
    autocommit=False,
    autoflush=False,
)


async def get_db() -> AsyncGenerator[AsyncSession, None]:
    """
    Dependency that provides a database session.
    
    Yields an async session and ensures it's closed after use.
    Uses the same session throughout the request lifecycle.
    """
      async_session_factory()  session:
        :
             session
        :
             session.close()