t-sql

A lightweight SQL templating library that leverages Python 3.14's t-strings (PEP 750). (Note: This library has absolutely nothing to do with Microsoft SQLServer)

t-sql provides a safe way to write SQL queries using Python's template strings (t-strings) while preventing SQL injection attacks through multiple parameter styling options.

⚠️ Python Version Requirement

This library requires Python 3.14+

t-sql is built specifically to take advantage of the new t-string feature introduced in PEP 750, which is only available in Python 3.14+.

Installing

# with pip
pip install t-sql
# with uv
uv add t-sql

Quick Start

importtsql# Basic usagename='billy'query=t'select * from users where name={name}'# Render with default QMARK stylesql, params=tsql.render(query)
# ('select * from users where name = ?', ['billy'])# Or use a different parameter stylesql, params=tsql.render(query, style=tsql.styles.NUMERIC_DOLLAR)
# ('select * from users where name = $1', ['billy'])

Parameter Styles

QMARK (default): Uses ? placeholders
NUMERIC: Uses :1, :2, etc. placeholders
NAMED: Uses :name placeholders
FORMAT: Uses %s placeholders
PYFORMAT: Uses %(name)s placeholders
NUMERIC_DOLLAR: Uses $1, $2, etc. (PostgreSQL native)
ESCAPED: Escapes values directly into SQL (no parameters)

Core Features

SQL Injection Prevention

# SQL injection prevention works automaticallyname="billy ' and 1=1 --"sql, params=tsql.render(t'select * from users where name={name}')
# Even with ESCAPED style, quotes are properly escapedsql, _=tsql.render(t'select * from users where name={name}', style=tsql.styles.ESCAPED)
# ("select * from users where name = 'billy '' and 1=1 --'", [])

Format-spec helpers

Literal

For table/column names that can't be parameterized:

table="users"col="name"val="billy"query=t'select * from {table:literal} where {col:literal}={val}'sql, params=tsql.render(query)
# ('select * from users where name = ?', ['billy'])

unsafe

For cases where you need to bypass safety (use with extreme caution):

dynamic_where="age > 18 AND active = true"sql, params=tsql.render(t"SELECT * FROM users WHERE {dynamic_where:unsafe}")

as_values

Formats a dictionary for INSERT statements:

values= {'id': 'abc123', 'name': 'bob', 'email': 'bob@example.com'}
sql, params=tsql.render(t"INSERT INTO users {values:as_values}")
# ('INSERT INTO users (id, name, email) VALUES (?, ?, ?)', ['abc123', 'bob', 'bob@example.com'])

as_set

Formats a dictionary for UPDATE statements:

values= {'name': 'joe', 'email': 'joe@example.com'}
sql, params=tsql.render(t"UPDATE users SET {values:as_set} WHERE id='abc123'")
# ('UPDATE users SET name = ?, email = ? WHERE id='abc123'', ['joe', 'joe@example.com'])

LIKE Pattern Matching

Safe pattern matching with automatic wildcard escaping:

# Contains search (%value%)search="john"sql, params=tsql.render(t"SELECT * FROM users WHERE name ILIKE {search:%like%}")
# ('SELECT * FROM users WHERE name ILIKE ? ESCAPE '\\'', ['%john%'])# Prefix search (value% - starts with)prefix="admin"sql, params=tsql.render(t"SELECT * FROM users WHERE username LIKE {prefix:like%}")
# ('SELECT * FROM users WHERE username LIKE ? ESCAPE '\\'', ['admin%'])# Suffix search (%value - ends with)domain="@gmail.com"sql, params=tsql.render(t"SELECT * FROM users WHERE email LIKE {domain:%like}")
# ('SELECT * FROM users WHERE email LIKE ? ESCAPE '\\'', ['%@gmail.com'])

Security: All LIKE format specs automatically escape %, _, and \ wildcards in user input to prevent injection attacks:

Wildcards in data are escaped

search = "50%_discount" sql, params = tsql.render(t"SELECT * FROM products WHERE name LIKE {search:%like%}")

('SELECT * FROM products WHERE name LIKE ? ESCAPE '\'', ['%50\%\_discount%'])

Matches the literal string "50%_discount", not "50X" or "50Xdiscount"


