Queries

DML query builders for SELECT, INSERT, UPDATE, DELETE, and MERGE operations.

Select

class sqlspec.builder.Select

Bases: QueryBuilder, WhereClauseMixin, OrderByClauseMixin, LimitOffsetClauseMixin, SelectClauseMixin, JoinClauseMixin, HavingClauseMixin, SetOperationMixin, CommonTableExpressionMixin, PivotClauseMixin, UnpivotClauseMixin, ExplainMixin

Builder for SELECT queries.

Provides a fluent interface for constructing SQL SELECT statements with parameter binding and validation.

__init__(*columns, **kwargs)

Initialize SELECT with optional columns.

Parameters:

• *columns (str) -- Column names to select

• **kwargs (Any) -- Additional QueryBuilder arguments (dialect, schema, etc.)

with_hint(hint, *, location='statement', table=None, dialect=None)

Attach an optimizer or dialect-specific hint to the query.

Parameters:

• hint (str) -- The raw hint string.

• location (str) -- Where to apply the hint ('statement', 'table').

• table (str | None) -- Table name if the hint is for a specific table.

• dialect (str | None) -- Restrict the hint to a specific dialect (optional).

Return type:

Self

Returns:

The current builder instance for method chaining.

build(dialect=None)

Builds the SQL query string and parameters with hint injection.

Parameters:

dialect (Union[str, Dialect, type[Dialect], None]) -- Optional dialect override for SQL generation.

Returns:

A dataclass containing the SQL string and parameters.

Return type:

BuiltQuery

for_update(*, skip_locked=False, nowait=False, of=None)

Add FOR UPDATE clause to SELECT statement for row-level locking.

Parameters:

• skip_locked (bool) -- Skip rows that are already locked (SKIP LOCKED)

• nowait (bool) -- Return immediately if row is locked (NOWAIT)

• of (str | list[str] | None) -- Table names/aliases to lock (FOR UPDATE OF table)

Return type:

Self

Returns:

Self for method chaining

for_share(*, skip_locked=False, nowait=False, of=None)

Add FOR SHARE clause for shared row-level locking.

Parameters:

• skip_locked (bool) -- Skip rows that are already locked (SKIP LOCKED)

• nowait (bool) -- Return immediately if row is locked (NOWAIT)

• of (str | list[str] | None) -- Table names/aliases to lock (FOR SHARE OF table)

Return type:

Self

Returns:

Self for method chaining

for_key_share()

Add FOR KEY SHARE clause (PostgreSQL-specific).

FOR KEY SHARE is like FOR SHARE, but the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE.

Return type:

Self

Returns:

Self for method chaining

for_no_key_update()

Add FOR NO KEY UPDATE clause (PostgreSQL-specific).

FOR NO KEY UPDATE is like FOR UPDATE, but the lock is weaker: it does not block SELECT FOR KEY SHARE commands that attempt to acquire a share lock on the same rows.

Return type:

Self

Returns:

Self for method chaining

Select Clause Mixins

class sqlspec.builder.SelectClauseMixin

Bases: object

Mixin providing SELECT clause methods.

select_only(*columns)

Replace currently selected columns with new ones.

Return type:

Self

from_(table, alias=None, as_of=None, as_of_type=None)

Set the FROM clause and optionally attach temporal versioning.

as_of copies the resolved table expression, normalizes aliases, and adds an exp.Version so sqlglot's generator emits dialect-specific time-travel SQL.

Return type:

Self

class sqlspec.builder.WhereClauseMixin

Bases: object

class sqlspec.builder.OrderByClauseMixin

Bases: object

class sqlspec.builder.LimitOffsetClauseMixin

Bases: object

class sqlspec.builder.HavingClauseMixin

Bases: object

class sqlspec.builder.ReturningClauseMixin

Bases: object

class sqlspec.builder.CommonTableExpressionMixin

Bases: object

with_(name, query, recursive=False, columns=None)

Add a CTE via the WITH clause.

When query is another builder we reuse its expression, merge parameters with unique names, and let sqlglot handle the actual CTE wrapping to avoid duplicating _with_ctes state.

Return type:

Self

class sqlspec.builder.SetOperationMixin

