DDL

Data Definition Language builders for tables, indexes, views, and schemas.

Base

class sqlspec.builder.DDLBuilder

Bases: QueryBuilder

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

__init__(dialect=None)

build(dialect=None)

Builds the SQL query string and parameters.

Parameters:

dialect (Union[str, Dialect, type[Dialect], None]) -- Optional dialect override. If provided, generates SQL for this dialect instead of the builder's default dialect.

Returns:

A dataclass containing the SQL string and parameters.

Return type:

BuiltQuery

to_statement(config=None)

Converts the built query into a SQL statement object.

Parameters:

config (StatementConfig | None) -- Optional SQL configuration.

Returns:

A SQL statement object.

Return type:

SQL

Tables

class sqlspec.builder.CreateTable

Bases: DDLBuilder

Builder for CREATE TABLE statements with columns and constraints.

__init__(table_name, dialect=None)

in_schema(schema_name)

Set the schema for the table.

Return type:

Self

if_not_exists()

Add IF NOT EXISTS clause.

Return type:

Self

temporary()

Create a temporary table.

Return type:

Self

like(source_table)

Create table LIKE another table.

Return type:

Self

tablespace(name)

Set tablespace for the table.

Return type:

Self

partition_by(partition_spec)

Set partitioning specification.

Return type:

Self

property columns: list[ColumnDefinition]

Get the list of column definitions for this table.

Returns:

List of ColumnDefinition objects.

column(name, dtype, default=None, not_null=False, primary_key=False, unique=False, auto_increment=False, comment=None, check=None, generated=None, collate=None)

Add a column definition to the table.

Return type:

Self

primary_key_constraint(columns, name=None)

Add a primary key constraint.

Return type:

Self

foreign_key_constraint(columns, references_table, references_columns, name=None, on_delete=None, on_update=None, deferrable=False, initially_deferred=False)

Add a foreign key constraint.

Return type:

Self

unique_constraint(columns, name=None)

Add a unique constraint.

Return type:

Self

check_constraint(condition, name=None)

Add a check constraint.

Return type:

Self

engine(engine_name)

Set storage engine (MySQL/MariaDB).

Return type:

Self

charset(charset_name)

Set character set.

Return type:

Self

collate(collation)

Set table collation.

Return type:

Self

comment(comment_text)

Set table comment.

Return type:

Self

with_option(key, value)

Add custom table option.

Return type:

Self

class sqlspec.builder.CreateTableAsSelect

Bases: DDLBuilder

Builder for CREATE TABLE [IF NOT EXISTS] ... AS SELECT ... (CTAS).

- name(table_name

str): Set the table name.

- if_not_exists()

Add IF NOT EXISTS.

- columns(*cols

str): Set explicit column list (optional).

- as_select(select_query)

Set the SELECT source (SQL, SelectBuilder, or str).

__init__(dialect=None)

class sqlspec.builder.AlterTable

Bases: DDLBuilder

Builder for ALTER TABLE operations.

__init__(table_name, dialect=None)

if_exists()

Add IF EXISTS clause.

Return type:

Self

add_column(name, dtype, default=None, not_null=False, unique=False, comment=None, after=None, first=False)

Add a new column to the table.

Return type:

Self

drop_column(name, cascade=False)

Drop a column from the table.

Return type:

Self

alter_column_type(name, new_type, using=None)

Change the type of an existing column.

Return type:

Self

rename_column(old_name, new_name)

Rename a column.

Return type:

Self

add_constraint(constraint_type, columns=None, name=None, references_table=None, references_columns=None, condition=None, on_delete=None, on_update=None)

Add a constraint to the table.

Parameters:

• constraint_type (str) -- Type of constraint ('PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE', 'CHECK')

• columns (str | list[str] | None) -- Column(s) for the constraint (not needed for CHECK)

• name (str | None) -- Optional constraint name

• references_table (str | None) -- Table referenced by foreign key

• references_columns (str | list[str] | None) -- Columns referenced by foreign key

• condition (str | ColumnExpression | None) -- CHECK constraint condition

• on_delete (str | None) -- Foreign key ON DELETE action

• on_update (str | None) -- Foreign key ON UPDATE action

Return type:

Self

drop_constraint(name, cascade=False)

Drop a constraint from the table.

Return type:

Self

set_not_null(column)

Set a column to NOT NULL.

Return type:

Self

drop_not_null(column)

Remove NOT NULL constraint from a column.

Return type:

Self

in_schema(schema_name)

Set the schema for the table.

Return type:

Self

set_column_default(column, value)

