Show HN: SQL-tString a t-string SQL builder in Python
61 pgjones 30 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.
Seems similar on this front? You also need to print the final SQL to understand what the query looks like, what conditions have been dropped etc.
What you write still isn’t the sql that’s actually executed, it’s some sort of template.
In general I find that the right approach is to avoid the conditional clauses altogether: instead of repository methods with many options, make several dedicated repository methods. You repeat a good amount of sql, but it’s so much simpler, easier to understand what’s happening, closer to the use-case, easier to optimise…
sqlglot: https://github.com/tobymao/sqlglot :
> SQLGlot is a no-dependency SQL parser, transpiler, optimizer, and engine [written in Python]. It can be used to format SQL or translate between 24 different dialects like DuckDB, Presto / Trino, Spark / Databricks, Snowflake, and BigQuery. It aims to read a wide variety of SQL inputs and output syntactically and semantically correct SQL in the targeted dialects.
Here is Python master branch:
1. this is relying on the `col = "x"` in the previous example
2. columns is a set of strings, so it might be sql_context(columns={"foo", "bar", "x"}) to allow those as valid options. It just happens that "x" is a collection supporting the `in` operator so it works much like the set {"x"} would.
2a. (You might hope that something would convert such a string to a singleton set, but I don't think it does, which would have weird results with a multi-letter string.)
No comments yet
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
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?
Designing the “right” approach to look like the “wrong” approach (string concatenation) is a bad idea, however cute it is.
It’s annoying that the wrong thing is the more ergonomic one, but at least it jumps out at any dev with any experience, they know what sqli risk looks like. With templated strings, it’s not so obvious anymore.
What I’m saying is that, regardless of how it works, I don’t think string templating for SQL is a good idea because it looks almost exactly like string concatenation. It makes more difficult to distinguish beteeen the right approach and the wrong approach (or learn about it)
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...