{"id":5217,"library":"fastapi-users-db-sqlalchemy","title":"FastAPI Users database adapter for SQLAlchemy","description":"FastAPI Users database adapter for SQLAlchemy. It provides the necessary tools to integrate SQLAlchemy ORM with asyncio for user management in FastAPI applications. The library is actively maintained with frequent major version updates, often including breaking changes and bug fixes.","status":"active","version":"7.0.0","language":"en","source_language":"en","source_url":"https://github.com/fastapi-users/fastapi-users-db-sqlalchemy","tags":["fastapi","users","authentication","sqlalchemy","orm","database","asyncio"],"install":[{"cmd":"pip install fastapi-users-db-sqlalchemy sqlalchemy aiosqlite","lang":"bash","label":"With SQLite support"},{"cmd":"pip install fastapi-users-db-sqlalchemy sqlalchemy asyncpg","lang":"bash","label":"With PostgreSQL support"}],"dependencies":[{"reason":"Core dependency for user management logic.","package":"fastapi-users"},{"reason":"ORM layer for database interactions.","package":"SQLAlchemy"},{"reason":"Asynchronous SQLite driver, optional depending on database.","package":"aiosqlite","optional":true},{"reason":"Asynchronous PostgreSQL driver, optional depending on database.","package":"asyncpg","optional":true},{"reason":"Required by SQLAlchemy for some asyncio contexts, typically installed automatically but can be a source of 'MissingGreenlet' errors if missing.","package":"greenlet","optional":true}],"imports":[{"symbol":"SQLAlchemyUserDatabase","correct":"from fastapi_users.db import SQLAlchemyUserDatabase"},{"symbol":"SQLAlchemyBaseUserTable","correct":"from fastapi_users.db import SQLAlchemyBaseUserTable"},{"symbol":"SQLAlchemyBaseUserTableUUID","correct":"from fastapi_users.db import SQLAlchemyBaseUserTableUUID"},{"symbol":"AsyncSession","correct":"from sqlalchemy.ext.asyncio import AsyncSession"},{"note":"Use DeclarativeBase for SQLAlchemy 2.0 style models; declarative_base is for SQLAlchemy 1.x or legacy modes.","wrong":"from sqlalchemy.ext.declarative import declarative_base","symbol":"DeclarativeBase","correct":"from sqlalchemy.orm import DeclarativeBase"}],"quickstart":{"code":"import os\nfrom typing import AsyncGenerator\n\nfrom fastapi import Depends, FastAPI\nfrom fastapi_users import FastAPIUsers\nfrom fastapi_users.authentication import CookieAuthentication, AuthenticationBackend\nfrom fastapi_users.db import SQLAlchemyBaseUserTableUUID, SQLAlchemyUserDatabase\nfrom sqlalchemy.ext.asyncio import AsyncSession, create_async_engine\nfrom sqlalchemy.orm import sessionmaker, DeclarativeBase, Mapped, mapped_column\nfrom sqlalchemy import String\n\n# Define your database URL (using SQLite for this example)\nDATABASE_URL = os.environ.get(\"DATABASE_URL\", \"sqlite+aiosqlite:///./test.db\")\nSECRET = os.environ.get(\"SECRET\", \"YOUR_SECRET_KEY\") # IMPORTANT: Change in production!\n\n# 1. Define your SQLAlchemy Base\nclass Base(DeclarativeBase):\n    pass\n\n# 2. Define your User model\nclass User(SQLAlchemyBaseUserTableUUID, Base):\n    # You can add custom fields here\n    first_name: Mapped[str | None] = mapped_column(String(255), nullable=True)\n    last_name: Mapped[str | None] = mapped_column(String(255), nullable=True)\n\n# 3. Create SQLAlchemy engine and session maker\nengine = create_async_engine(DATABASE_URL)\nasync_session_maker = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)\n\n# 4. Utility function to create database tables\nasync def create_db_and_tables():\n    async with engine.begin() as conn:\n        await conn.run_sync(Base.metadata.create_all)\n\n# 5. Database session dependency\nasync def get_async_session() -> AsyncGenerator[AsyncSession, None]:\n    async with async_session_maker() as session:\n        yield session\n\n# 6. User database adapter dependency\nasync def get_user_db(\n    session: AsyncSession = Depends(get_async_session),\n) -> AsyncGenerator[SQLAlchemyUserDatabase, None]:\n    yield SQLAlchemyUserDatabase(session, User)\n\n# 7. Authentication Backend (example with Cookie Authentication)\ncookie_authentication = CookieAuthentication(secret=SECRET, lifetime_seconds=3600)\n\nauth_backend = AuthenticationBackend(\n    name=\"cookie\",\n    transport=cookie_authentication,\n    get_user_manager=lambda user_db: None # UserManager is typically defined outside this module\n)\n\n# Example FastAPI app (UserManager and other FastAPIUsers components needed for full functionality)\napp = FastAPI()\n\n@app.on_event(\"startup\")\nasync def on_startup():\n    await create_db_and_tables()\n\n# This is a minimal quickstart for the db adapter itself.\n# For a full working FastAPIUsers example, you'd integrate this with FastAPIUsers, UserManager, and routers.\n# Example of how you'd use get_user_db in a FastAPI route (simplified):\n# @app.get(\"/users/me\")\n# async def get_current_user(\n#     user_db: SQLAlchemyUserDatabase = Depends(get_user_db),\n# ):\n#     # In a real app, you'd get the current user from auth backend\n#     # and then use user_db to fetch more user data if needed.\n#     # This simply demonstrates the user_db dependency is available.\n#     return {\"message\": \"User database dependency available!\"}\n\n# To run this: \n# 1. Save as main.py\n# 2. pip install fastapi uvicorn 'fastapi-users[sqlalchemy]' aiosqlite\n# 3. uvicorn main:app --reload","lang":"python","description":"This quickstart demonstrates how to set up the `fastapi-users-db-sqlalchemy` adapter with a SQLAlchemy 2.0-style User model and asynchronous database session management for FastAPI. It includes defining the User model, database engine, session maker, and the necessary dependencies (`get_async_session`, `get_user_db`). Remember to install an async database driver like `aiosqlite` or `asyncpg`."},"warnings":[{"fix":"Upgrade your Python environment to 3.9+ or pin `fastapi-users-db-sqlalchemy<7.0.0` if you must remain on Python 3.8.","message":"Python 3.8 support was dropped in `fastapi-users-db-sqlalchemy` v7.0.0. Ensure your project uses Python 3.9 or higher.","severity":"breaking","affected_versions":">=7.0.0"},{"fix":"Migrate your SQLAlchemy models and query patterns to SQLAlchemy 2.0 style or pin `fastapi-users-db-sqlalchemy<5.0.0` if you need to stay on SQLAlchemy 1.4.","message":"Version 5.0.0 migrated to SQLAlchemy 2.0. This introduces significant API changes (e.g., `Mapped` for columns, `select()` statements instead of `query()`). If you need to use SQLAlchemy 1.4, you must pin the dependency.","severity":"breaking","affected_versions":">=5.0.0"},{"fix":"Use SQLAlchemy's eager loading options (e.g., `selectinload`, `joinedload`) for relationships that need to be accessed when fetching users via `fastapi-users`'s underlying database operations. Ensure your `UserRead` schemas align with what's eagerly loaded.","message":"When using asynchronous SQLAlchemy, relationships are not implicitly lazy-loaded. Attempting to access unloaded relationships in generated FastAPI Users routes (e.g., in a Pydantic `UserRead` model) will result in `MissingGreenlet` errors or similar issues due to implicit I/O. Eager loading must be explicitly configured.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Define separate Pydantic schemas (e.g., `UserCreate`, `UserUpdate`) for input data to your FastAPI endpoints. Use these Pydantic schemas for request body validation and then convert them to SQLAlchemy models for database operations.","message":"FastAPI path operation functions should receive Pydantic models as input parameters for data validation and serialization, not raw SQLAlchemy ORM models.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Install the correct driver: `pip install aiosqlite` or `pip install asyncpg`. The `DATABASE_URL` should then use `sqlite+aiosqlite://` or `postgresql+asyncpg://`.","message":"Ensure you install an appropriate asynchronous database driver (e.g., `aiosqlite` for SQLite, `asyncpg` for PostgreSQL) corresponding to your `DATABASE_URL` dialect. Failure to do so will result in connection errors.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-13T00:00:00.000Z","next_check":"2026-07-12T00:00:00.000Z"}