Skip to content

DML Builders

from sqlspec.builder import (
    ConflictBuilder,
    Delete,
    DeleteFromClauseMixin,
    Insert,
    InsertFromSelectMixin,
    InsertIntoClauseMixin,
    InsertValuesMixin,
    Merge,
    ReturningClauseMixin,
    Update,
    UpdateFromClauseMixin,
    UpdateSetClauseMixin,
    UpdateTableClauseMixin,
    Values,
)

Fluent builders for INSERT, ON CONFLICT / ON DUPLICATE KEY UPDATE upserts, UPDATE, DELETE, MERGE, VALUES, and RETURNING clauses. See DML, Upserts, MERGE and DDL Schema Builders for usage examples.

Insert, Update, Delete and Merge

Insert

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

Builder for INSERT statements.

Constructs SQL INSERT queries with parameter binding and validation.

get_insert_expression()

Get the insert expression (public API).

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 :func: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:

Name Type Description Default
data Any

A dict, dataclass instance, msgspec.Struct, pydantic.BaseModel, or attrs-decorated class instance.

required
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.

True

Returns:

Type Description
Self

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 :func: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:

Name Type Description Default
items Sequence[Any]

A sequence of schema instances or dicts.

required
exclude_unset bool

See :meth:values_from for per-library semantics.

True

Returns:

Type Description
Self

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

Self

builder unchanged.

on_conflict(*columns)

Adds an ON CONFLICT clause with specified columns.

Parameters:

Name Type Description Default
*columns str

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

()

Returns:

Type Description
ConflictBuilder

A ConflictBuilder instance for chaining conflict resolution methods.

on_conflict_do_nothing(*columns)

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

Parameters:

Name Type Description Default
*columns str

Column names that define the conflict. If no columns provided, creates an ON CONFLICT without specific columns.

()

Returns:

Type Description
Insert

The current builder instance for method chaining.

on_duplicate_key_update(**kwargs)

Adds MySQL-style ON DUPLICATE KEY UPDATE clause.

Parameters:

Name Type Description Default
**kwargs Any

Column-value pairs to update on duplicate key.

{}

Returns:

Type Description
Insert

The current builder instance for method chaining.

ConflictBuilder

Builder for ON CONFLICT clauses in INSERT statements.

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

do_nothing()

Add DO NOTHING conflict resolution.

Returns:

Type Description
Insert

The parent Insert builder for method chaining.

do_update(**kwargs)

Add DO UPDATE conflict resolution with SET clauses.

Parameters:

Name Type Description Default
**kwargs Any

Column-value pairs to update on conflict.

{}

Returns:

Type Description
Insert

The parent Insert builder for method chaining.

Update

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

Builder for UPDATE statements.

Constructs SQL UPDATE statements with parameter binding and validation.

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

Add JOIN clause to the UPDATE statement.

Parameters:

Name Type Description Default
table str | Expr | Select

The table name, expression, or subquery to join.

required
on str | Expr

The JOIN condition.

required
alias str | None

Optional alias for the joined table.

None
join_type str

Type of join (INNER, LEFT, RIGHT, FULL).

'INNER'

Returns:

Type Description
Self

The current builder instance for method chaining.

Raises:

Type Description
SQLBuilderError

If the current expression is not an UPDATE statement.

build(dialect=None)

Build the UPDATE query with validation.

Parameters:

Name Type Description Default
dialect DialectType

Optional dialect override for SQL generation.

None

Returns:

Name Type Description
BuiltQuery BuiltQuery

The built query with SQL and parameters.

Raises:

Type Description
SQLBuilderError

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

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.

build(dialect=None)

Build the DELETE query with validation.

Parameters:

Name Type Description Default
dialect DialectType

Optional dialect override for SQL generation.

None

Returns:

Name Type Description
BuiltQuery BuiltQuery

The built query with SQL and parameters.

Raises:

Type Description
SQLBuilderError

If the table is not specified.

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.

build(dialect=None)

Build MERGE statement with dialect validation.

Parameters:

