Skip to content

feat: add direct database query APIs on App (fetch_all, fetch_one, execute) #289

Open

Description

@ZhuchkaTriplesix

Summary

Currently, native database queries can only be executed by having a route dependency return a DBQuery instance (Depends(get_query)).

This creates a major functional limitation:

  • Handlers cannot execute queries dynamically based on parsed request bodies (e.g. INSERT INTO users (name, email) VALUES ($1, $2)).
  • Routes cannot execute multiple sequential queries or conditionally fetch records based on business logic.
  • Write queries (INSERT, UPDATE, DELETE) have no way to return rows_affected: int.
  • Single-row queries (SELECT ... WHERE id = $1) always return a list[dict], requiring awkward rows[0] indexing and empty list checks.

Proposed Solution

Expose native async query helper methods on App (and on Request):

  1. await app.fetch_all(query: str, args=None) -> list[dict]:
    • Executes a parameterized query and returns all matching rows as Python dictionaries.
  2. await app.fetch_one(query: str, args=None) -> dict | None:
    • Executes a query, fetches the first row (or None if empty), and returns it as a dictionary.
  3. await app.execute(query: str, args=None) -> int:
    • Executes a data manipulation statement (INSERT, UPDATE, DELETE) via sqlx::query(...).execute(pool) and returns the number of rows affected.
  4. Convenience access on Request:
    • Allow handlers to access request.db.fetch_all(...) or request.db.execute(...).

Acceptance Criteria

  • app.fetch_all, app.fetch_one, and app.execute exposed and documented on App.
  • fetch_one returns None when zero rows match.
  • execute returns integer count of affected rows.
  • Raises descriptive RuntimeError if database pool is not configured via app.setup_database.
  • Integration tests added in tests/test_db_direct.py.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions