iron_sql

iron_sql is a typed SQL code generator and async runtime for PostgreSQL. Write SQL where you use it, run generate_sql_module, and get a module with typed dataclasses, query helpers, and pooled connections without hand-written boilerplate.

Installation

pip install iron-sql             # runtime only (psycopg + psycopg-pool + pydantic)
pip install iron-sql[codegen]    # + inflection for code generation

The sqlc binary is bundled automatically via the sqlc Python package.

Key Features

• Query discovery. generate_sql_module scans your codebase for calls like <module>_sql("SELECT ..."), runs sqlc for type analysis, and emits a typed module.
• Strong typing. Generated dataclasses and method signatures flow through your IDE and type checker.
• Async runtime. Built on psycopg v3 with pooled connections, context-based connection reuse, and transaction helpers.
• Streaming. query_stream() uses server-side cursors for memory-efficient iteration over large result sets.
• Safe by default. Helper methods enforce expected row counts instead of returning silent None.

Package Layout

• runtime.py -- async ConnectionPool, row helpers (get_one_row, typed_scalar_row), JSON validation decorators.
• codegen/generator.py -- query discovery, type resolution, module rendering.
• codegen/sqlc.py -- wraps the sqlc CLI and models its JSON output.
• codegen/util.py -- shared codegen utilities (indent_block, write_if_changed).

Getting Started

1. Add a schema file. A Postgres DDL dump, e.g. db/schema.sql.
2. Write queries where they live. Import the future helper and use SQL literals inline:

   from myapp.db.mydb import mydb_sql
   
   user = await mydb_sql(
       "SELECT id, username, email, created_at FROM users WHERE id = @user_id"
   ).query_single_row(user_id=uid)

   Named parameters use @param (required) or @param? (optional, expands to sqlc.narg). Positional $1 works too.
3. Generate the client module.

   from pathlib import Path
   
   from iron_sql.codegen import generate_sql_module
   
   generate_sql_module(
       schema_path=Path("schema.sql"),
       module_full_name="myapp.db.mydb",
       dsn_expr="myapp.config:DSN",
       src_path=Path("."),
   )

   This writes myapp/db/mydb.py containing:
   • a connection pool singleton,
   • *_connection() and *_transaction() context managers,
   • *_listen_session(channel) and *_notify(channel, payload="") helpers,
   • dataclasses for multi-column results (deduplicated by table),
   • StrEnum classes for PostgreSQL enums,
   • a query class per statement with typed methods,
   • overloads for the *_sql() helper so editors infer return types.

Customization

• Type overrides. type_overrides={"float8": "decimal.Decimal"} maps database type names to Python type strings. For built-in types the key is the PostgreSQL internal name (float8, varchar, timestamptz), not the SQL-standard spelling (double precision, character varying); for a user-defined enum, domain or extension type it is the type name as declared (custom_int, citext). A key that no query column or parameter uses raises ValueError listing the type names actually in use.
• JSON model overrides. json_model_overrides={"users.metadata": "myapp.models:UserMeta"} adds Pydantic validation for JSON/JSONB columns.
• Naming conventions. Supply to_pascal_fn and to_snake_fn callables to control generated names.
• Connection settings. dsn_expr and pool_options_expr are written verbatim into the generated module; point them at config variables, env var lookups, or function calls.
• Debug artifacts. Pass debug_path to save sqlc inputs and outputs for inspection.

Runtime Highlights

• ConnectionPool opens lazily and reopens after close(), with ContextVar-based connection reuse for nested contexts.
• *_listen_session() uses a dedicated pooled connection and doesn't reuse ContextVar transaction connections.
• query_single_row() raises NoRowsError; query_optional_row() returns None. Both raise TooManyRowsError on 2+ rows.
• query_stream() returns an async context manager yielding an AsyncGenerator; uses server-side cursors with automatic transaction management.
• JSONB params are sent with psycopg.types.json.Jsonb; JSON with psycopg.types.json.Json. Scalar row factories validate types at runtime.
• json_validated decorator applies Pydantic model validation to dataclass fields on construction.

Example

The example/ directory contains a complete working setup: a PostgreSQL schema, generation script with testcontainers, and sample query definitions. See example/generate.py for the codegen call and example/main.py for query usage.

Validation and Troubleshooting

• Errors identify the file and line where the problematic statement lives.
• Unknown SQL types map to object and emit UnknownSQLTypeWarning (promotable to error with warnings.filterwarnings).
• Statements with the same SQL but conflicting row_type values are rejected at generation time.
• A user-defined type may reuse a standard SQL spelling of a built-in (integer, real). Enums and composite types are recognised as user-defined either way. A domain or extension type is recognised only where the column references it schema-qualified (v public."integer"), because an unqualified reference reaches iron_sql spelled exactly like the built-in; such a column resolves to the built-in, and type_overrides cannot name it.