<!-- markdownlint-disable -->
<p align="center">
<!-- github-banner-start -->
<img src="https://raw.githubusercontent.com/litestar-org/branding/main/assets/Branding%20-%20SVG%20-%20Transparent/AA%20-%20Banner%20-%20Inline%20-%20Light.svg#gh-light-mode-only" alt="Litestar Logo - Light" width="100%" height="auto" />
<img src="https://raw.githubusercontent.com/litestar-org/branding/main/assets/Branding%20-%20SVG%20-%20Transparent/AA%20-%20Banner%20-%20Inline%20-%20Dark.svg#gh-dark-mode-only" alt="Litestar Logo - Dark" width="100%" height="auto" />
<!-- github-banner-end -->
</p>
<div align="center">
<!-- markdownlint-restore -->
<!-- prettier-ignore-start -->
| Project | | Status |
|-----------|:----|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| CI/CD | | [![Latest Release](https://github.com/litestar-org/advanced-alchemy/actions/workflows/publish.yml/badge.svg)](https://github.com/litestar-org/advanced-alchemy/actions/workflows/publish.yml) [![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) [![Documentation Building](https://github.com/litestar-org/advanced-alchemy/actions/workflows/docs.yml/badge.svg?branch=main)](https://github.com/litestar-org/advanced-alchemy/actions/workflows/docs.yml) |
| Quality | | [![Coverage](https://codecov.io/github/litestar-org/advanced-alchemy/graph/badge.svg?token=vKez4Pycrc)](https://codecov.io/github/litestar-org/advanced-alchemy) [![Quality Gate Status](https://sonarcloud.io/api/project_badges/measure?project=litestar-org_advanced-alchemy&metric=alert_status)](https://sonarcloud.io/summary/new_code?id=litestar-org_advanced-alchemy) [![Maintainability Rating](https://sonarcloud.io/api/project_badges/measure?project=litestar-org_advanced-alchemy&metric=sqale_rating)](https://sonarcloud.io/summary/new_code?id=litestar-org_advanced-alchemy) [![Reliability Rating](https://sonarcloud.io/api/project_badges/measure?project=litestar-org_advanced-alchemy&metric=reliability_rating)](https://sonarcloud.io/summary/new_code?id=litestar-org_advanced-alchemy) [![Security Rating](https://sonarcloud.io/api/project_badges/measure?project=litestar-org_advanced-alchemy&metric=security_rating)](https://sonarcloud.io/summary/new_code?id=litestar-org_advanced-alchemy) |
| Package | | [![PyPI - Version](https://img.shields.io/pypi/v/advanced-alchemy?labelColor=202235&color=edb641&logo=python&logoColor=edb641)](https://badge.fury.io/py/litestar) ![PyPI - Support Python Versions](https://img.shields.io/pypi/pyversions/litestar?labelColor=202235&color=edb641&logo=python&logoColor=edb641) ![Advanced Alchemy PyPI - Downloads](https://img.shields.io/pypi/dm/advanced-alchemy?logo=python&label=package%20downloads&labelColor=202235&color=edb641&logoColor=edb641) |
| Community | | [![Reddit](https://img.shields.io/reddit/subreddit-subscribers/litestarapi?label=r%2FLitestar&logo=reddit&labelColor=202235&color=edb641&logoColor=edb641)](https://reddit.com/r/litestarapi) [![Discord](https://img.shields.io/discord/919193495116337154?labelColor=202235&color=edb641&label=chat%20on%20discord&logo=discord&logoColor=edb641)](https://discord.gg/litestar) [![Matrix](https://img.shields.io/badge/chat%20on%20Matrix-bridged-202235?labelColor=202235&color=edb641&logo=matrix&logoColor=edb641)](https://matrix.to/#/#litestar:matrix.org) [![Medium](https://img.shields.io/badge/Medium-202235?labelColor=202235&color=edb641&logo=medium&logoColor=edb641)](https://blog.litestar.dev) [![Twitter](https://img.shields.io/twitter/follow/LitestarAPI?labelColor=202235&color=edb641&logo=twitter&logoColor=edb641&style=flat)](https://twitter.com/LitestarAPI) [![Blog](https://img.shields.io/badge/Blog-litestar.dev-202235?logo=blogger&labelColor=202235&color=edb641&logoColor=edb641)](https://blog.litestar.dev) |
| Meta | | [![Litestar Project](https://img.shields.io/badge/Litestar%20Org-%E2%AD%90%20Advanced%20Alchemy-202235.svg?logo=python&labelColor=202235&color=edb641&logoColor=edb641)](https://github.com/litestar-org/advanced-alchemy) [![types - Mypy](https://img.shields.io/badge/types-Mypy-202235.svg?logo=python&labelColor=202235&color=edb641&logoColor=edb641)](https://github.com/python/mypy) [![License - MIT](https://img.shields.io/badge/license-MIT-202235.svg?logo=python&labelColor=202235&color=edb641&logoColor=edb641)](https://spdx.org/licenses/) [![Litestar Sponsors](https://img.shields.io/badge/Sponsor-%E2%9D%A4-%23edb641.svg?&logo=github&logoColor=edb641&labelColor=202235)](https://github.com/sponsors/litestar-org) [![linting - Ruff](https://img.shields.io/endpoint?url=https://raw.githubusercontent.com/charliermarsh/ruff/main/assets/badge/v2.json&labelColor=202235)](https://github.com/astral-sh/ruff) [![code style - Ruff](https://img.shields.io/endpoint?url=https://raw.githubusercontent.com/astral-sh/ruff/main/assets/badge/format.json&labelColor=202235)](https://github.com/psf/black) |
</div>
# Advanced Alchemy
Check out the [project documentation][project-docs] 📚 for more information.
## About
A carefully crafted, thoroughly tested, optimized companion library for SQLAlchemy,
offering:
- Sync and async repositories, featuring common CRUD and highly optimized bulk operations
- Integration with major web frameworks including Litestar, Starlette, FastAPI, Sanic
- Custom-built alembic configuration and CLI with optional framework integration
- Utility base classes with audit columns, primary keys and utility functions
- Optimized JSON types including a custom JSON type for Oracle
- Integrated support for UUID6 and UUID7 using [`uuid-utils`](https://github.com/aminalaee/uuid-utils) (install with the `uuid` extra)
- Integrated support for Nano ID using [`fastnanoid`](https://github.com/oliverlambson/fastnanoid) (install with the `nanoid` extra)
- Pre-configured base classes with audit columns UUID or Big Integer primary keys and
a [sentinel column](https://docs.sqlalchemy.org/en/20/core/connections.html#configuring-sentinel-columns).
- Synchronous and asynchronous repositories featuring:
- Common CRUD operations for SQLAlchemy models
- Bulk inserts, updates, upserts, and deletes with dialect-specific enhancements
- Integrated counts, pagination, sorting, filtering with `LIKE`, `IN`, and dates before and/or after.
- Tested support for multiple database backends including:
- SQLite via [aiosqlite](https://aiosqlite.omnilib.dev/en/stable/) or [sqlite](https://docs.python.org/3/library/sqlite3.html)
- Postgres via [asyncpg](https://magicstack.github.io/asyncpg/current/) or [psycopg3 (async or sync)](https://www.psycopg.org/psycopg3/)
- MySQL via [asyncmy](https://github.com/long2ice/asyncmy)
- Oracle via [oracledb (async or sync)](https://oracle.github.io/python-oracledb/) (tested on 18c and 23c)
- Google Spanner via [spanner-sqlalchemy](https://github.com/googleapis/python-spanner-sqlalchemy/)
- DuckDB via [duckdb_engine](https://github.com/Mause/duckdb_engine)
- Microsoft SQL Server via [pyodbc](https://github.com/mkleehammer/pyodbc) or [aioodbc](https://github.com/aio-libs/aioodbc)
- CockroachDB via [sqlalchemy-cockroachdb (async or sync)](https://github.com/cockroachdb/sqlalchemy-cockroachdb)
- ...and much more
## Usage
### Installation
```shell
pip install advanced-alchemy
```
> [!IMPORTANT]\
> Check out [the installation guide][install-guide] in our official documentation!
### Repositories
Advanced Alchemy includes a set of asynchronous and synchronous repository classes for easy CRUD
operations on your SQLAlchemy models.
<!-- markdownlint-disable -->
<details>
<summary>Click to expand the example</summary>
<!-- markdownlint-restore -->
```python
from advanced_alchemy.base import UUIDBase
from advanced_alchemy.filters import LimitOffset
from advanced_alchemy.repository import SQLAlchemySyncRepository
from sqlalchemy import create_engine
from sqlalchemy.orm import Mapped, sessionmaker
class User(UUIDBase):
# you can optionally override the generated table name by manually setting it.
__tablename__ = "user_account" # type: ignore[assignment]
email: Mapped[str]
name: Mapped[str]
class UserRepository(SQLAlchemySyncRepository[User]):
"""User repository."""
model_type = User
# use any compatible sqlalchemy engine.
engine = create_engine("duckdb:///:memory:")
session_factory = sessionmaker(engine, expire_on_commit=False)
# Initializes the database.
with engine.begin() as conn:
User.metadata.create_all(conn)
with session_factory() 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. Pass additional filters as kwargs
created_objs, total_objs = repo.list_and_count(LimitOffset(limit=10, offset=0), name="Cody")
print(f"Selected {len(created_objs)} records out of a total of {total_objs}.")
# 3) Let's remove the batch of records selected.
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) Let's 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
Advanced Alchemy includes an additional service class to make working with a repository easier.
This class is designed to accept data as a dictionary or SQLAlchemy model,
and it will handle the type conversions for you.
<!-- markdownlint-disable -->
<details>
<summary>Here's the same example from above but using a service to create the data:</summary>
<!-- markdownlint-restore -->
```python
from advanced_alchemy.base import UUIDBase
from advanced_alchemy.filters import LimitOffset
from advanced_alchemy import SQLAlchemySyncRepository, SQLAlchemySyncRepositoryService
from sqlalchemy import create_engine
from sqlalchemy.orm import Mapped, sessionmaker
class User(UUIDBase):
# you can optionally override the generated table name by manually setting it.
__tablename__ = "user_account" # type: ignore[assignment]
email: Mapped[str]
name: Mapped[str]
class UserRepository(SQLAlchemySyncRepository[User]):
"""User repository."""
model_type = User
class UserService(SQLAlchemySyncRepositoryService[User]):
"""User repository."""
repository_type = UserRepository
# use any compatible sqlalchemy engine.
engine = create_engine("duckdb:///:memory:")
session_factory = sessionmaker(engine, expire_on_commit=False)
# Initializes the database.
with engine.begin() as conn:
User.metadata.create_all(conn)
with session_factory() as db_session:
service = UserService(session=db_session)
# 1) Create multiple users with `add_many`
objs = 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(objs)
print(f"Created {len(objs)} new objects.")
# 2) Select paginated data and total row count. Pass additional filters as kwargs
created_objs, total_objs = service.list_and_count(LimitOffset(limit=10, offset=0), name="Cody")
print(f"Selected {len(created_objs)} records out of a total of {total_objs}.")
# 3) Let's remove the batch of records selected.
deleted_objs = 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) Let's count the remaining rows
remaining_count = service.count()
print(f"Found {remaining_count} remaining records after delete.")
```
</details>
### Web Frameworks
Advanced Alchemy works with nearly all Python web frameworks.
Several helpers for popular libraries are included, and additional PRs to support others are welcomed.
#### Litestar
Advanced Alchemy is the official SQLAlchemy integration for Litestar.
In addition to installing with `pip install advanced-alchemy`,
it can also be installed as a Litestar extra with `pip install litestar[sqlalchemy]`.
<!-- markdownlint-disable -->
<details>
<summary>Litestar Example</summary>
<!-- markdownlint-restore -->
```python
from litestar import Litestar
from litestar.plugins.sqlalchemy import SQLAlchemyPlugin, SQLAlchemyAsyncConfig
# alternately...
# from advanced_alchemy.extensions.litestar.plugins import SQLAlchemyPlugin
# from advanced_alchemy.extensions.litestar.plugins.init.config import SQLAlchemyAsyncConfig
alchemy = SQLAlchemyPlugin(
config=SQLAlchemyAsyncConfig(connection_string="sqlite+aiosqlite:///test.sqlite"),
)
app = Litestar(plugins=[alchemy])
```
</details>
For a full Litestar example, check [here][litestar-example]
#### FastAPI
<!-- markdownlint-disable -->
<details>
<summary>FastAPI Example</summary>
<!-- markdownlint-restore -->
```python
from fastapi import FastAPI
from advanced_alchemy.config import SQLAlchemyAsyncConfig
from advanced_alchemy.extensions.starlette import StarletteAdvancedAlchemy
app = FastAPI()
alchemy = StarletteAdvancedAlchemy(
config=SQLAlchemyAsyncConfig(connection_string="sqlite+aiosqlite:///test.sqlite"), app=app,
)
```
</details>
For a full FastAPI example, see [here][fastapi-example]
#### Starlette
<!-- markdownlint-disable -->
<details>
<summary>Pre-built Example Apps</summary>
<!-- markdownlint-restore -->
```python
from starlette.applications import Starlette
from advanced_alchemy.config import SQLAlchemyAsyncConfig
from advanced_alchemy.extensions.starlette import StarletteAdvancedAlchemy
app = Starlette()
alchemy = StarletteAdvancedAlchemy(
config=SQLAlchemyAsyncConfig(connection_string="sqlite+aiosqlite:///test.sqlite"), app=app,
)
```
</details>
#### Sanic
<!-- markdownlint-disable -->
<details>
<summary>Pre-built Example Apps</summary>
<!-- markdownlint-restore -->
```python
from sanic import Sanic
from sanic_ext import Extend
from advanced_alchemy.config import SQLAlchemyAsyncConfig
from advanced_alchemy.extensions.sanic import SanicAdvancedAlchemy
app = Sanic("AlchemySanicApp")
alchemy = SanicAdvancedAlchemy(
sqlalchemy_config=SQLAlchemyAsyncConfig(connection_string="sqlite+aiosqlite:///test.sqlite"),
)
Extend.register(alchemy)
```
</details>
## Contributing
All [Litestar Organization][litestar-org] projects will always be a community-centered, available for 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">
<!-- github-banner-start -->
<img src="https://raw.githubusercontent.com/litestar-org/branding/main/assets/Branding%20-%20SVG%20-%20Transparent/Organization%20Project%20-%20Banner%20-%20Inline%20-%20Dark.svg" alt="Litestar Logo - Light" width="40%" height="auto" />
<br>An official <a href="https://github.com/litestar-org">Litestar Organization</a> Project
<!-- github-banner-end -->
</p>
[litestar-org]: https://github.com/litestar-org
[contributing]: https://docs.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://docs.advanced-alchemy.litestar.dev
[install-guide]: https://docs.advanced-alchemy.litestar.dev/latest/#installation
[fastapi-example]: https://github.com/litestar-org/advanced-alchemy/blob/main/examples/fastapi.py
[litestar-example]: https://github.com/litestar-org/advanced-alchemy/blob/main/examples/litestar.py
[standalone-example]: https://github.com/litestar-org/advanced-alchemy/blob/main/examples/standalone.py
Raw data
{
"_id": null,
"home_page": null,
"name": "advanced-alchemy",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": "Litestar Developers <hello@litestar.dev>, Cody Fincher <cody@litestar.dev>, Jacob Coffee <jacob@litestar.dev>, Janek Nouvertn\u00e9 <provinzkraut@litestar.dev>, Peter Schutt <peter@litestar.dev>, Visakh Unnikrishnan <guacs@litestar.dev>, Alc <alc@litestar.dev>",
"keywords": "alembic, fastapi, flask, litestar, sanic, sqlalchemy",
"author": null,
"author_email": "Cody Fincher <cody.fincher@gmail.com>, Peter Schutt <peter.github@proton.me>, Janek Nouvertn\u00e9 <j.a.nouvertne@posteo.de>, Jacob Coffee <jacob@z7x.org>",
"download_url": "https://files.pythonhosted.org/packages/7d/e2/ea2b9307b0f2795e8891876fa4136ea3b1a3c0f0464d93c3edacfc72aa0b/advanced_alchemy-0.26.0.tar.gz",
"platform": null,
"description": "<!-- markdownlint-disable -->\n<p align=\"center\">\n <!-- github-banner-start -->\n <img src=\"https://raw.githubusercontent.com/litestar-org/branding/main/assets/Branding%20-%20SVG%20-%20Transparent/AA%20-%20Banner%20-%20Inline%20-%20Light.svg#gh-light-mode-only\" alt=\"Litestar Logo - Light\" width=\"100%\" height=\"auto\" />\n <img src=\"https://raw.githubusercontent.com/litestar-org/branding/main/assets/Branding%20-%20SVG%20-%20Transparent/AA%20-%20Banner%20-%20Inline%20-%20Dark.svg#gh-dark-mode-only\" alt=\"Litestar Logo - Dark\" width=\"100%\" height=\"auto\" />\n <!-- github-banner-end -->\n</p>\n<div align=\"center\">\n<!-- markdownlint-restore -->\n\n<!-- prettier-ignore-start -->\n\n| Project | | Status |\n|-----------|:----|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|\n| CI/CD | | [![Latest Release](https://github.com/litestar-org/advanced-alchemy/actions/workflows/publish.yml/badge.svg)](https://github.com/litestar-org/advanced-alchemy/actions/workflows/publish.yml) [![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) [![Documentation Building](https://github.com/litestar-org/advanced-alchemy/actions/workflows/docs.yml/badge.svg?branch=main)](https://github.com/litestar-org/advanced-alchemy/actions/workflows/docs.yml) |\n| Quality | | [![Coverage](https://codecov.io/github/litestar-org/advanced-alchemy/graph/badge.svg?token=vKez4Pycrc)](https://codecov.io/github/litestar-org/advanced-alchemy) [![Quality Gate Status](https://sonarcloud.io/api/project_badges/measure?project=litestar-org_advanced-alchemy&metric=alert_status)](https://sonarcloud.io/summary/new_code?id=litestar-org_advanced-alchemy) [![Maintainability Rating](https://sonarcloud.io/api/project_badges/measure?project=litestar-org_advanced-alchemy&metric=sqale_rating)](https://sonarcloud.io/summary/new_code?id=litestar-org_advanced-alchemy) [![Reliability Rating](https://sonarcloud.io/api/project_badges/measure?project=litestar-org_advanced-alchemy&metric=reliability_rating)](https://sonarcloud.io/summary/new_code?id=litestar-org_advanced-alchemy) [![Security Rating](https://sonarcloud.io/api/project_badges/measure?project=litestar-org_advanced-alchemy&metric=security_rating)](https://sonarcloud.io/summary/new_code?id=litestar-org_advanced-alchemy) |\n| Package | | [![PyPI - Version](https://img.shields.io/pypi/v/advanced-alchemy?labelColor=202235&color=edb641&logo=python&logoColor=edb641)](https://badge.fury.io/py/litestar) ![PyPI - Support Python Versions](https://img.shields.io/pypi/pyversions/litestar?labelColor=202235&color=edb641&logo=python&logoColor=edb641) ![Advanced Alchemy PyPI - Downloads](https://img.shields.io/pypi/dm/advanced-alchemy?logo=python&label=package%20downloads&labelColor=202235&color=edb641&logoColor=edb641) |\n| Community | | [![Reddit](https://img.shields.io/reddit/subreddit-subscribers/litestarapi?label=r%2FLitestar&logo=reddit&labelColor=202235&color=edb641&logoColor=edb641)](https://reddit.com/r/litestarapi) [![Discord](https://img.shields.io/discord/919193495116337154?labelColor=202235&color=edb641&label=chat%20on%20discord&logo=discord&logoColor=edb641)](https://discord.gg/litestar) [![Matrix](https://img.shields.io/badge/chat%20on%20Matrix-bridged-202235?labelColor=202235&color=edb641&logo=matrix&logoColor=edb641)](https://matrix.to/#/#litestar:matrix.org) [![Medium](https://img.shields.io/badge/Medium-202235?labelColor=202235&color=edb641&logo=medium&logoColor=edb641)](https://blog.litestar.dev) [![Twitter](https://img.shields.io/twitter/follow/LitestarAPI?labelColor=202235&color=edb641&logo=twitter&logoColor=edb641&style=flat)](https://twitter.com/LitestarAPI) [![Blog](https://img.shields.io/badge/Blog-litestar.dev-202235?logo=blogger&labelColor=202235&color=edb641&logoColor=edb641)](https://blog.litestar.dev) |\n| Meta | | [![Litestar Project](https://img.shields.io/badge/Litestar%20Org-%E2%AD%90%20Advanced%20Alchemy-202235.svg?logo=python&labelColor=202235&color=edb641&logoColor=edb641)](https://github.com/litestar-org/advanced-alchemy) [![types - Mypy](https://img.shields.io/badge/types-Mypy-202235.svg?logo=python&labelColor=202235&color=edb641&logoColor=edb641)](https://github.com/python/mypy) [![License - MIT](https://img.shields.io/badge/license-MIT-202235.svg?logo=python&labelColor=202235&color=edb641&logoColor=edb641)](https://spdx.org/licenses/) [![Litestar Sponsors](https://img.shields.io/badge/Sponsor-%E2%9D%A4-%23edb641.svg?&logo=github&logoColor=edb641&labelColor=202235)](https://github.com/sponsors/litestar-org) [![linting - Ruff](https://img.shields.io/endpoint?url=https://raw.githubusercontent.com/charliermarsh/ruff/main/assets/badge/v2.json&labelColor=202235)](https://github.com/astral-sh/ruff) [![code style - Ruff](https://img.shields.io/endpoint?url=https://raw.githubusercontent.com/astral-sh/ruff/main/assets/badge/format.json&labelColor=202235)](https://github.com/psf/black) |\n\n</div>\n\n# Advanced Alchemy\n\nCheck out the [project documentation][project-docs] \ud83d\udcda for more information.\n\n## About\n\nA carefully crafted, thoroughly tested, optimized companion library for SQLAlchemy,\noffering:\n\n- Sync and async repositories, featuring common CRUD and highly optimized bulk operations\n- Integration with major web frameworks including Litestar, Starlette, FastAPI, Sanic\n- Custom-built alembic configuration and CLI with optional framework integration\n- Utility base classes with audit columns, primary keys and utility functions\n- Optimized JSON types including a custom JSON type for Oracle\n- Integrated support for UUID6 and UUID7 using [`uuid-utils`](https://github.com/aminalaee/uuid-utils) (install with the `uuid` extra)\n- Integrated support for Nano ID using [`fastnanoid`](https://github.com/oliverlambson/fastnanoid) (install with the `nanoid` extra)\n- Pre-configured base classes with audit columns UUID or Big Integer primary keys and\n a [sentinel column](https://docs.sqlalchemy.org/en/20/core/connections.html#configuring-sentinel-columns).\n- Synchronous and asynchronous repositories featuring:\n - Common CRUD operations for SQLAlchemy models\n - Bulk inserts, updates, upserts, and deletes with dialect-specific enhancements\n - Integrated counts, pagination, sorting, filtering with `LIKE`, `IN`, and dates before and/or after.\n- Tested support for multiple database backends including:\n - SQLite via [aiosqlite](https://aiosqlite.omnilib.dev/en/stable/) or [sqlite](https://docs.python.org/3/library/sqlite3.html)\n - Postgres via [asyncpg](https://magicstack.github.io/asyncpg/current/) or [psycopg3 (async or sync)](https://www.psycopg.org/psycopg3/)\n - MySQL via [asyncmy](https://github.com/long2ice/asyncmy)\n - Oracle via [oracledb (async or sync)](https://oracle.github.io/python-oracledb/) (tested on 18c and 23c)\n - Google Spanner via [spanner-sqlalchemy](https://github.com/googleapis/python-spanner-sqlalchemy/)\n - DuckDB via [duckdb_engine](https://github.com/Mause/duckdb_engine)\n - Microsoft SQL Server via [pyodbc](https://github.com/mkleehammer/pyodbc) or [aioodbc](https://github.com/aio-libs/aioodbc)\n - CockroachDB via [sqlalchemy-cockroachdb (async or sync)](https://github.com/cockroachdb/sqlalchemy-cockroachdb)\n- ...and much more\n\n## Usage\n\n### Installation\n\n```shell\npip install advanced-alchemy\n```\n\n> [!IMPORTANT]\\\n> Check out [the installation guide][install-guide] in our official documentation!\n\n### Repositories\n\nAdvanced Alchemy includes a set of asynchronous and synchronous repository classes for easy CRUD\noperations on your SQLAlchemy models.\n<!-- markdownlint-disable -->\n<details>\n<summary>Click to expand the example</summary>\n<!-- markdownlint-restore -->\n\n```python\nfrom advanced_alchemy.base import UUIDBase\nfrom advanced_alchemy.filters import LimitOffset\nfrom advanced_alchemy.repository import SQLAlchemySyncRepository\nfrom sqlalchemy import create_engine\nfrom sqlalchemy.orm import Mapped, sessionmaker\n\n\nclass User(UUIDBase):\n # you can optionally override the generated table name by manually setting it.\n __tablename__ = \"user_account\" # type: ignore[assignment]\n email: Mapped[str]\n name: Mapped[str]\n\n\nclass UserRepository(SQLAlchemySyncRepository[User]):\n \"\"\"User repository.\"\"\"\n\n model_type = User\n\n\n# use any compatible sqlalchemy engine.\nengine = create_engine(\"duckdb:///:memory:\")\nsession_factory = sessionmaker(engine, expire_on_commit=False)\n\n# Initializes the database.\nwith engine.begin() as conn:\n User.metadata.create_all(conn)\n\nwith session_factory() as db_session:\n repo = UserRepository(session=db_session)\n # 1) Create multiple users with `add_many`\n bulk_users = [\n {\"email\": 'cody@litestar.dev', 'name': 'Cody'},\n {\"email\": 'janek@litestar.dev', 'name': 'Janek'},\n {\"email\": 'peter@litestar.dev', 'name': 'Peter'},\n {\"email\": 'jacob@litestar.dev', 'name': 'Jacob'}\n ]\n objs = repo.add_many([User(**raw_user) for raw_user in bulk_users])\n db_session.commit()\n print(f\"Created {len(objs)} new objects.\")\n\n # 2) Select paginated data and total row count. Pass additional filters as kwargs\n created_objs, total_objs = repo.list_and_count(LimitOffset(limit=10, offset=0), name=\"Cody\")\n print(f\"Selected {len(created_objs)} records out of a total of {total_objs}.\")\n\n # 3) Let's remove the batch of records selected.\n deleted_objs = repo.delete_many([new_obj.id for new_obj in created_objs])\n print(f\"Removed {len(deleted_objs)} records out of a total of {total_objs}.\")\n\n # 4) Let's count the remaining rows\n remaining_count = repo.count()\n print(f\"Found {remaining_count} remaining records after delete.\")\n```\n\n</details>\n\nFor a full standalone example, see the sample [here][standalone-example]\n\n### Services\n\nAdvanced Alchemy includes an additional service class to make working with a repository easier.\nThis class is designed to accept data as a dictionary or SQLAlchemy model,\nand it will handle the type conversions for you.\n<!-- markdownlint-disable -->\n<details>\n<summary>Here's the same example from above but using a service to create the data:</summary>\n<!-- markdownlint-restore -->\n\n```python\nfrom advanced_alchemy.base import UUIDBase\nfrom advanced_alchemy.filters import LimitOffset\nfrom advanced_alchemy import SQLAlchemySyncRepository, SQLAlchemySyncRepositoryService\nfrom sqlalchemy import create_engine\nfrom sqlalchemy.orm import Mapped, sessionmaker\n\n\nclass User(UUIDBase):\n # you can optionally override the generated table name by manually setting it.\n __tablename__ = \"user_account\" # type: ignore[assignment]\n email: Mapped[str]\n name: Mapped[str]\n\n\nclass UserRepository(SQLAlchemySyncRepository[User]):\n \"\"\"User repository.\"\"\"\n\n model_type = User\n\n\nclass UserService(SQLAlchemySyncRepositoryService[User]):\n \"\"\"User repository.\"\"\"\n\n repository_type = UserRepository\n\n\n# use any compatible sqlalchemy engine.\nengine = create_engine(\"duckdb:///:memory:\")\nsession_factory = sessionmaker(engine, expire_on_commit=False)\n\n# Initializes the database.\nwith engine.begin() as conn:\n User.metadata.create_all(conn)\n\nwith session_factory() as db_session:\n service = UserService(session=db_session)\n # 1) Create multiple users with `add_many`\n objs = service.create_many([\n {\"email\": 'cody@litestar.dev', 'name': 'Cody'},\n {\"email\": 'janek@litestar.dev', 'name': 'Janek'},\n {\"email\": 'peter@litestar.dev', 'name': 'Peter'},\n {\"email\": 'jacob@litestar.dev', 'name': 'Jacob'}\n ])\n print(objs)\n print(f\"Created {len(objs)} new objects.\")\n\n # 2) Select paginated data and total row count. Pass additional filters as kwargs\n created_objs, total_objs = service.list_and_count(LimitOffset(limit=10, offset=0), name=\"Cody\")\n print(f\"Selected {len(created_objs)} records out of a total of {total_objs}.\")\n\n # 3) Let's remove the batch of records selected.\n deleted_objs = service.delete_many([new_obj.id for new_obj in created_objs])\n print(f\"Removed {len(deleted_objs)} records out of a total of {total_objs}.\")\n\n # 4) Let's count the remaining rows\n remaining_count = service.count()\n print(f\"Found {remaining_count} remaining records after delete.\")\n```\n\n</details>\n\n### Web Frameworks\n\nAdvanced Alchemy works with nearly all Python web frameworks.\nSeveral helpers for popular libraries are included, and additional PRs to support others are welcomed.\n\n#### Litestar\n\nAdvanced Alchemy is the official SQLAlchemy integration for Litestar.\n\nIn addition to installing with `pip install advanced-alchemy`,\nit can also be installed as a Litestar extra with `pip install litestar[sqlalchemy]`.\n\n<!-- markdownlint-disable -->\n<details>\n<summary>Litestar Example</summary>\n<!-- markdownlint-restore -->\n\n```python\nfrom litestar import Litestar\nfrom litestar.plugins.sqlalchemy import SQLAlchemyPlugin, SQLAlchemyAsyncConfig\n# alternately...\n# from advanced_alchemy.extensions.litestar.plugins import SQLAlchemyPlugin\n# from advanced_alchemy.extensions.litestar.plugins.init.config import SQLAlchemyAsyncConfig\n\nalchemy = SQLAlchemyPlugin(\n config=SQLAlchemyAsyncConfig(connection_string=\"sqlite+aiosqlite:///test.sqlite\"),\n)\napp = Litestar(plugins=[alchemy])\n```\n\n</details>\n\nFor a full Litestar example, check [here][litestar-example]\n\n#### FastAPI\n\n<!-- markdownlint-disable -->\n<details>\n<summary>FastAPI Example</summary>\n<!-- markdownlint-restore -->\n\n```python\nfrom fastapi import FastAPI\n\nfrom advanced_alchemy.config import SQLAlchemyAsyncConfig\nfrom advanced_alchemy.extensions.starlette import StarletteAdvancedAlchemy\n\napp = FastAPI()\nalchemy = StarletteAdvancedAlchemy(\n config=SQLAlchemyAsyncConfig(connection_string=\"sqlite+aiosqlite:///test.sqlite\"), app=app,\n)\n```\n\n</details>\n\nFor a full FastAPI example, see [here][fastapi-example]\n\n#### Starlette\n\n<!-- markdownlint-disable -->\n<details>\n<summary>Pre-built Example Apps</summary>\n<!-- markdownlint-restore -->\n\n```python\nfrom starlette.applications import Starlette\n\nfrom advanced_alchemy.config import SQLAlchemyAsyncConfig\nfrom advanced_alchemy.extensions.starlette import StarletteAdvancedAlchemy\n\napp = Starlette()\nalchemy = StarletteAdvancedAlchemy(\n config=SQLAlchemyAsyncConfig(connection_string=\"sqlite+aiosqlite:///test.sqlite\"), app=app,\n)\n```\n\n</details>\n\n#### Sanic\n\n<!-- markdownlint-disable -->\n<details>\n<summary>Pre-built Example Apps</summary>\n<!-- markdownlint-restore -->\n\n```python\nfrom sanic import Sanic\nfrom sanic_ext import Extend\n\nfrom advanced_alchemy.config import SQLAlchemyAsyncConfig\nfrom advanced_alchemy.extensions.sanic import SanicAdvancedAlchemy\n\napp = Sanic(\"AlchemySanicApp\")\nalchemy = SanicAdvancedAlchemy(\n sqlalchemy_config=SQLAlchemyAsyncConfig(connection_string=\"sqlite+aiosqlite:///test.sqlite\"),\n)\nExtend.register(alchemy)\n```\n\n</details>\n\n## Contributing\n\nAll [Litestar Organization][litestar-org] projects will always be a community-centered, available for contributions of any size.\n\nBefore contributing, please review the [contribution guide][contributing].\n\nIf you have any questions, reach out to us on [Discord][discord], our org-wide [GitHub discussions][litestar-discussions] page,\nor the [project-specific GitHub discussions page][project-discussions].\n\n<!-- markdownlint-disable -->\n<hr />\n<p align=\"center\">\n <!-- github-banner-start -->\n <img src=\"https://raw.githubusercontent.com/litestar-org/branding/main/assets/Branding%20-%20SVG%20-%20Transparent/Organization%20Project%20-%20Banner%20-%20Inline%20-%20Dark.svg\" alt=\"Litestar Logo - Light\" width=\"40%\" height=\"auto\" />\n <br>An official <a href=\"https://github.com/litestar-org\">Litestar Organization</a> Project\n <!-- github-banner-end -->\n</p>\n\n[litestar-org]: https://github.com/litestar-org\n[contributing]: https://docs.advanced-alchemy.litestar.dev/latest/contribution-guide.html\n[discord]: https://discord.gg/litestar\n[litestar-discussions]: https://github.com/orgs/litestar-org/discussions\n[project-discussions]: https://github.com/litestar-org/advanced-alchemy/discussions\n[project-docs]: https://docs.advanced-alchemy.litestar.dev\n[install-guide]: https://docs.advanced-alchemy.litestar.dev/latest/#installation\n[fastapi-example]: https://github.com/litestar-org/advanced-alchemy/blob/main/examples/fastapi.py\n[litestar-example]: https://github.com/litestar-org/advanced-alchemy/blob/main/examples/litestar.py\n[standalone-example]: https://github.com/litestar-org/advanced-alchemy/blob/main/examples/standalone.py\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Ready-to-go SQLAlchemy concoctions.",
"version": "0.26.0",
"project_urls": {
"Changelog": "https://docs.advanced-alchemy.litestar.dev/latest/changelog",
"Discord": "https://discord.gg/litestar",
"Documentation": "https://docs.advanced-alchemy.litestar.dev/latest/",
"Funding": "https://github.com/sponsors/litestar-org",
"Homepage": "https://docs.advanced-alchemy.litestar.dev/latest/",
"Issue": "https://github.com/litestar-org/advanced-alchemy/issues/",
"Source": "https://github.com/litestar-org/advanced-alchemy"
},
"split_keywords": [
"alembic",
" fastapi",
" flask",
" litestar",
" sanic",
" sqlalchemy"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "b71c5ca7e49d575ba5649ec8377e1e9c5057f3dd133567abdb3cd957d178e4db",
"md5": "4b1118fb4c14406fa9383ad0482afe9e",
"sha256": "abee7fd3402fc2c399a25f7eae3a033b04a49ae8b887a6fee3415889fc2bcb66"
},
"downloads": -1,
"filename": "advanced_alchemy-0.26.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "4b1118fb4c14406fa9383ad0482afe9e",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 147809,
"upload_time": "2024-12-05T19:25:15",
"upload_time_iso_8601": "2024-12-05T19:25:15.814935Z",
"url": "https://files.pythonhosted.org/packages/b7/1c/5ca7e49d575ba5649ec8377e1e9c5057f3dd133567abdb3cd957d178e4db/advanced_alchemy-0.26.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "7de2ea2b9307b0f2795e8891876fa4136ea3b1a3c0f0464d93c3edacfc72aa0b",
"md5": "d016db273f70a632c6cc774d0f48042b",
"sha256": "aae2127ae771630e2b4ef41b6f4d8d7c50151fe35cc43447e830b5e2743d1fcb"
},
"downloads": -1,
"filename": "advanced_alchemy-0.26.0.tar.gz",
"has_sig": false,
"md5_digest": "d016db273f70a632c6cc774d0f48042b",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 942598,
"upload_time": "2024-12-05T19:25:19",
"upload_time_iso_8601": "2024-12-05T19:25:19.679735Z",
"url": "https://files.pythonhosted.org/packages/7d/e2/ea2b9307b0f2795e8891876fa4136ea3b1a3c0f0464d93c3edacfc72aa0b/advanced_alchemy-0.26.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-12-05 19:25:19",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "sponsors",
"github_project": "litestar-org",
"github_not_found": true,
"lcname": "advanced-alchemy"
}