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

class sqlspec.builder.SQLFactory

Bases: object

Factory for creating SQL builders and column expressions.

__init__(dialect=None)

Initialize the SQL factory.

Parameters:

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

__call__(statement, dialect=None)

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.

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:

• statement (str | Expression | 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

Examples

Basic EXPLAIN:

plan = sql.explain(“SELECT * FROM users”).build()

With options:

plan = (

sql.explain(“SELECT * FROM users”, analyze=True, format=”json”) .buffers() .timing() .build()

)

From QueryBuilder:

query = sql.select(“*”).from_(“users”).where(“id = :id”, id=1) plan = sql.explain(query, analyze=True).build()

Chained configuration:

plan = (

sql.explain(sql.select(“*”).from_(“large_table”)) .analyze() .format(“json”) .buffers() .timing() .build()

)

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.

Examples

query = sql.merge_.into(“products”).using(data, alias=”src”) query = sql.merge_.into(“products”, alias=”t”).on(“t.id = src.id”)

Returns:

New Merge builder instance

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

Examples

PostgreSQL/Oracle/BigQuery (uses MERGE):

upsert_query = (

sql.upsert(“products”, dialect=”postgres”) .using([{“id”: 1, “name”: “Product 1”}], alias=”src”) .on(“t.id = src.id”) .when_matched_then_update(name=”src.name”) .when_not_matched_then_insert(id=”src.id”, name=”src.name”)

)

SQLite/DuckDB/MySQL (uses INSERT ON CONFLICT):

upsert_query = (

sql.upsert(“products”, dialect=”sqlite”) .values(id=1, name=”Product 1”) .on_conflict(“id”) .do_update(name=”EXCLUDED.name”)

)

create_table(table_name, dialect=None)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

Build a COPY … FROM statement.

Return type:

SQL

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

Build a COPY … TO statement.

Return type:

SQL

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

Build a COPY statement, inferring direction from provided arguments.

Return type:

SQL

column(name, table=None)

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.

Example

```python case_expr = (

sql.case_ .when(“x = 1”, “one”) .when(“x = 2”, “two”) .else_(“other”) .end()

) aliased_case = (

sql.case_ .when(“status = ‘active’”, 1) .else_(0) .as_(“is_active”)

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()

Example

>>> query = (
...     sql
...     .select("*", sql.count_over_.as_("total"))
...     .from_("users")
...     .where_eq("status", "active")
...     .limit(10)
... )
# Produces: SELECT *, COUNT(*) OVER() AS total FROM users WHERE status = :status LIMIT 10

>>> # With partition:
>>> query = sql.select(
...     "*",
...     sql.count_over_.partition_by("department").as_(
...         "dept_count"
...     ),
... )

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

Example

```python query = (

sql .select(“u.name”, “arr.value”) .from_(“users u”) .join(sql.lateral_join_(“UNNEST(u.tags)”).on(“true”))

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

__getattr__(name)

Dynamically create column references.

Parameters:

name (str) – Column name.

Return type:

Column

Returns:

Column object for the given name.

Note

Special SQL constructs like case_, row_number_, etc. are handled as properties for type safety.

static raw(sql_fragment, **parameters)

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:

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

Example

```python expr = sql.raw(“COALESCE(name, ‘Unknown’)”)

stmt = sql.raw(

“LOWER(name) LIKE LOWER(:pattern)”, pattern=f”%{query}%”

)

expr = sql.raw(

“price BETWEEN :min_price AND :max_price”, min_price=100, max_price=500,

)

query = sql.select(

“name”, sql.raw(

“ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC)”

),

).from_(“employees”) ```

count(column='*', distinct=False)

Create a COUNT expression.

Parameters:

• column (Union[str, Expression, ExpressionWrapper, Case, Column]) – Column to count (default “*”).

• distinct (bool) – Whether to use COUNT DISTINCT.

Return type:

AggregateExpression

Returns:

COUNT expression.

count_distinct(column)

Create a COUNT(DISTINCT column) expression.

Parameters:

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

Return type:

AggregateExpression

Returns:

COUNT DISTINCT expression.

count_over(column='*', partition_by=None)

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:

• column (Union[str, Expression, ExpressionWrapper, Case, Column]) – Column to count (default “*” for COUNT(*)).

• partition_by (str | list[str] | Expression | None) – Optional columns to partition by.

Return type:

FunctionExpression

Returns:

COUNT() OVER() window function expression.

Example

>>> # Simple total count alongside paginated results:
>>> query = (
...     sql
...     .select("id", "name", sql.count_over().as_("total"))
...     .from_("users")
...     .where_eq("status", "active")
...     .limit(10)
...     .offset(0)
... )
# Produces: SELECT id, name, COUNT(*) OVER() AS total FROM users WHERE status = :status LIMIT 10

>>> # With partition (count per group):
>>> query = sql.select(
...     "*",
...     sql.count_over(partition_by="department").as_(
...         "dept_count"
...     ),
... )

sum_over(column, partition_by=None, order_by=None)

Create a SUM() OVER() window function.

Parameters:

• column (Union[str, Expression, ExpressionWrapper, Case]) – Column to sum.

• partition_by (str | list[str] | Expression | None) – Columns to partition by.

• order_by (str | list[str] | Expression | None) – Columns to order by.

Return type:

FunctionExpression

Returns:

SUM() OVER() window function expression.

avg_over(column, partition_by=None, order_by=None)

Create an AVG() OVER() window function.

Parameters:

• column (Union[str, Expression, ExpressionWrapper, Case]) – Column to average.

• partition_by (str | list[str] | Expression | None) – Columns to partition by.

• order_by (str | list[str] | Expression | None) – Columns to order by.

Return type:

FunctionExpression

Returns:

AVG() OVER() window function expression.

max_over(column, partition_by=None, order_by=None)

Create a MAX() OVER() window function.

Parameters:

• column (Union[str, Expression, ExpressionWrapper, Case]) – Column to find maximum.

• partition_by (str | list[str] | Expression | None) – Columns to partition by.

• order_by (str | list[str] | Expression | None) – Columns to order by.

Return type:

FunctionExpression

Returns:

MAX() OVER() window function expression.

min_over(column, partition_by=None, order_by=None)

Create a MIN() OVER() window function.

Parameters:

• column (Union[str, Expression, ExpressionWrapper, Case]) – Column to find minimum.

• partition_by (str | list[str] | Expression | None) – Columns to partition by.

• order_by (str | list[str] | Expression | None) – Columns to order by.

Return type:

FunctionExpression

Returns:

MIN() OVER() window function expression.

static sum(column, distinct=False)

Create a SUM expression.

Parameters:

• column (Union[str, Expression, ExpressionWrapper, Case]) – Column to sum.

• distinct (bool) – Whether to use SUM DISTINCT.

Return type:

AggregateExpression

Returns:

SUM expression.

static avg(column)

Create an AVG expression.

Parameters:

column (Union[str, Expression, ExpressionWrapper, Case]) – Column to average.

Return type:

AggregateExpression

Returns:

AVG expression.

static max(column)

Create a MAX expression.

Parameters:

column (Union[str, Expression, ExpressionWrapper, Case]) – Column to find maximum.

Return type:

AggregateExpression

Returns:

MAX expression.

static min(column)

Create a MIN expression.

Parameters:

column (Union[str, Expression, ExpressionWrapper, Case]) – Column to find minimum.

Return type:

AggregateExpression

Returns:

MIN expression.

static rollup(*columns)

Create a ROLLUP expression for GROUP BY clauses.

Parameters:

*columns (str | Expression) – Columns to include in the rollup.

Return type:

FunctionExpression

Returns:

ROLLUP expression.

Example

```python query = (

sql .select(“product”, “region”, sql.sum(“sales”)) .from_(“sales_data”) .group_by(sql.rollup(“product”, “region”))

static cube(*columns)

Create a CUBE expression for GROUP BY clauses.

Parameters:

*columns (str | Expression) – Columns to include in the cube.

Return type:

FunctionExpression

Returns:

CUBE expression.

Example

```python query = (

sql .select(“product”, “region”, sql.sum(“sales”)) .from_(“sales_data”) .group_by(sql.cube(“product”, “region”))

static grouping_sets(*column_sets)

Create a GROUPING SETS expression for GROUP BY clauses.

Parameters:

*column_sets (tuple[str, ...] | list[str]) – Sets of columns to group by.

Return type:

FunctionExpression

Returns:

GROUPING SETS expression.

Example

```python query = (

sql .select(“product”, “region”, sql.sum(“sales”)) .from_(“sales_data”) .group_by(

sql.grouping_sets((“product”,), (“region”,), ())

)

static any(values)

Create an ANY expression for use with comparison operators.

Parameters:

values (list[Any] | Expression | str) – Values, expression, or subquery for the ANY clause.

Return type:

FunctionExpression

Returns:

ANY expression.

Example

```python subquery = sql.select(“user_id”).from_(“active_users”) query = (

sql .select(“*”) .from_(“users”) .where(sql.id.eq(sql.any(subquery)))

static not_any_(values)

Create a NOT ANY expression for use with comparison operators.

Parameters:

values (list[Any] | Expression | str) – Values, expression, or subquery for the NOT ANY clause.

Return type:

FunctionExpression

Returns:

NOT ANY expression.

Example

```python subquery = sql.select(“user_id”).from_(“blocked_users”) query = (

sql .select(“*”) .from_(“users”) .where(sql.id.neq(sql.not_any(subquery)))

static concat(*expressions)

Create a CONCAT expression.

Parameters:

*expressions (str | Expression) – Expressions to concatenate.

Return type:

StringExpression

Returns:

CONCAT expression.

static upper(column)

Create an UPPER expression.

Parameters:

column (str | Expression) – Column to convert to uppercase.

Return type:

StringExpression

Returns:

UPPER expression.

static lower(column)

Create a LOWER expression.

Parameters:

column (str | Expression) – Column to convert to lowercase.

Return type:

StringExpression

Returns:

LOWER expression.

static length(column)

Create a LENGTH expression.

Parameters:

column (str | Expression) – Column to get length of.

Return type:

StringExpression

Returns:

LENGTH expression.

static round(column, decimals=0)

Create a ROUND expression.

Parameters:

• column (str | Expression) – Column to round.

• decimals (int) – Number of decimal places.

Return type:

MathExpression

Returns:

ROUND expression.

static to_literal(value)

Convert a Python value to a SQLGlot literal expression.

Uses SQLGlot’s built-in exp.convert() function for literal creation. Handles all Python primitive types: - None -> exp.Null (renders as NULL) - bool -> exp.Boolean (renders as TRUE/FALSE or 1/0 based on dialect) - int/float -> exp.Literal with is_number=True - str -> exp.Literal with is_string=True - exp.Expression -> returned as-is (passthrough)

Parameters:

value (Any) – Python value or SQLGlot expression to convert.

Return type:

FunctionExpression

Returns:

SQLGlot expression representing the literal value.

static decode(column, *args)

Create a DECODE expression (Oracle-style conditional logic).

DECODE compares column to each search value and returns the corresponding result. If no match is found, returns the default value (if provided) or NULL.

Parameters:

• column (str | Expression) – Column to compare.

• *args (str | Expression | Any) – Alternating search values and results, with optional default at the end. Format: search1, result1, search2, result2, …, [default]

Raises:

ValueError – If fewer than two search/result pairs are provided.

Return type:

FunctionExpression

Returns:

CASE expression equivalent to DECODE.

Example

```python sql.decode(

“status”, “A”, “Active”, “I”, “Inactive”, “Unknown”

static cast(column, data_type)

Create a CAST expression for type conversion.

Parameters:

• column (str | Expression) – Column or expression to cast.

• data_type (str) – Target data type (e.g., ‘INT’, ‘VARCHAR(100)’, ‘DECIMAL(10,2)’).

Return type:

ConversionExpression

Returns:

CAST expression.

static coalesce(*expressions)

Create a COALESCE expression.

Parameters:

*expressions (str | Expression) – Expressions to coalesce.

Return type:

ConversionExpression

Returns:

COALESCE expression.

static nvl(column, substitute_value)

Create an NVL (Oracle-style) expression using COALESCE.

Parameters:

• column (str | Expression) – Column to check for NULL.

• substitute_value (str | Expression | Any) – Value to use if column is NULL.

Return type:

ConversionExpression

Returns:

COALESCE expression equivalent to NVL.

static nvl2(column, value_if_not_null, value_if_null)

Create an NVL2 (Oracle-style) expression using CASE.

NVL2 returns value_if_not_null if column is not NULL, otherwise returns value_if_null.

Parameters:

• column (str | Expression) – Column to check for NULL.

• value_if_not_null (str | Expression | Any) – Value to use if column is NOT NULL.

• value_if_null (str | Expression | Any) – Value to use if column is NULL.

Return type:

ConversionExpression

Returns:

CASE expression equivalent to NVL2.

Example

`python sql.nvl2("salary", "Has Salary", "No Salary") `

static bulk_insert(table_name, column_count, placeholder_style='?')

Create bulk INSERT expression for executemany operations.

For bulk loading operations like CSV ingestion where an INSERT expression with placeholders for executemany() is needed.

Parameters:

• table_name (str) – Name of the table to insert into

• column_count (int) – Number of columns (for placeholder generation)

• placeholder_style (str) – Placeholder style (“?” for SQLite/PostgreSQL, “%s” for MySQL, “:1” for Oracle)

Return type:

FunctionExpression

Returns:

INSERT expression with placeholders for bulk operations

Example

```python from sqlspec import sql

insert_expr = sql.bulk_insert(“my_table”, 3)

insert_expr = sql.bulk_insert(

“my_table”, 3, placeholder_style=”%s”

)

insert_expr = sql.bulk_insert(

“my_table”, 3, placeholder_style=”:1”

truncate(table_name)

Create a TRUNCATE TABLE builder.

Parameters:

table_name (str) – Name of the table to truncate

Return type:

Truncate

Returns:

TruncateTable builder instance

Example

```python from sqlspec import sql

truncate_sql = sql.truncate_table(“my_table”).build().sql

truncate_sql = (

sql .truncate_table(“my_table”) .cascade() .restart_identity() .build() .sql

static case()

Create a CASE expression builder.

Return type:

Case

Returns:

CaseExpressionBuilder for building CASE expressions.

row_number(partition_by=None, order_by=None)

Create a ROW_NUMBER() window function.

Parameters:

• partition_by (str | list[str] | Expression | None) – Columns to partition by.

• order_by (str | list[str] | Expression | None) – Columns to order by.

Return type:

FunctionExpression

Returns:

ROW_NUMBER window function expression.

rank(partition_by=None, order_by=None)

Create a RANK() window function.

Parameters:

• partition_by (str | list[str] | Expression | None) – Columns to partition by.

• order_by (str | list[str] | Expression | None) – Columns to order by.

Return type:

FunctionExpression

Returns:

RANK window function expression.

dense_rank(partition_by=None, order_by=None)

Create a DENSE_RANK() window function.

Parameters:

• partition_by (str | list[str] | Expression | None) – Columns to partition by.

• order_by (str | list[str] | Expression | None) – Columns to order by.

Return type:

FunctionExpression

Returns:

DENSE_RANK window function expression.

lag(column, offset=1, default=None, partition_by=None, order_by=None)

Create a LAG() window function.

LAG accesses data from a previous row in the same result set without using a self-join.

Parameters:

• column (str | Expression) – The column to get the lagged value from.

• offset (int) – Number of rows to look back (default 1).

• default (Any) – Value to return when there is no row at the offset.

• partition_by (str | list[str] | Expression | None) – Columns to partition by.

• order_by (str | list[str] | Expression | None) – Columns to order by.

Return type:

FunctionExpression

Returns:

LAG window function expression.

Example

>>> sql.lag("salary", offset=1, order_by="hire_date")
>>> sql.lag("price", partition_by="category", order_by="date")

lead(column, offset=1, default=None, partition_by=None, order_by=None)

Create a LEAD() window function.

LEAD accesses data from a subsequent row in the same result set without using a self-join.

Parameters:

• column (str | Expression) – The column to get the lead value from.

• offset (int) – Number of rows to look forward (default 1).

• default (Any) – Value to return when there is no row at the offset.

• partition_by (str | list[str] | Expression | None) – Columns to partition by.

• order_by (str | list[str] | Expression | None) – Columns to order by.

Return type:

FunctionExpression

Returns:

LEAD window function expression.

Example

>>> sql.lead("salary", offset=1, order_by="hire_date")
>>> sql.lead("price", partition_by="category", order_by="date")

Convenience Instance

sqlspec.builder.sql: SQLFactory

Pre-configured SQLFactory instance for convenient query building.

Callable as sql(statement, dialect=None) to create a builder from a SQL string.

COPY Statement Helpers

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

Return type:

SQL

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

Return type:

SQL

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

Return type:

SQL

Temporal Table Support

sqlspec.builder.create_temporal_table(table, as_of, kind=None)

Create a table expression with temporal (time-travel) version clause.

Parameters:

• table (str | Table | Expression) – Table name or expression.

• as_of (Expression | str) – Timestamp or SCN expression for the point-in-time.

• kind (str | None) – Optional version kind (e.g., “TIMESTAMP”, “SCN”, “SYSTEM TIME”). Defaults to dialect-appropriate value.

Return type:

Table

Returns:

Table expression with version clause that generates dialect-specific SQL.

Notes

Inputs are normalized before building the exp.Version clause so both string table names and literal timestamps work consistently.

Example

>>> from sqlspec.builder import create_temporal_table
>>> from sqlglot import exp
>>> t = create_temporal_table(
...     "orders", exp.Literal.string("2024-01-01")
... )
>>> t.sql(dialect="oracle")
"orders AS OF TIMESTAMP '2024-01-01'"
>>> t.sql(dialect="bigquery")
"orders FOR SYSTEM_TIME AS OF '2024-01-01'"

sqlspec.builder.register_version_generators()

Register dialect-specific version_sql generators.

This function is idempotent - calling it multiple times has no effect after the first call. It is called automatically when the builder module is imported.

Registers custom SQL generators for temporal (time-travel) queries: - Default (no dialect): AS OF SYSTEM TIME (CockroachDB style) - Oracle: AS OF TIMESTAMP / AS OF SCN - Snowflake: AT (TIMESTAMP => …) / BEFORE (…) - DuckDB: AT (TIMESTAMP => …) - Postgres/CockroachDB: AS OF SYSTEM TIME - BigQuery: Uses built-in FOR SYSTEM_TIME AS OF

Return type:

None