Metadata-Version: 2.5
Name: advanced_alchemy
Version: 1.13.0b2
Summary: Ready-to-go SQLAlchemy concoctions.
Project-URL: Changelog, https://advanced-alchemy.litestar.dev/latest/changelog
Project-URL: Discord, https://discord.gg/litestar
Project-URL: Documentation, https://advanced-alchemy.litestar.dev/latest/
Project-URL: Funding, https://github.com/sponsors/litestar-org
Project-URL: Homepage, https://advanced-alchemy.litestar.dev/latest/
Project-URL: Issue, https://github.com/litestar-org/advanced-alchemy/issues/
Project-URL: Source, https://github.com/litestar-org/advanced-alchemy
Author-email: Cody Fincher <cody.fincher@gmail.com>, Peter Schutt <peter.github@proton.me>, Janek Nouvertné <j.a.nouvertne@posteo.de>, Jacob Coffee <jacob@z7x.org>
Maintainer-email: Litestar Developers <hello@litestar.dev>, Cody Fincher <cody@litestar.dev>, Jacob Coffee <jacob@litestar.dev>, Janek Nouvertné <janek@litestar.dev>, Julien Courtes <julien@litestar.dev>
License: MIT
License-File: LICENSE
Keywords: alembic,fastapi,flask,litestar,sanic,sqlalchemy
Classifier: Development Status :: 3 - Alpha
Classifier: Environment :: Web Environment
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: System Administrators
Classifier: License :: OSI Approved :: MIT License
Classifier: Natural Language :: English
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Programming Language :: Python :: 3.15
Classifier: Topic :: Database
Classifier: Topic :: Database :: Database Engines/Servers
Classifier: Topic :: Software Development
Classifier: Typing :: Typed
Requires-Python: >=3.9
Requires-Dist: alembic>=1.12.0
Requires-Dist: eval-type-backport; python_full_version < '3.10'
Requires-Dist: exceptiongroup; python_full_version < '3.11'
Requires-Dist: greenlet
Requires-Dist: sqlalchemy>=2.0.41
Requires-Dist: typing-extensions>=4.0.0
Provides-Extra: aioodbc
Requires-Dist: aioodbc>=0.5.0; extra == 'aioodbc'
Provides-Extra: aiosqlite
Requires-Dist: aiosqlite>=0.20.0; extra == 'aiosqlite'
Provides-Extra: argon2
Requires-Dist: argon2-cffi; extra == 'argon2'
Provides-Extra: asyncmy
Requires-Dist: asyncmy>=0.2.9; extra == 'asyncmy'
Provides-Extra: asyncpg
Requires-Dist: alembic-postgresql-enum>=1.10.0; extra == 'asyncpg'
Requires-Dist: asyncpg>=0.29.0; extra == 'asyncpg'
Provides-Extra: bcrypt
Requires-Dist: bcrypt>=5.0.0; extra == 'bcrypt'
Provides-Extra: cli
Requires-Dist: rich-click; extra == 'cli'
Provides-Extra: cockroachdb-asyncpg
Requires-Dist: asyncpg>=0.29.0; extra == 'cockroachdb-asyncpg'
Requires-Dist: sqlalchemy-cockroachdb>=2.0.2; extra == 'cockroachdb-asyncpg'
Provides-Extra: cockroachdb-psycopg
Requires-Dist: psycopg[binary,pool]>=3.2.3; extra == 'cockroachdb-psycopg'
Requires-Dist: sqlalchemy-cockroachdb>=2.0.2; extra == 'cockroachdb-psycopg'
Provides-Extra: cryptography
Requires-Dist: cryptography; extra == 'cryptography'
Provides-Extra: dogpile
Requires-Dist: dogpile-cache; extra == 'dogpile'
Provides-Extra: duckdb
Requires-Dist: duckdb-engine>=0.13.4; extra == 'duckdb'
Requires-Dist: duckdb>=1.1.2; extra == 'duckdb'
Requires-Dist: pytz>=2024.2; extra == 'duckdb'
Provides-Extra: fastapi
Requires-Dist: fastapi>=0.115.3; extra == 'fastapi'
Provides-Extra: flask
Requires-Dist: flask-sqlalchemy>=3.1.1; extra == 'flask'
Requires-Dist: flask[async]; extra == 'flask'
Provides-Extra: fsspec
Requires-Dist: fsspec; extra == 'fsspec'
Provides-Extra: litestar
Requires-Dist: litestar[cli]>=2.23.0; extra == 'litestar'
Provides-Extra: nanoid
Requires-Dist: fastnanoid>=0.4.1; extra == 'nanoid'
Provides-Extra: obstore
Requires-Dist: obstore; extra == 'obstore'
Provides-Extra: oracledb
Requires-Dist: oracledb>=2.4.1; extra == 'oracledb'
Provides-Extra: passlib
Requires-Dist: passlib[argon2]; extra == 'passlib'
Provides-Extra: pgvector
Requires-Dist: pgvector; extra == 'pgvector'
Provides-Extra: psycopg
Requires-Dist: alembic-postgresql-enum>=1.10.0; extra == 'psycopg'
Requires-Dist: psycopg[binary,pool]>=3.2.3; extra == 'psycopg'
Provides-Extra: psycopg2
Requires-Dist: alembic-postgresql-enum>=1.10.0; extra == 'psycopg2'
Requires-Dist: psycopg2-binary>=2.9.10; extra == 'psycopg2'
Provides-Extra: pwdlib
Requires-Dist: pwdlib[argon2]; extra == 'pwdlib'
Provides-Extra: pyodbc
Requires-Dist: pyodbc>=5.2.0; extra == 'pyodbc'
Provides-Extra: pyotp
Requires-Dist: pyotp; extra == 'pyotp'
Provides-Extra: sanic
Requires-Dist: sanic; extra == 'sanic'
Requires-Dist: sanic[ext]>=24.6.0; extra == 'sanic'
Provides-Extra: spanner
Requires-Dist: sqlalchemy-spanner>=1.7.0; extra == 'spanner'
Provides-Extra: starlette
Requires-Dist: starlette; extra == 'starlette'
Provides-Extra: uuid
Requires-Dist: uuid-utils>=0.6.1; extra == 'uuid'
Description-Content-Type: text/markdown

