Feature: allow passing execution-time bind parameters to `paginate` / `apaginate`

[vahidzhe]

Summary

The SQLAlchemy extension has no channel for execution-time bind parameters. paginate / apaginate always call conn.execute(query) and conn.scalar(count_query) without a parameters argument, so the only way to get a value into a query is to bake it into the statement beforehand.

For a plain select(), Select.params(...) bakes the value into the clause tree and it survives pagination, so this usually goes unnoticed. But statement-baking is not a general substitute for execution-time parameters, and there are common cases where no value can be supplied at all:

1. Raw SQL (text()) - even when values are baked with TextClause.bindparams(...), they are dropped in the count query, because create_count_query rebuilds the statement from the raw SQL string (text(...query.text...)) and does not carry the bound values across.
2. ORM-compiled parameters (column_property, deferred, with_loader_criteria) - Select.params(...) cannot reach these, because their SQL is generated during ORM compilation, after .params() values were captured.

In both cases the query cannot be paginated at all today.

What works (no change needed)

A bindparam in the statement body of a select(), bound via .params(), survives pagination - item query and count query both receive the value:

stmt = (
    select(Post)
    .where(Post.author == bindparam("who"))
    .order_by(Post.id)
    .params(who="john")
)
page = await apaginate(session, stmt, params)   # OK: items + total correct

What does not work

1. Raw SQL - baked bindparams lost in the count query

stmt = text("SELECT id, author FROM post WHERE author = :who ORDER BY id").bindparams(who="john")
await apaginate(session, stmt, params)
# -> InvalidRequestError: A value is required for bind parameter 'who'
#    (raised by the count query: SELECT count(*) FROM (SELECT id, ...))

The item query would work; the count query fails because create_count_query reconstructs a fresh text() from the raw SQL string and drops the bound values.

2. column_property / deferred - .params() cannot reach the parameter

class Post(Base):
    __tablename__ = "post"
    id: Mapped[int] = mapped_column(primary_key=True)

    is_saved: Mapped[bool] = column_property(
        select(func.count(Saved.id) > 0)
        .where(
            Saved.post_id == literal_column("post.id"),
            Saved.user_username == bindparam("current_username"),
        )
        .correlate_except(Saved)
        .scalar_subquery(),
        deferred=True,
    )

stmt = select(Post).options(undefer(Post.is_saved)).order_by(Post.id)

# .params() does NOT reach the column_property bindparam (fails even outside pagination):
await session.execute(stmt.params(current_username="john"))
# -> InvalidRequestError: A value is required for bind parameter 'current_username'

# The only thing that binds it is an execution-time params dict:
await session.execute(stmt, {"current_username": "john"})   # works

# ...but paginate never forwards a params dict, so this is unreachable through the public API.

Root cause

In fastapi_pagination/ext/sqlalchemy.py, all three execution sites call execute / scalar with no parameters argument:

• _limit_offset_flow → conn.execute(query)
• _sqlalchemy_inline_count_flow → conn.execute(paginated_query)
• _total_flow → conn.scalar(count_query)

Proposed solution

Add an optional bind_params: Mapping[str, Any] | None = None argument to paginate / apaginate, threaded through _sqlalchemy_flow down to the three execution sites, and forwarded as the parameters argument to conn.execute(query, bind_params) / conn.scalar(count_query, bind_params).

# raw SQL
await apaginate(session, text("... WHERE author = :who ..."), params, bind_params={"who": "john"})

# column_property / deferred
await apaginate(session, select(Post).options(undefer(Post.is_saved)), params,
                bind_params={"current_username": "john"})

Notes:

• Fully backward compatible: default None reproduces the current behavior (conn.execute(query, None) == conn.execute(query)).
• Applies to both the item query and the count query (needed for the text() case, where the count query is where the value is currently lost).
• Verified with a prototype against fastapi-pagination==0.15.15 / SQLAlchemy==2.0.51. With bind_params forwarded, all three cases pass:
  • text() - items and total both correct.
  • column_property / deferred - correct value per row, correct total.
  • queries without bind_params - behavior unchanged (no regression).

Alternatives considered

• Select.params(...) - works only for select() statement-body parameters; does not cover text() count queries or ORM-compiled parameters.
• with_expression() / query_expression() - a good workaround for the column_property case specifically, but does not address raw SQL or the general absence of an execution-time parameter channel.

Implementation

I would like to implement this myself. If the direction and the bind_params argument name are acceptable, I'll open a PR threading the argument through the sync and async entrypoints (including the @overload signatures), with tests for the text() count-query case and the column_property / deferred case, plus a short docs note.

[mo-hossam-stack]

i would like to work on this issue could u plz assign it to me?