Testing Infrastructure

Relevant source files

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

• tests/live/sqlite/src/main.rs
• tests/live/postgres/src/main.rs
• tests/live/mysql/src/main.rs
• sea-schema-sync/tests/live/sqlite/src/main.rs
• Cargo.toml
• build-tools/make-sync.sh

The testing infrastructure of SeaSchema is designed to validate the accuracy of SQL schema discovery and writing across multiple database backends, including MySQL, PostgreSQL, and SQLite. It utilizes a "live" testing approach where a temporary database environment is established, schemas are programmatically created using sea-query, and the resulting metadata is discovered and compared against the original definitions.

This infrastructure is categorized into workspace members that handle specific testing scenarios: discovery tests focus on metadata extraction, writer tests validate the generation of SQL statements from internal definitions, and live tests perform end-to-end verification against active database instances.

Sources: Cargo.toml:3-15, README.md:27-31

Test Execution Workflow

The core logic for live tests follows a consistent pattern across all supported databases. The infrastructure automates the setup of the database, the execution of DDL statements, the discovery of the resulting schema, and the final assertion of correctness.

Typical Live Test Sequence

1. Environment Setup: Connection strings are retrieved from environment variables (e.g., DATABASE_URL_LIVE), falling back to local defaults or in-memory instances for SQLite.
2. Database Provisioning: The test suite drops existing test databases/tables and recreates them to ensure a clean state.
3. Schema Construction: Database tables are defined using TableCreateStatement from the sea-query crate.
4. Schema Discovery: The SchemaDiscovery module connects to the live database to inspect and parse the schema into internal TableDef structures.
5. Validation: The discovered schema is converted back to SQL and compared with the original sea-query definitions using pretty_assertions::assert_eq.

Sources: tests/live/mysql/src/main.rs:14-65, tests/live/sqlite/src/main.rs:24-60, tests/live/postgres/src/main.rs:18-70

Test Data Flow Diagram

The following diagram illustrates the flow from initial schema definition to the final assertion within a live test case.

Database-Specific Test Configurations

The infrastructure supports three primary database backends, each with specialized setup requirements handled in their respective tests/live modules.

Backend	Connection Utility	Discovery Component	Features Validated
MySQL	MySqlPool (sqlx)	mysql::discovery::SchemaDiscovery	Engines (InnoDB), Charsets, Enums, Foreign Keys
PostgreSQL	PgPool (sqlx)	postgres::discovery::SchemaDiscovery	Citext, Custom Enums, Arrays, Serial/Identity
SQLite	connect_sqlite	sqlite::discovery::SchemaDiscovery	Auto-increment, Primary Keys, Composite FKs

Sources: tests/live/mysql/src/main.rs:7-12, tests/live/postgres/src/main.rs:7-12, tests/live/sqlite/src/main.rs:7-14

PostgreSQL Specific Setup

PostgreSQL tests include specialized steps for handling extensions and custom types. For instance, tests explicitly create the citext extension and custom enumerations before table creation to verify that SchemaDiscovery can accurately map these non-standard types.

Sources: tests/live/postgres/src/main.rs:32-52

MySQL SQL Stripping

Due to variations in how MySQL returns metadata (e.g., case sensitivity of types or default lengths like int(11) vs int), the testing infrastructure includes a strip_generated_sql utility. This uses regular expressions to normalize the generated SQL before assertion, ensuring that cosmetic differences in type representation do not fail the tests.

Sources: tests/live/mysql/src/main.rs:88-100

Sync and Async Testing Variants

SeaSchema maintains two versions of its testing infrastructure: an asynchronous version (using sqlx and async-std/tokio) and a synchronous version (using rusqlite).

The Sync Conversion Process

The synchronous testing infrastructure located in sea-schema-sync is generated from the main source tree using a build script. This script performs automated string replacements to convert asynchronous Rust code into synchronous equivalents.

Comprehensive Test Case: The "Bakery" Schema

A recurring complex schema used across all live tests is the "Bakery" domain. It serves as a benchmark for verifying:

• Foreign Key Constraints: Links between baker, bakery, order, and customer.
• Primary Keys: Both single-column auto-increments and composite keys (e.g., cakes_bakers table).
• Indices: Unique indices on columns like cake_id in the lineitem table.
• Complex Types: JSON columns, Decimals with precision, and Boolean flags.

Sources: tests/live/mysql/src/main.rs:118-200, tests/live/sqlite/src/main.rs:240-320, tests/live/postgres/src/main.rs:145-220