SQLAlchemy adapter¶

The default adapter. Uses SQLAlchemy 2.0 async. Ships with the standard extra (aiosqlite); use postgresql for asyncpg, or supply any async driver for MySQL.

Setup¶

from fastauth.adapters.sqlalchemy import SQLAlchemyAdapter

# engine_url form:
adapter = SQLAlchemyAdapter(engine_url="sqlite+aiosqlite:///./auth.db")
adapter = SQLAlchemyAdapter(engine_url="postgresql+asyncpg://user:pass@host/db")

# or pre-built engine:
adapter = SQLAlchemyAdapter(engine=my_async_engine)

Provide either engine_url or engine (raises ValueError otherwise). The shared async_sessionmaker is built with expire_on_commit=False.

Sub-adapters¶

SQLAlchemyAdapter exposes one property per sub-adapter, each lazily constructing a sub-adapter that shares the session factory:

Property	Class	Used for
`.user`	`SQLAlchemyUserAdapter`	`config.adapter`
`.token`	`SQLAlchemyTokenAdapter`	`config.token_adapter`
`.session`	`SQLAlchemySessionAdapter`	`auth.session_adapter`
`.role`	`SQLAlchemyRoleAdapter`	`auth.role_adapter`
`.oauth`	`SQLAlchemyOAuthAccountAdapter`	`config.oauth_adapter`
`.passkey`	`SQLAlchemyPasskeyAdapter`	`config.passkey_adapter`

config = FastAuthConfig(
    ...,
    adapter=adapter.user,
    token_adapter=adapter.token,
    oauth_adapter=adapter.oauth,           # only if you use OAuth
    passkey_adapter=adapter.passkey,       # only if you use passkeys
)
auth = FastAuth(config)
auth.role_adapter = adapter.role           # only if you use RBAC
auth.session_adapter = adapter.session     # only if you use /auth/sessions

Tables¶

SQLAlchemyAdapter manages seven tables under Base (a DeclarativeBase):

Table	Purpose
`fastauth_users`	Users (id, email unique, hashed_password, email_verified, is_active, timestamps).
`fastauth_roles`	RBAC role names.
`fastauth_user_roles`	User ↔ role association.
`fastauth_role_permissions`	Role ↔ permission strings.
`fastauth_sessions`	Server-side session rows for `/auth/sessions`.
`fastauth_tokens`	One-time tokens (verification, reset, email change, magic-link request) + refresh JTIs + login attempts.
`fastauth_oauth_accounts`	Linked OAuth accounts; unique on `(provider, provider_account_id)`.
`fastauth_passkeys`	Passkey credentials (public_key, sign_count, aaguid, name, last_used_at).

Create / drop tables¶

@asynccontextmanager
async def lifespan(app: FastAPI):
    await adapter.create_tables()   # CREATE IF NOT EXISTS — safe to call repeatedly
    yield

drop_tables() is provided for tests only:

@pytest.fixture
async def fresh_db():
    adapter = SQLAlchemyAdapter(engine_url="sqlite+aiosqlite:///:memory:")
    await adapter.create_tables()
    yield adapter
    await adapter.drop_tables()

Database URLs¶

DB	URL prefix	Extra
SQLite	`sqlite+aiosqlite:///./auth.db`	`standard`
PostgreSQL	`postgresql+asyncpg://...`	`postgresql` (asyncpg)
MySQL	`mysql+asyncmy://...`	install asyncmy yourself

SQLite in production: not recommended. SQLite does not allow multiple concurrent writers; multi-worker FastAPI deployments will hit database is locked. Use PostgreSQL in production.

Common mistakes¶

Forgetting await adapter.create_tables() — endpoints return 500 no such table.
Switching DB engines in place — tables are not migrated automatically. Recreate or use Alembic.
Sharing a single engine across processes — every process should construct its own SQLAlchemyAdapter.