Changelog¶

v0.46.2 - Framework Filter orderBy Aliases (Unreleased)¶

Changed (breaking default):

• sqlspec.utils.serializers.schema_dump (and its helpers serialize_collection / get_collection_serializer) now default wire_format=False. Calling schema_dump(struct) on a msgspec.Struct declared with rename= now returns Python attribute names by default (matching Pydantic, dataclass, and attrs output) instead of wire-aligned field.encode_name keys. Pass wire_format=True explicitly to restore the prior wire-aligned output for JSON / API payloads. This default flip closes the silent footgun where sql.update(t).set(**schema_dump(struct)) emitted camelCase column names for snake_case database tables. The kwarg remains a no-op for non-msgspec inputs.

Added:

• Insert.values_from(data, *, exclude_unset=True), Insert.values_from_many(items, *, exclude_unset=True), and Update.set_from(data, *, exclude_unset=True) builder methods. Each accepts any schema kind (dict, dataclass, msgspec.Struct, pydantic.BaseModel, attrs class) and dispatches through schema_dump(wire_format=False) so SQL column names are always Python attribute names regardless of any wire-rename meta on the source schema.

Deprecated:

• Insert.values_from_dict and Insert.values_from_dicts now emit a DeprecationWarning pointing at Insert.values_from / Insert.values_from_many respectively. The dict-shaped methods continue to work; they are scheduled for removal in 0.48.0.

Fixed:

