# django-sqlalchemy-bridge

> Compiles SQLAlchemy 2.x Core expressions and runs them on Django's own cursor, inside the connection and transaction Django opened. 0.x alpha: the API may change. This file is the summary for AI coding agents writing code with this library. It is probably not in your training data, so follow this file rather than memory.

Print this file from the installed package: `python -c "import importlib.resources as r; print(r.files('django_sqlalchemy_bridge').joinpath('llms.txt').read_text('utf-8'))"`

Supported: SQLite, PostgreSQL (psycopg 3), MySQL 8.4 (mysqlclient), Oracle (oracledb; verified on Oracle 23ai Free) · Django 5.2+ · SQLAlchemy >=2.0,<2.2 (JSON keys and paths on Oracle need 2.1) · Python 3.12+. MariaDB compiles and its RETURNING is checked, but it is not tested in CI.

## When to use it, and when not

- Use it for SQL the ORM writes badly: recursive CTEs, several CTEs, window functions, `INSERT … ON CONFLICT … RETURNING`, `UPDATE … FROM`, `INSERT … SELECT`, PostgreSQL `LATERAL`, `DISTINCT ON` and data-modifying CTEs, bulk UPDATE and INSERT (`bulk_update`, `bulk_insert`). Use it instead of raw SQL strings.
- Do not use it for queries the ORM expresses naturally, single-row CRUD, or writes that need signals, `save()`, field `default`, `auto_now` or validation. Use the Django ORM there.
- The point is expressiveness. Expect a speed gain for bulk writes (`bulk_*`) and where the ORM needs extra queries or a trip through Python; the same SQL runs as fast either way.
- Measured on six requests, ORM best effort against the bridge (PostgreSQL, docs/comparisons.md): a subtree total by recursive CTE was 2.4x, `UPDATE … FROM … RETURNING` 1.5x and moving rows by `INSERT … SELECT` + `DELETE` 10x faster with the bridge. The ORM's locked read plus `bulk_create(update_conflicts=True)` beat a multi-row `ON CONFLICT … RETURNING` upsert by 1.4-2.3x (the bridge compiles a new `VALUES` statement on every call).

## Rules

