Schema Synchronization Tool
Relevant source files
The following files were used as context for generating this wiki page:
Introduction
The Schema Synchronization Tool, implemented primarily via the sea-schema-sync crate, is a specialized variant of the SeaSchema library designed for synchronous execution environments. Its primary purpose is to provide SQL schema definition, discovery, and writing capabilities for MySQL, PostgreSQL, and SQLite without relying on asynchronous runtimes like tokio or async-std. Sources: README.md:1-10, sea-schema-sync/Cargo.toml:1-25
This tool serves as a bridge between live database instances and programmatic schema representations. It enables developers to discover existing database structures (tables, columns, indexes, and constraints) and convert these definitions back into SQL statements or SeaQuery structures. The sea-schema-sync version specifically utilizes rusqlite and synchronous database drivers to facilitate operations in environments where async overhead is undesirable. Sources: sea-schema-sync/src/lib.rs:1-35, build-tools/make-sync.sh:1-20
Architecture and Components
The synchronization tool is structured into backend-specific modules that mirror the core SeaSchema library but operate synchronously. The architecture relies on three primary pillars: def (type definitions), discovery (live schema extraction), and writer (SQL generation). Sources: README.md:16-25, sea-schema-sync/src/lib.rs:4-15
Core Modules and Backend Support
| Module | Description | Supported Backends |
|---|---|---|
def | Fundamental type definitions representing tables, columns, and indexes. | MySQL, Postgres, SQLite |
discovery | Logic to query INFORMATION_SCHEMA or system tables to build a Schema. | MySQL, Postgres, SQLite |
writer | Logic to export Schema definitions into SeaQuery and raw SQL statements. | MySQL, Postgres, SQLite |
rusqlite_types | Synchronous database connection abstractions for the sync crate. | SQLite (via rusqlite) |
Sources: README.md:16-25, sea-schema-sync/Cargo.toml:35-55, sea-schema-sync/src/lib.rs:18-28
Synchronization Transformation Logic
The sea-schema-sync crate is maintained through an automated transformation process. A build script (make-sync.sh) converts the primary asynchronous codebase into synchronous code by stripping async/await keywords and replacing sqlx dependencies with rusqlite counterparts. Sources: build-tools/make-sync.sh:10-25
The diagram shows the transformation flow from the asynchronous core library to the synchronous synchronization tool. Sources: build-tools/make-sync.sh:1-29, sea-schema-sync/src/lib.rs:20-30
Schema Discovery Logic
Discovery is the process of connecting to a live database and constructing a comprehensive Schema object. The SchemaDiscovery struct coordinates this process by executing specific metadata queries. Sources: sea-schema-sync/src/mysql/discovery/mod.rs:25-35, sea-schema-sync/src/postgres/discovery/mod.rs:28-40
MySQL Discovery Flow
For MySQL, the tool first discovers the system version and then iterates through tables to extract column, index, and foreign key metadata. Sources: sea-schema-sync/src/mysql/discovery/mod.rs:55-70
This sequence illustrates the top-down discovery process used for MySQL backends. Sources: sea-schema-sync/src/mysql/discovery/mod.rs:56-110
PostgreSQL Discovery Flow
Postgres discovery includes an additional step for user-defined types (enums). The discovery logic maps enum variants into an EnumVariantMap which is then used to resolve column types during table discovery. Sources: sea-schema-sync/src/postgres/discovery/mod.rs:51-65
Key functions include:
discover_enums_with: Extracts custom enums and their labels. Sources: sea-schema-sync/src/postgres/discovery/mod.rs:176-200discover_constraints_with: Aggregates Check, NotNull, PrimaryKey, and Reference constraints. Sources: sea-schema-sync/src/postgres/discovery/mod.rs:129-155discover_unique_indexes_with: Specifically parses unique constraints and partial indexes. Sources: sea-schema-sync/src/postgres/discovery/mod.rs:157-174
Data Models and Traits
The tool relies on a unified internal representation of database entities. Backends implement discovery and writing for these shared definitions. Sources: README.md:30-100, sea-schema-sync/src/mysql/discovery/mod.rs:5-15
Table Definition Structure
A TableDef is the primary unit of schema synchronization, containing all metadata required to recreate a table. Sources: sea-schema-sync/src/mysql/discovery/mod.rs:95-105, sea-schema-sync/src/postgres/discovery/mod.rs:95-115
| Component | Content Description |
|---|---|
info | Basic metadata like table name, engine, and character set. |
columns | List of ColumnInfo including data type, nullability, and defaults. |
indexes | List of IndexInfo including unique status and BTree/Hash types. |
foreign_keys | Relationships, including referenced tables and ON DELETE/UPDATE actions. |
constraints | (Postgres) Check, Exclusion, and NotNull constraints. |
Sources: README.md:40-100, sea-schema-sync/src/postgres/discovery/mod.rs:105-115
Connection and Execution
The Connection trait abstracts the underlying database driver. In the sync tool, this is typically backed by rusqlite_types::Executor. Sources: sea-schema-sync/src/lib.rs:30-38, sea-schema-sync/src/mysql/discovery/mod.rs:20-25
// Example of discovering a SQLite schema synchronously
let url = "sqlite::memory:";
let connection = connect_sqlite(&url).unwrap();
let schema_discovery = SchemaDiscovery::new(connection);
let schema = schema_discovery.discover().unwrap();
Sources: sea-schema-sync/tests/live/sqlite/src/main.rs:255-260
Summary
The Schema Synchronization Tool provides a robust, synchronous API for database schema management within the SeaQL ecosystem. By automating the conversion from the async SeaSchema core, it maintains feature parity for MySQL, Postgres, and SQLite backends while allowing for integration into synchronous Rust applications. Its primary strengths lie in its deep discovery capabilities, which accurately map database-specific features like MySQL's ON UPDATE attributes and Postgres's custom ENUM types into a unified Rust-native model. Sources: README.md:1-25, sea-schema-sync/Cargo.toml:1-15, build-tools/make-sync.sh:1-29