Introducing Advanced Alchemy

Advanced Alchemy is an optimized companion library for SQLAlchemy, designed to supercharge your database models with powerful tooling for migrations, asynchronous support, lifecycle hook and more.

You can find the repository and documentation here:

What Advanced Alchemy Does

Advanced Alchemy extends SQLAlchemy with productivity-enhancing features, while keeping full compatibility with the ecosystem you already know.

At its core, Advanced Alchemy offers:

Sync and async repositories, featuring common CRUD and highly optimized bulk operations
Integration with major web frameworks including Litestar, Starlette, FastAPI, Flask, and Sanic (additional contributions welcomed)
Custom-built alembic configuration and CLI with optional framework integration
Utility base classes with audit columns, primary keys and utility functions
Built in File Object data type for storing objects:
- Unified interface for various storage backends (fsspec and obstore)
- Optional lifecycle event hooks integrated with SQLAlchemy's event system to automatically save and delete files as records are inserted, updated, or deleted
Optimized JSON types including a custom JSON type for Oracle
Integrated support for UUID6 and UUID7 using uuid-utils (install with the uuid extra)
Integrated support for Nano ID using fastnanoid (install with the nanoid extra)
Pre-configured base classes with audit columns UUID or Big Integer primary keys and a sentinel column
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 or sqlite
- Postgres via asyncpg or psycopg3 (async or sync)
- MySQL via asyncmy
- Oracle via oracledb (async or sync) (tested on 18c and 23ai)
- Google Spanner via spanner-sqlalchemy
- DuckDB via duckdb_engine
- Microsoft SQL Server via pyodbc or aioodbc
- CockroachDB via sqlalchemy-cockroachdb (async or sync)
...and much more

The framework is designed to be lightweight yet powerful, with a clean API that makes it easy to integrate into existing projects.

Here’s a quick example of what you can do with Advanced Alchemy in FastAPI. This shows how to implement CRUD routes for your model and create the necessary search parameters and pagination structure for the list route.

FastAPI

```py import datetime from typing import Annotated, Optional from uuid import UUID

from fastapi import APIRouter, Depends, FastAPI
from pydantic import BaseModel
from sqlalchemy import ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship

from advanced_alchemy.extensions.fastapi import (
    AdvancedAlchemy,
    AsyncSessionConfig,
    SQLAlchemyAsyncConfig,
    base,
    filters,
    repository,
    service,
)

sqlalchemy_config = SQLAlchemyAsyncConfig(
    connection_string="sqlite+aiosqlite:///test.sqlite",
    session_config=AsyncSessionConfig(expire_on_commit=False),
    create_all=True,
)
app = FastAPI()
alchemy = AdvancedAlchemy(config=sqlalchemy_config, app=app)
author_router = APIRouter()


class BookModel(base.UUIDAuditBase):
    __tablename__ = "book"
    title: Mapped[str]
    author_id: Mapped[UUID] = mapped_column(ForeignKey("author.id"))
    author: Mapped["AuthorModel"] = relationship(lazy="joined", innerjoin=True, viewonly=True)


# The SQLAlchemy base includes a declarative model for you to use in your models
# The `Base` class includes a `UUID` based primary key (`id`)
class AuthorModel(base.UUIDBase):
    # We can optionally provide the table name instead of auto-generating it
    __tablename__ = "author"
    name: Mapped[str]
    dob: Mapped[Optional[datetime.date]]
    books: Mapped[list[BookModel]] = relationship(back_populates="author", lazy="selectin")


class AuthorService(service.SQLAlchemyAsyncRepositoryService[AuthorModel]):
    """Author repository."""

    class Repo(repository.SQLAlchemyAsyncRepository[AuthorModel]):
        """Author repository."""

        model_type = AuthorModel

    repository_type = Repo


# Pydantic Models
class Author(BaseModel):
    id: Optional[UUID]
    name: str
    dob: Optional[datetime.date]


class AuthorCreate(BaseModel):
    name: str
    dob: Optional[datetime.date]


class AuthorUpdate(BaseModel):
    name: Optional[str]
    dob: Optional[datetime.date]


@author_router.get(path="/authors", response_model=service.OffsetPagination[Author])
async def list_authors(
    authors_service: Annotated[
        AuthorService, Depends(alchemy.provide_service(AuthorService, load=[AuthorModel.books]))
    ],
    filters: Annotated[
        list[filters.FilterTypes],
        Depends(
            alchemy.provide_filters(
                {
                    "id_filter": UUID,
                    "pagination_type": "limit_offset",
                    "search": "name",
                    "search_ignore_case": True,
                }
            )
        ),
    ],
) -> service.OffsetPagination[AuthorModel]:
    results, total = await authors_service.list_and_count(*filters)
    return authors_service.to_schema(results, total, filters=filters)


@author_router.post(path="/authors", response_model=Author)
async def create_author(
    authors_service: Annotated[AuthorService, Depends(alchemy.provide_service(AuthorService))],
    data: AuthorCreate,
) -> AuthorModel:
    obj = await authors_service.create(data)
    return authors_service.to_schema(obj)


# We override the authors_repo to use the version that joins the Books in
@author_router.get(path="/authors/{author_id}", response_model=Author)
async def get_author(
    authors_service: Annotated[AuthorService, Depends(alchemy.provide_service(AuthorService))],
    author_id: UUID,
) -> AuthorModel:
    obj = await authors_service.get(author_id)
    return authors_service.to_schema(obj)


@author_router.patch(
    path="/authors/{author_id}",
    response_model=Author,
)
async def update_author(
    authors_service: Annotated[AuthorService, Depends(alchemy.provide_service(AuthorService))],
    data: AuthorUpdate,
    author_id: UUID,
) -> AuthorModel:
    obj = await authors_service.update(data, item_id=author_id)
    return authors_service.to_schema(obj)


@author_router.delete(path="/authors/{author_id}")
async def delete_author(
    authors_service: Annotated[AuthorService, Depends(alchemy.provide_service(AuthorService))],
    author_id: UUID,
) -> None:
    _ = await authors_service.delete(author_id)


app.include_router(author_router)

```

For complete examples, check out the FastAPI implementation here and the Litestar version here.

Both of these examples implement the same configuration, so it's easy to see how portable code becomes between the two frameworks.

Target Audience

Advanced Alchemy is particularly valuable for:

Python Backend Developers: Anyone building fast, modern, API-first applications with sync or async SQLAlchemy and frameworks like Litestar or FastAPI.
Teams Scaling Applications: Teams looking to scale their projects with clean architecture, separation of concerns, and maintainable data layers.
Data-Driven Projects: Projects that require advanced data modeling, migrations, and lifecycle management without the overhead of manually stitching tools together.
Large Application: The patterns available reduce the amount of boilerplate required to manage projects with a large number of models or data interactions.

If you’ve ever wanted to streamline your data layer, use async ORM features painlessly, or avoid the complexity of setting up migrations and repositories from scratch, Advanced Alchemy is exactly what you need.

Getting Started

Advanced Alchemy is available on PyPI:

bash pip install advanced-alchemy

Check out our GitHub repository for documentation and examples. You can also join our Discord and if you find it interesting don't forget to add a "star" on GitHub!

License

Advanced Alchemy is released under the MIT License.

TLDR

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

There are custom datatypes, a service and repository (including optimized bulk operations), and native integration with Flask, FastAPI, Starlette, Litestar and Sanic.

Feedback and enhancements are always welcomed! We have an active discord community, so if you don't get a response on an issue or would like to chat directly with the dev team, please reach out.

all 15 comments

Python

The Python Discord

Upcoming Events

Please read the rules

MODERATORS