SQLx Integration

Relevant source files

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

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

SQLx Integration in SeaSchema provides the infrastructure to connect to live databases and perform automated schema discovery. By leveraging SQLx connection pools, the library can query system-specific tables (like INFORMATION_SCHEMA in MySQL and Postgres) to construct a high-level programmatic representation of the database structure, including tables, columns, indexes, and foreign keys.

This integration is primarily used in the discovery module, which bridges the gap between a live database connection and the library's internal definition types. It supports MySQL, PostgreSQL, and SQLite through specialized feature flags and runtime configurations.

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

Feature Configuration and Runtimes

The integration is modular and relies on several Cargo features to enable specific database drivers and async runtimes. SQLx is treated as an optional dependency that is activated when discovery features are required.

Database Driver Features

Users must enable specific sqlx-* features to include the necessary SQLx drivers:

Feature	Description	Included Dependencies
sqlx-mysql	Enables MySQL discovery via SQLx	mysql, sqlx/mysql, sea-query-sqlx/sqlx-mysql
sqlx-postgres	Enables Postgres discovery via SQLx	postgres, sqlx/postgres, sea-query-sqlx/sqlx-postgres
sqlx-sqlite	Enables SQLite discovery via SQLx	sqlite, sqlx/sqlite, sea-query-sqlx/sqlx-sqlite
sqlx-all	Enables all supported SQLx drivers	sqlx-mysql, sqlx-postgres, sqlx-sqlite

Sources: Cargo.toml:57-75

Async Runtime Support

SeaSchema supports multiple async runtimes and TLS backends through specialized features:

• Tokio: runtime-tokio, runtime-tokio-native-tls, runtime-tokio-rustls
• Actix: runtime-actix, runtime-actix-native-tls, runtime-actix-rustls
• Async-std: runtime-async-std, runtime-async-std-native-tls, runtime-async-std-rustls

Sources: Cargo.toml:76-114

Schema Discovery Architecture

The discovery process is centered around the SchemaDiscovery struct, which manages the database connection and orchestrates the sequence of queries required to build a full schema definition.

The diagram above shows the data flow from a raw SQLx pool to a fully populated Schema definition object.

Sources: src/mysql/discovery/mod.rs:34-160

Key Components

1. SchemaDiscovery: The entry point for discovery. It takes a SQLx pool (e.g., MySqlPool) and a schema name string.
2. Executor: An internal wrapper around the SQLx pool that abstracts the execution of sea-query statements into database rows.
3. Connection Trait: Provides an interface for query_all operations, allowing the discovery logic to remain agnostic of the underlying SQLx pool type.

Sources: src/mysql/discovery/mod.rs:21-32, src/mysql/discovery/executor.rs

Implementation Patterns

In practice, SQLx integration involves initializing a pool and passing it to the discovery engine. The library converts SQLx result rows into internal QueryResult types which are then parsed into definitions.

Database-Specific Usage

MySQL Discovery

In MySQL, discovery focuses on the INFORMATION_SCHEMA. The SchemaDiscovery implementation for MySQL queries system variables first to identify the database version before fetching table details.

// Example from MySQL live tests
let url = "mysql://root:root@localhost/sea-schema";
let connection = MySqlPool::connect(&url).await.unwrap();
let schema_discovery = SchemaDiscovery::new(connection, "sea-schema");
let schema = schema_discovery.discover().await.expect("Error");

Sources: tests/live/mysql/src/main.rs:56-62, src/mysql/discovery/mod.rs:65-80

Postgres Discovery

Postgres integration includes support for advanced types such as Enums and Arrays. It also handles schema-specific discovery within the Postgres search path (typically "public").

The sequence diagram illustrates how SchemaDiscovery interacts with a PgPool to retrieve both standard table metadata and custom Postgres types.

Sources: tests/live/postgres/src/main.rs:88-144, CHANGELOG.md:14-16

SQLite Discovery

For SQLite, the integration uses SQLx to query sqlite_master and PRAGMA functions. This includes mapping SQLite's dynamic typing to SeaSchema's more rigid definitions.

Sources: tests/live/sqlite/src/main.rs:150-160, CHANGELOG.md:41-42

Error Handling

The integration uses SqlxError to wrap database-related failures during discovery. Common errors include:

• PoolClosed: Attempting to discover using a pool that has been dropped or closed.
• RowNotFound: Failing to retrieve mandatory system information (e.g., version strings).
• Database Errors: SQL execution failures or permission issues when accessing metadata tables.

Sources: src/mysql/discovery/mod.rs:54-85

Conclusion

The SQLx integration is a core pillar of SeaSchema's discovery capabilities. By providing a common interface across MySqlPool, PgPool, and SqlitePool, it allows developers to reflect their database state into Rust code with minimal boilerplate. This integration supports a wide range of async runtimes and keeps the library compatible with the latest SQLx versions (v0.8.4 as of current implementation).

Sources: Cargo.toml:47, CHANGELOG.md:38