Builder

The builder provides a fluent API for composing SQL statements with chaining, filters, and dialect-aware SQL compilation.

Example

builder usage

from sqlspec import sql

query = sql.select("id", "name").from_("users").where_eq("status", "active").limit(10).offset(0)

Factory

class sqlspec.builder.SQLFactory

Bases: object

Factory for creating SQL builders and column expressions.

classmethod detect_sql_type(sql, dialect=None)

Return type:

str

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

select(*columns_or_sql, dialect=None)

Return type:

Select

insert(table_or_sql=None, dialect=None)

Return type:

Insert

update(table_or_sql=None, dialect=None)

Return type:

Update

delete(table_or_sql=None, dialect=None)

Return type:

Delete

merge(table_or_sql=None, dialect=None)

Return type:

Merge

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

Select Builder

class sqlspec.builder.Select

Bases: QueryBuilder, WhereClauseMixin, OrderByClauseMixin, LimitOffsetClauseMixin, SelectClauseMixin, JoinClauseMixin, HavingClauseMixin, SetOperationMixin, CommonTableExpressionMixin, PivotClauseMixin, UnpivotClauseMixin, ExplainMixin

Builder for SELECT queries.

Provides a fluent interface for constructing SQL SELECT statements with parameter binding and validation.

Example

>>> class User(BaseModel):
...     id: int
...     name: str
>>> builder = Select("id", "name").from_("users")
>>> result = driver.execute(builder)

__init__(*columns, **kwargs)

Initialize SELECT with optional columns.

Parameters:

• *columns (str) – Column names to select (e.g., “id”, “name”, “u.email”)

• **kwargs (Any) – Additional QueryBuilder arguments (dialect, schema, etc.)

Examples

Select(“id”, “name”) # Shorthand for Select().select(“id”, “name”) Select() # Same as Select() - start empty

with_hint(hint, *, location='statement', table=None, dialect=None)

Attach an optimizer or dialect-specific hint to the query.

Parameters:

• hint (str) – The raw hint string (e.g., ‘INDEX(users idx_users_name)’).

• location (str) – Where to apply the hint (‘statement’, ‘table’).

• table (str | None) – Table name if the hint is for a specific table.

• dialect (str | None) – Restrict the hint to a specific dialect (optional).

Return type:

Self

Returns:

The current builder instance for method chaining.

build(dialect=None)

Builds the SQL query string and parameters with hint injection.

Parameters:

dialect (Union[str, Dialect, Type[Dialect], None]) – Optional dialect override for SQL generation.

Returns:

A dataclass containing the SQL string and parameters.

Return type:

BuiltQuery

for_update(*, skip_locked=False, nowait=False, of=None)

Add FOR UPDATE clause to SELECT statement for row-level locking.

Parameters:

• skip_locked (bool) – Skip rows that are already locked (SKIP LOCKED)

• nowait (bool) – Return immediately if row is locked (NOWAIT)

• of (str | list[str] | None) – Table names/aliases to lock (FOR UPDATE OF table)

Return type:

Self

Returns:

Self for method chaining

for_share(*, skip_locked=False, nowait=False, of=None)

Add FOR SHARE clause for shared row-level locking.

Parameters:

• skip_locked (bool) – Skip rows that are already locked (SKIP LOCKED)

• nowait (bool) – Return immediately if row is locked (NOWAIT)

• of (str | list[str] | None) – Table names/aliases to lock (FOR SHARE OF table)

Return type:

Self

Returns:

Self for method chaining

for_key_share()

Add FOR KEY SHARE clause (PostgreSQL-specific).

FOR KEY SHARE is like FOR SHARE, but the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE.

Return type:

Self

Returns:

Self for method chaining

for_no_key_update()

Add FOR NO KEY UPDATE clause (PostgreSQL-specific).

FOR NO KEY UPDATE is like FOR UPDATE, but the lock is weaker: it does not block SELECT FOR KEY SHARE commands that attempt to acquire a share lock on the same rows.

Return type:

Self

Returns:

Self for method chaining

More Examples

• Query Builder for query modifiers and joins.

• Query Builder for focused workflows.