**For controlled values where you WANT wildcards**, build the pattern manually without format specs:
```python
# Developer-controlled pattern (wildcards intentional)
pattern = f"%{category}%"
sql, params = tsql.render(t"SELECT * FROM products WHERE tags LIKE {pattern}")
# No escaping - % and _ work as wildcards

Tuples for IN clauses

Use tuples to expand lists of values for SQL IN clauses:

# Convert list to tuple for IN clausemy_ids= ['123', '234', '531']
sql, params=tsql.render(t"SELECT * FROM mytable WHERE id IN {tuple(my_ids)}")
# ('SELECT * FROM mytable WHERE id IN (?, ?, ?)', ['123', '234', '531'])# Or use a tuple directlyactive_statuses= ('active', 'pending', 'approved')
sql, params=tsql.render(t"SELECT * FROM orders WHERE status IN {active_statuses}")
# ('SELECT * FROM orders WHERE status IN (?, ?, ?)', ['active', 'pending', 'approved'])

Helper Functions

t-sql provides several convenience functions for common SQL operations:

t_join

Joins multiple t-strings together:

importtsqlmin_age=18parts= [t"SELECT *", t"FROM users", t"WHERE age > {min_age}"]
query=tsql.t_join(t" ", parts)
sql, params=tsql.render(query)
# ('SELECT * FROM users WHERE age > ?', [18])

select

Quick SELECT queries:

# Select all columnsquery=tsql.select('users')
sql, params=query.render()
# ('SELECT * FROM users', [])# Select specific columnsquery=tsql.select('users', columns=['name', 'email'])
sql, params=query.render()
# ('SELECT name, email FROM users', [])# With WHERE clausequery=tsql.select('users', columns=['name', 'email'], where={'age': 18})
sql, params=query.render()
# ('SELECT name, email FROM users WHERE age = ?', [18])

insert

Quick INSERT queries:

query=tsql.insert('users', id='abc123', name='bob', email='bob@example.com')
sql, params=query.render()
# ('INSERT INTO users (id, name, email) VALUES (?, ?, ?)', ['abc123', 'bob', 'bob@example.com'])

update

Quick UPDATE queries:

# Update by IDquery=tsql.update('users', 'abc123', email='new@example.com')
sql, params=query.render()
# ('UPDATE users SET email = ? WHERE id = ?', ['new@example.com', 'abc123'])

delete

Quick DELETE queries:

# Delete by IDquery=tsql.delete('users', id_value='abc123')
sql, params=query.render()
# ('DELETE FROM users WHERE id = ?', ['abc123'])# Delete with custom WHEREquery=tsql.delete('users', where={'age': 18})
sql, params=query.render()
# ('DELETE FROM users WHERE age = ?', [18])

Note: These helper functions return query builder objects, so you can chain additional methods:

query=tsql.select('users').where(t'age > {min_age}').limit(10)
sql, params=query.render()

Query Builder

For a more structured approach, t-sql includes an optional query builder with a fluent interface and type-safe column references.

Basic Usage

fromtsql.query_builderimportTable, ColumnclassUsers(Table):
id: Columnusername: Columnemail: Columnage: Column# Simple SELECTquery=Users.select(Users.id, Users.username)
sql, params=query.render()
# ('SELECT users.id, users.username FROM users', [])# With WHERE clausequery=Users.select().where(Users.age>18)
sql, params=query.render()
# ('SELECT * FROM users WHERE users.age > ?', [18])# Multiple conditions (ANDed together)query= (Users.select(Users.username, Users.email)
.where(Users.age>18)
.where(Users.email!=None))

Table Names: The table name defaults to the lowercase class name. To specify a custom name:

classUserAccount(Table, table_name='user_accounts'):
id: Columnusername: Column

Joins

classPosts(Table):
id: Columnuser_id: Columntitle: Column# INNER JOINquery= (Posts.select(Posts.title, Users.username)
.join(Users, on=Posts.user_id==Users.id)
.where(Posts.id>100))
# LEFT JOINquery= (Posts.select()
.left_join(Users, on=Posts.user_id==Users.id))
# join_type= accepts only INNER, LEFT, RIGHT and FULL (the OUTER spellings normalize# to the bare keyword: 'LEFT OUTER' -> 'LEFT'). Anything else raises ValueError.query=Posts.select().join(Users, on=Posts.user_id==Users.id, join_type='FULL OUTER')

join() always emits an ON clause, so CROSS is not in the allowlist — cross, lateral and vendor-specific joins go through join_raw().

Raw JOIN clauses (escape hatch)

For join shapes the typed join()/left_join()/right_join() can't express — LATERAL subqueries, ON-less cross joins, set-returning functions — use join_raw() to splice a complete JOIN clause Template verbatim. The Template must carry the whole clause including the join keyword; nothing is added around it. Parameters inside the Template are still parameterized and renumber correctly alongside the rest of the query.

query= (SelectQueryBuilder.from_table('records', schema='dataset', alias='t')
.select(t't.*')
.join_raw(t'CROSS JOIN LATERAL unnest(t.tags) AS tag'))
# Parameterized raw join, composed with a WHERE clausequery= (SelectQueryBuilder.from_table('records', alias='t')
.select(t't.*')
.join_raw(t'LEFT JOIN other o ON o.id = t.ref AND o.kind = {"x"}')
.where(t't.active = {True}'))

⚠️Not advised.join_raw() bypasses the builder's join structure. Prefer the typed join()/left_join()/right_join() with a Table + Condition wherever they suffice.

Query Features

Selecting All Columns from a Table

Use Table.ALL to select all columns from a specific table:

# Select all columns from postsquery=Posts.select(Posts.ALL)
# ('SELECT posts.* FROM posts', [])# Select all columns from posts + specific columns from joined tablesquery= (Posts.select(Posts.ALL, Users.username, Users.email)
.join(Users, Posts.user_id==Users.id))
# ('SELECT posts.*, users.username, users.email FROM posts INNER JOIN users ON ...', [])# Select all columns from multiple tablesquery=Posts.select(Posts.ALL, Users.ALL).join(Users, Posts.user_id==Users.id)
# ('SELECT posts.*, users.* FROM posts INNER JOIN users ON ...', [])

This is particularly useful when joining tables where you want all columns from one table but only specific columns from others.

DISTINCT ON (PostgreSQL)

distinct_on() emits a SELECT DISTINCT ON (...) clause. It accepts Column objects, string column names, or raw Templates — coerced exactly like select().

query=Users.select(Users.id, Users.username).distinct_on('username')
# ('SELECT DISTINCT ON (username) users.id, users.username FROM users', [])# Multiple columns are comma-joined inside the parensquery=Users.select(Users.id).distinct_on('username', 'email')
# ('SELECT DISTINCT ON (username, email) users.id FROM users', [])

FROM ONLY and Table Aliases

When building from a string table name, from_table() accepts only=True to emit FROM ONLY {table} (excludes inheriting child tables in PostgreSQL) and alias=... to emit FROM {table} AS {alias} (the alias is identifier-validated):

query= (SelectQueryBuilder.from_table('records', schema='dataset', alias='t', only=True)
.select(t't.*')
.where(t't.active = {True}'))
# ('SELECT t.* FROM ONLY dataset.records AS t WHERE (t.active = $1)', [True])

NULL Checks and Other Operators

# NULL checksquery=Users.select().where(Users.email.is_null())
query=Users.select().where(Users.email.is_not_null())
# IN clausequery=Users.select().where(Users.id.in_([1, 2, 3]))
query=Users.select().where(Users.id.not_in([1, 2, 3]))
# LIKE clausequery=Users.select().where(Users.username.like('%john%'))
query=Users.select().where(Users.username.not_like('%john%'))
query=Users.select().where(Users.username.ilike('%JOHN%')) # case-insensitivequery=Users.select().where(Users.username.not_ilike('%JOHN%'))
# BETWEEN clausequery=Users.select().where(Users.age.between(18, 65))
query=Users.select().where(Users.age.not_between(18, 65))
# Condition() is public, and its operator is validated against exactly the set the# methods above emit: IS, IS NOT, =, !=, <, <=, >, >=, IN, NOT IN, LIKE, NOT LIKE,# ILIKE, NOT ILIKE, BETWEEN, NOT BETWEEN. Anything else raises ValueError, so a# ?field=&op=&value= filter endpoint cannot smuggle a predicate through op.fromtsql.query_builderimportConditionquery=Users.select().where(Condition(Users.age, '>=', 21))
# For a predicate the allowlist rejects, pass a Template to where() — the values are# still parameterized:query=Users.select().where(t'users.age IS DISTINCT FROM {21}')
# ORDER BYquery=Posts.select().order_by(Posts.id) # defaults to ASCquery=Posts.select().order_by(Posts.id.desc())
query=Posts.select().order_by(Posts.created_at.asc(), Posts.id.desc())
# NULLS orderingquery=Posts.select().order_by(Posts.created_at.desc().nulls_last())
query=Posts.select().order_by(Posts.created_at.asc().nulls_first())
# The `direction=` keyword accepts only 'ASC' or 'DESC'; anything else raises# ValueError. Use .nulls_first()/.nulls_last() or a Template for richer ordering.query=Posts.select().order_by('created_at', direction='DESC')
# ORDER BY / GROUP BY also accept raw Templates, emitted verbatim# (parity with where()/having()/select()), for computed expressions:query=Posts.select().order_by(t'lower(title) DESC')
query=Posts.select().group_by(t"date_trunc('day', created_at)")
# LIMIT and OFFSETquery=Posts.select().limit(10).offset(20)
# GROUP BY and HAVINGquery= (Posts.select()
.group_by(Posts.user_id)
.having(t'COUNT(*) > {min_count}'))

Breaking change (4.14.0): order_by(..., direction=...) now validates its argument and accepts only 'ASC' or 'DESC'. Previously the string was interpolated into the SQL unvalidated, so an app passing user input straight through to direction= had an injection sink. Undocumented forms such as direction='DESC NULLS LAST' now raise ValueError — use .nulls_last() or order_by(t'created_at DESC NULLS LAST') instead. Validation is eager: direction= is checked on every call, so a bad value raises even when every column already carries its own direction via .asc()/.desc() and the keyword would have gone unused.
Scope: 4.14.0 adds validation to the keyword and name parameters that were previously rendered into the SQL text unvalidated. Each now raises ValueError naming the offending value:
order_by(..., direction=...) — 'ASC' / 'DESC', plus the NULLS ordering
Condition(left, operator, right) — left must be a real Column; operator is one of the 16 the comparison API emits
join(..., join_type=...) and Join(...) — INNER / LEFT / RIGHT / FULL
Column(table_name, column_name, alias, schema), and therefore .as_() — every one of the four name fields must be a single unqualified identifier ('*' is allowed as a column name, since Table.ALL is Column(table_name, '*'))
returning(), on_conflict_do_nothing() / on_conflict_update() and with_cte() — each name is an identifier (returning('*') remains the wildcard form)
So a ?field=&op=&value= filter endpoint is now safe on all three inputs: resolve field to a Column (getattr(Users, field)) and both the name and the operator are checked for you.
The two escape hatches for anything the allowlists reject are a t-string Template (where(), having(), select(), order_by(), group_by()) and join_raw() for non-standard join shapes. Both parameterize their interpolations. :unsafe remains the only way to splice unvalidated text — see Danger Zones.
Where the check happens.Column, Condition, Join and with_cte() validate at call time. The write-builder name lists (returning(), the on_conflict_* conflict targets) and the string-builder path (SelectQueryBuilder.from_table()'s table_name/schema/alias) are validated at render, so a bad value raises from .render() rather than from the call that supplied it.
:literal is one identifier. It used to accept a dotted name of up to three parts, so t'SELECT * FROM {table:literal}' with table='public.users' rendered. Every part was identifier-checked, so this was never an injection — but it let a single data-derived value choose a schema the query never meant to reach. The qualifier now comes from the code:
# beforetable='public.users'tsql.render(t'SELECT * FROM {table:literal}') # 4.14.0: ValueError# after — the dot belongs to the query, not to the valueschema, table='public', 'users'tsql.render(t'SELECT * FROM {schema:literal}.{table:literal}')
The builder's own column-name arguments (select(), group_by(), order_by(), distinct_on()) still take 'users.id' and 'public.users.id' — a column reference cannot escape the query's scope, because the FROM clause already fixes which tables are reachable. Table names cannot: use schema=.
To mix a builder column into a raw t-string, splice the Column itself — it writes its own dots and needs no format spec:
# before: name_col = str(Users.name); t"{name_col:literal} LIKE {pattern}"query.where(t"{Users.name} LIKE {pattern}")
Breaking changes. A Table subclass whose table_name= or schema= is not a bare identifier now fails at class-definition time rather than at render: table_name='my-table' previously raised from .render(), and now raises at import. A dot is likewise rejected — table_name='public.users' becomes table_name='users', schema='public'.
The column-remapping pattern (Column(column_name='systemvar')) is likewise restricted to identifiers, so a legacy DB column containing a space, a hyphen, a $, or a leading digit can no longer be mapped as a Table field.
Condition(left, ...) now rejects a non-Column left operand, so code that passed a bare string or a Template there must switch to a Column or move the whole predicate into where(t'...').

Write Operations

The query builder supports INSERT, UPDATE, and DELETE with database-agnostic conflict handling.

INSERT

# Basic insertquery=Users.insert(id='abc123', username='john', email='john@example.com')
sql, params=query.render()
# ('INSERT INTO users (id, username, email) VALUES (?, ?, ?)', ['abc123', 'john', 'john@example.com'])# INSERT with RETURNING (Postgres/SQLite)query=Users.insert(id='abc123', username='john', email='john@example.com').returning()
sql, params=query.render()
# ('INSERT INTO users (id, username, email) VALUES (?, ?, ?) RETURNING *', [...])# INSERT with all column defaults — no values provided (Postgres/SQLite)# Emits `DEFAULT VALUES`; useful when every column has a DB/SA default.# Note: MySQL does not support `DEFAULT VALUES` syntax. For MySQL, provide at# least one column or use raw SQL (`INSERT INTO t () VALUES ()`).query=Users.insert().returning('id')
sql, params=query.render()
# ('INSERT INTO users DEFAULT VALUES RETURNING id', [])# INSERT IGNORE (MySQL)query=Users.insert(id='abc123', username='john', email='john@example.com').ignore()
sql, params=query.render()
# ('INSERT IGNORE INTO users (id, username, email) VALUES (?, ?, ?)', [...])# ON CONFLICT DO NOTHING (Postgres/SQLite)query=Users.insert(id='abc123', username='john', email='john@example.com').on_conflict_do_nothing()
# ('INSERT INTO users (...) VALUES (...) ON CONFLICT DO NOTHING', [...])# ON CONFLICT DO NOTHING with specific conflict target (Postgres/SQLite)query=Users.insert(id='abc123', username='john', email='john@example.com').on_conflict_do_nothing(conflict_on='email')
# ('INSERT INTO users (...) VALUES (...) ON CONFLICT (email) DO NOTHING', [...])# ON CONFLICT DO UPDATE (Postgres/SQLite upsert)query=Users.insert(id='abc123', username='john', email='john@example.com').on_conflict_update(conflict_on='id')
# ('INSERT INTO users (...) VALUES (...)# ON CONFLICT (id) DO UPDATE SET username = EXCLUDED.username, email = EXCLUDED.email', [...])# ON CONFLICT with custom updatequery=Users.insert(id='abc123', username='john', email='john@example.com').on_conflict_update(
conflict_on='id',
update={'username': 'updated_name'}
)
# ON DUPLICATE KEY UPDATE (MySQL)query=Users.insert(id='abc123', username='john', email='john@example.com').on_duplicate_key_update()
# ('INSERT INTO users (...) VALUES (...)# ON DUPLICATE KEY UPDATE id = VALUES(id), username = VALUES(username), ...', [...])# Chain multiple modifiersquery= (Users.insert(id='abc123', username='john', email='john@example.com')
.on_conflict_update(conflict_on='id')
.returning('id', 'username'))

UPDATE

# UPDATE requires WHERE clause or explicit .all_rows() for safetyquery=Users.update(email='newemail@example.com')
# ❌ Raises UnsafeQueryError: UPDATE without WHERE requires .all_rows()# UPDATE with WHEREquery=Users.update(email='newemail@example.com').where(Users.id=='abc123')
sql, params=query.render()
# ('UPDATE users SET email = ? WHERE users.id = ?', ['newemail@example.com', 'abc123'])# Multiple WHERE conditionsquery= (Users.update(email='newemail@example.com')
.where(Users.id=='abc123')
.where(Users.age>18))
# Explicitly update all rows (use with caution!)query=Users.update(status='inactive').all_rows()
sql, params=query.render()
# ('UPDATE users SET status = ?', ['inactive'])# With RETURNING (Postgres/SQLite)query= (Users.update(email='new@example.com')
.where(Users.id=='abc123')
.returning())
# ('UPDATE users SET email = ? WHERE users.id = ? RETURNING *', [...])

DELETE

# DELETE requires WHERE clause or explicit .all_rows() for safetyquery=Users.delete()
# ❌ Raises UnsafeQueryError: DELETE without WHERE requires .all_rows()# DELETE with WHEREquery=Users.delete().where(Users.id=='abc123')
sql, params=query.render()
# ('DELETE FROM users WHERE users.id = ?', ['abc123'])# Multiple conditionsquery=Users.delete().where(Users.age<18).where(Users.active==False)
# Explicitly delete all rows (use with extreme caution!)query=Users.delete().all_rows()
sql, params=query.render()
# ('DELETE FROM users', [])# With RETURNING (Postgres/SQLite)query=Users.delete().where(Users.id=='abc123').returning()
# ('DELETE FROM users WHERE users.id = ? RETURNING *', ['abc123'])

Database Compatibility

The query builder is database-agnostic - all methods are available regardless of which database you're using. It's your responsibility to use the appropriate methods for your database:

PostgreSQL:

✅ .returning() - RETURNING clause
✅ .on_conflict_do_nothing() - ON CONFLICT DO NOTHING
✅ .on_conflict_update() - ON CONFLICT DO UPDATE with EXCLUDED.*
❌ .ignore() - Not supported
❌ .on_duplicate_key_update() - Not supported

MySQL:

❌ .returning() - Not supported (MySQL limitation)
✅ .ignore() - INSERT IGNORE
✅ .on_duplicate_key_update() - ON DUPLICATE KEY UPDATE with VALUES()
❌ .on_conflict_do_nothing() - Not supported
❌ .on_conflict_update() - Not supported

SQLite:

✅ .returning() - RETURNING clause (SQLite 3.35+)
✅ .on_conflict_do_nothing() - ON CONFLICT DO NOTHING
✅ .on_conflict_update() - ON CONFLICT DO UPDATE
❌ .ignore() - Not supported
❌ .on_duplicate_key_update() - Not supported

If you use an unsupported method, your database will raise a syntax error when you execute the query.

String-Based Query Builder

t-sql also supports building queries with string table/column names instead of Table class definitions:

fromtsql.query_builderimportSelectQueryBuilder, InsertBuilder, UpdateBuilder, DeleteBuilder# SELECTuser_id=123status='active'query=SelectQueryBuilder.from_table('users', schema='public') \
.select('id', 'name', 'email') \
.where(t'id = {user_id} AND status = {status}') \
.order_by('created_at', direction='DESC') \
.limit(10)
sql, params=query.render()
# INSERTquery=InsertBuilder.into_table('users', {'name': 'Bob', 'email': 'bob@test.com'}) \
.on_conflict_do_nothing('email') \
.returning('id')
# UPDATEcutoff_date='2024-01-01'query=UpdateBuilder.table('users', {'status': 'inactive'}) \
.where(t'last_login < {cutoff_date}')
# DELETEcutoff='2023-01-01'query=DeleteBuilder.from_table('users') \
.where(t'created_at < {cutoff}')

String identifiers are validated using the same :literal format spec as the core library, providing the same SQL injection protection.

Mixing Query Builder with T-Strings

You can combine the query builder with raw t-strings for complex logic:

fromtsql.query_builderimportTable, ColumnclassUsers(Table):
id: Columnname: Columnage: Columnemail: Column# Start with query builderquery=Users.select(Users.id, Users.name, Users.email)
# Add structured conditionquery=query.where(Users.age>18)
# Add complex t-string condition for OR logic# A Column splices straight into a t-string — it writes its own qualified namesearch_term="john"complex_condition=t"{Users.name} LIKE '%' || {search_term} || '%' OR {Users.email} LIKE '%' || {search_term} || '%'"query=query.where(complex_condition)
sql, params=query.render()
# SELECT users.id, users.name, users.email FROM users# WHERE users.age > ? AND (users.name LIKE '%' || ? || '%' OR users.email LIKE '%' || ? || '%')# params: [18, 'john', 'john']

Note: T-string conditions passed to .where() are automatically wrapped in parentheses to ensure proper operator precedence.

SQLAlchemy & Alembic Integration

The query builder can integrate with SQLAlchemy's metadata system for alembic autogenerate:

pip install t-sql[sqlalchemy]
# or
uv add t-sql --optional sqlalchemy

Note: the sqlalchemy extra installs SQLAlchemy only. Alembic is not a t-sql dependency — if you use alembic autogenerate, add alembic to your own project's dependencies (it's your migration tooling, not ours).

Two Ways to Define Columns

1. Simple Column annotations (for query builder only):

fromtsql.query_builderimportTable, ColumnclassUsers(Table):
id: Columnname: Columnage: Column

2. SQLAlchemy with SAColumn wrapper (recommended for type checkers):

fromsqlalchemyimportMetaData, Integer, Stringfromtsql.query_builderimportTable, SAColumnmetadata=MetaData()
classUsers(Table, metadata=metadata):
id=SAColumn(Integer, primary_key=True)
email=SAColumn(String(255), unique=True, nullable=False)
name=SAColumn(String(100))
age=SAColumn(Integer)
# Use for alembictarget_metadata=metadata# Use for queriesquery=Users.select().where(Users.age>18)

The SAColumn wrapper tells type checkers it returns a tsql Column, while at runtime it creates a SQLAlchemy Column. This gives you proper IDE completions for methods like .is_null(), .like(), etc.

Table Constraints

For Alembic migrations, you can define table-level constraints using the constraints attribute:

fromsqlalchemyimportMetaData, String, UniqueConstraint, CheckConstraint, Indexfromtsql.query_builderimportTable, SAColumnmetadata=MetaData()
classClients(Table, table_name='clients', metadata=metadata):
id=SAColumn(String, primary_key=True)
tenant_id=SAColumn(String)
email=SAColumn(String, nullable=False)
# Define table-level constraintsconstraints= [
UniqueConstraint('tenant_id', 'email', name='uq_clients_tenant_email'),
CheckConstraint('length(email) > 0', name='ck_clients_email_not_empty'),
Index('ix_clients_tenant', 'tenant_id')
]

The constraints attribute accepts both lists and tuples, and supports all SQLAlchemy constraint types:

UniqueConstraint - Multi-column unique constraints
CheckConstraint - Table-level check constraints
Index - Multi-column indexes
ForeignKeyConstraint - Table-level foreign keys

Note: Single-column constraints like unique indexes and foreign keys can still be defined directly on SAColumn (e.g., SAColumn(String, unique=True, index=True)).

Table Comments

Add database-level documentation with the comment parameter:

classUsers(Table, metadata=metadata, comment='Application user accounts'):
id=SAColumn(Integer, primary_key=True)
email=SAColumn(String(255), nullable=False)

Table comments appear in database introspection tools and migration files, making your schema self-documenting.

Type Processors

Type processors enable automatic value transformation when reading from and writing to the database, similar to SQLAlchemy's TypeDecorator. This is useful for encryption, serialization, and custom data transformations.

fromtsqlimportTypeProcessorfromtsql.query_builderimportTable, SAColumnfromsqlalchemyimportInteger, String, MetaDataimportjsonmetadata=MetaData()
# Define custom type processorsclassEncryptedString(TypeProcessor):
def__init__(self, key):
self.key=keydefprocess_bind_param(self, value):
"""Transform Python value -> DB value (encrypt on write)"""ifvalueisNone:
returnNonereturnencrypt(value, self.key)
defprocess_result_value(self, value):
"""Transform DB value -> Python value (decrypt on read)"""ifvalueisNone:
returnNonereturndecrypt(value, self.key)
classJSONType(TypeProcessor):
defprocess_bind_param(self, value):
"""Serialize Python dict/list -> JSON string"""returnjson.dumps(value) ifvalueisnotNoneelseNonedefprocess_result_value(self, value):
"""Deserialize JSON string -> Python dict/list"""returnjson.loads(value) ifvalueisnotNoneelseNone# Use type processors in table definitionclassUser(Table, metadata=metadata):
id=SAColumn(Integer, primary_key=True)
ssn=SAColumn(String(255), type_processor=EncryptedString(key="secret"))
metadata_=SAColumn(String, type_processor=JSONType())
email=SAColumn(String(255)) # No processor = no transformation# Write - automatic encryption/serializationUser.insert(ssn="123-45-6789", metadata_={"role": "admin"})
# SQL: INSERT INTO user (ssn, metadata_) VALUES (?, ?)# Params: [encrypt("123-45-6789", "secret"), '{"role": "admin"}']User.update(ssn="new-ssn").where(User.id==1)
# SQL: UPDATE user SET ssn = ? WHERE user.id = ?# Params: [encrypt("new-ssn", "secret"), 1]# Where clauses - automatic transformationUser.select().where(User.ssn=="123-45-6789")
# SQL: SELECT * FROM user WHERE user.ssn = ?# Params: [encrypt("123-45-6789", "secret")]# Read - manual decryption/deserialization with map_results()query=User.select().where(User.id==1)
sql, params=query.render()
rows=awaitconnection.fetch(sql, *params) # Returns encrypted/serialized datatransformed_rows=query.map_results(rows) # Applies type processors# transformed_rows = [{"id": 1, "ssn": "123-45-6789", "metadata_": {"role": "admin"}, ...}]

Key features:

Write-side: Automatically applied in INSERT, UPDATE, and WHERE clauses
Read-side: Manual via query.map_results(rows) - you control when transformation happens
NULL handling: NULL values are passed through to processors (they decide how to handle)
Column comparisons: Type processors are NOT applied when comparing columns to other columns

Why manual read-side transformation? The query builder stays database-agnostic and doesn't execute queries directly. You control when to apply transformations after fetching results from your specific database driver.

Schema Support

classUsers(Table, schema='public'):
id: Columnname: Column

Or with custom table name and schema:

classUsers(Table, table_name='user_accounts', schema='public'):
id: Columnname: Column

Rendering Queries

All query types (t-strings, TSQL objects, and QueryBuilder objects) can be rendered using tsql.render():

importtsqlfromtsql.query_builderimportTable, ColumnclassUsers(Table):
id: Columnname: Column# All of these work with tsql.render():sql, params=tsql.render(t"SELECT * FROM users WHERE id = {user_id}")
sql, params=tsql.render(Users.select().where(Users.id==user_id))
sql, params=tsql.render(tsql.select('users', user_id))
# Or call .render() directly on TSQL/QueryBuilder objects:query=Users.select().where(Users.age>18)
sql, params=query.render()

Type Safety & Preventing SQL Injection

This library should ideally be used in middleware or library code to enforce safe query construction. Use the TSQLQuery type to prevent raw strings:

fromtsqlimportTSQLQuery, renderdefexecute_sql_query(query: TSQLQuery):
"""Only accepts safe, parameterized queries"""sql, params=render(query)
returnsql_engine.execute(sql, params)
# Type checker allows these:execute_sql_query(t"SELECT * FROM users WHERE id = {user_id}") # ✓execute_sql_query(Users.select()) # ✓execute_sql_query(tsql.select('users')) # ✓# Type checker rejects raw strings:execute_sql_query("SELECT * FROM users") # ✗ Type error!

The TSQLQuery type is a union of TSQL, Template (t-strings), and QueryBuilder, ensuring all queries are safe from SQL injection.

Security Considerations

Overview

SQL injection is one of the most critical web application security risks (OWASP Top 10). This library is designed from the ground up to prevent SQL injection attacks through multiple layers of protection. However, understanding how these protections work—and where they can be bypassed—is essential for secure usage.

How t-sql Prevents SQL Injection

1. Automatic Parameterization (Primary Defense)

By default, all interpolated values in t-strings are converted to parameterized queries:

# User input (potentially malicious)user_input="admin' OR 1=1 --"# t-sql automatically parameterizes thissql, params=tsql.render(t"SELECT * FROM users WHERE name = {user_input}")
# Result: ('SELECT * FROM users WHERE name = ?', ["admin' OR 1=1 --"])

The malicious SQL becomes literal string data in the parameter, not executable SQL code. The database treats it as a string value to match, not as SQL syntax.

Attack vectors prevented:

Classic injection: ' OR 1=1 --
Union-based: ' UNION SELECT * FROM secrets --
Stacked queries: '; DROP TABLE users; --
Boolean-based blind: ' AND SLEEP(5) --
Authentication bypass: admin'--

2. Literal Validation (Identifier Safety)

For table and column names that cannot be parameterized, use :literal:

table="users"col="name"sql, params=tsql.render(t"SELECT * FROM {table:literal} WHERE {col:literal} = {value}")

Validation rules:

Must be a single valid Python identifier (str.isidentifier())
Rejects anything with spaces, quotes, dots, or special characters

A qualified name is written with the dot in the t-string, one :literal per part, so the qualifier comes from the query rather than from a value that may be data-derived:

sql, params=tsql.render(t"SELECT * FROM {schema:literal}.{table:literal}")

# These are REJECTED with ValueError:bad_table="users; DROP TABLE secrets"# Contains semicolonbad_col="name' OR 1=1"# Contains quotebad_schema="public.users"# Qualified — write the dot in the t-stringtsql.render(t"SELECT * FROM {bad_table:literal}") # Raises ValueError

Attack vectors prevented:

Table/column injection: users; DROP TABLE secrets
Second-order injection via identifiers
Schema manipulation

3. Escape-based Protection (ESCAPED Style)

For databases or scenarios where parameterization isn't available, the ESCAPED style properly escapes values:

malicious="'; DROP TABLE users; --"sql, _=tsql.render(t"SELECT * FROM users WHERE name = {malicious}", style=tsql.styles.ESCAPED)
# Result: "SELECT * FROM users WHERE name = '''; DROP TABLE users; --'"# (single quotes are doubled, making it literal data)

Important: While effective, parameterization is always preferred when available. Use ESCAPED only when necessary.

4. Query Builder Safety: UPDATE/DELETE Protection

The query builder prevents accidental mass UPDATE/DELETE operations by requiring an explicit WHERE clause or .all_rows() call:

fromtsqlimportUnsafeQueryError# This raises UnsafeQueryError at render timeUsers.update(status='inactive').render() # ❌ Error!Users.delete().render() # ❌ Error!# Must add WHERE clauseUsers.update(status='inactive').where(Users.id==user_id).render() # ✅# Or explicitly confirm mass operationUsers.update(status='inactive').all_rows().render() # ✅Users.delete().all_rows().render() # ✅

This protection catches the most common and dangerous SQL mistake: forgetting the WHERE clause.

Danger Zones: Where You Can Still Get Hurt

The :unsafe Format Spec

The :unsafe format spec bypasses all safety mechanisms:

# DANGEROUS - no validation or parameterization!dynamic_sql="age > 18 OR role = 'admin'"# If this comes from user input, you're vulnerablesql, params=tsql.render(t"SELECT * FROM users WHERE {dynamic_sql:unsafe}")

When :unsafe is acceptable:

Hard-coded SQL fragments in your own code
SQL generated by trusted, validated builder logic
Dynamic ORDER BY clauses (after validation)

When :unsafe is DANGEROUS:

Never with user input (even "validated" input)
Dynamic WHERE clauses from external sources
Any data from forms, APIs, or databases

Recommendation: Treat :unsafe like eval() in your code reviews. Every usage should be scrutinized and documented.

Name		Name	Last commit message	Last commit date
.github/workflows		.github/workflows
tests		tests
tsql		tsql
.dockerignore		.dockerignore
.gitignore		.gitignore
Dockerfile		Dockerfile
LICENSE		LICENSE
README.md		README.md
compose.yaml		compose.yaml
context7.json		context7.json
pyproject.toml		pyproject.toml
pytest.ini		pytest.ini

Latest commit

History

Folders and files

Repository files navigation