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…
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: 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.
with sql_context(columns="x"):
query, values = sql(t"SELECT {col} FROM y")
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.)
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
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 '}' db.query(f"SELECT * FROM table WHERE id={id};")
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/).
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?
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)
As for syntax highlighting, that’s available in VS Code, we auto install the appropriate extension.