Skip to content

DDL Builders

from sqlspec.builder import (
    AlterOperation,
    AlterTable,
    ColumnDefinition,
    CommentOn,
    ConstraintDefinition,
    CreateIndex,
    CreateMaterializedView,
    CreateSchema,
    CreateSequence,
    CreateTable,
    CreateTableAsSelect,
    CreateView,
    DDLBuilder,
    DropIndex,
    DropMaterializedView,
    DropSchema,
    DropSequence,
    DropTable,
    DropView,
    RenameTable,
    Truncate,
    compile_table_ddl_results,
    create_temporal_table,
    register_version_generators,
)

Schema, sequence, table, column, constraint, index, view, materialized view, and metadata-to-DDL builders. See DML, Upserts, MERGE and DDL Schema Builders for usage examples.

Table, Sequence and Schema DDL

DDLBuilder

Bases: QueryBuilder

Base class for DDL builders (CREATE, DROP, ALTER, etc).

CreateTable

Bases: DDLBuilder, _IfNotExistsDDLMixin

Attributes:

Name Type Description
columns list[ColumnDefinition]

Get the list of column definitions for this table.

in_schema

temporary

like

tablespace

partition_by

column

primary_key_constraint

foreign_key_constraint

unique_constraint

check_constraint

engine

charset

collate

comment

with_option

from_metadata

from_arrow_schema

from_records

CreateTableAsSelect

Bases: DDLBuilder, _IfNotExistsDDLMixin

name
columns
as_select

AlterTable

Bases: DDLBuilder, _IfExistsDDLMixin

add_column

drop_column

alter_column_type

rename_column

add_constraint

drop_constraint

set_not_null

drop_not_null

in_schema

set_column_default

drop_column_default

DropTable

Bases: _SingleObjectDropBuilder

table

RenameTable

Bases: DDLBuilder

table
to

Truncate

Bases: DDLBuilder, _CascadeRestrictDDLMixin

table
restart_identity
continue_identity

CreateSchema

Bases: DDLBuilder, _IfNotExistsDDLMixin

name
authorization

DropSchema

Bases: _SingleObjectDropBuilder

name

CreateSequence

Bases: DDLBuilder

name
in_schema
if_not_exists
start_with
increment_by
min_value
max_value
cycle
no_cycle
cache

DropSequence

Bases: DDLBuilder

name
in_schema
if_exists
cascade

compile_table_ddl_results(table_name, columns, *, schema=None, constraints=None, foreign_keys=None, indexes=None, table_comment=None, column_comments=None, dialect=None, source_dialect=None, object_type='table', warnings=())

Compile structured catalog metadata into a generated DDLResult for a table.

Catalog attributes that cannot be reproduced on dialect (unreported generated expressions, partial-index predicates, expression index keys, unsupported referential actions, unparsable defaults) are listed in DDLResult.warnings and the result's fidelity becomes LOSSY instead of GENERATED.

Columns, Constraints and Comments

ColumnDefinition

Column definition for CREATE TABLE.

Attributes:

Name Type Description
name
dtype
default
not_null
primary_key
unique
auto_increment
comment
check
generated
collate
identity
generated_expression
persisted

ConstraintDefinition

Constraint definition for CREATE TABLE.

Attributes:

Name Type Description
constraint_type
name
columns
references_table
references_columns
condition
condition_expr
on_delete
on_update
deferrable
initially_deferred

AlterOperation

Represents a single ALTER TABLE operation.

Attributes:

Name Type Description
operation_type
column_name
column_definition
constraint_name
constraint_definition
new_type
new_name
after_column
first
using_expression

CommentOn

Bases: DDLBuilder

in_schema
on_table
on_column
is_

create_temporal_table(table, as_of, kind=None)

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

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 lazily when temporal tables or joins are constructed.

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

Index and View DDL

CreateIndex

Bases: DDLBuilder, _IfNotExistsDDLMixin

name
in_schema
on
on_table
columns
include
expressions
unique
using
where
clustered
from_metadata

DropIndex

Bases: _SingleObjectDropBuilder

name
on_table

CreateView

Bases: DDLBuilder, _IfNotExistsDDLMixin

name
columns
as_select
with_hint

DropView

Bases: _SingleObjectDropBuilder

name

CreateMaterializedView

Bases: DDLBuilder, _IfNotExistsDDLMixin

name
columns
as_select
with_data
no_data
refresh_mode
storage_parameter
tablespace
using_index
with_hint

DropMaterializedView

Bases: _SingleObjectDropBuilder

name