René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Async :many codegen breaks for DML + RETURNING queries (uses .stream() server-side cursor)","articleBody":"## Summary\n\nFor `: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.\n\nThe sync codepath uses `.execute()` and works correctly for the same SQL — only the async path is affected.\n\n## Reproducer\n\n`schema.sql`\n```sql\nCREATE TABLE widgets (\n    id BIGSERIAL PRIMARY KEY,\n    counter INT NOT NULL DEFAULT 0\n);\n```\n\n`queries.sql`\n```sql\n-- name: BumpWidgets :many\nWITH selected AS (\n    SELECT id FROM widgets WHERE counter \u003c $1 LIMIT $2\n)\nUPDATE widgets\nSET counter = widgets.counter + 1\nFROM selected\nWHERE widgets.id = selected.id\nRETURNING widgets.id, widgets.counter;\n```\n\n`sqlc.yaml`\n```yaml\nversion: \"2\"\nplugins:\n  - name: py\n    wasm:\n      url: https://downloads.sqlc.dev/plugin/sqlc-gen-python_1.3.0.wasm\n      sha256: fbedae96b5ecae2380a70fb5b925fd4bff58a6cfb1f3140375d098fbab7b3a3c\nsql:\n  - schema: \"schema.sql\"\n    queries: \"queries.sql\"\n    engine: postgresql\n    codegen:\n      - plugin: py\n        out: gen\n        options:\n          package: gen\n          emit_async_querier: true\n          emit_sync_querier: false\n```\n\nGenerated wrapper (abridged):\n```python\nasync def bump_widgets(self, *, threshold: int, limit: int) -\u003e AsyncIterator[models.Widget]:\n    result = await self._conn.stream(\n        sqlalchemy.text(BUMP_WIDGETS), {\"p1\": threshold, \"p2\": limit}\n    )\n    async for row in result:\n        yield models.Widget(id=row[0], counter=row[1])\n```\n\nCalling it raises:\n```\npsycopg.errors.SyntaxError: syntax error at or near \"UPDATE\"\nLINE 5: UPDATE widgets\n        ^\n```\n\n## Expected\n\nAsync `: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.\n\n## Workaround\n\nBypass 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.\n\n## Suggested fix direction\n\nThe 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.\n\nHappy to send a PR if there's interest.\n\n## Environment\n\n- sqlc-gen-python `1.3.0` (downloads.sqlc.dev wasm, sha256 fbedae96…)\n- sqlc CLI `1.31.1`\n- Postgres 18 + psycopg 3 + SQLAlchemy 2.0\n","author":{"url":"https://github.com/jrc2139","@type":"Person","name":"jrc2139"},"datePublished":"2026-05-05T23:34:48.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":1},"url":"https://github.com/101/sqlc-gen-python/issues/101"}