Show HN: SQL-tString a t-string SQL builder in Python (opens in new tab)

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.