PostgreSQL Support

Relevant source files

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

• src/postgres/mod.rs
• src/lib.rs
• Cargo.toml
• README.md
• CHANGELOG.md
• tests/live/postgres/src/main.rs
• tests/sakila/postgres/sakila-schema.sql

PostgreSQL support in SeaSchema provides a comprehensive suite for managing database structures, including schema definition, discovery, and writing. It maps PostgreSQL-specific database features closely to Rust type definitions, allowing developers to interact with live PostgreSQL instances to extract metadata or generate SQL statements programmatically.

The implementation is modular, separating the logic for querying the information_schema, parsing results, and writing back to sea-query statements. This ensures that complex PostgreSQL features such as custom enums, partial indexes, and schema-specific foreign keys are handled accurately.

Sources: README.md:16-23, src/postgres/mod.rs:1-25

Module Architecture

The PostgreSQL implementation follows the general crate architecture but is specialized for the Postgres backend. It is organized into several sub-modules that handle different stages of the schema management lifecycle.

Core Components

Module	Description
def	Contains type definitions representing Postgres-specific structures like Tables, Columns, and Enums.
discovery	Logic for connecting to a live Postgres database and extracting its Schema.
query	Handles the construction of SQL queries targeting PostgreSQL metadata tables.
parser	Responsible for parsing the raw results from metadata queries into structured definitions.
writer	Translates internal definitions into sea-query objects or raw SQL strings.
probe	Provides utilities to check for the existence of tables, columns, or indexes.

Sources: src/postgres/mod.rs:4-25, README.md:25-30

Logic Flow for Discovery

The following diagram illustrates the high-level flow when a developer initiates a schema discovery on a PostgreSQL database:

The diagram shows how SchemaDiscovery orchestrates multiple specialized sub-tasks to build a complete representation of the database.
Sources: tests/live/postgres/src/main.rs:100-145, src/postgres/mod.rs:7-10

Data Type Mapping

SeaSchema supports a wide array of PostgreSQL-specific data types, ensuring that discovery and writing are technically accurate. This includes support for standard SQL types and advanced Postgres extensions.

Supported Postgres Types

Recent updates have expanded support for:

• Vector Extension: Mapping postgres-vector types for AI and similarity search workloads.
• Arrays: Support for both standard type arrays and custom enum arrays.
• Network/Text: Specialized types like citext (case-insensitive text).
• Time: Proper handling of TimestampWithTimeZone and interval types.

Sources: CHANGELOG.md:17-18, CHANGELOG.md:40-45, CHANGELOG.md:83-84, tests/live/postgres/src/main.rs:343-360

Type Discovery Example

When discovering a table, the system maps database types to Rust enums. For instance, a Postgres array of integers is identified as ColumnType::Array(ColumnType::Integer).

fn create_collection_table() -> TableCreateStatement {
    Table::create()
        .table("collection")
        .col(
            ColumnDef::new("integers")
                .array(ColumnType::Integer)
                .not_null(),
        )
        .col(ColumnDef::new("integers_opt").array(ColumnType::Integer))
        .col(
            ColumnDef::new("case_insensitive_text")
                .custom("citext")
                .not_null(),
        )
        .to_owned()
}

Sources: tests/live/postgres/src/main.rs:343-360, CHANGELOG.md:144-145

Advanced Discovery Features

PostgreSQL support includes logic to handle complex relational structures and metadata that vary by schema.

Enum Discovery

Enums in PostgreSQL are user-defined types. SeaSchema can discover these globally or within specific schemas, capturing their variants in the correct order.

• Schema-Specific: Discovery logic filters enums by the targeted schema to avoid conflicts.
• Ordering: The system ensures that the order of enum values matches the database definition.

Sources: CHANGELOG.md:21-22, CHANGELOG.md:31-32, tests/live/postgres/src/main.rs:136-150

Constraints and Indexes

Postgres support covers various index and constraint types, ensuring integrity is maintained during migrations or discovery.

This relationship shows how table definitions aggregate various metadata components discovered from PostgreSQL.
Sources: CHANGELOG.md:15-16, README.md:100-150, tests/live/postgres/src/main.rs:430-445

• Partial Indexes: Support for is_partial attribute in index discovery.
• Identity Columns: Support for is_identity to track auto-incrementing columns.
• Foreign Keys: Handles composite foreign keys and ensures they are schema-specific.

Sources: CHANGELOG.md:15-16, CHANGELOG.md:23-24, CHANGELOG.md:174-175

Live Testing and Verification

The tests/live/postgres module provides a comprehensive suite to verify PostgreSQL support by:

1. Creating a complex schema with enums, partitioned tables, and foreign keys.
2. Running SchemaDiscovery to extract the definition.
3. Generating SQL from the discovered metadata.
4. Comparing the original SQL with the generated SQL to ensure 100% fidelity.

Sources: tests/live/postgres/src/main.rs:18-120, tests/sakila/postgres/sakila-schema.sql:1-100

Conclusion

PostgreSQL support in SeaSchema is a robust implementation that bridges the gap between Rust type safety and Postgres's advanced relational features. By providing specialized modules for discovery and writing, it allows for accurate schema synchronization and management, supporting modern Postgres features like vectors, arrays, and complex constraints.

Sources: README.md:10-15, CHANGELOG.md:10-25