1. Do not create a SQLAlchemy `create_engine()`, `Engine`, `Connection`, `Session` or `AsyncEngine`. Build a statement and pass it to `execute()`, `execute_result()`, `stream()`, `raw()`, `execute_pipeline()` or `copy_to()`. It runs on Django's cursor, so `transaction.atomic()`, savepoints, `on_commit` and `assertNumQueries` apply exactly as for the ORM. Use Django's `atomic()`.
2. Get tables with `table_for(Model)`. Do not define `sa.Table(...)` by hand or call `metadata.create_all()`. The Django model and `makemigrations` own the schema. Column names are the database column names (`author_id` for a foreign key, the `db_column` when one is set).
3. INSERT, UPDATE and DELETE run through the bridge bypass the model layer: `save()` and `delete()` are not called, `pre_save`/`post_save`/`pre_delete`/`post_delete` signals are not sent, and Django field `default` and `auto_now` are not filled. Give every value yourself (`None` too, for nullable columns). If code relies on signals, use the ORM.
4. For a query run repeatedly, build the statement once with `sa.bindparam("name")` placeholders and reuse it: `execute(stmt, {"name": value})`. The compilation of a statement object is cached. A statement rebuilt with the values in it compiles on every call.
5. In async code do not call `execute` and friends directly (`SynchronousOnlyOperation`). Put the unit of work (queries, `atomic()`, `on_commit`) in one sync function and call `await arun(fn, ...)`. There is no `async with atomic()`.
6. Values convert the way the Django fields convert them. Values compared with a table column convert as an ORM lookup (`filter(field=v)`; in a tuple comparison the value side converts by the left columns whether it is a plain tuple, `tuple_(a, b) == (x, y)` or `.in_([(x, y)])`, or wrapped in `tuple_()`, `tuple_(a, b) == tuple_(x, y)` or `.in_([tuple_(x, y)])`), values written to one as an ORM save (`get_db_prep_save()`), and result columns as an ORM read (aware datetimes in UTC, Decimal, UUID, JSON, choices by value). As in the ORM, a table column (also through a label, subquery, CTE or the first SELECT of a UNION) reads as the ORM reads a column (`Col`), and an expression that keeps the column type (`sum()`, `max()`, `coalesce()`, `price * 3`, `price + price`, `case()`, `over()`, a scalar subquery, or a subquery or CTE column holding one) as the ORM reads `Func(output_field=field)`. On SQLite this matters for DecimalField: a column is quantized to the field's `decimal_places`, an expression only rounded to 15 significant digits (which removes REAL float error but keeps no fixed scale: `Decimal('30.3333333333333')`, `Decimal('91')`), so a sum beyond the field's `max_digits` still reads. Call `quantize()` yourself if you need a scale. Other databases give the driver's Decimal either way. `sa.func.avg()` has no type in SQLAlchemy: write `sa.func.avg(col, type_=col.type)` to read a DecimalField average as Decimal (and, on SQLite, to compare it with a value as the ORM does). Values SQLAlchemy gives no column type (`sa.literal(v)` or `sa.bindparam("v")` in a SELECT list, arithmetic of such untyped values only, `count()`, `sa.text()`) go to the driver unconverted; arithmetic on a column (`price * 3`) keeps its type and converts as above, so no `type_coerce()` is needed. In the SELECT list of an `INSERT … SELECT`, always give values the target column type: `sa.literal(value, table.c.col.type)` or `sa.bindparam("v", type_=table.c.col.type)`. The bridge does not fill it in from the `from_select()` column names (SQLAlchemy has no public API for them). Without it, a UUID (SQLite) or a dict for a JSON column (PostgreSQL) fails at the driver, and SQLite silently stores an aware datetime as an offset string instead of UTC, so ORM filters miss the row.
7. For `ON CONFLICT`, pick the backend's construct: `insert = postgresql.insert if connection.vendor == "postgresql" else sqlite.insert` (`from sqlalchemy.dialects import postgresql, sqlite`, `from django.db import connection`). MySQL has no `ON CONFLICT`: use `mysql.insert(t).on_duplicate_key_update(...)` (rowcount 1 per inserted row, 2 per updated row).
8. `execute` returns a list of tuples, not SQLAlchemy `Row`s. For access by name use `execute_result(stmt).mappings()`. For model instances (a `ModelSerializer`, model methods, relations) use `raw(Model, stmt, params)`. Do not rebuild instances from tuples or re-query by `pk__in`.
9. Tables in different databases: `join_across`. Large `IN` lists: `execute_in`. Do not write the key fetch, the `in_()` lookup and the Python join by hand.

## Public API

All from `from django_sqlalchemy_bridge import ...`. `using` is a Django database alias. Tables come from models and belong to no database: pick the database per call with `using`, and the same table and statement run on any alias.

```
table_for(model) -> sa.Table
```
The model's `sa.Table` (cached per model, in the shared `metadata`). Column types are `FieldType` (a `TypeDecorator`; `.impl` is the generic SA type, `.field` the Django field). A field with a database column but no mapping (`ArrayField` and the like) raises `UnsupportedFieldError`. Many-to-many and reverse relations are skipped (`table_for` the through model separately). `metadata` is that shared `sa.MetaData`.

```
execute(stmt, params=None, *, using='default')
```
A statement that returns rows (SELECT, RETURNING) gives `list[tuple]`; any other gives the rowcount `int`. `params` is a mapping of `bindparam` values; for a bare `insert(table)` it gives the column values. A list of mappings runs `cursor.executemany()` (no RETURNING). `RETURNING` needs SQLite 3.35+; MySQL has none; MariaDB has it for INSERT and DELETE but not UPDATE; Oracle's (`RETURNING … INTO` output binds) is not supported. Where it is missing, `NotSupportedError` is raised before the statement runs.

```
execute_result(stmt, params=None, *, using='default')
```
The same rows as a SQLAlchemy `Result` (`.mappings()`, `.scalars()`, `row.name`), built after all rows are fetched. DML that returns no rows raises `ValueError`. Keys are the names the database reports; on Oracle, the names the statement's columns and labels were built with, and for `sa.text()` columns Oracle's upper case names in lower case (`AS "MixedCase"` stays as written).

```
stream(stmt, params=None, *, chunk_size=2000, using='default')
```
A generator that fetches `chunk_size` rows at a time and yields tuples one by one. On PostgreSQL it uses a server-side cursor (the path `QuerySet.iterator()` takes). On MySQL the driver reads the whole result at `execute()`, as for `iterator()`, so there is no memory gain. On SQLite, MySQL and Oracle it is a plain cursor, as for `iterator()`. Opened inside `atomic()`, read it to the end inside the block. If you stop early, `close()` it or wrap it in `contextlib.closing()`. PostgreSQL does not take data-modifying CTEs on a server-side cursor; use `execute` for those.