Bases: object

class sqlspec.builder.PivotClauseMixin

Bases: object

class sqlspec.builder.UnpivotClauseMixin

Bases: object

Window Functions

class sqlspec.builder.WindowFunctionBuilder

Bases: object

Helper to fluently construct window function expressions.

__init__(function_name, *function_args)

Case Expressions

class sqlspec.builder.Case

Bases: object

Represent a SQL CASE expression with structured components.

__init__(*ifs, default=None)

class sqlspec.builder.CaseBuilder

Bases: object

Fluent builder for CASE expressions used within SELECT clauses.

Subqueries

class sqlspec.builder.SubqueryBuilder

Bases: object

Helper to build subquery expressions for EXISTS/IN/ANY/ALL operations.

__init__(operation)

Insert

class sqlspec.builder.Insert

Bases: QueryBuilder, ReturningClauseMixin, InsertValuesMixin, InsertFromSelectMixin, InsertIntoClauseMixin, ExplainMixin

Builder for INSERT statements.

Constructs SQL INSERT queries with parameter binding and validation.

__init__(table=None, **kwargs)

Initialize INSERT with optional table.

Parameters:

• table (str | None) -- Target table name

• **kwargs (Any) -- Additional QueryBuilder arguments

get_insert_expression()

Get the insert expression (public API).

Return type:

Insert

values_from(data, *, exclude_unset=True)

Add a row of values from a dict, dataclass, msgspec.Struct, Pydantic model, or attrs class.

Schema instances are normalised via sqlspec.utils.serializers.schema_dump() with wire_format=False then bound as a single row. The dict shape uses Python attribute names regardless of msgspec rename= or Pydantic Field(alias=...); wire-aligned names never make sense for SQL column binding.

Parameters:

• data (Any) -- A dict, dataclass instance, msgspec.Struct, pydantic.BaseModel, or attrs-decorated class instance.

• exclude_unset (bool) -- If True, exclude fields that were never set. Honoured by msgspec (UNSET fields), Pydantic (model_fields_set), and dataclass (via empty default-field semantics). No-op for attrs.

Return type:

Self

Returns:

The current builder instance for method chaining.

values_from_many(items, *, exclude_unset=True)

Add multiple rows from a sequence of dicts, dataclasses, msgspec Structs, Pydantic models, or attrs classes.

Each item is normalised via sqlspec.utils.serializers.schema_dump() with wire_format=False then bound as a multi-row INSERT. Mixed schema kinds in the same sequence are supported (each item normalises independently); however, the resulting dict shapes must agree on key sets.

Parameters:

• items (Sequence[typing.Any]) -- A sequence of schema instances or dicts.

• exclude_unset (bool) -- See values_from() for per-library semantics.

Return type:

Self

Returns:

The current builder instance for method chaining. Empty input returns the builder unchanged.

on_conflict(*columns)

Adds an ON CONFLICT clause with specified columns.

Parameters:

*columns (str) -- Column names that define the conflict. If no columns provided, creates an ON CONFLICT without specific columns (catches all conflicts).

Return type:

ConflictBuilder

Returns:

A ConflictBuilder instance for chaining conflict resolution methods.

on_conflict_do_nothing(*columns)

Adds an ON CONFLICT DO NOTHING clause (convenience method).

Parameters:

*columns (str) -- Column names that define the conflict. If no columns provided, creates an ON CONFLICT without specific columns.

Return type:

Insert

Returns:

The current builder instance for method chaining.

on_duplicate_key_update(**kwargs)

Adds MySQL-style ON DUPLICATE KEY UPDATE clause.

Parameters:

**kwargs (Any) -- Column-value pairs to update on duplicate key.

Return type:

Insert

Returns:

The current builder instance for method chaining.

class sqlspec.builder._insert.ConflictBuilder

Bases: object

Builder for ON CONFLICT clauses in INSERT statements.

Constructs conflict resolution clauses using PostgreSQL-style syntax, which SQLGlot can transpile to other dialects.

__init__(insert_builder, columns)

Initialize ConflictBuilder.

Parameters:

• insert_builder (Insert) -- The parent Insert builder

• columns (tuple[str, ...]) -- Column names that define the conflict

