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

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