Show HN: SQL-tString a t-string SQL builder in Python

85 pointsby pgjones3 days ago

SQL-tString is a SQL builder that utilises the recently accepted PEP-750, <a href="https://peps.python.org/pep-0750/" rel="nofollow">https://peps.python.org/pep-0750/</a>, t-strings to build SQL queries, for example,<pre><code> 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 </code></pre> The placeholder ? protects against SQL injection, but cannot be used everywhere. For example, a column name cannot be a placeholder. If you try this SQL-tString will raise an error,<pre><code> col = "x" sql(t"SELECT {col} FROM y") # Raises ValueError </code></pre> To proceed you'll need to declare what the valid values of col can be,<pre><code> 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 == [] </code></pre> Thus allowing you to protect against SQL injection.As t-strings are format strings you can safely format the literals you'd like to pass as variables,<pre><code> 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"] </code></pre> This is especially useful when used with the Absent rewriting value.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,<pre><code> 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"] ) </code></pre> Specifically Absent (which is an alias of RewritingValue.ABSENT) will remove the expression it is present in, and if there an no expressions left after the removal it will also remove the clause.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,<pre><code> optional = None sql(t"SELECT x FROM y WHERE x = {optional}") </code></pre> Instead you can use IsNull to achieve the right result,<pre><code> 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 == [] </code></pre> There is also a IsNotNull for the negated comparison.The final feature allows for complex query building by nesting a t-string within the existing,<pre><code> inner = t"x = 'a'" query, _ = sql(t"SELECT x FROM y WHERE {inner}") assert query == "SELECT x FROM y WHERE x = 'a'" </code></pre> This library can be used today without Python3.14's t-strings with some limitations, <a href="https://github.com/pgjones/sql-tstring?tab=readme-ov-file#pre-python-314-usage">https://github.com/pgjones/sql-tstring?tab=readme-ov-file#pr...</a>, and I've been doing so this year. Thoughts and feedback very welcome.

13 comments

hombre_fatal3 days ago

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.

评论 #44008289 未加载

bvrmn2 days ago

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]: <a href="https://github.com/baverman/sqlbind">https://github.com/baverman/sqlbind</a>

schultzer3 days ago

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 <a href="https://github.com/elixir-dbvisor/sql/tree/main/lib">https://github.com/elixir-dbvisor/sql/tree/main/lib</a> and do conformance testing with <a href="https://github.com/elliotchance/sqltest">https://github.com/elliotchance/sqltest</a>

评论 #44006014 未加载

caturopath3 days ago

For any of you also confused by<pre><code> with sql_context(columns="x"): query, values = sql(t"SELECT {col} FROM y") </code></pre> I think1. this is relying on the `col = "x"` in the previous example2. 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.)

评论 #44007704 未加载

jitl3 days ago

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”.

评论 #44006039 未加载

throwaway1274822 days ago

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?

评论 #44016725 未加载

badmonster3 days ago

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?

ilyagr3 days ago

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.

评论 #44008977 未加载

bencyoung3 days ago

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!

评论 #44008717 未加载

schultzer3 days ago

Not really sure what a t string is or if it’s a macro, but feel similar to <a href="https://github.com/elixir-dbvisor/sql">https://github.com/elixir-dbvisor/sql</a> but less elegant and ergonomic.

评论 #44006827 未加载

评论 #44006927 未加载

sirfz3 days ago

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.

评论 #44006695 未加载

owlstuffing3 days ago

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.<a href="https://github.com/manifold-systems/manifold/blob/master/manifold-deps-parent/manifold-sql/readme.md">https://github.com/manifold-systems/manifold/blob/master/man...</a>

评论 #44006657 未加载

评论 #44006691 未加载

90s_dev3 days ago

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.

评论 #44005924 未加载

评论 #44006033 未加载

13 comments

hombre_fatal3 days ago

评论 #44008289 未加载

bvrmn2 days ago

schultzer3 days ago

评论 #44006014 未加载

caturopath3 days ago

评论 #44007704 未加载

jitl3 days ago

评论 #44006039 未加载

throwaway1274822 days ago

评论 #44016725 未加载

badmonster3 days ago

ilyagr3 days ago

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.

评论 #44008977 未加载

bencyoung3 days ago

评论 #44008717 未加载

schultzer3 days ago

Not really sure what a t string is or if it’s a macro, but feel similar to <a href="https://github.com/elixir-dbvisor/sql">https://github.com/elixir-dbvisor/sql</a> but less elegant and ergonomic.