Show HN: SQL-tString a t-string SQL builder in Python
32 pgjones 15 5/16/2025, 12:48:22 PM github.com ↗
SQL-tString is a SQL builder that utilises the recently accepted PEP-750, https://peps.python.org/pep-0750/, t-strings to build SQL queries, for example,
from sql_tstring import sql
val = 2
query, values = sql(t"SELECT x FROM y WHERE x = {val}")
assert query == "SELECT x FROM y WHERE x = ?"
assert values == [2]
db.execute(query, values) # Most DB engines support this
col = "x"
sql(t"SELECT {col} FROM y") # Raises ValueError
from sql_tstring import sql_context
with sql_context(columns="x"):
query, values = sql(t"SELECT {col} FROM y")
assert query == "SELECT x FROM y"
assert values == []
As t-strings are format strings you can safely format the literals you'd like to pass as variables,
text = "world"
query, values = sql(t"SELECT x FROM y WHERE x LIKE '%{text}'")
assert query == "SELECT x FROM y WHERE x LIKE ?"
assert values == ["%world"]
SQL-tString is a SQL builder and as such you can use special RewritingValues to alter and build the query you want at runtime. This is best shown by considering a query you sometimes want to search by one column a, sometimes by b, and sometimes both,
def search(
*,
a: str | AbsentType = Absent,
b: str | AbsentType = Absent
) -> tuple[str, list[str]]:
return sql(t"SELECT x FROM y WHERE a = {a} AND b = {b}")
assert search() == "SELECT x FROM y", []
assert search(a="hello") == "SELECT x FROM y WHERE a = ?", ["hello"]
assert search(b="world") == "SELECT x FROM y WHERE b = ?", ["world"]
assert search(a="hello", b="world") == (
"SELECT x FROM y WHERE a = ? AND b = ?", ["hello", "world"]
)
The other rewriting values I've included are handle the frustrating case of comparing to NULL, for example the following is valid but won't work as you'd likely expect,
optional = None
sql(t"SELECT x FROM y WHERE x = {optional}")
from sql_tstring import IsNull
optional = IsNull
query, values = sql(t"SELECT x FROM y WHERE x = {optional}")
assert query == "SELECT x FROM y WHERE x IS NULL"
assert values == []
The final feature allows for complex query building by nesting a t-string within the existing,
inner = t"x = 'a'"
query, _ = sql(t"SELECT x FROM y WHERE {inner}")
assert query == "SELECT x FROM y WHERE x = 'a'"
I can imagine the behavior takes some trial and error to figure out, but it looks like you can write a search() query that contains fully-loaded sql statement as if all facets were provided, yet you can make each facet optional and those expressions will get removed from the statement.
That would be much nicer than the traditional route of building up a where clause with a bunch of if-statements where it's very hard to understand what the final where clause might look like without print(statement).
I'd rather write the whole SQL statement upfront which this seems to let you do.
Do you support templating a sql tstring into an sql tstring for composition?
I use that feature a lot with the roughly equivalent TypeScript sql`…` template literals for the NOT NULL thing and handling absence but it’s all ternaries in “user space”.
> Do you support templating a sql tstring into an sql tstring for composition?
Yep
If you are curious, t-strings have previously been discussed here (https://news.ycombinator.com/item?id=43748512 and https://news.ycombinator.com/item?id=43647716) and you can read the PEP that proposed their addition to the language (https://peps.python.org/pep-0750/).
No comments yet
With Java the manifold project achieves this via compiler plugin. The manifold-sql[1] module provides inline, type safe, native SQL.
1.https://github.com/manifold-systems/manifold/blob/master/man...
Like why make me state “A goes here, also the value of A is 1” when I can just say “1 goes here”? When I build an array or map, I just write the expression
{ key1: value1 }
I don’t need to write
build({ key1, value1 }, { “key1”: key1, “value1”: value1 })
Why should an sql literal be any different from an array or dictionary literal?