Added

Support INSERT with all default values

Table.insert() with no values now emits INSERT INTO t DEFAULT VALUES, useful when every column has a database or SQLAlchemy default. Combines with .returning() and .on_conflict_do_nothing() on Postgres/SQLite.

MyTable.insert().returning('id').render()
# ('INSERT INTO mytable DEFAULT VALUES RETURNING id', [])

MySQL note: DEFAULT VALUES is not valid MySQL syntax. Combining empty values with .on_duplicate_key_update() raises ValueError.

See commit 8148264 for full details.

Added

Bare Boolean Column Support in WHERE Clauses

You can now use boolean columns directly in .where() without a comparison operator.

# Before (still works):
Contacts.select().where(Contacts.is_primary == True)

# Now also works:
Contacts.select().where(Contacts.is_primary)
# Produces: SELECT * FROM contacts WHERE contacts.is_primary

Works with SELECT, UPDATE, and DELETE query builders.

See commit 4a46072 for full details.

Changed

Pass Through All Types by Default

All interpolated values now pass through as native Python objects by default, allowing database drivers with custom codecs to handle any type natively.

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

# With a custom asyncpg codec, this just works
p = Point(3, 4)
query, params = tsql.render(t"INSERT INTO coords VALUES ({p})")
# params[0] is the Point object, not a string

Format specs still stringify as before - {dt:%Y-%m-%d} will format the datetime to a string.

Fixed

Float Values in ESCAPED Style

Float values are now correctly rendered as numeric (19.99) instead of being quoted as strings ('19.99').

See commit f82e330 for full details.

Fixed

Dict/List/Set Type Preservation

Fixed a bug where dict, list, and set values were stringified to Python repr strings (e.g., "{'name': 'billy'}") instead of being passed through as native Python objects for database drivers to handle.

Now these collection types are preserved, enabling proper JSONB and array handling with database drivers like asyncpg. For JSONB columns, set up a type codec:

await conn.set_type_codec('jsonb', encoder=json.dumps, decoder=json.loads, schema='pg_catalog')

# Now dicts work directly
my_dict = {'name': 'billy', 'tags': ['admin']}
query, params = tsql.render(t"INSERT INTO users (data) VALUES ({my_dict})")
# params[0] is the actual dict, not a string

See commit 3ede68b for full details.

Fixed

SelectQueryBuilder.select() Now Appends Columns

Fixed a bug where calling .select() multiple times would replace the column list instead of appending to it. This was inconsistent with other builder methods like .where() which append conditions.

Migration:

# Before (broken):
query = Users.select(Users.id)
query.select(Users.username, Users.email)
# Result: Only username and email selected (id was lost)

# After (fixed):
query = Users.select(Users.id)
query.select(Users.username, Users.email)
# Result: All three columns selected (id, username, email)

Calling .select() with no arguments still resets to SELECT * as before.

See commit 10f7614 for full details.

Added

CTE (Common Table Expression) Support

Added comprehensive CTE support to SelectQueryBuilder with the new .with_cte() method.

Features:

• Basic CTEs: Chain .with_cte() to add named subqueries
• Multiple CTEs: Chain multiple .with_cte() calls
• Recursive CTEs: Use recursive=True flag for recursive queries
• Flexible query types: Accepts SelectQueryBuilder, Template (t-strings), or TSQL objects
• Auto RECURSIVE keyword: Automatically adds WITH RECURSIVE if any CTE is recursive
• Security: CTE names validated with str.isidentifier() and rendered with :literal

Example:

# Basic CTE
query = (
    SelectQueryBuilder.from_table('active_users')
    .with_cte('active_users', Users.select().where(Users.active == True))
    .select('id', 'name')
)

# Recursive CTE
query = (
    SelectQueryBuilder.from_table('tree')
    .with_cte('tree', t'''
        SELECT id, parent_id FROM categories WHERE parent_id IS NULL
        UNION ALL
        SELECT c.id, c.parent_id FROM categories c
        JOIN tree t ON c.parent_id = t.id
    ''', recursive=True)
)

