Information Schema Queries

Relevant source files

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

• src/mysql/query/mod.rs
• src/mysql/query/schema.rs
• src/postgres/query/schema.rs
• src/mysql/discovery/mod.rs
• src/postgres/discovery/mod.rs
• sea-schema-sync/src/mysql/query/schema.rs

Information Schema Queries are a core component of the SeaSchema library used to perform database introspection. These queries target the standard information_schema tables (and database-specific system catalogs) to extract metadata about tables, columns, indexes, constraints, and data types. This functionality serves as the foundation for the "discovery" module, which converts live database states into structured Rust definitions.

The system is designed with a provider-agnostic interface where a SchemaQueryBuilder generates SQL statements specific to the target database backend (MySQL or PostgreSQL). These queries are then executed by a connection handler to retrieve raw results for further parsing into high-level schema objects.

Sources: README.md, src/mysql/discovery/mod.rs:1-20

Architecture and Discovery Flow

The introspection process follows a tiered approach, starting from the system level and drilling down to specific table attributes. The SchemaDiscovery struct manages this flow, utilizing the SchemaQueryBuilder to generate the necessary SQL.

General Discovery Sequence

1. System Information: Retrieve database version and system-specific metadata.
2. Table Listing: Query all tables and views within a specified schema.
3. Attribute Extraction: For each table, perform sub-queries to discover:
   • Columns (data types, nullability, defaults)
   • Indexes (uniqueness, column ordering)
   • Foreign Keys (referenced tables, update/delete actions)
   • Constraints (checks, primary keys, exclusions)

The following diagram illustrates the relationship between discovery components:

Backend Implementations

MySQL Information Schema

MySQL discovery relies on the InformationSchema enum to identify standard tables. It utilizes specialized query modules for different metadata types, such as char_set, column, and version.

Entity	Information Schema Table	Description
Tables	TABLES	Basic table metadata and storage engine info
Columns	COLUMNS	Data types, precision, and default values
Indexes	STATISTICS	Index names, uniqueness, and column parts
Foreign Keys	KEY_COLUMN_USAGE	Referenced tables and columns
Constraints	REFERENTIAL_CONSTRAINTS	Cascade actions and constraint names

Sources: src/mysql/query/schema.rs:16-27, src/mysql/query/mod.rs:3-17

PostgreSQL System Catalogs

PostgreSQL implementation queries both the information_schema and internal pg_catalog tables (like pg_class and pg_inherits). This allows SeaSchema to distinguish between ordinary tables, views, materialized views, and partitioned tables.

Postgres-specific logic includes a filter for relkind to identify:

• r: Ordinary table
• v: View
• m: Materialized view
• p: Partitioned table
• f: Foreign table

Sources: src/postgres/query/schema.rs:27-56, src/postgres/discovery/mod.rs:90-110

Key Components and Data Structures

SchemaQueryBuilder

The SchemaQueryBuilder is the primary interface for generating introspection SQL. In MySQL, it optionally stores SystemInfo to adjust query logic based on the database version (e.g., handling differences between MySQL 5.7 and 8.0).

#[derive(Debug, Default)]
pub struct SchemaQueryBuilder {
    pub system: SystemInfo,
}

impl SchemaQueryBuilder {
    pub fn new(system: SystemInfo) -> Self {
        Self { system }
    }
}

Sources: src/mysql/query/schema.rs:4-14

InformationSchema Identifiers

The InformationSchema enum provides sea_query::Iden implementations for the standard tables defined in the SQL standard.

#[derive(Debug, Clone, Copy, sea_query::Iden)]
pub enum InformationSchema {
    #[iden = "information_schema"]
    Schema,
    Tables,
    Columns,
    Statistics,
    KeyColumnUsage,
    ReferentialConstraints,
    #[iden = "collation_character_set_applicability"]
    CollationCharacterSet,
}

Sources: sea-schema-sync/src/mysql/query/schema.rs:16-28

Discovery Execution Logic

The discovery process is coordinated by the discover_with method. It establishes a sequence where dependencies are resolved before dependent objects are queried (e.g., discovering enums before columns that might use those enums).

Conclusion

Information Schema Queries provide the vital bridge between a live SQL database and the SeaSchema type system. By leveraging structured queries against system catalogs and implementing specialized builders for different SQL dialects, the system ensures accurate discovery of complex database structures including partitions, custom enums, and intricate foreign key relationships.