do_nothing()

Add DO NOTHING conflict resolution.

Return type:

Insert

Returns:

The parent Insert builder for method chaining.

do_update(**kwargs)

Add DO UPDATE conflict resolution with SET clauses.

Parameters:

**kwargs (Any) -- Column-value pairs to update on conflict.

Return type:

Insert

Returns:

The parent Insert builder for method chaining.

Update

class sqlspec.builder.Update

Bases: QueryBuilder, WhereClauseMixin, ReturningClauseMixin, UpdateSetClauseMixin, UpdateFromClauseMixin, UpdateTableClauseMixin, ExplainMixin

Builder for UPDATE statements.

Constructs SQL UPDATE statements with parameter binding and validation.

__init__(table=None, **kwargs)

Initialize UPDATE with optional table.

Parameters:

• table (str | None) -- Target table name

• **kwargs (Any) -- Additional QueryBuilder arguments

join(table, on, alias=None, join_type='INNER')

Add JOIN clause to the UPDATE statement.

Parameters:

• table (str | Expr | Select) -- The table name, expression, or subquery to join.

• on (str | Expr) -- The JOIN condition.

• alias (str | None) -- Optional alias for the joined table.

• join_type (str) -- Type of join (INNER, LEFT, RIGHT, FULL).

Return type:

Self

Returns:

The current builder instance for method chaining.

Raises:

SQLBuilderError -- If the current expression is not an UPDATE statement.

build(dialect=None)

Build the UPDATE query with validation.

Parameters:

dialect (Union[str, Dialect, type[Dialect], None]) -- Optional dialect override for SQL generation.

Returns:

The built query with SQL and parameters.

Return type:

BuiltQuery

Raises:

SQLBuilderError -- If no table is set or expression is not an UPDATE.

Delete

class sqlspec.builder.Delete

Bases: QueryBuilder, WhereClauseMixin, ReturningClauseMixin, DeleteFromClauseMixin, ExplainMixin

Builder for DELETE statements.

Constructs SQL DELETE statements with parameter binding and validation. Does not support JOIN operations to maintain cross-dialect compatibility.

__init__(table=None, **kwargs)

Initialize DELETE with optional table.

Parameters:

• table (str | None) -- Target table name

• **kwargs (Any) -- Additional QueryBuilder arguments

build(dialect=None)

Build the DELETE query with validation.

Parameters:

dialect (Union[str, Dialect, type[Dialect], None]) -- Optional dialect override for SQL generation.

Returns:

The built query with SQL and parameters.

Return type:

BuiltQuery

Raises:

SQLBuilderError -- If the table is not specified.

Merge

class sqlspec.builder.Merge

Bases: QueryBuilder, MergeUsingClauseMixin, MergeOnClauseMixin, MergeMatchedClauseMixin, MergeNotMatchedClauseMixin, MergeIntoClauseMixin, MergeNotMatchedBySourceClauseMixin, ExplainMixin

Builder for MERGE statements.

Constructs SQL MERGE statements (also known as UPSERT in some databases) with parameter binding and validation.

__init__(target_table=None, **kwargs)

Initialize MERGE with optional target table.

Parameters:

• target_table (str | None) -- Target table name

• **kwargs (Any) -- Additional QueryBuilder arguments

build(dialect=None)

Build MERGE statement with dialect validation.

Parameters:

dialect (Union[str, Dialect, type[Dialect], None]) -- Optional dialect override for SQL generation.

Return type:

typing.Any

Returns:

Built statement object.

Explain

class sqlspec.builder.Explain

Bases: object

Builder for EXPLAIN statements with dialect-aware rendering.

Provides a fluent API for constructing EXPLAIN statements with various options that are translated to dialect-specific syntax.

__init__(statement, dialect=None, options=None)

Initialize ExplainBuilder.

Parameters:

• statement (str | Expr | SQL | SQLBuilderProtocol) -- SQL statement to explain (string, expression, SQL object, or builder)

• dialect (Union[str, Dialect, type[Dialect], None]) -- Target SQL dialect

• options (ExplainOptions | None) -- Initial ExplainOptions (or None for defaults)

analyze(enabled=True)

