Factory#

The SQLFactory class is the main entry point for the query builder system. It provides methods to create SELECT, INSERT, UPDATE, DELETE, MERGE, and DDL builders, plus COPY statement generation.

SQLFactory#

final class sqlspec.builder.SQLFactory[source]#

Bases: object

Factory for creating SQL builders and column expressions.

__init__(dialect=None)[source]#

Initialize the SQL factory.

Parameters:

dialect (Union[str, Dialect, type[Dialect], None]) -- Default SQL dialect to use for all builders.

__call__(statement, dialect=None)[source]#

Create a SelectBuilder from a SQL string, or SQL object for DML with RETURNING.

Parameters:

  • statement (str) -- The SQL statement string.

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

Return type:

typing.Any

Returns:

SelectBuilder instance for SELECT/WITH statements, SQL object for DML statements with RETURNING clause.

Raises:

SQLBuilderError -- If the SQL is not a SELECT/CTE/DML+RETURNING statement.

values(rows, *, alias=None, columns=None, dialect=None)[source]#

Create a VALUES builder for bulk rows.

Parameters:

  • rows (Sequence[Sequence[typing.Any] | Mapping[str, typing.Any]]) -- Sequence of row tuples/lists or mappings.

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

  • columns (Sequence[str] | None) -- Optional column names for the table alias.

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

Return type:

Values

Returns:

Values builder instance.

explain(statement, *, analyze=False, verbose=False, format=None, dialect=None)[source]#

Create an EXPLAIN builder for a SQL statement.

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

Parameters:

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

  • analyze (bool) -- Execute the statement and show actual runtime statistics

  • verbose (bool) -- Show additional information

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

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

Return type:

Explain

Returns:

Explain builder for further configuration

property merge_: Merge#

Create a new MERGE builder (property shorthand).

Property that returns a new Merge builder instance using the factory's default dialect. Cleaner syntax alternative to merge() method.

Returns:

New Merge builder instance

upsert(table, dialect=None)[source]#

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:

  • table (str) -- Target table name

  • dialect (Union[str, Dialect, type[Dialect], None]) -- Optional SQL dialect (uses factory default if not provided)

Return type:

Merge | Insert

Returns:

MERGE builder for supported databases, INSERT builder otherwise

create_table(table_name, dialect=None)[source]#

Create a CREATE TABLE builder.

Parameters:

  • table_name (str) -- Name of the table to create

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

Return type:

CreateTable

Returns:

CreateTable builder instance

create_table_as_select(dialect=None)[source]#

Create a CREATE TABLE AS SELECT builder.

Parameters:

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

Return type:

CreateTableAsSelect

Returns:

CreateTableAsSelect builder instance

create_view(view_name, dialect=None)[source]#

Create a CREATE VIEW builder.

Parameters:

  • view_name (str) -- Name of the view to create

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

Return type:

CreateView

Returns:

CreateView builder instance

create_materialized_view(view_name, dialect=None)[source]#

Create a CREATE MATERIALIZED VIEW builder.

Parameters:

  • view_name (str) -- Name of the materialized view to create

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

Return type:

CreateMaterializedView

Returns:

CreateMaterializedView builder instance

create_index(index_name, dialect=None)[source]#

Create a CREATE INDEX builder.

Parameters:

  • index_name (str) -- Name of the index to create

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

Return type:

CreateIndex

Returns:

CreateIndex builder instance

create_schema(schema_name, dialect=None)[source]#

Create a CREATE SCHEMA builder.

Parameters:

  • schema_name (str) -- Name of the schema to create

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

Return type:

CreateSchema

Returns:

CreateSchema builder instance

drop_table(table_name, dialect=None)[source]#

Create a DROP TABLE builder.

Parameters:

  • table_name (str) -- Name of the table to drop

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

Return type:

DropTable

Returns:

DropTable builder instance

drop_view(view_name, dialect=None)[source]#

Create a DROP VIEW builder.

Parameters:

  • view_name (str) -- Name of the view to drop

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

Return type:

DropView

Returns:

DropView builder instance

drop_materialized_view(view_name, dialect=None)[source]#

Create a DROP MATERIALIZED VIEW builder.

Parameters:

  • view_name (str) -- Name of the materialized view to drop

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

Return type:

DropMaterializedView

Returns:

DropMaterializedView builder instance

drop_index(index_name, dialect=None)[source]#

Create a DROP INDEX builder.

Parameters:

  • index_name (str) -- Name of the index to drop

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

Return type:

DropIndex

Returns:

DropIndex builder instance

drop_schema(schema_name, dialect=None)[source]#

Create a DROP SCHEMA builder.

Parameters:

  • schema_name (str) -- Name of the schema to drop

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

Return type:

DropSchema

Returns:

DropSchema builder instance

alter_table(table_name, dialect=None)[source]#

Create an ALTER TABLE builder.

Parameters:

  • table_name (str) -- Name of the table to alter

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

Return type:

AlterTable

Returns:

AlterTable builder instance

rename_table(old_name, dialect=None)[source]#

Create a RENAME TABLE builder.

Parameters:

  • old_name (str) -- Current name of the table

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

Return type:

RenameTable

Returns:

RenameTable builder instance

comment_on(dialect=None)[source]#

Create a COMMENT ON builder.

Parameters:

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

Return type:

CommentOn

Returns:

CommentOn builder instance

copy_from(table, source, *, columns=None, options=None, dialect=None)[source]#

Build a COPY ... FROM statement.

Return type:

SQL

