Async :many codegen breaks for DML + RETURNING queries (uses .stream() server-side cursor)

[jrc2139]

Summary

For :many queries that contain DML + RETURNING (e.g. WITH cte AS (...) UPDATE ... RETURNING ...), the async querier emits await self._conn.stream(...). SQLAlchemy's AsyncConnection.stream() opens a psycopg server-side cursor (DECLARE ... CURSOR FOR ...), and Postgres rejects DML inside a DECLARE CURSOR with syntax error at or near "UPDATE". The query is unrunnable from the generated wrapper.

The sync codepath uses .execute() and works correctly for the same SQL — only the async path is affected.

Reproducer

schema.sql

CREATE TABLE widgets (
 id BIGSERIAL PRIMARY KEY,
 counter INT NOT NULL DEFAULT 0
);

queries.sql

-- name: BumpWidgets :many
WITH selected AS (
 SELECT id FROM widgets WHERE counter < $1 LIMIT $2
)
UPDATE widgets
SET counter = widgets.counter + 1
FROM selected
WHERE widgets.id = selected.id
RETURNING widgets.id, widgets.counter;

sqlc.yaml

version: "2"
plugins:
 - name: py
 wasm:
 url: https://downloads.sqlc.dev/plugin/sqlc-gen-python_1.3.0.wasm
 sha256: fbedae96b5ecae2380a70fb5b925fd4bff58a6cfb1f3140375d098fbab7b3a3c
sql:
 - schema: "schema.sql"
 queries: "queries.sql"
 engine: postgresql
 codegen:
 - plugin: py
 out: gen
 options:
 package: gen
 emit_async_querier: true
 emit_sync_querier: false

Generated wrapper (abridged):

async def bump_widgets(self, *, threshold: int, limit: int) -> AsyncIterator[models.Widget]:
 result = await self._conn.stream(
 sqlalchemy.text(BUMP_WIDGETS), {"p1": threshold, "p2": limit}
 )
 async for row in result:
 yield models.Widget(id=row[0], counter=row[1])

Calling it raises:

psycopg.errors.SyntaxError: syntax error at or near "UPDATE"
LINE 5: UPDATE widgets
 ^

Expected

Async :many should work for the same SQL the sync :many path handles correctly today. The CTE + UPDATE + RETURNING pattern is canonical for any batched-update workload.

Workaround

Bypass the generated wrapper and run the SQL through conn.execute(...) from app code, importing the SQL constant from the generated module. Survives sqlc generate because the generated file is left untouched.

Suggested fix direction

The bug is isolated to the async :many branch in internal/gen.go (~lines 1022-1040). Sync :many already uses execute correctly. A minimal patch swaps stream → execute and AsyncFor → For over an awaited Result. Behavioral change: rows are buffered into the result before iteration rather than streamed — same memory profile as the sync path. For very large result sets, an opt-in keeps streaming via a per-query annotation or a config option, but defaulting to execute matches sync behavior and avoids the silent DML breakage.

Happy to send a PR if there's interest.

Environment

• sqlc-gen-python 1.3.0 (downloads.sqlc.dev wasm, sha256 fbedae96…)
• sqlc CLI 1.31.1
• Postgres 18 + psycopg 3 + SQLAlchemy 2.0

[borissmidt]

FYI you can use my fork for the meantime using:
It was an easy change.

- name: py
 wasm:
 url: https://github.com/borissmidt/sqlc-gen-python/releases/download/v1.3.1/sqlc-gen-python.wasm
 sha256: 46759d5f10ae0dc09f6051fc8c1c545482807777fe14e6966cfab4f1d9d564d4

Ideally i think sqlc could have an 'upgrade' with a :stream setting especially now that golang has iterators.