Welcome to SeaSchema

Relevant source files

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

• README.md
• Cargo.toml
• CHANGELOG.md
• src/mysql/discovery/mod.rs
• src/postgres/discovery/mod.rs
• sea-schema-sync/Cargo.toml
• tests/live/mysql/src/main.rs
• sea-schema-sync/tests/live/sqlite/src/main.rs

SeaSchema is a comprehensive Rust library designed to manage database schemas for MySQL, PostgreSQL, and SQLite. It serves two primary functions: providing detailed type definitions that map closely to database-specific structures and offering utilities for the automated discovery of existing database schemas.

The project is a member of the SeaQL ecosystem, integrating tightly with sea-query to facilitate the translation between discovered schema definitions and executable SQL statements or migration scripts.
Sources: README.md:16-20, Cargo.toml:44-46

Core Architecture

The SeaSchema codebase is modularized to separate concerns between data definition, database querying, result parsing, and output generation.

Module Breakdown

Module	Responsibility
def	Core type definitions representing tables, columns, indexes, and constraints.
query	Generation of SQL queries to inspect INFORMATION_SCHEMA or system catalogs.
parser	Logic to transform raw database query results into structured definitions.
discovery	Orchestration of connection management and full schema inspection.
writer	Translating structured definitions back into sea-query statements or raw SQL.

Sources: README.md:28-33

The following diagram illustrates the internal relationship between these modules:

The architecture ensures that the discovery process is decoupled from the final output format, allowing discovered schemas to be serialized to JSON or converted to SQL.
Sources: README.md:28-35

Schema Discovery Process

Discovery is the process of connecting to a live database and generating a Schema object that represents the structure of the database. This process varies slightly between database dialects due to how they store metadata.

MySQL Discovery Flow

For MySQL, the SchemaDiscovery engine first identifies the system version to handle dialect variations (e.g., MariaDB vs. MySQL 5.6/8.0). It then iterates through the schema to fetch table metadata, column details, indexes, and foreign keys.
Sources: src/mysql/discovery/mod.rs:56-78

PostgreSQL Discovery Flow

Postgres discovery includes an additional step to discover User-Defined Types (UDTs) like Enums, which are then used to correctly map column types during table inspection.
Sources: src/postgres/discovery/mod.rs:60-75

1. Enum Discovery: Queries system catalogs for custom enum types and their variants.
2. Table Discovery: Identifies all tables within the target schema.
3. Constraint Resolution: Unlike MySQL, Postgres discovery explicitly separates check constraints, not-null constraints, and exclusion constraints during the folding of metadata results.
   Sources: src/postgres/discovery/mod.rs:87-122

Feature Support Matrix

SeaSchema utilizes Cargo features to manage dependencies on specific database drivers and runtimes.

Feature	Description	Relevant Dependencies
mysql	MySQL/MariaDB support	sea-query/backend-mysql
postgres	PostgreSQL support	sea-query/backend-postgres
sqlite	SQLite support	sea-query/backend-sqlite
sqlx-all	Async support for all DBs via SQLx	sqlx, sea-query-sqlx
rusqlite	Synchronous SQLite support	rusqlite, sea-query-rusqlite
with-serde	JSON Serialization/Deserialization	serde
postgres-vector	Support for pgvector extension	sea-query/postgres-vector

Sources: Cargo.toml:44-70, sea-schema-sync/Cargo.toml:39-58, CHANGELOG.md:18-20

Implementation Example: Schema Definitions

When a table is discovered, it is represented as a TableDef. Below is a simplified representation of the data structure hierarchy found in the discovery logic.

SQLite Specifics

In SQLite, integer types are frequently mapped to BigInteger during writing to ensure compatibility with various size constraints. Recent updates also improved the discovery of all indexes, including those generated by primary keys.
Sources: CHANGELOG.md:12-14, sea-schema-sync/tests/live/sqlite/src/main.rs:40-65

Usage in Live Environments

The library provides an Executor trait (or uses sqlx::Pool) to facilitate discovery against live instances. A typical discovery pattern involves:

1. Establishing a connection pool (e.g., MySqlPool or PgPool).
2. Instantiating SchemaDiscovery with the pool and schema name.
3. Calling .discover().await to obtain the structured Schema.
   Sources: tests/live/mysql/src/main.rs:50-55, src/mysql/discovery/mod.rs:36-43

// Example: Discovery usage for MySQL
let schema_discovery = SchemaDiscovery::new(connection, "sea-schema");
let schema = schema_discovery
    .discover()
    .await
    .expect("Error discovering schema");

Sources: tests/live/mysql/src/main.rs:50-55

Summary

SeaSchema acts as the metadata layer for the SeaQL ecosystem. By providing robust discovery tools for MySQL, Postgres, and SQLite, it enables developers to programmatically inspect database structures and translate them into actionable Rust objects or SQL definitions via SeaQuery. Its support for modern extensions like pgvector and complex constraints ensures it remains suitable for advanced database management tasks.
Sources: README.md:16-25, CHANGELOG.md:18-20