Table Management

Relevant source files

The following files were used as context for generating this wiki page:

• src/table/mod.rs
• src/table/create.rs
• src/table/column.rs
• src/backend/table_builder.rs
• src/backend/mysql/table.rs
• src/backend/postgres/table.rs
• src/table/alter.rs
• src/table/drop.rs

Table Management in SeaQuery provides a fluent, type-safe API for defining and manipulating database schemas. It serves as the Schema Definition Language (SDL) layer of the library, allowing developers to construct abstract syntax trees (ASTs) for DDL (Data Definition Language) operations such as creating, altering, renaming, truncating, and dropping tables across different database backends like MySQL, PostgreSQL, and SQLite.

The system is built around the Table helper struct, which acts as an entry point for generating various statement builders. These builders capture the intent of the schema change and are later translated into backend-specific SQL by a TableBuilder implementation.

Sources: src/table/mod.rs:1-12, src/lib.rs:563-577

Core Components and Architecture

The architecture follows a builder pattern where high-level statements are decoupled from the low-level SQL generation logic.

The Table Helper

The Table struct provides static methods to initiate different schema operations.

Method	Returns	Description
create()	TableCreateStatement	Initiates a table creation operation.
alter()	TableAlterStatement	Initiates a table alteration (add/modify/drop columns).
drop()	TableDropStatement	Initiates a table removal operation.
rename()	TableRenameStatement	Initiates a table renaming operation.
truncate()	TableTruncateStatement	Initiates a table truncation (clear data) operation.

Sources: src/table/mod.rs:48-72

Statement Builders

Each operation is represented by a dedicated struct that holds the configuration for that specific SQL command. These structs implement SchemaStatementBuilder, which enables them to be converted into SQL strings using a specific SchemaBuilder.

Table Creation

The TableCreateStatement allows for detailed configuration of new tables, including column definitions, constraints, and backend-specific options.

Column Definitions

Columns are defined using the ColumnDef struct. Developers specify the column name, data type, and various specifications (nullability, defaults, auto-increment, etc.).

Feature	Method	Description
Nullability	not_null(), null()	Defines if the column accepts null values.
Keys	primary_key(), unique_key()	Sets primary or unique constraints.
Behavior	auto_increment()	Enables auto-incrementing values.
Defaults	default(value)	Sets a default value or expression.
Generated	generated(expr, stored)	Defines a generated column (Postgres/MySQL).

Sources: src/table/column.rs:102-130, src/table/column.rs:345-360

Partitioning Support

SeaQuery supports table partitioning specifically for PostgreSQL and MySQL backends. This includes range, list, hash, and key partitioning strategies.

Backend Implementation Logic

The TableBuilder trait defines how schema statements are translated into SQL. Each database backend (MySQL, Postgres, SQLite) implements this trait to handle its unique syntax and limitations.

MySQL Implementation

MySQL implementation handles specific table options like ENGINE, CHARACTER SET, and COLLATE. It also supports MODIFY COLUMN syntax during alterations.
Sources: src/backend/mysql/table.rs:7-25, src/backend/mysql/table.rs:154-180

PostgreSQL Implementation

The Postgres builder handles complex types like Arrays, Cidr, Inet, and LTree. It utilizes GENERATED BY DEFAULT AS IDENTITY for auto-incrementing columns instead of the AUTO_INCREMENT keyword used in MySQL.
Sources: src/backend/postgres/table.rs:65-95, src/backend/postgres/table.rs:271-285

SQL Mapping Comparison

SeaQuery Type	MySQL	PostgreSQL	SQLite
String	varchar(255)	varchar	varchar
Boolean	bool	bool	boolean
JsonBinary	json	jsonb	jsonb_text
Uuid	binary(16)	uuid	uuid_text

Sources: src/table/column.rs:33-70

Table Alteration and Management

Management of existing tables is performed via Alter, Rename, Drop, and Truncate statements.

Alteration Flow

The TableAlterStatement collects a sequence of TableAlterOption variants, such as adding, modifying, or dropping columns and foreign keys.

Table Removal and Truncation

• Drop: Supports dropping multiple tables at once and includes optional IF EXISTS and CASCADE/RESTRICT modifiers.
• Truncate: Clears all data from a table. Note that SQLite does not natively support the TRUNCATE statement.

Sources: src/table/drop.rs:10-30, src/backend/table_builder.rs:205-225

Summary

Table Management in SeaQuery provides a robust abstraction for DDL operations. By leveraging a common TableBuilder trait, it ensures that schema definitions remain portable across different SQL engines while allowing for backend-specific optimizations like Postgres-specific types or MySQL table engines.