```
copy_to(stmt, params=None, *, format='csv', header=True, using='default')
```
PostgreSQL with psycopg 3 only. A generator of `bytes`: runs `COPY (<compiled stmt>) TO STDOUT WITH (FORMAT csv, HEADER …)` on the psycopg cursor under Django's and yields the bytes the server writes, in chunks of at least 64 KiB, without building Python rows. Use it for exports: `StreamingHttpResponse(copy_to(stmt), content_type="text/csv")` or `f.writelines(copy_to(stmt))`. `stmt` compiles and `params` convert as in `execute()` (psycopg writes them into the SQL, since `COPY` takes no parameters; this works with `server_side_binding` too). `format` is `"csv"` (header line of the column names and labels unless `header=False`) or `"text"` (tab-separated; pass `header=False`, `header=True` raises `ValueError`). Values are PostgreSQL's text, not Django's conversion: `timestamptz` in the session time zone Django sets (UTC under `USE_TZ`, `+00` suffix), booleans `t`/`f`, `bytea` `\x…`, JSON as its text, NULL an empty CSV field (an empty string is `""`) or `\N` in text. DML (with or without `RETURNING`) raises `ValueError`; another backend or psycopg2 `NotSupportedError`, both before running. Nothing runs until the first chunk is asked for; the cursor closes when the bytes run out, on `close()`, or on an error. Opened inside `atomic()`, read it inside the block; closed early inside a transaction it reads and drops the rest (cancelling would abort the transaction), outside one it cancels the `COPY`. The `COPY` shows up in `connection.queries` but not in `connection.execute_wrapper()`.