Enable ANALYZE option (execute statement for real statistics).

Parameters:

enabled (bool) -- Whether to enable ANALYZE

Return type:

Self

Returns:

Self for method chaining

verbose(enabled=True)

Enable VERBOSE option (show additional information).

Parameters:

enabled (bool) -- Whether to enable VERBOSE

Return type:

Self

Returns:

Self for method chaining

format(fmt)

Set output format.

Parameters:

fmt (ExplainFormat | str) -- Output format (TEXT, JSON, XML, YAML, TREE, TRADITIONAL)

Return type:

Self

Returns:

Self for method chaining

costs(enabled=True)

Enable COSTS option (show estimated costs).

Parameters:

enabled (bool) -- Whether to show costs

Return type:

Self

Returns:

Self for method chaining

buffers(enabled=True)

Enable BUFFERS option (show buffer usage).

Parameters:

enabled (bool) -- Whether to show buffer usage

Return type:

Self

Returns:

Self for method chaining

timing(enabled=True)

Enable TIMING option (show actual timing).

Parameters:

enabled (bool) -- Whether to show timing

Return type:

Self

Returns:

Self for method chaining

summary(enabled=True)

Enable SUMMARY option (show summary information).

Parameters:

enabled (bool) -- Whether to show summary

Return type:

Self

Returns:

Self for method chaining

memory(enabled=True)

Enable MEMORY option (show memory usage, PostgreSQL 17+).

Parameters:

enabled (bool) -- Whether to show memory usage

Return type:

Self

Returns:

Self for method chaining

settings(enabled=True)

Enable SETTINGS option (show configuration parameters, PostgreSQL 12+).

Parameters:

enabled (bool) -- Whether to show settings

Return type:

Self

Returns:

Self for method chaining

wal(enabled=True)

Enable WAL option (show WAL usage, PostgreSQL 13+).

Parameters:

enabled (bool) -- Whether to show WAL usage

Return type:

Self

Returns:

Self for method chaining

generic_plan(enabled=True)

Enable GENERIC_PLAN option (ignore parameter values, PostgreSQL 16+).

Parameters:

enabled (bool) -- Whether to use generic plan

Return type:

Self

Returns:

Self for method chaining

with_options(options)

Replace all options with the provided ExplainOptions.

Parameters:

options (ExplainOptions) -- New options to use

Return type:

Self

Returns:

Self for method chaining

property options: ExplainOptions

Get current ExplainOptions.

property dialect: str | Dialect | type[Dialect] | None

Get current dialect.

property parameters: dict[str, Any]

Get parameters from the underlying statement.

build(dialect=None)

Build the EXPLAIN statement as a SQL object.

Parameters:

dialect (Union[str, Dialect, type[Dialect], None]) -- Optional dialect override

Return type:

SQL

Returns:

SQL object containing the EXPLAIN statement

to_sql(dialect=None)

Build and return just the SQL string.

Parameters:

dialect (Union[str, Dialect, type[Dialect], None]) -- Optional dialect override

Return type:

str

Returns:

EXPLAIN SQL string

__repr__()

String representation.

Return type:

str

Joins

final class sqlspec.builder.JoinBuilder

Bases: object

Builder for JOIN operations with fluent syntax.

__init__(join_type, lateral=False)

Initialize the join builder.

Parameters:

• join_type (str) -- Type of join (inner, left, right, full, cross, lateral)

• lateral (bool) -- Whether this is a LATERAL join

__call__(table, alias=None)

Set the table to join.

Parameters:

• table (str | Expr) -- Table name or expression to join

• alias (str | None) -- Optional alias for the table

Return type:

Self

Returns:

Self for method chaining

as_of(time_expr, kind=None)

Set AS OF clause for the join (Time Travel/Flashback).

Parameters:

• time_expr (Any) -- Timestamp or system time expression

• kind (str | None) -- Type of AS OF clause (SYSTEM TIME, TIMESTAMP). If None, defaults based on dialect.

Return type:

Self

Returns:

Self for method chaining

on(condition)

Set the join condition and build the JOIN expression.

Parameters:

condition (str | Expr) -- JOIN condition

Return type:

Expr

Returns:

Complete JOIN expression