Set the default value for a column.

Return type:

Self

drop_column_default(column)

Remove the default value from a column.

Return type:

Self

class sqlspec.builder.DropTable

Bases: DDLBuilder, _DropDDLMixin

Builder for DROP TABLE [IF EXISTS] ... [CASCADE|RESTRICT].

__init__(table_name, dialect=None)

Initialize DROP TABLE with table name.

Parameters:

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

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

class sqlspec.builder.RenameTable

Bases: DDLBuilder

Builder for ALTER TABLE ... RENAME TO ... statements.

__init__(old_name, dialect=None)

Initialize RENAME TABLE with old name.

Parameters:

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

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

class sqlspec.builder.Truncate

Bases: DDLBuilder

Builder for TRUNCATE TABLE ... [CASCADE|RESTRICT] [RESTART IDENTITY|CONTINUE IDENTITY].

__init__(table_name, dialect=None)

Initialize TRUNCATE with table name.

Parameters:

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

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

class sqlspec.builder.CommentOn

Bases: DDLBuilder

Builder for COMMENT ON ... IS ... statements.

__init__(dialect=None)

Initialize COMMENT ON builder.

Parameters:

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

Column and Constraint Definitions

class sqlspec.builder._ddl.ColumnDefinition

Bases: object

Column definition for CREATE TABLE.

__init__(name, dtype, default=None, not_null=False, primary_key=False, unique=False, auto_increment=False, comment=None, check=None, generated=None, collate=None)

class sqlspec.builder._ddl.ConstraintDefinition

Bases: object

Constraint definition for CREATE TABLE.

__init__(constraint_type, name=None, columns=None, references_table=None, references_columns=None, condition=None, condition_expr=None, on_delete=None, on_update=None, deferrable=False, initially_deferred=False)

class sqlspec.builder._ddl.AlterOperation

Bases: object

Represents a single ALTER TABLE operation.

__init__(operation_type, column_name=None, column_definition=None, constraint_name=None, constraint_definition=None, new_type=None, new_name=None, after_column=None, first=False, using_expression=None)

Indexes

class sqlspec.builder.CreateIndex

Bases: DDLBuilder

Builder for CREATE [UNIQUE] INDEX [IF NOT EXISTS] ... ON ... (...).

__init__(index_name, dialect=None)

Initialize CREATE INDEX with index name.

Parameters:

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

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

class sqlspec.builder.DropIndex

Bases: DDLBuilder, _DropDDLMixin

Builder for DROP INDEX [IF EXISTS] ... [ON table] [CASCADE|RESTRICT].

__init__(index_name, dialect=None)

Initialize DROP INDEX with index name.

Parameters:

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

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

Views

class sqlspec.builder.CreateView

Bases: DDLBuilder

Builder for CREATE VIEW [IF NOT EXISTS] ... AS SELECT ...

__init__(view_name, dialect=None)

Initialize CREATE VIEW with view name.

Parameters:

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

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

class sqlspec.builder.CreateMaterializedView

Bases: DDLBuilder

Builder for CREATE MATERIALIZED VIEW [IF NOT EXISTS] ... AS SELECT ...

__init__(view_name, dialect=None)

Initialize CREATE MATERIALIZED VIEW with view name.

Parameters:

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

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

class sqlspec.builder.DropView

Bases: DDLBuilder, _DropDDLMixin

Builder for DROP VIEW [IF EXISTS] ... [CASCADE|RESTRICT].

__init__(view_name, dialect=None)

Initialize DROP VIEW with view name.

Parameters:

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

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

class sqlspec.builder.DropMaterializedView

Bases: DDLBuilder, _DropDDLMixin

Builder for DROP MATERIALIZED VIEW [IF EXISTS] ... [CASCADE|RESTRICT].

__init__(view_name, dialect=None)

Initialize DROP MATERIALIZED VIEW with view name.

Parameters:

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

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

name(view_name)

Set the materialized view name.

Return type:

Self

Schemas

class sqlspec.builder.CreateSchema

Bases: DDLBuilder

Builder for CREATE SCHEMA [IF NOT EXISTS] schema_name [AUTHORIZATION user_name].

__init__(schema_name, dialect=None)

Initialize CREATE SCHEMA with schema name.

Parameters:

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

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

class sqlspec.builder.DropSchema

Bases: DDLBuilder, _DropDDLMixin

Builder for DROP SCHEMA [IF EXISTS] ... [CASCADE|RESTRICT].

__init__(schema_name, dialect=None)

Initialize DROP SCHEMA with schema name.

Parameters:

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

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