<!-- markdownlint-disable -->
<div align="center">
  <img src="https://raw.githubusercontent.com/litestar-org/branding/main/assets/projects/advanced-alchemy/banners/banner-inline-light.png" alt="Advanced Alchemy" width="560" />

<p><strong>A carefully crafted, thoroughly tested, optimized companion library for SQLAlchemy.</strong></p>

<!-- prettier-ignore-start -->
[![CI](https://github.com/litestar-org/advanced-alchemy/actions/workflows/ci.yml/badge.svg)](https://github.com/litestar-org/advanced-alchemy/actions/workflows/ci.yml)
[![Coverage](https://codecov.io/github/litestar-org/advanced-alchemy/graph/badge.svg?token=vKez4Pycrc)](https://codecov.io/github/litestar-org/advanced-alchemy)
[![PyPI](https://img.shields.io/pypi/v/advanced-alchemy)](https://pypi.org/project/advanced-alchemy/)
[![Python](https://img.shields.io/pypi/pyversions/advanced-alchemy)](https://pypi.org/project/advanced-alchemy/)
[![License](https://img.shields.io/pypi/l/advanced-alchemy)](https://github.com/litestar-org/advanced-alchemy/blob/main/LICENSE)
[![Docs](https://img.shields.io/badge/docs-advanced--alchemy.litestar.dev-1d4ed8)](https://advanced-alchemy.litestar.dev/latest/)

[Documentation](https://advanced-alchemy.litestar.dev/latest/) · [Getting Started](https://advanced-alchemy.litestar.dev/latest/getting-started.html) · [Data Modeling](https://advanced-alchemy.litestar.dev/latest/usage/modeling/index.html) · [Repositories](https://advanced-alchemy.litestar.dev/latest/usage/repositories/index.html) · [Services](https://advanced-alchemy.litestar.dev/latest/usage/services.html) · [Frameworks](https://advanced-alchemy.litestar.dev/latest/usage/frameworks/index.html) · [API Reference](https://advanced-alchemy.litestar.dev/latest/reference/index.html)
<!-- prettier-ignore-end -->

</div>
<!-- markdownlint-restore -->

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

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

> [!NOTE]
> See [the installation guide][install-guide] in the documentation for optional database driver and framework extras.

### Repositories

Advanced Alchemy provides asynchronous and synchronous repository classes for CRUD, filtering, pagination, and bulk operations on SQLAlchemy models.
<!-- markdownlint-disable -->
<details>
<summary>Sync repository CRUD example</summary>
<!-- markdownlint-restore -->

```python
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.")
```

</details>

For a full standalone example, see the sample [here][standalone-example].

### 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.
<!-- markdownlint-disable -->
<details>
<summary>Sync service CRUD example</summary>
<!-- markdownlint-restore -->

```python
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.")
```

</details>

### 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]"`).

<!-- markdownlint-disable -->
<details>
<summary>Litestar example</summary>
<!-- markdownlint-restore -->

```python
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])
```

</details>

For a full Litestar example, see [here][litestar-example].

#### Flask

<!-- markdownlint-disable -->
<details>
<summary>Flask example</summary>
<!-- markdownlint-restore -->

```python
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,
)
```

</details>

For a full Flask example, see [here][flask-example].

#### FastAPI

<!-- markdownlint-disable -->
<details>
<summary>FastAPI example</summary>
<!-- markdownlint-restore -->

```python
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,
)
```

</details>

For a full FastAPI example with optional CLI integration, see [here][fastapi-example].

#### Starlette

<!-- markdownlint-disable -->
<details>
<summary>Starlette example</summary>
<!-- markdownlint-restore -->

```python
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,
)
```

</details>

#### Sanic

<!-- markdownlint-disable -->
<details>
<summary>Sanic example</summary>
<!-- markdownlint-restore -->

```python
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)
```

</details>

For a full Sanic example, see [here][sanic-example].

## Contributing

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

Before contributing, please review the [contribution guide][contributing].

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

<!-- markdownlint-disable -->
<hr />
<p align="center">

</p>

[litestar-org]: https://github.com/litestar-org
[contributing]: https://advanced-alchemy.litestar.dev/latest/contribution-guide.html
[discord]: https://discord.gg/litestar
[litestar-discussions]: https://github.com/orgs/litestar-org/discussions
[project-discussions]: https://github.com/litestar-org/advanced-alchemy/discussions
[project-docs]: https://advanced-alchemy.litestar.dev
[install-guide]: https://advanced-alchemy.litestar.dev/latest/getting-started.html#installation
[fastapi-example]: https://github.com/litestar-org/advanced-alchemy/blob/main/examples/fastapi/fastapi_service.py
[flask-example]: https://github.com/litestar-org/advanced-alchemy/blob/main/examples/flask/flask_services.py
[litestar-example]: https://github.com/litestar-org/advanced-alchemy/blob/main/examples/litestar/litestar_service.py
[sanic-example]: https://github.com/litestar-org/advanced-alchemy/blob/main/examples/sanic.py
[standalone-example]: https://github.com/litestar-org/advanced-alchemy/blob/main/examples/standalone.py
