SQL Factory & Base Builder¶

from sqlspec.builder import (
    BuiltQuery,
    Explain,
    ExplainMixin,
    ExpressionBuilder,
    QueryBuilder,
    SQLFactory,
    build_copy_from_statement,
    build_copy_statement,
    build_copy_to_statement,
    sql,
)

Entry-point sql factory (SQLFactory), base QueryBuilder class, Explain builder, and COPY statement helpers. See the Query Builder Guide for usage examples.

SQL Factory¶

SQLFactory ¶

Factory for creating SQL builders and column expressions.

select ¶

select(*columns_or_sql, dialect=None)

insert ¶

insert(table_or_sql=None, dialect=None)

update ¶

update(table_or_sql=None, dialect=None)

delete ¶

delete(table_or_sql=None, dialect=None)

merge ¶

merge(table_or_sql=None, dialect=None)

upsert ¶

upsert(table, dialect=None)

Create an upsert builder (MERGE or INSERT ON CONFLICT).

Automatically selects the appropriate builder based on database dialect

PostgreSQL 15+, Oracle, BigQuery, Db2: Returns MERGE builder
SQLite, DuckDB, MySQL: Returns INSERT builder with ON CONFLICT support

Parameters:

Name	Type	Description	Default
`table`	`str`	Target table name	required
`dialect`	`DialectType`	Optional SQL dialect (uses factory default if not provided)	`None`

Returns:

Type	Description
`Merge \| Insert`	MERGE builder for supported databases, INSERT builder otherwise

explain ¶

explain(statement, *, analyze=False, verbose=False, format=None, dialect=None)

Create an EXPLAIN builder for a SQL statement.

Wraps any SQL statement in an EXPLAIN clause with dialect-aware syntax generation.

Parameters:

Name	Type	Description	Default
`statement`	`str \| Expr \| SQL \| SQLBuilderProtocol`	SQL statement to explain (string, expression, SQL object, or builder)	required
`analyze`	`bool`	Execute the statement and show actual runtime statistics	`False`
`verbose`	`bool`	Show additional information	`False`
`format`	`ExplainFormat \| str \| None`	Output format (TEXT, JSON, XML, YAML, TREE, TRADITIONAL)	`None`
`dialect`	`DialectType`	Optional SQL dialect override	`None`

Returns:

Type	Description
`Explain`	Explain builder for further configuration

create_table ¶

create_table(table_name, dialect=None)

Create a CREATE TABLE builder.

Parameters:

Name	Type	Description	Default
`table_name`	`str`	Name of the table to create	required
`dialect`	`DialectType`	Optional SQL dialect	`None`

Returns:

Type	Description
`CreateTable`	CreateTable builder instance

create_view ¶

create_view(view_name, dialect=None)

Create a CREATE VIEW builder.

Parameters:

Name	Type	Description	Default
`view_name`	`str`	Name of the view to create	required
`dialect`	`DialectType`	Optional SQL dialect	`None`

Returns:

Type	Description
`CreateView`	CreateView builder instance

create_index ¶

create_index(index_name, dialect=None)

Create a CREATE INDEX builder.

Parameters:

Name	Type	Description	Default
`index_name`	`str`	Name of the index to create	required
`dialect`	`DialectType`	Optional SQL dialect	`None`

Returns:

Type	Description
`CreateIndex`	CreateIndex builder instance

alter_table ¶

alter_table(table_name, dialect=None)

Create an ALTER TABLE builder.

Parameters:

Name	Type	Description	Default
`table_name`	`str`	Name of the table to alter	required
`dialect`	`DialectType`	Optional SQL dialect	`None`

Returns:

Type	Description
`AlterTable`	AlterTable builder instance

drop_table ¶

drop_table(table_name, dialect=None)

Create a DROP TABLE builder.

Parameters:

Name	Type	Description	Default
`table_name`	`str`	Name of the table to drop	required
`dialect`	`DialectType`	Optional SQL dialect	`None`

Returns:

Type	Description
`DropTable`	DropTable builder instance

truncate ¶

truncate(table_name, dialect=None)

Create a TRUNCATE TABLE builder.

Parameters:

Name	Type	Description	Default
`table_name`	`str`	Name of the table to truncate	required
`dialect`	`DialectType`	Optional SQL dialect	`None`

Returns:

Type	Description
`Truncate`	TruncateTable builder instance

raw ¶

raw(sql_fragment, **parameters)

Create a raw SQL expression from a string fragment with optional parameters.

Parameters:

Name	Type	Description	Default
`sql_fragment`	`str`	Raw SQL string to parse into an expression.	required
`**parameters`	`Any`	Named parameters for parameter binding.	`{}`

Returns:

Type	Description
`Expr \| SQL`	SQLGlot expression from the parsed SQL fragment (if no parameters).
`Expr \| SQL`	SQL statement object (if parameters provided).

Raises:

Type	Description
`SQLBuilderError`	If the SQL fragment cannot be parsed.

column ¶

column(name, table=None)

Create a column reference.

Parameters:

Name	Type	Description	Default
`name`	`str`	Column name.	required
`table`	`str \| None`	Optional table name.	`None`

Returns:

Type	Description
`Column`	Column object that supports method chaining and operator overloading.

Base Query Builder¶

QueryBuilder ¶

Abstract base class for SQL query builders.

Provides common functionality for dialect handling, parameter management, and query construction using SQLGlot.

dialect ¶

dialect = dialect

parameters ¶

parameters

Public access to query parameters.

build ¶

build(dialect=None)

Builds the SQL query string and parameters.

Parameters:

Name	Type	Description	Default
`dialect`	`DialectType`	Optional dialect override. If provided, generates SQL for this dialect instead of the builder's default dialect.	`None`

Returns:

Name	Type	Description
`BuiltQuery`	`BuiltQuery`	A dataclass containing the SQL string and parameters.

to_sql ¶

to_sql(show_parameters=False, dialect=None)

Return SQL string with optional parameter substitution.

Parameters:

Name	Type	Description	Default
`show_parameters`	`bool`	If True, replace parameter placeholders with actual values (for debugging). If False (default), return SQL with parameter placeholders.	`False`
`dialect`	`DialectType`	Optional dialect override. If provided, generates SQL for this dialect instead of the builder's default dialect.	`None`

Returns:

Type	Description
`str`	SQL string with or without parameter values filled in

Warning

SQL with show_parameters=True is for debugging ONLY. Never execute SQL with interpolated parameters directly - use parameterized queries.

to_statement ¶

to_statement(config=None)

Converts the built query into a SQL statement object.

Parameters:

Name	Type	Description	Default
`config`	`StatementConfig \| None`	Optional SQL configuration.	`None`

Returns:

Name	Type	Description
`SQL`	`SQL`	A SQL statement object.

BuiltQuery ¶

SQL query with bound parameters.

ExpressionBuilder ¶

Bases: QueryBuilder

Builder wrapper for a pre-parsed SQLGlot expression.

Explain & COPY Helpers¶

Explain ¶

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.

ExplainMixin ¶

Mixin to add .explain() method to QueryBuilder subclasses.

This mixin can be added to any QueryBuilder subclass to provide EXPLAIN plan functionality.

build_copy_statement ¶

build_copy_statement(
    *, direction, table, location, columns=None, options=None, dialect=None
)

build_copy_from_statement ¶

build_copy_from_statement(
    table, source, *, columns=None, options=None, dialect=None
)

build_copy_to_statement ¶

build_copy_to_statement(
    table, target, *, columns=None, options=None, dialect=None
)