sqlphilosophy

Current source and planned customer-subdomain impact: current-state summary.

Portable SQLAlchemy repository kit: sync and async CRUD, fluent statement builders, sort/pagination, Core SQL helpers, and optional audit listeners.

Documentation

What this package does

• Repository pattern for a single mapped model (BaseRepository, AsyncBaseRepository).
• Fluent query builders with pagination/sort (StatementQueryBuilder, ListQuery, SortConfig).
• SQL helpers for row mapping, partial updates, and developer-defined fragments via sqlphilosophy.trusted_sql.
• Opt-in strict row decoders via sqlphilosophy.strict for non-coercing scalars, timestamps, enums, collections, UUIDs, and recursive JSON.
• Optional audit listeners and timestamp mixins.

What this package does not do

• Migrations, schema design, or connection pooling configuration.
• Authorization, multi-tenant isolation, or query sandboxing.
• Automatic commits for normal CRUD — see Transaction ownership.

Install

pip install sqlphilosophy

Async ORM (AsyncSession) also needs greenlet:

pip install sqlphilosophy[async]

Requires Python 3.12+ and SQLAlchemy 2.x.

Quick start (sync)

from sqlalchemy import String, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, sessionmaker

from sqlphilosophy.sorting import ListQuery
from sqlphilosophy.sync.repository import BaseRepository


class Base(DeclarativeBase):
    pass


class Widget(Base):
    __tablename__ = "widget"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(64))


engine = create_engine("sqlite:///:memory:", future=True)
Base.metadata.create_all(engine)
SessionLocal = sessionmaker(bind=engine, expire_on_commit=False)

with SessionLocal() as session:
    repo = BaseRepository(Widget, session)
    repo.create(name="alpha")  # stages + flush; does not commit
    session.commit()

    rows, total = repo.statement().fetch_page(ListQuery.from_page(page=1, size=20))
    assert total >= 1

Async: use AsyncSession, AsyncBaseRepository from sqlphilosophy.aio.repository, and await on repository/builder terminals. See the repository guide.

Package layout

Transaction ownership

• create / add / update_partial / remove / delete_* / update_where — flush or execute DML; caller commits.
• delete_all() — bulk delete; does not commit.
• batched_purge_ids(..., batch_size=...) — deletes in batches and commits after each batch; requires batch_size >= 1. Authorize in application code first.

Raw SQL trust boundaries

Identifiers and SQL fragments (table/column names, ORDER BY text, sort allowlists) must be developer-defined, never built from end-user input. User values use bind parameters.

Import trusted helpers from sqlphilosophy.trusted_sql (sql_table, col_eq, col_icontains, col_range, literal_order_expr, …). The same names are re-exported from sqlphilosophy.sql. Details: SECURITY.md and the repository guide.

Development

This repo uses uv and Ruff:

uv sync --extra dev
uv run pytest
uv run ruff check src tests docs/examples
uv run ruff format src tests docs/examples

# Optional: validate runnable docs examples (SQLite in-memory; CI runs these in smoke-package)
uv run --extra dev python docs/examples/typed_repository_sync.py
uv run --extra dev python docs/examples/typed_repository_async.py

uv run python -m build

Security

See SECURITY.md for vulnerability reporting and SQL trust boundaries.

Releasing

See RELEASING.md for GitHub + PyPI trusted publishing. See CHANGELOG.md.

License

MIT — see LICENSE.

Expression lookups and scope overrides (0.3.1)

Both repository bases provide find_entity(criteria=..., lock=False), find_row(criteria=..., columns=..., lock=False) and count_scoped(criteria=...). Criteria must not be empty; projected lookups also require columns. Entity lookups return a mapped object, projected lookups return RowMapping, and absence returns None. These methods never commit.

Override apply_scope(statement) for additional predicates on these helpers. It runs after projection initialization, because select_columns/select_entity reset the builder's SELECT. This hook does not implicitly scope existing CRUD helpers or arbitrary builder calls: keep ownership checks in named application repository methods. Existing first, get_by_id, count and statement APIs remain available and unchanged.

The 0.3.1 artifact is a local candidate until separately published. Dependent candidate packages use a vendored wheel during validation; publish sqlPhilosophy first before replacing their local sources with registry-only dependencies.