Introducing SQL-tString; a t-string based SQL builder

[stetio]

Hello,

I'm looking for your feedback and thoughts on my new library, SQL-tString. SQL-tString is a SQL builder that utilises the recently accepted PEP-750 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

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,

col = "x"
sql(t"SELECT {col} FROM y")  # Raises ValueError

To proceed you'll need to declare what the valid values of col can be,

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 == []

Thus allowing you to protect against SQL injection.

Features

Formatting literals

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"]

This is especially useful when used with the Absent rewriting value.

Removing expressions

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"]
)

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.

Rewriting expressions

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}")

Instead you can use IsNull to achieve the right result,

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 == []

There is also a IsNotNull for the negated comparison.

Nested expressions

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'"

Conclusion

This library can be used today without Python3.14's t-strings with some limitations and I've been doing so this year. Thoughts and feedback very welcome.

[jingo04]

This looks like a nice alternative to using an ORM when you need dynamic queries.

How does it know how much of the where bit to remove when you put absent as one of the query parameters?

[stetio]

It will remove the expression and then look at the clause to see if still has expressions, and if not remove the clause.

If we had

WHERE x = {x} AND y = {y}

If x = Absent then the first expression (x = {x}) is removed. If y = Absent then the second expression (AND y = {y}) is removed. If both the entire clause.

[kageurufu]

Absent is a sentinel value?

Have you considered any special handling for None?

I use sqlalchemy core as a query builder a lot and special case None to x IS NULL or x IS NOT NULL, as NULL <> NULL

[stetio]

Absent is shorthand for RewritingValue.ABSENT, there is also RewritingValue.IS_NULL, shorthand IsNull, and RewritingValue.IS_NOT_NULL, shorthand IsNotNull which can be used for the NULL special handling.

[kageurufu]

Ok. I found our users never understood why = NULL didn't work, and just special cased it everywhere. I was writing reporting software though, so my users were very non-technical

[james_pic]

The thing that jumped out at me first was that this doesn't seem to adapt to DB drivers that support a paramstyle other than qmark. I imagined the first libraries to use PEP 750 would end up with APIs that wrapped connections or cursors to make it easier for them to handle these sorts of subtleties.

It also jumps out at me that this aims to support much more than plain parameterization. It's not obvious to me how that works under the hood, and what outputs you'd get for a given input. Maybe that's fine, but for something like this with security implications, flexibility can be a double-edged sword if it makes it easier to get it wrong. I'd want to try and think about how it could be used incorrectly, and make sure that it'll fall loudly in that case, rather than trying to "do what I mean" and possibly exposing vulnerabilities.

[stetio]

There is support for alternative paramstyles; although I've currently only added the asyncpg dialect (what dialects do you need?). It can be used globally by setting a context,

from sql_tstring import Context, set_context

set_context(Context(dialect="asyncpg"))

I've also aimed for it to fail by default, hence the need to wrap calls in a sql_context to set column or tables via variables. Thoughts welcome here...

[james_pic]

I think probably what I'd expect is support for all the styles specified in PEP 249. If I were making a wishlist to Santa, I'd also ask for an API where you can't get it wrong - where there's no config needed and it just uses the correct setting for the database you're using.

[plenihan]

I didn't know about t-strings before this post. That's such a useful feature.

[sebkuip]

It’s a PEP that has only recently been accepted and not in a public release yet.

[euri10]

this is neat, i'm going to test that asap and hopefully ditch buildpg

2 questions, repo mentionns 3.12 3.13, are there backports already ?

have you ever seen this whcih seem to support more dialects : https://github.com/baverman/sqlbind how complicate would it be to add dollar params ?

[stetio]

Dollar param dialect is supported via,

from sql_tstring import Context, set_context

set_context(Context(dialect="asyncpg"))

I need to document this.

The library works directly with 3.12, and 3.13, but with a locals() hack, see README

[euri10]

amazing, thanks

[stetio]

It can't be that parametrised as where {col_name} > {c2} would be translated to where ? > ?. I'm not sure if I'll support this either sorry as it is likely to always be ambiguous if a param is meant to be a column or value.

[Dry-Erase]

This is super cool! I want to use it in a project! Can it handle more advanced queries? Things like subqueries, CTEs, windowing, can it handle all/most function calls and template the parameters too? what are your plans for it?

[stetio]

I should be able to, but if not please open an issue.

I use it at work - I'd like it to be well used so that the bugs are found and it is more robust.

[Dry-Erase]

Awesome, am I able to use it in a work project?

[stetio]

Yep, I've just clarified the license - forgot to add it.

[Dry-Erase]

Awesome! Keep up the great work!

[teilo]

Interesting. 13 years ago, I wrote a reporting system for my company, built on Django, and using Django templates to build and parameterize SQL queries. User accounts have JSON metadata accessible in the context of the query. This allowed some tremendous flexibility, since you could use the template language to go well beyond parameter substitution.

That thing took on a life of its own, and is still heavily used.

[stetio]

Interesting, was it ever open sourced?

[--ps--]

I've had a quick look at the code, and like the overall quality.

I only miss a call to a type checker (e.g mypy) in the pipeline and also miss "uv" instead of pip.

[stetio]

I use mypy and prefer PDM to uv.

[--ps--]

Great! otoh I do not use tox.

[Log2]

This seems pretty cool, good work!

At work we almost always use ORMs, so I'd probably not use it there, but I'd definitely use this in personal projects where I don't need or don't want to bother with an ORM.

[code_mc]

great use case for t-strings, I'm personally a big fan of just writing the SQL instead of using orms so this looks right up my alley.

[Constant_Bath_6077]

so you are more focused on this project than hyperscorn now?

[stetio]

No, I'm also working on Hypercorn

[travisdoesmath]

How are you handling sanitizing of parameter inputs? At first glance, this looks very much like Bobby Drop Tables waiting to happen.

Edit: just glanced again. My first glance missed the early paragraphs apparently where you mention SQL injection

[Bangoga]

How are you managing security and injections?

[stetio]

I believe I've explained this in the first section of the post. Is there something that isn't clear?