Skip to main content
Pre-release

This release is a pre-release and may not be stable for production use.

Advanced Alchemy

A carefully crafted, thoroughly tested, optimized companion library for SQLAlchemy.

CI Coverage PyPI Python License Docs

Documentation · Getting Started · Data Modeling · Repositories · Services · Frameworks · API Reference

Advanced Alchemy builds on SQLAlchemy 2.0+ and Alembic 1.12+ with synchronous and asynchronous repositories, typed service layers, declarative bases and mixins, portable custom column types, replica routing, query caching, and web framework plugins. Models stay standard SQLAlchemy declarative classes (including SQLModel table=True models and composite primary keys) while gaining consistent CRUD, filtering, pagination, and dialect-native bulk upserts.

Capabilities

Area Overview
Declarative Bases & Mixins Ready-to-use UUIDBase, UUIDv6Base, UUIDv7Base, NanoIDBase, and BigIntBase models (plus *AuditBase variants with created_at and updated_at), SlugKey, SoftDeleteMixin, UniqueMixin, sentinel columns, and SQLModel compatibility.
Repositories & Services Sync and asyncio repositories with get_many_and_count, statement and relationship filters, FilterSet, and native bulk upserts (ON CONFLICT and MERGE), paired with services hydrating Pydantic, msgspec, dataclass, attrs, and SQLModel schemas.
Custom Column Types Dialect-adaptive GUID, DateTimeUTC, JsonB (including Oracle ORA_JSONB), BigIntIdentity, Vector, EncryptedString (pgcrypto and Fernet), PasswordHash (Argon2, Pwdlib, Passlib), TOTPSecret, OneTimeCode, and StoredObject (fsspec and obstore).
Database Backends Tested across PostgreSQL (asyncpg, psycopg), CockroachDB, SQLite (sqlite3, aiosqlite), MySQL & MariaDB (asyncmy), Oracle 18c, 23ai, and 26ai (oracledb), Google Cloud Spanner, DuckDB (duckdb_engine), and SQL Server (pyodbc, aioodbc).
Migrations, CLI & Seeding Programmatic Alembic commands and the alchemy CLI, read/write replica routing with primary pinning after writes, Dogpile L2 query caching, and streaming JSON, JSONL, and CSV fixture seeding.
Web Frameworks First-party plugins, per-request session lifecycle management, dependency injection providers, file-storage lifecycle hooks, and CLI integration for Litestar, FastAPI, Starlette, Flask, and Sanic.

Usage

Installation

uv add advanced-alchemy
# or
pip install advanced-alchemy

Repositories

Advanced Alchemy provides asynchronous and synchronous repository classes for CRUD, filtering, pagination, and bulk operations on SQLAlchemy models.

Sync repository CRUD example
from sqlalchemy.orm import Mapped

from advanced_alchemy import base, config, repository
from advanced_alchemy.filters import LimitOffsetFilter


class User(base.UUIDBase):
    __tablename__ = "user_account"
    email: Mapped[str]
    name: Mapped[str]


class UserRepository(repository.SQLAlchemySyncRepository[User]):
    """User repository."""

    model_type = User


db = config.SQLAlchemySyncConfig(
    connection_string="sqlite:///:memory:",
    session_config=config.SyncSessionConfig(expire_on_commit=False),
)

with db.get_engine().begin() as conn:
    User.metadata.create_all(conn)

with db.get_session() as db_session:
    repo = UserRepository(session=db_session)
    # 1) Create multiple users with `add_many`.
    bulk_users = [
        {"email": "cody@litestar.dev", "name": "Cody"},
        {"email": "janek@litestar.dev", "name": "Janek"},
        {"email": "peter@litestar.dev", "name": "Peter"},
        {"email": "jacob@litestar.dev", "name": "Jacob"},
    ]
    objs = repo.add_many([User(**raw_user) for raw_user in bulk_users])
    db_session.commit()
    print(f"Created {len(objs)} new objects.")

    # 2) Select paginated data and total row count, passing equality filters as kwargs.
    created_objs, total_objs = repo.get_many_and_count(LimitOffsetFilter(limit=10, offset=0), name="Cody")
    print(f"Selected {len(created_objs)} records out of a total of {total_objs}.")

    # 3) Delete the selected batch of records.
    deleted_objs = repo.delete_many([new_obj.id for new_obj in created_objs])
    print(f"Removed {len(deleted_objs)} records out of a total of {total_objs}.")

    # 4) Count the remaining rows.
    remaining_count = repo.count()
    print(f"Found {remaining_count} remaining records after delete.")

For a full standalone example, see the sample here.

Services

Service classes wrap repositories and accept dictionaries, SQLAlchemy models, or typed DTO schemas (Pydantic, msgspec, dataclass, attrs, SQLModel), handling model hydration and output conversion.

Sync service CRUD example
from sqlalchemy.orm import Mapped

from advanced_alchemy import base, config, repository, service
from advanced_alchemy.filters import LimitOffsetFilter


class User(base.UUIDBase):
    __tablename__ = "user_account"
    email: Mapped[str]
    name: Mapped[str]


class UserService(service.SQLAlchemySyncRepositoryService[User]):
    """User service."""

    class Repo(repository.SQLAlchemySyncRepository[User]):
        """User repository."""

        model_type = User

    repository_type = Repo


db = config.SQLAlchemySyncConfig(
    connection_string="sqlite:///:memory:",
    session_config=config.SyncSessionConfig(expire_on_commit=False),
)

with db.get_engine().begin() as conn:
    User.metadata.create_all(conn)

