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 upserts, UPDATE, DELETE, MERGE, VALUES, and RETURNING clauses. See the Query Builder Guide for usage examples.

Insert, Update, Delete & 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_insert_expression()

Get the insert expression (public API).

values_from 

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 

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 

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 

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 

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 

do_nothing()

Add DO NOTHING conflict resolution.

Returns:

Type Description
Insert

The parent Insert builder for method chaining.

do_update 

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 

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 

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 

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 

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.

DML Clause Mixins

ReturningClauseMixin

Mixin providing RETURNING clause support for DML builders.

InsertIntoClauseMixin

InsertValuesMixin

InsertFromSelectMixin

UpdateTableClauseMixin

UpdateSetClauseMixin

UpdateFromClauseMixin

DeleteFromClauseMixin

Mixin providing FROM clause support for DELETE builders.