Schema Discovery Engine
Relevant source files
The following files were used as context for generating this wiki page:
The Schema Discovery Engine is a core module of the SeaSchema library designed to connect to live database instances and extract their structural definitions. It translates low-level database metadata—such as tables, columns, indexes, and constraints—into a unified Schema representation that can be used for schema management, synchronization, or code generation.
The engine supports MySQL, PostgreSQL, and SQLite, utilizing specific discovery logic for each backend to handle differences in system catalogs like INFORMATION_SCHEMA or PRAGMA commands. It provides both asynchronous (via sqlx) and synchronous (via rusqlite) discovery implementations depending on the enabled crate features.
Sources: README.md:16-24, src/mysql/discovery/mod.rs:35-50, src/postgres/discovery/mod.rs:46-64
Core Discovery Logic
The discovery process follows a hierarchical approach, starting from the global schema level and descending into specific table components. The engine first identifies the database system version and available tables, then iterates through each table to discover its internal structure.
Discovery Workflow
The following diagram illustrates the sequence of operations performed during a full schema discovery:
Backend Implementations
MySQL Discovery
In MySQL, the engine primarily queries the INFORMATION_SCHEMA. It extracts table metadata including the storage engine, character sets, and collation. A unique aspect of MySQL discovery is the handling of SystemInfo, which determines how column defaults and expressions are parsed based on the specific MySQL or MariaDB version.
- Key Components:
SchemaQueryBuilderfor generating MySQL-specific SQL, andparse_index_query_resultsfor index reconstruction. - Metadata Captured: Table Engine (e.g., InnoDB), Auto Increment values, Column Extras (e.g.,
on update CURRENT_TIMESTAMP).
Sources: src/mysql/discovery/mod.rs:1-25, sea-schema-sync/src/mysql/discovery/mod.rs:73-86
PostgreSQL Discovery
PostgreSQL discovery includes advanced support for User-Defined Types (UDTs) and Enums. Before discovering tables, the engine fetches all custom enums within the schema to create a map used for column type resolution.
SQLite Discovery
SQLite discovery utilizes a combination of sqlite_master table queries and PRAGMA commands. Since SQLite does not follow INFORMATION_SCHEMA standards, the engine manually checks for autoincrement behavior by searching the sql definition in sqlite_master.
| Feature | SQLite Discovery Method |
|---|---|
| Table List | Query SqliteMaster where type = 'table' |
| Column Info | PRAGMA table_xinfo('table_name') |
| Foreign Keys | PRAGMA foreign_key_list('table_name') |
| Indexes | PRAGMA index_list('table_name') |
| Autoincrement | Search sql text for "AUTOINCREMENT" |
Sources: sea-schema-sync/src/sqlite/def/table.rs:55-70, sea-schema-sync/src/sqlite/def/table.rs:141-160
Data Structures
The discovery engine populates a hierarchy of data structures defined in the backend-specific def modules.
Schema Hierarchy
Summary of Constraints and Relationships
The engine distinguishes between different types of database constraints during discovery. In PostgreSQL, these are categorized into Check, NotNull, PrimaryKey, References, and Exclusion constraints. In SQLite and MySQL, indexes and unique constraints are often discovered via separate metadata queries and then merged into the table definition.
Sources: src/postgres/discovery/mod.rs:88-109, sea-schema-sync/src/sqlite/def/table.rs:80-95
The Schema Discovery Engine ensures that the high-level Schema object accurately reflects the current state of the database, providing the necessary foundation for the Writer module to generate equivalent SQL or SeaQuery statements.