See commit 47a483b for full details.

Added

LIKE Pattern Format Specs with Automatic Wildcard Escaping

Added new format specs for safe SQL LIKE pattern matching with automatic escaping:

• :like% - prefix search (value%)
• :%like - suffix search (%value)
• :%like% - contains search (%value%)

All wildcards (%, _, \) in user input are automatically escaped to prevent injection attacks.

Example:

search = "john"
sql, params = tsql.render(t"SELECT * FROM users WHERE name LIKE {search:%like%}")
# ('SELECT * FROM users WHERE name LIKE ? ESCAPE '\\'', ['%john%'])

See the updated README for full documentation and security examples.

See commit 91820ba for full details.

Bug Fix

Fix whitespace collapsing breaking -- style SQL comments

The regex r'\s+' collapsed all whitespace including newlines, which broke -- style SQL comments by putting everything on one line. PostgreSQL (and other databases) interpret everything after -- as a comment until end of line, so queries like:

query = t"""
SELECT * FROM users
-- Filter by active status
WHERE active = true
"""

Would silently drop the WHERE clause entirely.

Fix

Changed to r'[ \t]+' to only collapse horizontal whitespace (spaces/tabs) while preserving newlines.

New Feature: Table indexes Attribute

This release adds support for defining indexes directly in Table class definitions using an indexes class attribute, mirroring the existing constraints pattern.

What's New

• indexes class attribute for Table definitions
• Supports both list and tuple formats (consistent with constraints)
• Full support for database-specific index types (GIN, GiST, BRIN, etc.)
• PostgreSQL-specific options like postgresql_using and postgresql_ops
• Proper Alembic autogenerate detection (indexes appear as separate add_index operations)

Example Usage

from sqlalchemy import MetaData, Index
from tsql.query_builder import Table
from sqlalchemy import Column, String

metadata = MetaData()

class users(Table, metadata=metadata):
    id = Column(String, primary_key=True)
    email = Column(String)
    first_name = Column(String)
    
    indexes = [
        Index('idx_users_email_gin', 'email',
              postgresql_using='gin',
              postgresql_ops={'email': 'gin_trgm_ops'}),
        Index('idx_users_name_gin', 'first_name',
              postgresql_using='gin',
              postgresql_ops={'first_name': 'gin_trgm_ops'}),
    ]

Benefits

• Colocation: All table metadata (columns, constraints, indexes) in one place
• Migration safety: Indexes survive migration squashing
• Consistency: Matches existing constraints pattern
• Readability: Clear table structure at a glance
• Alembic compatible: Works with alembic revision --autogenerate

This is particularly useful for full-text search indexes, partial indexes with WHERE clauses, multi-column indexes, and database-specific index types.

What Changed

• Updated query_builder.py to extract and pass indexes to SQLAlchemy Table constructor
• Added comprehensive test coverage (SQLAlchemy integration and Alembic autogenerate)
• Updated documentation

Full Changelog: v4.6.0...v4.7.0

New Features

String-Based Query Builder API

Added support for building queries with string table/column names instead of requiring Table class definitions. Perfect for dynamic schemas and runtime-determined table structures.

New APIs:

• SelectQueryBuilder.from_table(table_name, schema=None)
• InsertBuilder.into_table(table_name, values, schema=None)
• UpdateBuilder.table(table_name, values, schema=None)
• DeleteBuilder.from_table(table_name, schema=None)

Example:
```python
from tsql.query_builder import SelectQueryBuilder

Build query with string table/column names

user_id = 123
query = SelectQueryBuilder.from_table('users') \
.select('id', 'name', 'email') \
.where(t'id = {user_id}') \
.order_by('created_at', direction='DESC')

sql, params = query.render()
```

Key Points:

• WHERE clauses use t-strings for safety and flexibility
• String identifiers validated via :literal format spec (same security as Table-based approach)
• All query builder features supported (JOINs, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET, etc.)
• Comprehensive test coverage included

See README for full documentation.