copy_to(table, target, *, columns=None, options=None, dialect=None)[source]#

Build a COPY ... TO statement.

Return type:

SQL

copy(table, *, source=None, target=None, columns=None, options=None, dialect=None)[source]#

Build a COPY statement, inferring direction from provided arguments.

Return type:

SQL

column(name, table=None)[source]#

Create a column reference.

Parameters:

  • name (str) -- Column name.

  • table (str | None) -- Optional table name.

Return type:

Column

Returns:

Column object that supports method chaining and operator overloading.

property case_: Case#

Create a CASE expression builder.

Returns:

Case builder instance for CASE expression building.

property row_number_: WindowFunctionBuilder#

Create a ROW_NUMBER() window function builder.

property rank_: WindowFunctionBuilder#

Create a RANK() window function builder.

property dense_rank_: WindowFunctionBuilder#

Create a DENSE_RANK() window function builder.

property lag_: WindowFunctionBuilder#

Create a LAG() window function builder.

property lead_: WindowFunctionBuilder#

Create a LEAD() window function builder.

property count_over_: WindowFunctionBuilder#

Create a COUNT(*) OVER() window function builder.

Returns a WindowFunctionBuilder pre-configured with COUNT(*) for fluent chaining. Useful for pagination queries where you want to get the total count in the same query.

Returns:

WindowFunctionBuilder configured for COUNT(*) OVER()

property sum_over_: WindowFunctionBuilder#

Create a SUM() OVER() window function builder.

property avg_over_: WindowFunctionBuilder#

Create an AVG() OVER() window function builder.

property max_over_: WindowFunctionBuilder#

Create a MAX() OVER() window function builder.

property min_over_: WindowFunctionBuilder#

Create a MIN() OVER() window function builder.

property exists_: SubqueryBuilder#

Create an EXISTS subquery builder.

property in_: SubqueryBuilder#

Create an IN subquery builder.

property any_: SubqueryBuilder#

Create an ANY subquery builder.

property all_: SubqueryBuilder#

Create an ALL subquery builder.

property inner_join_: JoinBuilder#

Create an INNER JOIN builder.

property left_join_: JoinBuilder#

Create a LEFT JOIN builder.

property right_join_: JoinBuilder#

Create a RIGHT JOIN builder.

property full_join_: JoinBuilder#

Create a FULL OUTER JOIN builder.

property cross_join_: JoinBuilder#

Create a CROSS JOIN builder.

property lateral_join_: JoinBuilder#

Create a LATERAL JOIN builder.

Returns:

JoinBuilder configured for LATERAL JOIN

property left_lateral_join_: JoinBuilder#

Create a LEFT LATERAL JOIN builder.

Returns:

JoinBuilder configured for LEFT LATERAL JOIN

property cross_lateral_join_: JoinBuilder#

Create a CROSS LATERAL JOIN builder.

Returns:

JoinBuilder configured for CROSS LATERAL JOIN

static raw(sql_fragment, **parameters)[source]#

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

Parameters:

  • sql_fragment (str) -- Raw SQL string to parse into an expression.

  • **parameters (Any) -- Named parameters for parameter binding.

Return type:

Expr | SQL

Returns:

SQLGlot expression from the parsed SQL fragment (if no parameters). SQL statement object (if parameters provided).

Raises:

SQLBuilderError -- If the SQL fragment cannot be parsed.

count(column='*', distinct=False)[source]#

Create a COUNT expression.

Parameters:
Return type:

AggregateExpression

Returns:

COUNT expression.

count_distinct(column)[source]#

Create a COUNT(DISTINCT column) expression.

Parameters:

column (Union[str, Expr, ExpressionWrapper, Case, Column]) -- Column to count distinct values.

Return type:

AggregateExpression

Returns:

COUNT DISTINCT expression.

count_over(column='*', partition_by=None, order_by=None)[source]#

Create a COUNT() OVER() window function for inline total counts.

This is particularly useful for pagination queries where you want to get the total count in the same query as the paginated results.

Parameters:
Return type:

FunctionExpression

Returns:

COUNT() OVER() window function expression.

sum_over(column, partition_by=None, order_by=None)[source]#

Create a SUM() OVER() window function.

Parameters:
Return type:

FunctionExpression

Returns:

SUM() OVER() window function expression.

avg_over(column, partition_by=None, order_by=None)[source]#

Create an AVG() OVER() window function.

Parameters:
Return type:

FunctionExpression

Returns:

AVG() OVER() window function expression.

max_over(column, partition_by=None, order_by=None)[source]#

Create a MAX() OVER() window function.

Parameters:
Return type:

FunctionExpression

Returns:

MAX() OVER() window function expression.

min_over(column, partition_by=None, order_by=None)[source]#

Create a MIN() OVER() window function.

Parameters:
Return type:

FunctionExpression

Returns:

MIN() OVER() window function expression.

static sum(column, distinct=False)[source]#

Create a SUM expression.

Parameters:
Return type:

AggregateExpression

Returns:

SUM expression.

static avg(column)[source]#

Create an AVG expression.

Parameters:

column (Union[str, Expr, ExpressionWrapper, Case, Column]) -- Column to average.

Return type:

AggregateExpression

Returns:

AVG expression.

static max(column)[source]#

Create a MAX expression.

Parameters:

column (Union[str, Expr, ExpressionWrapper, Case, Column]) -- Column to find maximum.

Return type:

AggregateExpression

Returns:

MAX expression.

static min(column)[source]#

Create a MIN expression.

Parameters:

column (Union[str, Expr,