Name Type Description Default
dialect DialectType

Optional dialect override for SQL generation.

None

Returns:

Type Description
Any

Built statement object.

Values

Bases: QueryBuilder

Builder for SQL VALUES clauses.

Constructs parameterized VALUES expressions that can be executed directly, used as Common Table Expressions (CTEs), or embedded in FROM clauses.

Attributes:

Name Type Description
alias_name str | None

Get the table alias name if defined.

columns list[str] | None

Get the column names if defined.

as_(alias)

Set the table alias for this VALUES clause.

Parameters:

Name Type Description Default
alias str

Table alias name.

required

Returns:

Type Description
Self

Self for method chaining.

set_columns(*columns)

Set the column names for this VALUES clause.

Parameters:

Name Type Description Default
*columns str

Column names to assign.

()

Returns:

Type Description
Self

Self for method chaining.

Raises:

Type Description
SQLBuilderError

If column count does not match row width.

add_rows(rows)

Add rows to the VALUES clause.

Parameters:

Name Type Description Default
rows Sequence[Sequence[Any] | Mapping[str, Any]]

Sequence of row tuples, lists, or mappings.

required

Returns:

Type Description
Self

Self for method chaining.

Raises:

Type Description
SQLBuilderError

If rows is empty, non-uniform, or column count mismatches.

DML Clause Mixins

ReturningClauseMixin

Mixin providing RETURNING clause support for DML builders.

dialect
returning(*columns)

Add RETURNING clause to the DML statement.

Each string is one SQL projection parsed with the builder dialect, so it must be trusted text, as with Select.select. Other arguments must be SQLGlot nodes, SQLSpec expression wrappers, SQL objects, or query builders.

Parameters:

Name Type Description Default
*columns str | Expr | Column | ExpressionWrapper | Case

Columns or expressions to return.

()

Returns:

Type Description
Self

The builder instance for method chaining.

Raises:

Type Description
SQLBuilderError

If expression not initialized or not DML, if no column is given, if a string is not exactly one projection, or if an argument cannot render as SQL.

InsertIntoClauseMixin

dialect
get_expression()
set_expression(expression)
into(table)

InsertValuesMixin

get_expression()
set_expression(expression)
columns(*columns)
values(*values, **kwargs)
add_values(values)

InsertFromSelectMixin

get_expression()
set_expression(expression)
from_select(select_builder)

UpdateTableClauseMixin

dialect
get_expression()
set_expression(expression)
table(table_name, alias=None)

UpdateSetClauseMixin

get_expression()
set_expression(expression)
set(*args, **kwargs)
set_from(data, *, exclude_unset=True)

Set columns from a dict, dataclass, msgspec.Struct, Pydantic model, or attrs class.

Schema instances are normalised via :func:sqlspec.utils.serializers.schema_dump with wire_format=False and dispatched to :meth:set via keyword unpack. The dict shape uses Python attribute names regardless of msgspec rename= or Pydantic Field(alias=...).

Parameters:

Name Type Description Default
data Any

A dict, dataclass instance, msgspec.Struct, pydantic.BaseModel, or attrs-decorated class instance.

required
exclude_unset bool

If True, exclude fields that were never set (msgspec UNSET, Pydantic model_fields_set, dataclass empty defaults). No-op for attrs.

True

Returns:

Type Description
Self

The current builder instance for method chaining.

UpdateFromClauseMixin

dialect
get_expression()
set_expression(expression)
from_(table, alias=None)

Add a table or subquery to the UPDATE statement's FROM clause.

Parameters:

Name Type Description Default
table str | Expr | Any

Target table name, expression, or builder instance.

required
alias str | None

Optional alias for the source table or subquery.

None

Returns:

Type Description
Self

The current builder instance for method chaining.

Raises:

Type Description
SQLBuilderError

If called on a non-UPDATE expression or with an unsupported table type.

DeleteFromClauseMixin

Mixin providing FROM clause support for DELETE builders.

dialect
get_expression()
set_expression(expression)
from_(table)