SQLAlchemy Rejects a Parameter Your SQL Never Asked For
An optional locale filter froze live search on the default locale. The culprit: bindparams validation that fires at construction, not execution.
I opened the live search tab on my blog, typed a query, and got nothing. Not a timeout, not a 500. Just an empty results page on the default locale. The locale-filtered searches worked. The path that didn't filter by locale at all, the default one, was the one lying flat.
The error trace pointed at `text().bindparams()`, which made zero sense. I hadn't changed anything in the search logic recently. The only recent commit touched the locale filter, making it optional instead of required.
What the Code Looked Like
Here's the mental model I had: the SQL clause conditionally includes `AND locale = :locale` only when a locale is provided. The params dict always includes `locale`. I figured SQLAlchemy would just ignore the extra param if the SQL text didn't reference it. Basic defensive coding, right?
Wrong.
I added a minimal reproduction in my venv to confirm it wasn't some MariaDB driver quirk:
In the repo venv, on the same 2.0.51 build the API runs, binding `limit=5` alongside a `locale="id"` value to `text("SELECT x FROM t LIMIT :limit")` throws on the spot, no driver involved, while the control with only matching params runs fine.The error: `ArgumentError: This text() construct doesn't define a bound parameter named 'locale'`. It fires at **construction time**, when SQLAlchemy parses the `text()` string and checks every key in `bindparams()` against placeholders it found in the SQL. The database never sees the query.
I ran the reproduction against the same version my API uses, 2.0.51, so there was no "maybe a newer release fixes it" escape hatch. What fooled me during review: the code reads correctly in both branches. When a locale is present, the clause and the param line up and everything passes, which is exactly the path my manual tests exercised. The broken state only exists when the filter is absent, the one combination nobody thinks to try because the diff looks symmetric.
Why SQLAlchemy Cares Before Execution
This is actually a safety net. SQL injection via string concatenation is the classic foot-gun, so `text()` with bound parameters is supposed to be the safe path. If SQLAlchemy silently accepted extra bind parameters that don't match any `:placeholder` in the query text, you'd lose that safety guarantee: a parameter bound to nothing means either a silent no-op or a mismatch that only blows up at runtime in production.
The fix is straightforward: a single source of truth for both the SQL clause and the params dict.
params: dict[str, Any] = {
"status": ArticleStatus.PUBLISHED.value,
"terms": " ".join(terms),
"limit": limit,
}
if locale:
params["locale"] = locale
# The SQL clause only appears when a locale is provided
text(
f"SELECT slug, title, excerpt, published_at, {match} AS score "
f"FROM articles WHERE status = :status "
f"{'AND locale = :locale ' if locale else ''}"
f"AND {match} ORDER BY score DESC LIMIT :limit"
).bindparams(**params)`locale` enters the params dict and the SQL string through the same gate. If there's no locale filter, neither one sees it. Both search paths in my repo, the FULLTEXT `MATCH AGAINST` path and the `LIKE` fallback with manual escaping, got the same treatment.
SQLAlchemy itself plays by the same rule with its expanding bind parameters for `IN` clauses [2]: the placeholder list and the values are produced together, so they can't drift apart. The ORM's `.where()` goes one better, since the column and the value travel as a single object and a mismatch is unrepresentable [3]. For an ordinary column filter, I'd pick `.where()` every time. The raw `text()` route earns its keep in my two search paths, `_search_fts` and `_search_like`, because `MATCH(...) AGAINST(...)` in natural language mode with its relevance scoring has no ORM equivalent that lets me control the ranking. That's the one case where I accept the extra discipline [1]. The discipline is one sentence long: whatever condition decides the shape of the SQL also decides the shape of the params. Write them in the same place, and this entire class of bug goes away.
Related reading: MariaDB FULLTEXT search plus LLM query expansion, a vector store in pure Python, and why TypeScript types don't reach your JSON.