Show HN: Advanced-Alchemy – A framework agnostic library for SQLAlchemy

https://github.com/litestar-org/advanced-alchemy

38•Kumzy•9mo ago

Hello HN!

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:

    - GitHub Repository: https://github.com/litestar-org/advanced-alchemy
    - Official Documentation : https://docs.advanced-alchemy.litestar.dev/latest/

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

- 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

- Integrated support for Nano ID using fastnanoid

- 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 23c)
    - Google Spanner via spanner-sqlalchemy
    - DuckDB via duckdb_engine
    - Microsoft SQL Server via pyodbc or aioodbc
    - CockroachDB via sqlalchemy-cockroachdb (async or sync)

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

You can find a full example using FastAPI there: https://docs.advanced-alchemy.litestar.dev/latest/usage/fram...

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.

The library: https://github.com/litestar-org/advanced-alchemy

Comments

stuaxo•9mo ago

Code examples could be further forward, I clicked around a bit and didn't see any on the initial pages.

mdaniel•9mo ago

For some else who's looking, they're folded in the readme under "click for more details": https://github.com/litestar-org/advanced-alchemy/blob/v1.3.1...

canadiantim•9mo ago

Nice, love seeing litestar continue improving!

Noumenon72•9mo ago

I'm using SQLAlchemy, Alembic, and FastAPI. Either this is for someone much more advanced than me, or it could use some examples showing "This is clunky in SQLAlchemy and easy in Advanced-Alchemy." When you look at SQLModel[1] from FastAPI, which I did adopt, it does a much better job at promising "this will make your life easier".

1. https://sqlmodel.tiangolo.com/

zo1•9mo ago

FastAPI and it's very opinionated "ecosystem" of "easy to use" and "hipster-esque documented" tools are fast-becoming a horrible one-man show version of Django, and we will all regret going so deep into it using it, one day. Until then, sure we can use it and it mostly makes sense and it mostly works really very well. Hopefully the community matures and moves away from opinionated tooling that forces you into the "one true way".

Kumzy•9mo ago

Hello, OP there, I have many use cases that fall outside of FastAPI or web apps in general, and this pattern allows me to consolidate that logic in a re-usable way.

We also have SQLSpec[1] (Work in progress) that could maybe a better use case for you with raw SQL

1. https://github.com/litestar-org/sqlspec

NeutralForest•9mo ago

I don't like SQLModel for the simple reason that it muddles boundaries between different parts of the application. I suppose when you have a pure CRUD app it's ok, but anytime you want to handle ORM objects and apply some business logic; you'll want to cleanly separate those from what you send and receive from your API.

globular-toast•9mo ago

Right. I have to wonder how often people are building "APIs" that are just direct CRUD interfaces to SQL DBs. SQLModel seems like taking DRY too seriously. I get it, SQLAlchemy models and Pydantic models look very similar, but they are doing completely different things. SQLModel is just causing you more work later that could have been avoided by just copy/pasting the SQLAlchemy models to Pydantic in the first place.

But even then you'll get people implementing business logic in Pydantic validators. I feel like basic layered architecture is being forgotten.

arewethereyeta•9mo ago

I don't think I ever worked with an ORM that turned against me more than SQLAlchemy and that's not because it's badly written, on the contrary.

DataDaoDe•9mo ago

This is a nice library and a really big time savings part of the litestar ecosystem. The litestar community has been really great and helpful in my experience so a big thank you to all the guys and gals over there!

yilugurlu•9mo ago

I use this library in production; it has great tools and features that I couldn't find in Python, that I missed from other frameworks or languages. The only downside is that you have to figure out what is what from the code; the documentation is not good. But once you figure out the details, it's downhill from there. We use this with the Litestar framework; there are things that I hate, but in general, it works out really well. Especially, if you managed to set up your services, repositories correctly, then many things happen magically. Though I suggest anyone who'd like to "taste" these use `msgspec` as a serialisation library. We hit the wall earlier when we wanted to use Pydantic in complex schemas for serialisation, so we went back to msgspec. The downside is that msgspec misses the validation part.

Anyway, I recommend that anyone go beyond the regular SQLAlchemy. I like the syntax `account = await accounts_service.get_one_or_none(*filters, id=account_id)` a lot.

Kumzy•9mo ago

I would acknowledge they have been lacking and if they'd reviewed them recently. If so, maybe see if they'd offer some improvement areas. Do you have anything in mind in the documentation that you would like improved?

Show HN: MCP to get latest dependency package and tool versions

The better you get at something, the harder it becomes to do

Show HN: WP Float – Archive WordPress blogs to free static hosting

Show HN: I Hacked My Family's Meal Planning with an App

Sony BMG copy protection rootkit scandal

The Future of Systems

NASA now allowing astronauts to bring their smartphones on space missions

Claude Code Is the Inflection Point

Show HN: MicroClaw – Agentic AI Assistant for Telegram, Built in Rust

Show HN: Omni-BLAS – 4x faster matrix multiplication via Monte Carlo sampling

The AI-Ready Software Developer: Conclusion – Same Game, Different Dice

AI Agent Automates Google Stock Analysis from Financial Reports

Voxtral Realtime 4B Pure C Implementation

I Was Trapped in Chinese Mafia Crypto Slavery [video]

U.S. CBP Reported Employee Arrests (FY2020 – FYTD)

Show HN: I built a free UCP checker – see if AI agents can find your store

Show HN: SVGV – A Real-Time Vector Video Format for Budget Hardware

Study of 150 developers shows AI generated code no harder to maintain long term

Spotify now requires premium accounts for developer mode API access

When Albert Einstein Moved to Princeton

Agents.md as a Dark Signal

System time, clocks, and their syncing in macOS

McCLIM and 7GUIs – Part 1: The Counter

So whats the next word, then? Almost-no-math intro to transformer models

Ed Zitron: The Hater's Guide to Microsoft

UK infants ill after drinking contaminated baby formula of Nestle and Danone

Show HN: Android-based audio player for seniors – Homer Audio Player

Starter Template for Ory Kratos

LLMs are powerful, but enterprises are deterministic by nature

Make your iPad 3 a touchscreen for your computer