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

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

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.

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.

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.

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.

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

This library can be used today without Python3.14's t-strings with some limitations, https://github.com/pgjones/sql-tstring?tab=readme-ov-file#pr..., and I've been doing so this year. Thoughts and feedback very welcome.

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

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.

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.

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

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