I thought this was just going to be the same ol "where id = {id}" interpolation but dang, those are some crazy examples.
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.
> 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).
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…
I'm author of sqlbind[1] and since t-strings announce have been thinking really hard how to incorporate it. Like obvious cases `t"select * from table where id = {id}"` are tempting only at first glance. But simple queries could be written by other means. Dynamic ones where you should drop part of query by some condition is a real problem.
Your solution is impressive. It would be quite hard to support crazy sql extensions, for example for ClickHouse but as a concept it really ingenious.
> 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.
with sql_context(columns="x"):
query, values = sql(t"SELECT {col} FROM y")
I think
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.)
How does the SQL parsing work for the rewrites like removing expressions? I have a project using some non-standard SQL features and we have quite complex queries going on, so the rewriting makes me a bit nervous. The great thing about tstrings for sql is that it’s a total escape from “magick” creating ineffable and unknown sql replacing with very straightforward what you see is what you get sql right in the source code.
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”.
The presence of Absent removes the entire expression, and if that removal results in an empty clause (or group) it will remove that as well. For example if `a = Absent` `WHERE a = {a}` will remove everything, whereas `WHERE a = {a} AND b = {b}` will result in `WHERE b = {b}`.
> Do you support templating a sql tstring into an sql tstring for composition?
How do you know what the expression is though? Don’t you need to be parsing the SQL? If I have non standard SQL somewhere upstream in the text how does the parser cope?
I had a question about dynamic query fragments: is there a recommended way to compose larger queries from smaller pieces while preserving placeholder safety and avoiding manual string concatenation? For example, conditionally building WHERE clauses or joins from pre-defined fragments?
The columns feature seems somewhat dangerous at first glance. What if I wanted different sets of allowed columns for different parts of the query? Would I have to provide the superset of columns supported in both subqueries / tables?
The `sql_context` feature is meant to protect against SQL injection. I think for your usecase you would need to supply all columns, but you could used nested t-strings included based on whatever logic you need.
I think that, since you don't allow `sql(t"SELECT {a-1}")`, you should allow `sql(t"SELECT {}", a - 1)`, as long as that is possible with t-strings. This would be similar to Rust's `format!` then.
Ah, apparently with real t-strings, `t"SELECT {a-1}"` should be allowed while `t"SELECT {}"` is not.
Here is Python master branch:
Python 3.15.0a0 (heads/main:ea2d707bd5, May 16 2025, 12:20:56) [Clang 16.0.0 (clang-1600.0.26.6)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> t"Hello {a}"
Traceback (most recent call last):
File "<python-input-1>", line 1, in <module>
t"Hello {a}"
^
NameError: name 'a' is not defined
>>> a=3
>>> t"Hello {a+5}"
Template(strings=('Hello ', ''), interpolations=(Interpolation(8, 'a+5', None, ''),))
>>> t"Hello {}"
File "<python-input-6>", line 1
t"Hello {}"
^
SyntaxError: t-string: valid expression required before '}'
Looks very nice but the Absent functionality seems like a potential foot gun to me, easy to remove an entire WHERE clause and blow up a whole table! If I have a filter in sql query it's because I really want it to be used!
t-strings (or template strings) are an upcoming Python 3.14 feature. They have similar syntax to f-strings (which were introduced in 3.6) except that they provide access to the string and the interpolated values (the bits inside the curly brackets) before they have been combined. Previously, something like
db.query(f"SELECT * FROM table WHERE id={id};")
would have been vulnerable to the classic "bobby tables" SQL injection but t-strings allow for almost the same syntax (which is quite natural for Python programmers) without incurring a security risk.
Technically this enforces parameterized queries since all it does is basically return the parameterized query in the given db client dialect and the list of parameters. It could be useful for building a unified interface for all sql db clients for example (instead of having to remember whether parameters are %s or ? or {param}, etc). On the other hand, db clients can utilize t-strings to directly allow you to safely execute queries such as t"select * from table where id = {id}" without passing any extra parameters.
Your library looks great. But a tangential rant about t-strings, using lexical scope for placeholder lookup is just a terrible, terrible design. It should be explicitly passed in a dictionary. I'm not sure why they made this decision.
If they’re gonna do that why bother making a new concept? You could already build(normalString, someDict)
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
No I think your point is valid, and is valid in JavaScript too.
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.
`...` and fn`...` in JavaScript are just syntactic sugar for function calls, the former for array.join(...) and the latter for fn(...) so there's no issue with these utilizing the current scope since that's what all function calls do.
I might have misunderstood your point, but scoping isn’t related to what I’m trying to say.
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)
No it was me who misunderstood you. And I kind of agree. I've never been a fan of tagged template literals. It gives you no autocompletion, no type checking, no syntax highlighting, nothing. And it requires a runtime string parser. I get why people like it, and maybe it's fine if you don't need those things and don't mind the cost of runtime parsing, but I need them and I do mind.
It really does not need runtime string parsing unless you want to do some kinds of manipulation. I built Notion’s sql`…` literal and “all” it does is concat the string parts arrays and concat the argument arrays so you can pass { query: string, args: PostgresArg[] } to the database. The database is the only one who needs to parse the query string.
As for syntax highlighting, that’s available in VS Code, we auto install the appropriate extension.
Oh wait I know why. It's because the PIP had no specialized syntax highlighting to show that it was getting the variables from scope. So I started reasoning about it differently than I do about JS string literals, rather lazily too, and ended up thinking of something like emacs's dynamic scope or something. Amazing what syntax highlighting does to how we think.
I thought this was just going to be the same ol "where id = {id}" interpolation but dang, those are some crazy examples.
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.
> 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).
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…
I'm author of sqlbind[1] and since t-strings announce have been thinking really hard how to incorporate it. Like obvious cases `t"select * from table where id = {id}"` are tempting only at first glance. But simple queries could be written by other means. Dynamic ones where you should drop part of query by some condition is a real problem.
Your solution is impressive. It would be quite hard to support crazy sql extensions, for example for ClickHouse but as a concept it really ingenious.
[1]: https://github.com/baverman/sqlbind
Just took a quick look, and it seams like the parser is hand written which is great, but you probably want to build a lexer and parser based on the BNF grammar take a look at how I do it here https://github.com/elixir-dbvisor/sql/tree/main/lib and do conformance testing with https://github.com/elliotchance/sqltest
Thanks, do you have a reference for SQL grammar - I've had no success finding an official source.
Ibis has sqlglot for parsing and rewriting SQL query graphs; and there's sql-to-ibis: https://github.com/ibis-project/ibis/issues/9529
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.
You can google SQL grammar. But here is the 2025: https://standards.iso.org/iso-iec/9075/-2/ed-6/en/
Thank you! My Google foo did not find this.
Google search sucks really bad now for very specific searches.
For any of you also confused by
I think1. 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.)
Sorry that is a typo, I meant,
How does the SQL parsing work for the rewrites like removing expressions? I have a project using some non-standard SQL features and we have quite complex queries going on, so the rewriting makes me a bit nervous. The great thing about tstrings for sql is that it’s a total escape from “magick” creating ineffable and unknown sql replacing with very straightforward what you see is what you get sql right in the source code.
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”.
The presence of Absent removes the entire expression, and if that removal results in an empty clause (or group) it will remove that as well. For example if `a = Absent` `WHERE a = {a}` will remove everything, whereas `WHERE a = {a} AND b = {b}` will result in `WHERE b = {b}`.
> Do you support templating a sql tstring into an sql tstring for composition?
Yep
How do you know what the expression is though? Don’t you need to be parsing the SQL? If I have non standard SQL somewhere upstream in the text how does the parser cope?
It does parse the SQL. At the moment an expression is defined as all the text between the appropriate separators given the clause.
I had a question about dynamic query fragments: is there a recommended way to compose larger queries from smaller pieces while preserving placeholder safety and avoiding manual string concatenation? For example, conditionally building WHERE clauses or joins from pre-defined fragments?
The columns feature seems somewhat dangerous at first glance. What if I wanted different sets of allowed columns for different parts of the query? Would I have to provide the superset of columns supported in both subqueries / tables?
The `sql_context` feature is meant to protect against SQL injection. I think for your usecase you would need to supply all columns, but you could used nested t-strings included based on whatever logic you need.
I think that, since you don't allow `sql(t"SELECT {a-1}")`, you should allow `sql(t"SELECT {}", a - 1)`, as long as that is possible with t-strings. This would be similar to Rust's `format!` then.
Ah, apparently with real t-strings, `t"SELECT {a-1}"` should be allowed while `t"SELECT {}"` is not.
Here is Python master branch:
Looks very nice but the Absent functionality seems like a potential foot gun to me, easy to remove an entire WHERE clause and blow up a whole table! If I have a filter in sql query it's because I really want it to be used!
Not really sure what a t string is or if it’s a macro, but feel similar to https://github.com/elixir-dbvisor/sql but less elegant and ergonomic.
t-strings (or template strings) are an upcoming Python 3.14 feature. They have similar syntax to f-strings (which were introduced in 3.6) except that they provide access to the string and the interpolated values (the bits inside the curly brackets) before they have been combined. Previously, something like
would have been vulnerable to the classic "bobby tables" SQL injection but t-strings allow for almost the same syntax (which is quite natural for Python programmers) without incurring a security risk.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/).
Technically this enforces parameterized queries since all it does is basically return the parameterized query in the given db client dialect and the list of parameters. It could be useful for building a unified interface for all sql db clients for example (instead of having to remember whether parameters are %s or ? or {param}, etc). On the other hand, db clients can utilize t-strings to directly allow you to safely execute queries such as t"select * from table where id = {id}" without passing any extra parameters.
Languages should strive to type safely inline SQL and other structured data.
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...
If your language has macros you can get by as well https://github.com/elixir-dbvisor/sql
Your library looks great. But a tangential rant about t-strings, using lexical scope for placeholder lookup is just a terrible, terrible design. It should be explicitly passed in a dictionary. I'm not sure why they made this decision.
If they’re gonna do that why bother making a new concept? You could already build(normalString, someDict)
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?
Yeah in retrospect it's identical to what JavaScript does with string literals. I don't know what I was thinking.
No I think your point is valid, and is valid in JavaScript too.
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.
`...` and fn`...` in JavaScript are just syntactic sugar for function calls, the former for array.join(...) and the latter for fn(...) so there's no issue with these utilizing the current scope since that's what all function calls do.
I might have misunderstood your point, but scoping isn’t related to what I’m trying to say.
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)
No it was me who misunderstood you. And I kind of agree. I've never been a fan of tagged template literals. It gives you no autocompletion, no type checking, no syntax highlighting, nothing. And it requires a runtime string parser. I get why people like it, and maybe it's fine if you don't need those things and don't mind the cost of runtime parsing, but I need them and I do mind.
It really does not need runtime string parsing unless you want to do some kinds of manipulation. I built Notion’s sql`…` literal and “all” it does is concat the string parts arrays and concat the argument arrays so you can pass { query: string, args: PostgresArg[] } to the database. The database is the only one who needs to parse the query string.
As for syntax highlighting, that’s available in VS Code, we auto install the appropriate extension.
Oh wait I know why. It's because the PIP had no specialized syntax highlighting to show that it was getting the variables from scope. So I started reasoning about it differently than I do about JS string literals, rather lazily too, and ended up thinking of something like emacs's dynamic scope or something. Amazing what syntax highlighting does to how we think.
[flagged]