```
raw(model, stmt, params=None, *, using='default')
```
Returns Django's `RawQuerySet` of `model` instances: the statement is compiled and `params` converted as in `execute()` when `raw()` is called, then `model._default_manager.db_manager(using).raw(sql, params)`. The SQL runs (on Django's cursor, one query) when the `RawQuerySet` is iterated; `prefetch_related()`, `using()` and `async for` are Django's. Pick the database with `raw(..., using=)`: the SQL and params are fixed for that connection when `raw()` is called, and `RawQuerySet.using("other")` only sends the same SQL and params to another connection, without compiling or converting them again, so on a database with another dialect or `TIME_ZONE` it can silently give wrong rows. Django builds the instances: a result column named as a field's column (`db_column`) fills that field with the ORM's read conversion; fields left out are deferred (loaded by one more query on access); any other column (a label) becomes an attribute holding the raw driver value, not converted (a `max()` of a datetime column is a `str` on SQLite). Use `execute_result()` when labels need conversion. Only row-returning statements (`select()`, `union_all()`, `sa.text()`): DML, even with `RETURNING`, and a list of params raise `ValueError`; a `select()` that lacks the primary key column raises `ValueError` before running (a `sa.text()` one raises Django's `FieldDoesNotExist` when iterated). On MySQL, `raw()` reads the server's version and `sql_mode` on a connection's first call, so in async code build it with `await sync_to_async(raw)(Model, stmt)`. On Oracle a column past 30 characters matches no field (Django's `raw()` reads the cut name) and is deferred.

```
execute_pipeline(items, *, using='default')
```
Runs several independent statements and returns a list with each one's `execute()` result (rows or rowcount), in input order; `[]` for no items. Each item is a statement or a `(stmt, params)` pair with `execute()`'s meaning (a list of mappings runs executemany). On PostgreSQL with psycopg 3 and libpq 14+, all statements are sent in one psycopg pipeline before any result is read: one round trip instead of one per statement (1,000 primary key lookups ran about 3x faster in the benchmark), except that each executemany item is followed by its own Sync (one more round trip each), and a call of several statements outside a transaction is wrapped in `atomic(savepoint=False)` (a Sync outside a transaction commits), which adds a `COMMIT` round trip. Elsewhere they run one by one with the same results. Use it for many independent lookups or a page's independent queries; a later item cannot use an earlier result. Every item is compiled and checked (RETURNING, Python-side defaults) before any runs. Each statement shows up in `connection.queries` and `assertNumQueries`. A call that runs more than one statement runs in `atomic(savepoint=False)`, so it writes all or nothing, also outside `atomic()`. A failing item raises its own database error with a note `execute_pipeline(): item N failed` (`e.__notes__`); later items take no effect.

```
execute_in(stmt, column, values, *, using='default', chunk_size=None)
join_across(outer, inner, on, *, using='default', inner_using=None, how='inner', chunk_size=None)
```
`execute_in` runs the `select()` `stmt` with `column IN (values)` added, one statement per chunk of values, and returns all rows as `list[tuple]` (converted as in `execute()`). `None` and repeated values are dropped; with none left nothing runs and the result is `[]`. Values convert as an `in_()` list does. `chunk_size` defaults to the smaller of `connection.ops.max_in_list_size()` (1000 on Oracle: the `ORA-01795` limit up to 19c; 23ai takes 65,535, verified on 23ai only) and `connection.features.max_query_params` (SQLite) less the statement's own parameters, else 1000. Each chunk is a statement of its own: `ORDER BY`, `LIMIT` and aggregates hold within a chunk only, so sort in Python.
`join_across` runs `outer` on `using`, then `inner` on `inner_using` (default `using`) through `execute_in` with `inner_key IN` the outer key values, and joins in Python: `1 + chunks` queries. `on=(outer_key, inner_key)`; each key is a column, a label or a result key name among that statement's selected columns (else `ValueError` before running; a composite key raises `NotImplementedError`). Each row is the outer row followed by the inner row (the inner key included), in outer order, repeated once per matching inner row. `how="inner"` drops unmatched outer rows, `how="left"` keeps them with `None` for every inner column; an outer key `None` matches nothing. Keys pair by Python equality of the converted values (so across vendors too), not by database collation (MySQL `_ci` matches case-insensitively in `IN`, Python does not). The two databases share no transaction.

```
bulk_update(table, rows, *, key=None, columns=None, batch_size=None, using='default')
bulk_insert(table, rows, *, returning=None, batch_size=None, using='default', method='insert')
```
`table` is a `table_for()` table or a model; `rows` is a sequence of column name → value mappings. `bulk_update` finds rows by `key`, updates each with its own values and returns the rowcount: the rows matched, so a key that matches no row counts nothing. `key` and `columns` are column names, not ORM names: `key` is the primary key column by default, otherwise a column name such as `"id"` or `"author_id"` (not `"pk"`, which raises `ValueError` unless a column has that name), or a tuple of them for a composite key. `columns=None` sets every column of the first row besides the key, and then every row must have exactly the first row's column names; `columns` given sets those and ignores other names in the rows. `bulk_insert` returns the rowcount, or with `returning=["id", ...]` a list of tuples in row order. One `unnest()` statement on PostgreSQL, executemany on SQLite, MySQL and Oracle (on MySQL an UPDATE executemany sends one statement per row; on MySQL and Oracle `returning` raises `NotSupportedError` before running). A positive `batch_size` sends one statement per that many rows; the rowcount is the sum and `returning` rows stay in input order. A call that runs more than one statement runs in `atomic(savepoint=False)`, so a failure writes none of its rows. Leave it `None` unless memory matters: one PostgreSQL statement for 1,000,000 rows peaked at about 850 MiB in the client and 230 MiB in the server backend; `batch_size=10_000` on `bulk_insert` cut that to about 230 MiB and 6 MiB and ran 4% faster, while batching `bulk_update` made it slower (use about 200,000 there). `bulk_insert` needs every column except an automatic primary key and `db_default` columns. Rows that leave every column to the database (`bulk_insert(Model, [{}, {}])`) insert one row each, as `bulk_create([Model(), Model()])` does: the auto primary key gets `NULL` (`DEFAULT` on PostgreSQL); without one, the first column gets `DEFAULT` on PostgreSQL and MySQL and SQLite and Oracle raise `NotSupportedError` before running; `method="copy"` raises `ValueError`. A row that lacks a column the call needs, or has other column names than the first row where they must match (always in `bulk_insert`, with `columns=None` in `bulk_update`), raises `ValueError` before anything runs. Only `bulk_update` checks for duplicate keys before running (two rows with the same key raise `ValueError`, so no rowcount comes back); a duplicate unique key in `bulk_insert` (between rows or with a stored row) is refused by the database while running, as Django's `IntegrityError`, as with `bulk_create`. `method="copy"` (PostgreSQL with psycopg 3) runs `COPY ... FROM STDIN` in text format instead, one per batch, with the same checks, converted values and atomicity, and returns the rowcount; use it for hundreds of thousands of rows when you need no `RETURNING`. With `returning` it raises `ValueError`, on other backends or psycopg2 `NotSupportedError`, both before running; it never falls back to `"insert"`. The `COPY` shows up in `connection.queries` (one per batch) but not in `connection.execute_wrapper()`.

```
arun(fn, /, *args, **kwargs)
```
`await arun(fn, ...)` runs the sync `fn` in one `sync_to_async(thread_sensitive=True)` hop and passes its return value or exception through. A coroutine function raises `TypeError`. It cleans up around the call as `close_old_connections()` does.

```
compare_table(model, table=None, *, check_db=False, using='default')
assert_table_matches(model, table=None, *, check_db=False, using='default')
forget_table(model)
```
`compare_table` lists the differences between a table (by default `table_for(model)`) and the model (and, with `check_db=True`, the database columns: presence, nullability, and on SQLite, PostgreSQL and MySQL (not MariaDB or Oracle) the column type the field's `db_type()` asks for, including varchar length and, on PostgreSQL and MySQL, numeric precision) as `Difference(source, kind, column, expected, actual)`; `assert_table_matches` raises `AssertionError` when there are any. Put it in a test. `forget_table` removes the table from the cache and `metadata` (for dynamic models in tests). `__version__` is the installed version string.

## Not supported, and pitfalls

- Databases other than SQLite, PostgreSQL, MySQL/MariaDB and Oracle. MariaDB is not tested in CI; its JSON comparisons can differ from the ORM's (MariaDB stores JSON as text).
- A JOIN across databases (Django aliases). A JOIN runs on one connection, as in the ORM and SQLAlchemy. With a server link (PostgreSQL `postgres_fdw`/`dblink`, SQLite `ATTACH`, MySQL `FEDERATED`, Oracle DB link) the remote table is a name on one connection: build it with `sa.table(name, sa.column(...), schema=...)` and `execute(stmt, using=...)` a plain JOIN (its values pass through unconverted). Without one, use `join_across(outer, inner, on, inner_using=...)`. The Django ORM's `select_related` and relation lookups across databases routed apart return no rows without an error.
- SQLAlchemy Python-side `Column(default=...)` and `onupdate=...` (values or callables) on a hand-made `sa.Table` cannot be applied, so an INSERT or UPDATE that leaves such a column out raises `ValueError` before it runs. Give the value, or use `server_default` or Django `db_default`. SQL expression defaults (`sa.func.now()`) work.
- PostgreSQL `standard_conforming_strings=off`.
- On SQLite and MySQL the jsonb operators (`contains`, `has_key`) fail when run. For JSON, `meta["k"] == 1`, `meta[("a", "b")]`, `meta == {...}` and `.as_string()` work on every backend. On MySQL a JSON column's bound values are wrapped in `JSON_EXTRACT(%s, '$')`, as the ORM does, so they compare as JSON documents. A tuple with a JSON member (`tuple_(price, meta).in_([(x, {...})])`, `.not_in()`) matches the ORM on every backend with a literal list: on MySQL and Oracle the bridge compiles it member by member (`OR` of `AND`s). There an expanding `bindparam` with a JSON member raises `NotSupportedError` before running; pass a literal list or compare the JSON column on its own. On Oracle a whole JSON column IN a literal list (`meta.in_([{...}])`, `.not_in()`) compiles to an `OR` of `JSON_EQUAL()`; there an expanding `bindparam` for it raises `NotSupportedError` before running. A key (`meta["k"].in_([...])`) keeps SQLAlchemy's `IN`. On MySQL an empty literal list (`meta.in_([])`, `meta["k"].not_in([])`) compiles to `1 != 1` / `1 = 1`, but an expanding `bindparam` given an empty list for a JSON column or key raises `NotSupportedError` before running (SQLAlchemy would write its empty set inside `JSON_EXTRACT()`); pass a literal list or skip the condition when the list is empty.
- MySQL: no `RETURNING`, no `DISTINCT ON`, no DML inside a CTE, no `(VALUES …) AS v (cols)`; `UPDATE … FROM` is written as `sa.update(t).where(t.c.k == other.c.k).values(...)` without `.returning()` (it compiles to `UPDATE t, other SET …`). `LATERAL` works (8.0.14+). In `sa.text()`, a backslash in a string literal is an escape under MySQL's default `sql_mode`. An untyped aware datetime in an `INSERT … SELECT` list is stored as its wall time, a different instant.
- Oracle: identifiers compile as Django's `quote_name()` spells them (upper case, cut past 30 characters with a hash), also for a `quoted_name(..., quote=True)`: Django makes no case-sensitive Oracle names. No `RETURNING`, `ON CONFLICT`, `UPDATE … FROM` or `DISTINCT ON` (`LATERAL` works); a multi-row `VALUES` does not compile (use a list of params, which runs executemany). Oracle stores `""` as NULL: `col == ""` matches nothing (the ORM rewrites it to `IS NULL`); read back, the ORM and the bridge give `""`. `TextField` is NCLOB, which `==` cannot compare (`ORA-22848`). JSON `==` and `!=` compile to `JSON_EQUAL()`, so `meta["k"] == 1` does not match `{"k": "1"}` (the ORM on Oracle matches both); `in_()` on a JSON key compares JSON text. `.as_string()` and the like give NULL where the key is missing. With SQLAlchemy 2.0, JSON keys and paths raise `UnsupportedCompilationError` before running (whole-document `==` works). NCLOB and BLOB values from `sa.text()` or expressions with no column come back as `oracledb` LOB objects. An untyped aware datetime in an `INSERT … SELECT` list is stored as its wall time. Boolean expressions in a SELECT list need Oracle 23ai.
- SQLite does not take `LATERAL`, `DISTINCT ON`, DML inside a CTE, or `(VALUES …) AS v (cols)`. In `INSERT … SELECT … ON CONFLICT`, when the SELECT ends with `FROM t` (no WHERE or GROUP BY), SQLite reads `ON` as a join condition and raises a syntax error; add `.where(sa.true())`.
- SQLAlchemy 2.1 deprecates `select().distinct(col)` (DISTINCT ON). On 2.1 use `stmt.ext(postgresql.distinct_on(col))`, on 2.0 `stmt.distinct(col)`.
- The databases do not document the row order of `bulk_insert(returning=...)`. If order matters, return a natural key too and pair the rows by it.

## Examples

Models: `Category(name, parent→self)`, `Product(sku unique, category, price Decimal)`, `Sale(product, sold_at, quantity, amount)`, `DailyStock(product, day, quantity, counted_at; unique(product, day))`, `PriceChange(sku unique, price)`. Common imports: `import sqlalchemy as sa`, `from django.db import connection, transaction`, `from django_sqlalchemy_bridge import execute, table_for`.

Recursive CTE (a subtree with depth):

```python
categories = table_for(Category)
tree = (
    sa.select(categories.c.id, categories.c.name, sa.literal_column("0").label("depth"))
    .where(categories.c.id == sa.bindparam("root"))
    .cte("tree", recursive=True)
)
tree = tree.union_all(
    sa.select(categories.c.id, categories.c.name, tree.c.depth + 1).join(tree, categories.c.parent_id == tree.c.id)
)
rows = execute(sa.select(tree.c.name, tree.c.depth).order_by(tree.c.depth), {"root": root_id})
```

Several CTEs (products that sold more than their category average):

```python
revenue = (
    sa.select(products.c.id, products.c.category_id, sa.func.sum(sales.c.amount).label("revenue"))
    .join(sales, sales.c.product_id == products.c.id)
    .group_by(products.c.id, products.c.category_id)
    .cte("revenue")
)
avg = sa.select(revenue.c.category_id, sa.func.avg(revenue.c.revenue).label("average")).group_by(revenue.c.category_id).cte("avg")
stmt = (
    sa.select(products.c.sku, revenue.c.revenue)  # revenue converts to Decimal
    .join(revenue, revenue.c.id == products.c.id)
    .join(avg, avg.c.category_id == revenue.c.category_id)
    .where(revenue.c.revenue > avg.c.average)
)
```

Upsert with RETURNING:

```python
from sqlalchemy.dialects import postgresql, sqlite

insert = postgresql.insert if connection.vendor == "postgresql" else sqlite.insert
stmt = insert(daily_stock)  # the params decide the columns
stmt = stmt.on_conflict_do_update(
    index_elements=[daily_stock.c.product_id, daily_stock.c.day],
    set_={"quantity": daily_stock.c.quantity + stmt.excluded.quantity, "counted_at": stmt.excluded.counted_at},
).returning(daily_stock.c.day, daily_stock.c.quantity, daily_stock.c.counted_at)
with transaction.atomic():
    rows = execute(stmt, {"product_id": pk, "day": day, "quantity": 3, "counted_at": aware_dt})
```

`ON CONFLICT DO NOTHING`: `insert(t).on_conflict_do_nothing(index_elements=[...]).returning(...)` returns `[]` for a skipped row.

`UPDATE … FROM` (SQLite 3.33+; on MySQL drop `.returning()`):

```python
stmt = (
    sa.update(products)
    .where(products.c.sku == changes.c.sku, products.c.price != changes.c.price)
    .values(price=changes.c.price)
    .returning(products.c.sku, products.c.price)
)
```

UPDATE from an aggregate subquery: `unit = sa.select(sales.c.product_id, (sa.func.sum(sales.c.amount) / sa.func.sum(sales.c.quantity)).label("unit")).group_by(sales.c.product_id).subquery()`, then `sa.update(products).where(products.c.id == unit.c.product_id).values(price=sa.cast(unit.c.unit, products.c.price.type))`.

`INSERT … SELECT` (give the values in the SELECT list a type):

```python
totals = sa.select(
    sales.c.product_id,
    sa.literal(day, daily_stock.c.day.type),
    sa.func.sum(sales.c.quantity),
    sa.literal(aware_dt, daily_stock.c.counted_at.type),
).group_by(sales.c.product_id)
execute(sa.insert(daily_stock).from_select(["product_id", "day", "quantity", "counted_at"], totals))
```

With a `.where(...)` on that SELECT and `.on_conflict_do_update(...)` chained onto `insert(...).from_select(...)`, it becomes an aggregating upsert.

Join across databases (products in `default`, the price list in `other`):

```python
from django_sqlalchemy_bridge import join_across

rows = join_across(
    sa.select(products.c.sku).order_by(products.c.sku),
    sa.select(price_changes.c.sku, price_changes.c.price),
    on=(products.c.sku, price_changes.c.sku),
    inner_using="other",
    how="left",
)
# [("JEANS", None, None), ("SHIRT", "SHIRT", Decimal("27.50")), ...]
```

PostgreSQL only (LATERAL also on MySQL and Oracle):

```python
# LATERAL: the two largest sales of each product
top = (
    sa.select(sales.c.sold_at, sales.c.amount)
    .where(sales.c.product_id == products.c.id)
    .order_by(sales.c.amount.desc())
    .limit(2)
    .lateral("top")
)
stmt = sa.select(products.c.sku, top.c.sold_at, top.c.amount).select_from(products).join(top, sa.true())

# DISTINCT ON: the latest sale of each product (SQLAlchemy 2.1; on 2.0 .distinct(sales.c.product_id))
stmt = (
    sa.select(sales.c.product_id, sales.c.sold_at)
    .order_by(sales.c.product_id, sales.c.sold_at.desc())
    .ext(postgresql.distinct_on(sales.c.product_id))
)

# Data-modifying CTE: move deleted rows in one statement (with execute; stream cannot)
moved = sa.delete(sales).where(sales.c.sold_at < cutoff).returning(sales.c.product_id, sales.c.sold_at, sales.c.quantity).cte("moved")
archive = sa.select(moved.c.product_id, sa.cast(moved.c.sold_at, sa.Date), moved.c.quantity, moved.c.sold_at)
execute(sa.insert(daily_stock).from_select(["product_id", "day", "quantity", "counted_at"], archive))
```

## Links

- [README](https://github.com/itda-work/django-sqlalchemy-bridge/blob/main/README.md) (Korean): design principles, value conversion, limits, benchmarks
- [Example tests](https://github.com/itda-work/django-sqlalchemy-bridge/blob/main/tests/test_examples.py): runnable originals of the examples above
- [ORM versus bridge](https://github.com/itda-work/django-sqlalchemy-bridge/blob/main/docs/comparisons.md) (Korean): six requests written with the ORM alone and with the bridge, with query counts and timings
- [DRF and class-based views](https://github.com/itda-work/django-sqlalchemy-bridge/blob/main/docs/drf.md) (Korean): recipes for ViewSet, pagination, writes and TemplateView, and what does not work (ModelViewSet, ModelSerializer, filter backends)
- [CHANGELOG](https://github.com/itda-work/django-sqlalchemy-bridge/blob/main/CHANGELOG.md)
