Advanced Alchemy

Project		Status
CI/CD
Quality
Community
Meta

Check out the project documentation 📚 for more information.

About

A carefully crafted, thoroughly tested, optimized companion library for SQLAlchemy, offering features such as:

• 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.

• 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
  • lambda_stmt when possible for improved query building performance
  • 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 (tested on 18c and 23c)
  • Google Spanner via spanner-sqlalchemy
  • DuckDB via duckdb_engine
  • Microsoft SQL Server via pyodbc

Usage

Important

Check out the installation guide in our official documentation!

Litestar

from advanced_alchemy.extensions.litestar.plugins import SQLAlchemyPlugin
from advanced_alchemy.extensions.litestar.plugins.init.config import SQLAlchemyAsyncConfig

from litestar import Litestar

plugin = SQLAlchemyPlugin(config=SQLAlchemyAsyncConfig(connection_string="sqlite+aiosqlite:///test.sqlite"))


app = Litestar(plugins=[plugin])

FastAPI

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

Starlette

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

Sanic

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)

Contributing

All Jolt projects will always be a community-centered, available for 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.

---

A Jolt Organization Project