This release is a pre-release and may not be stable for production use.
A carefully crafted, thoroughly tested, optimized companion library for SQLAlchemy.
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.0b1
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | Provenance | Actions |
|---|---|---|---|---|
| advanced_alchemy-1.13.0b1.tar.gz | 2.2 MB | With provenance | Details for advanced_alchemy-1.13.0b1.tar.gz |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| advanced_alchemy-1.13.0b1-py3-none-any.whl | Python 3 | none | any | Details for advanced_alchemy-1.13.0b1-py3-none-any.whl |
Total release size: 2.7 MB
Release files / advanced_alchemy-1.13.0b1.tar.gz
| Download URL | advanced_alchemy-1.13.0b1.tar.gz |
|---|---|
| Size | 2.2 MB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
5b5b48aa088330d5675c165fe49dd1ad191a7a39fc7fd33939cd50ac97bf2adb
|
|
BLAKE2b-256 checksum How to use checksums |
37f42c9f05e474cf2dd63fff382a0f9202a41ebf38c1e4a2cf6fb63fea3e0e56
|
| 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 17, 2026.
Transparency logRelease files / advanced_alchemy-1.13.0b1-py3-none-any.whl
| Download URL | advanced_alchemy-1.13.0b1-py3-none-any.whl |
|---|---|
| Size | 444.4 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
22e31df9014c7a28ad35fd28e1a135d6798313f5fe8153021a93f1064608399e
|
|
BLAKE2b-256 checksum How to use checksums |
b6d402ffcd60c5c54f786beeee0a39eba155bf3f89c34a1a699bf770eaed1c4a
|
| 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 17, 2026.
Transparency logSecurity for advanced-alchemy 1.13.0b1
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.
Attestation for advanced_alchemy-1.13.0b1.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 17, 2026.
Transparency logA 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.