fear-of-sql 0.3.0

Don't fear the SQL

fear-of-sql

sqlx-inspired query validation for PostgreSQL in Python. Validate SQL against a real database schema at startup, not at runtime. Supports t-string interpolation (Python 3.14+).

Overview

import fear_of_sql as fos

fear = fos.FearOfSQL()

# t-string query with parameters (requires python 3.14+)
@fear.query
def get_user(user_id: int) -> fos.Query[User]:
    return fos.Query(
        t"SELECT id, name, email FROM users WHERE id = {user_id}",
        result_type=User,
    )

# string SQL query with positional parameters (pre-3.14 or optional for 3.14+)
@fear.query
def get_user(user_id: int) -> fos.Query[User]:
    return fos.Query(
        "SELECT id, name, email FROM users WHERE id = $1",
        User,
        user_id,
    )

# validate all decorated queries against your DB
fear.validate_all("postgresql://localhost/mydb")

# execution helpers, fetch_one, fetch_all, fetch_optional, execute
user = await get_user(user_id=42).fetch_one(pool)
users = await list_users().fetch_all(pool)
maybe_user = await find_user("foo").fetch_optional(pool)
await delete_user(user_id=1).execute(pool)

Or validate raw SQL strings directly:

with fos.connect("postgresql://localhost/mydb") as conn:
    errors = fos.collect_errors(conn, "SELECT id, name FROM users", User)

Catch errors at startup, not at runtime:

# wrong type — id is an integer in the database
>>> fos.collect_errors(conn, "SELECT id FROM flashcards", str)
> TypeMismatchError("column 'id': expected ['str'], got int")

# wrong field type in a dataclass (pydantic also supported)
@dataclass
class Flashcard:
    id: int
    front: str
    back: int  # actually text in the database

>>> fos.collect_errors(conn, "SELECT id, front, back FROM flashcards", Flashcard)
> TypeMismatchError("column 'back': expected ['int'], got str")

# table doesn't exist, surfaces driver error
>>> fos.collect_errors(conn, "SELECT id FROM not_a_table", int)
> DatabaseError: relation "not_a_table" does not exist

Installation

pip install fear-of-sql

SQLAlchemy async drop-in support

To facilitate replacing existing SQLAlchemy async call sites with fear-of-sql queries, the execution helpers support AsyncSession and AsyncConnection from sqlalchemy.ext.asyncio. Internally, the raw connection is extracted from the session, so queries participate in the active transaction.

async with async_session() as session:
    # SQLAlchemy 2.0 async
    # result = await session.execute(select(User).where(User.id == 42))
    # user = result.scalar_one()

    # fear-of-sql
    user = await get_user(user_id=42).fetch_one(session)

Drivers

Driver support

asyncpg is used by default for async execution helpers.

psycopg is optionally supported for async by using your psycopg connections.

For sync operations, pass a DB-API 2.0 compatible connection.

Validation

Validation uses pg8000.native internally as it exposes the column metadata (table_oid, column_attrnum) needed for nullability inference via pg_catalog. DB-API 2.0 compatible drivers do not expose this information.

Query format

Queries can use either $1 or %s parameter style, or t-string interpolation on supported Python versions (3.14+).

Nullability overrides

Nullability is inferred automatically from pg_catalog and EXPLAIN plan analysis (including joins). For cases the inference can't handle, override with ! or ? column aliases:

# count(*) is always non-null, but Postgres can't prove it — force not-null
Query('SELECT count(*) AS "count!" FROM users', result_type=int)

# force a NOT NULL column to be treated as nullable
Query(r'SELECT id AS "id?" FROM flashcards', result_type=int | None)

Same convention as sqlx.

Supported Types

PostgreSQL	Python mapping
bool	bool
int2, int4, int8	int
float4, float8	float
numeric	Decimal
text, varchar, char, name	str
bytea	bytes
uuid	uuid.UUID
date	datetime.date
time	datetime.time
timestamp, timestamptz	datetime.datetime
interval	datetime.timedelta
json, jsonb	object
money	str
oid	int
All above as arrays	list[T]

Known Limitations

Unsupported PostgreSQL Types

The following built-in types are not yet supported and will raise UnsupportedTypeError during validation:

• Network types: inet, cidr, macaddr
• Geometric types: point, line, box, lseg, path, polygon, circle
• Range types: int4range, int8range, numrange, daterange, tsrange, tstzrange
• Bit string types: bit, varbit
• Full-text search: tsvector, tsquery
• XML: xml

To work around these issues, do not decorate the query functions (if using them), or do not pass them into the query validation.

User-Defined Types

Custom enums, composite types, and domain types use dynamically-assigned OIDs and require catalog lookup at validation time. Not yet implemented, but supported by the validation architecture.

Extension Types

Types from extensions (hstore, ltree, citext, etc.) are currently not supported.