with db.get_session() as db_session:
    user_service = UserService(session=db_session)
    # 1) Create multiple users directly from dictionaries.
    objs = user_service.create_many(
        [
            {"email": "cody@litestar.dev", "name": "Cody"},
            {"email": "janek@litestar.dev", "name": "Janek"},
            {"email": "peter@litestar.dev", "name": "Peter"},
            {"email": "jacob@litestar.dev", "name": "Jacob"},
        ]
    )
    print(f"Created {len(objs)} new objects.")

    # 2) Select paginated data and total row count, passing equality filters as kwargs.
    created_objs, total_objs = user_service.get_many_and_count(LimitOffsetFilter(limit=10, offset=0), name="Cody")
    print(f"Selected {len(created_objs)} records out of a total of {total_objs}.")

    # 3) Delete the selected batch of records.
    deleted_objs = user_service.delete_many([new_obj.id for new_obj in created_objs])
    print(f"Removed {len(deleted_objs)} records out of a total of {total_objs}.")

    # 4) Count the remaining rows.
    remaining_count = user_service.count()
    print(f"Found {remaining_count} remaining records after delete.")

Web Frameworks

Advanced Alchemy integrates with any Python web framework and includes first-party extensions for Litestar, Flask, FastAPI, Starlette, and Sanic.

Litestar

Advanced Alchemy is the official SQLAlchemy integration for Litestar (uv add "litestar[sqlalchemy]" or pip install "litestar[sqlalchemy]").

Litestar example
from litestar import Litestar
from litestar.plugins.sqlalchemy import SQLAlchemyAsyncConfig, SQLAlchemyPlugin

alchemy = SQLAlchemyPlugin(
    config=SQLAlchemyAsyncConfig(connection_string="sqlite+aiosqlite:///test.sqlite"),
)
app = Litestar(plugins=[alchemy])

For a full Litestar example, see here.

Flask

Flask example
from flask import Flask

from advanced_alchemy.extensions.flask import AdvancedAlchemy, SQLAlchemySyncConfig

app = Flask(__name__)
alchemy = AdvancedAlchemy(
    config=SQLAlchemySyncConfig(connection_string="sqlite:///:memory:"),
    app=app,
)

For a full Flask example, see here.

FastAPI

FastAPI example
from fastapi import FastAPI

from advanced_alchemy.extensions.fastapi import AdvancedAlchemy, SQLAlchemyAsyncConfig

app = FastAPI()
alchemy = AdvancedAlchemy(
    config=SQLAlchemyAsyncConfig(connection_string="sqlite+aiosqlite:///test.sqlite"),
    app=app,
)

For a full FastAPI example with optional CLI integration, see here.

Starlette

Starlette example
from starlette.applications import Starlette

from advanced_alchemy.extensions.starlette import AdvancedAlchemy, SQLAlchemyAsyncConfig

app = Starlette()
alchemy = AdvancedAlchemy(
    config=SQLAlchemyAsyncConfig(connection_string="sqlite+aiosqlite:///test.sqlite"),
    app=app,
)

Sanic

Sanic example
from sanic import Sanic
from sanic_ext import Extend

from advanced_alchemy.extensions.sanic import AdvancedAlchemy, SQLAlchemyAsyncConfig

app = Sanic("AlchemySanicApp")
alchemy = AdvancedAlchemy(
    sqlalchemy_config=SQLAlchemyAsyncConfig(connection_string="sqlite+aiosqlite:///test.sqlite"),
)
Extend.register(alchemy)

For a full Sanic example, see here.

Contributing

All Litestar Organization projects are community-driven and open to contributions of any size.

Before contributing, please review the contribution guide.

If you have any questions, reach out to us on Discord, our org-wide GitHub discussions page, or the project-specific GitHub discussions page.


Metadata

Release files for advanced-alchemy 1.13.0b2

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for advanced-alchemy 1.13.0b2
File Size Uploaded Provenance Actions
advanced_alchemy-1.13.0b2.tar.gz 2.2 MB With provenance Details for advanced_alchemy-1.13.0b2.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for advanced-alchemy 1.13.0b2
File Interpreter ABI Platform Actions
advanced_alchemy-1.13.0b2-py3-none-any.whl Python 3 none any Details for advanced_alchemy-1.13.0b2-py3-none-any.whl

Total release size: 2.7 MB

Release files / advanced_alchemy-1.13.0b2.tar.gz

Download URL advanced_alchemy-1.13.0b2.tar.gz
Size 2.2 MB
Tags Source
SHA-256 checksum
How to use checksums
43298b99f6854945d487e37f394411c8a24fda465388362e590725af8a9c8d31
BLAKE2b-256 checksum
How to use checksums
1d0036fbbe5ce6e84823254ee81a6af40d6af1730f3aee287fbfe109ac2cc491
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.16

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 18, 2026.

Transparency log

Release files / advanced_alchemy-1.13.0b2-py3-none-any.whl

Download URL advanced_alchemy-1.13.0b2-py3-none-any.whl
Size 445.2 kB
Tags Python 3
SHA-256 checksum
How to use checksums
82ee547f21eb3d1a5eb10e96b54569f266563328d04ce909f23aafd91afca142
BLAKE2b-256 checksum
How to use checksums
e09ac3960186955ea794175ec140497fd0ad72bc4d74bbbc4d1f1767377196c9
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.16

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 18, 2026.

Transparency log

Security for advanced-alchemy 1.13.0b2

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

Full provenance

Both files in this release have been uploaded with provenance metadata.

Select from 2 files

Attestation for advanced_alchemy-1.13.0b2.tar.gz

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 18, 2026.

Transparency log

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Release history Release notifications | RSS feed

This release
Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page