• AsyncPG and CockroachDB AsyncPG connections now register binary json and jsonb codecs by default. load_from_arrow() can bulk load Arrow tables into PostgreSQL JSON / JSONB columns without asyncpg rejecting binary jsonb payloads. (#452)

• Missing named SQL statements now raise SQLStatementNotFoundError, a SQLFileNotFoundError subclass with structured lookup context and bounded messages that report the loaded statement count instead of dumping available statement names. (#437)

• Litestar create_filter_dependencies() and FastAPI provide_filters() now accept camel-case API-facing sort aliases for configured orderBy fields by default. Endpoints can accept values such as orderBy=uploadedCollections while producing an OrderByFilter for the SQL-facing field uploaded_collections. Raw configured values remain accepted for compatibility, explicit aliases can still be added with sort_field_aliases, and sort_field_camelize=False restores snake_case-only validation. (#438)

Safety:

• Alias normalization is closed over the configured sort_field allowlist. Unknown aliases and aliases targeting fields outside sort_field are rejected before SQL construction, preserving the existing identifier allowlist.

Performance:

• Performance builds now compile SQLSpec's custom sqlglot dialect helper modules alongside sqlglot[c]: generator transforms, operator registries, and compatibility helpers. Selected extension helper modules are also compiled: shared filter alias resolution, event payload codecs/runtime hints, and ADK record type modules. SQLGlot subclass/registration modules, dynamic framework provider modules, and adapter-local data-dictionary classes remain interpreted by design. Event dataclass and table-queue modules also stay interpreted to preserve runtime slot/class-attribute behavior. The performance extra now includes librt for measured string-assembly wins in the compiled splitter and psqlpy copy-encoding hot paths.

v0.46.1 - Litestar Filter Provider Binding Fix¶

Fixed:

• Litestar generated filter providers now use unique dependency parameter names for sibling IN, NOT IN, null, not-null, and range filters. This stops sibling providers in the same filter family from cross-binding values while preserving the existing query parameter aliases. (#435, #436)

v0.46.0 - schema_dump Wire-Format Opt-Out¶

Added:

• sqlspec.utils.serializers.schema_dump (and its helpers serialize_collection / get_collection_serializer) now accept a wire_format: bool = True keyword. The default preserves existing output: msgspec.Struct instances continue to emit wire-aligned names (honouring rename= via field.encode_name); Pydantic, dataclass, and attrs branches continue to emit Python attribute names. Pass wire_format=False to opt the msgspec branch into Python attribute names (field.name) for cross-library consistency. The kwarg is a no-op for non-msgspec inputs.

• The internal serializer cache key now includes wire_format so that True and False calls for the same Struct type cannot collide.

v0.44.0 - Schema Wire Correctness¶

Fixed:

• sqlspec.utils.serializers.schema_dump now honors msgspec.Struct rename= meta. Structs declared with rename="camel" / "kebab" / "pascal" / callable now emit the renamed wire names instead of the Python attribute names. (#418)

• sqlspec.core.filters.OffsetPagination is now a stdlib dataclass(), living in sqlspec.core._pagination (excluded from mypyc because the @dataclass decorator mutates the class at definition time, and mypyc-compiled classes forbid that). The public import path from sqlspec.core.filters import OffsetPagination is unchanged. This restores runtime __annotations__ under mypyc-compiled wheels, fixing empty OpenAPI response schemas and missing component types when Litestar handlers return OffsetPagination[T]. The Litestar extension additionally registers an OpenAPISchemaPlugin as a defensive fallback. msgspec is no longer required to import the pagination container. (#419)

Behavior changes from the OffsetPagination conversion:

• __eq__ is now field-wise (was identity). Two pagination objects with identical contents now compare equal.

• __hash__ is now None (dataclass default without frozen=True). Instances can no longer be used as dict keys or set members. Sequence[T]-valued items already made this impractical, but the change is noted for completeness.

• __repr__ now prints OffsetPagination(items=..., limit=..., offset=..., total=...).

Logging Improvements¶

Cache Namespace Context:

Cache debug logs now include a cache_namespace field that identifies which cache type (statement, expression, builder, file, optimized) generated the log. This makes cache performance debugging significantly easier.

Example:

# Before
cache.miss extra_fields={'cache_size': 0}

# After
cache.miss extra_fields={'cache_namespace': 'statement', 'cache_size': 0}

SQL Logger Namespace (BREAKING CHANGE):

SQL execution logs now use a dedicated sqlspec.sql logger (previously used sqlspec.observability). This allows independent configuration of SQL log levels from other operational logs.

Migration:

# Before
logging.getLogger("sqlspec.observability").setLevel(logging.INFO)

# After
logging.getLogger("sqlspec.sql").setLevel(logging.INFO)

SQL Log Message Format (BREAKING CHANGE):

SQL execution log messages now use the operation type (SELECT, INSERT, etc.) as the message instead of the generic "db.query".

Example:

# Before
db.query driver=AsyncpgDriver duration_ms=3.5 rows=5 sql='SELECT ...'

# After
SELECT  driver=AsyncpgDriver bind_key=primary duration_ms=3.5 rows=5 sql='SELECT ...'

If you have log parsers matching "db.query", update them to match operation types.

See: Observability for the full logger hierarchy and configuration examples.

ADK Memory Store¶

• Added SQLSpecMemoryService and SQLSpecSyncMemoryService for SQLSpec-backed ADK memory storage.

• Implemented adapter-specific memory stores with optional full-text search (memory_use_fts) and simple fallback search.

• Extended ADK migrations to include memory tables with configurable include_memory_migration toggles.

• Added CLI commands for memory cleanup and verification (sqlspec adk memory cleanup/verify).

Driver Layer Compilation¶

• Compiled driver base classes and mixins with mypyc to reduce dispatch overhead in the execution pipeline.

• Replaced dynamic getattr patterns with protocol-driven access for mypyc compatibility.

• Added driver protocols and updated mypyc build configuration to include driver modules.

Database Event Channels¶

• Added sqlspec.extensions.events.EventChannel with queue-backed publish/listen APIs that work uniformly across sync and async adapters.

• Exposed SQLSpec.event_channel(config) so applications and agents can build channels directly from registered configs.

• Introduced the events extension migrations (ext_events_0001) which create the durable queue table plus composite index.

• Added the first native backend (AsyncPG LISTEN/NOTIFY) enabled via driver_features["events_backend"] = "listen_notify"; the API automatically falls back to the queue backend for other adapters.

• Introduced experimental Oracle Advanced Queuing support (sync adapters) via driver_features["events_backend"] = "advanced_queue" with automatic fallback when AQ is unavailable.

• Documented configuration patterns (queue table naming, lease/retention windows, Oracle INMEMORY toggle, Postgres native mode) for database event channels.

• Event telemetry now tracks events.publish, events.publish.native, events.deliver, events.ack, events.nack, events.shutdown and listener lifecycle, so Prometheus/Otel exporters see event workloads alongside query metrics.

• Added adapter-specific runtime hints (asyncmy, duckdb, bigquery/adbc) plus a poll_interval extension option so operators can tune leases and cadence per database.

• Publishing, dequeue, ack, nack, and shutdown operations now emit sqlspec.events.* spans whenever extension_config["otel"] is enabled, giving full trace coverage without extra plumbing.

• Documented adapter-specific guidance (asyncpg, psycopg, psqlpy, asyncmy, duckdb, oracle) and added a DuckDB integration test to cover the queue fallback path.

v0.33.0 - Configuration Parameter Standardization (BREAKING CHANGE)¶

Breaking Change: All adapter configuration parameter names have been standardized for consistency across the entire library.

What Changed:

All database adapter configurations now use consistent parameter names:

• pool_config → connection_config (configuration dictionary)

• pool_instance → connection_instance (pre-created pool/connection instance)

This affects all 11 database adapters: AsyncPG, Psycopg, Asyncmy, Psqlpy, OracleDB, SQLite, AioSQLite, DuckDB, BigQuery, ADBC, and Spanner.

Migration:

Simple search and replace in your codebase:

# Replace pool_config with connection_config
find . -name "*.py" -exec sed -i 's/pool_config=/connection_config=/g' {} +

# Replace pool_instance with connection_instance
find . -name "*.py" -exec sed -i 's/pool_instance=/connection_instance=/g' {} +

Before:

config = AsyncpgConfig(
    pool_config={"dsn": "postgresql://localhost/db"},
    pool_instance=my_pool
)

After:

config = AsyncpgConfig(
    connection_config={"dsn": "postgresql://localhost/db"},
    connection_instance=my_pool
)

Why This Change:

• Eliminates inconsistency between pooled and non-pooled adapters

• More intuitive naming (connection_instance works semantically for both pools and single connections)

• Reduces cognitive load when switching between adapters

• Clearer API for new users

See the connection configuration section in Configuration for detailed migration guidance with before/after examples for all adapters.

Query Stack Documentation Suite¶

• Expanded the Query Stack API reference (StatementStack, StackResult, driver hooks, and StackExecutionError) with the high-level workflow, execution modes, telemetry, and troubleshooting tips.

• Captured the detailed architecture and performance guidance inside the internal specs workspace for future agent runs.

• Updated every adapter reference with a Query Stack Support section so behavior is documented per database.

Migration Convenience Methods on Config Classes¶

Added migration methods directly to database configuration classes, eliminating the need to instantiate separate command objects.

What's New:

All database configs (both sync and async) now provide migration methods:

• migrate_up() / upgrade() - Apply migrations up to a revision

• migrate_down() / downgrade() - Rollback migrations

• get_current_migration() - Check current version

• create_migration() - Create new migration file

• init_migrations() - Initialize migrations directory

• stamp_migration() - Stamp database to specific revision

• fix_migrations() - Convert timestamp to sequential migrations

Before (verbose):

from sqlspec.adapters.asyncpg import AsyncpgConfig
from sqlspec.migrations.commands import AsyncMigrationCommands

config = AsyncpgConfig(
    connection_config={"dsn": "postgresql://..."},
    migration_config={"script_location": "migrations"}
)

commands = AsyncMigrationCommands(config)
await commands.upgrade("head")

After (recommended):

from sqlspec.adapters.asyncpg import AsyncpgConfig

config = AsyncpgConfig(
    connection_config={"dsn": "postgresql://..."},
    migration_config={"script_location": "migrations"}
)

await config.upgrade("head")

Key Benefits:

• Simpler API - no need to import and instantiate command classes

• Works with both sync and async adapters

• Full backward compatibility - command classes still available

• Cleaner test fixtures and deployment scripts

Async Adapters (AsyncPG, Asyncmy, Aiosqlite, Psqlpy):

await config.migrate_up("head")
await config.create_migration("add users")

Sync Adapters (SQLite, DuckDB):

config.migrate_up("head")  # No await needed
config.create_migration("add users")

SQL Loader Graceful Error Handling¶

Breaking Change: Files without named statements (-- name:) are now gracefully skipped instead of raising SQLFileParseError.

This allows loading directories containing named SQL queries and raw DDL/DML scripts without errors.

What Changed:

• Files without -- name: markers return empty dict instead of raising exception

• Directory loading continues when encountering such files

• Skipped files are logged at DEBUG level

• Malformed named statements (duplicate names, etc.) still raise exceptions

Migration Guide:

Code explicitly catching SQLFileParseError for files without named statements will need updating:

# OLD (breaks):
try:
    loader.load_sql("directory/")
except SQLFileParseError as e:
    if "No named SQL statements found" in str(e):
        pass

# NEW (recommended):
loader.load_sql("directory/")  # Just works - DDL files skipped
if not loader.list_queries():
    # No queries loaded
    pass

Example Use Case:

# Directory structure:
# migrations/
# ├── schema.sql              # Raw DDL (no -- name:) → SKIP
# ├── queries.sql             # Named queries → LOAD
# └── seed-data.sql          # Raw DML (no -- name:) → SKIP

loader = SQLFileLoader()
loader.load_sql("migrations/")  # Loads only named queries, skips DDL

Hybrid Versioning with Fix Command¶

Added comprehensive hybrid versioning support for database migrations:

• Fix Command - Convert timestamp migrations to sequential format

• Hybrid Workflow - Use timestamps in development, sequential in production

• Automatic Conversion - CI integration for seamless workflow

• Safety Features - Automatic backup, rollback on errors, dry-run preview

Key Features:

• Zero merge conflicts: Developers use timestamps (20251011120000) during development

• Deterministic ordering: Production uses sequential format (0001, 0002, etc.)

• Database synchronization: Automatically updates version tracking table

• File operations: Renames files and updates SQL query names

• CI-ready: --yes flag for automated workflows

# Preview changes
sqlspec --config myapp.config fix --dry-run

# Apply conversion
sqlspec --config myapp.config fix

# CI/CD mode
sqlspec --config myapp.config fix --yes --no-database

Example conversion:

Before:                              After:
migrations/                          migrations/
├── 0001_initial.sql                ├── 0001_initial.sql
├── 0002_add_users.sql              ├── 0002_add_users.sql
├── 20251011120000_products.sql →   ├── 0003_add_products.sql
└── 20251012130000_orders.sql   →   └── 0004_add_orders.sql

Documentation:

• Complete CLI reference: Command Line Interface

• Workflow guide for hybrid versioning

• CI integration examples for GitHub Actions and GitLab CI

Shell Completion Support¶

Added comprehensive shell completion support for the SQLSpec CLI:

• Bash, Zsh, and Fish support - Tab completion for commands and options

• Easy setup - One-time eval command in your shell rc file

• Comprehensive documentation - Setup instructions in Command Line Interface

# Bash - add to ~/.bashrc
eval "$(_SQLSPEC_COMPLETE=bash_source sqlspec)"

# Zsh - add to ~/.zshrc
eval "$(_SQLSPEC_COMPLETE=zsh_source sqlspec)"

# Fish - add to ~/.config/fish/completions/sqlspec.fish
eval (env _SQLSPEC_COMPLETE=fish_source sqlspec)

After setup, tab completion works for all commands and options:

sqlspec <TAB>              # Shows: create-migration, downgrade, init, ...
sqlspec create-migration --<TAB>  # Shows: --bind-key, --help, --message, ...

Extension Migration Configuration¶

Extension migrations now receive automatic version prefixes and configuration has been simplified:

1. Version Prefixing (Automatic)

   Extension migrations are automatically prefixed to prevent version collisions:

   # User migrations
   0001_initial.py       → version: 0001
   
   # Extension migrations (automatic prefix)
   0001_create_tables.py → version: ext_adk_0001
   0001_create_session.py → version: ext_litestar_0001
   

2. Configuration Format (Important)

   Extension settings must be in extension_config only:

   # Incorrect format
   migration_config={
       "include_extensions": [
           {"name": "adk", "session_table": "custom"}
       ]
   }
   
   # Correct format
   extension_config={
       "adk": {"session_table": "custom"}
   },
   migration_config={
       "include_extensions": ["adk"]  # Simple string list
   }
   

Configuration Guide: See Migrations for extension configuration details.

Features¶

• Extension migrations now automatically prefixed (ext_adk_0001, ext_litestar_0001)

• Eliminated version collision between extension and user migrations

• Simplified extension configuration API

• Single source of truth for extension settings (extension_config)

Bug Fixes¶

• Fixed version collision when extension and user migrations had the same version number

• Fixed duplicate key violation in ddl_migrations table when using extensions

• Improved migration tracking with clear extension identification

Technical Changes¶

• _load_migration_metadata() now accepts optional version parameter

• _parse_extension_configs() rewritten to read from extension_config only

• Extension migration version prefixing handled in _get_migration_files_sync()

• Removed dict format support from include_extensions