SQLite Support

Relevant source files

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

• src/sqlite/def/table.rs
• src/sqlite/def/column.rs
• src/lib.rs
• README.md
• CHANGELOG.md
• sea-schema-sync/src/sqlite/def/table.rs
• tests/live/sqlite/src/main.rs

SQLite support in SeaSchema provides a comprehensive suite for database schema management, including type definitions, discovery of existing schemas from live databases, and writing schemas into SQL or SeaQuery statements. It maps SQLite-specific behaviors—such as its dynamic typing and PRAGMA-based introspection—into the project's unified schema representation.

The system is architected to handle both asynchronous operations (via sqlx-sqlite) and synchronous operations (via rusqlite), ensuring compatibility across different Rust runtimes. This support includes the discovery of tables, columns, indexes, and foreign keys, as well as the specialized handling of SQLite's AUTOINCREMENT and hidden columns.

Sources: README.md:12-25, src/lib.rs:8-12, CHANGELOG.md:103-107

Architecture and Data Structures

The SQLite module is centered around the TableDef structure, which aggregates all metadata discovered from an SQLite database.

The diagram above illustrates the relationship between the primary table definition and its child components like columns and indexes.
Sources: src/sqlite/def/table.rs:18-30, src/sqlite/def/column.rs:10-23, src/sqlite/def/column.rs:77-87

Core Components Summary

Component	Description	Source
TableDef	The primary container for table metadata, including constraints and autoincrement status.	src/sqlite/def/table.rs:18-30
ColumnInfo	Detailed column metadata including types, nullability, and visibility (hidden/virtual).	src/sqlite/def/column.rs:10-23
IndexInfo	Represents both explicit indexes and implicit unique/primary key constraints.	src/sqlite/def/column.rs:77-87
ForeignKeysInfo	Stores relationship metadata including ON UPDATE and ON DELETE actions.	src/sqlite/def/column.rs:207-217

Schema Discovery Logic

Discovery in SQLite relies on specific PRAGMA commands rather than the information_schema found in other databases. The process follows a structured sequence to build a complete TableDef.

Discovery flow showing the sequence of PRAGMA calls and internal filtering logic.
Sources: src/sqlite/def/table.rs:48-150, src/sqlite/def/column.rs:31-60

Discovery Implementation Details

• Autoincrement Detection: SQLite only supports AUTOINCREMENT on INTEGER PRIMARY KEY columns. SeaSchema detects this by querying sqlite_master for the specific keyword in the table's creation SQL.
• Hidden Columns: The discovery logic specifically checks for ColumnVisibility to skip hidden virtual columns (e.g., in FTS tables) during standard discovery.
• Index Origin: The system distinguishes between user-created indexes (origin: "c") and those created by unique/primary constraints (origin: "u" or "pk").

Sources: src/sqlite/def/table.rs:48-60, src/sqlite/def/column.rs:65-68, src/sqlite/def/column.rs:105-108

Data Type Mapping

SQLite is dynamically typed, requiring SeaSchema to parse type strings into structured ColumnType enums.

SQLite Type Pattern	SeaSchema/SeaQuery Type	Source
INT, INTEGER, BIGINT	BigInteger (Default for integers)	CHANGELOG.md:8
REAL	Double	CHANGELOG.md:144
NULL	DefaultType::Null	src/sqlite/def/column.rs:39
CURRENT_TIMESTAMP	DefaultType::CurrentTimestamp	src/sqlite/def/column.rs:50

Default Value Handling

SeaSchema parses the dflt_value string from SQLite metadata into a DefaultType enum, stripping quotes and distinguishing between literal values (Integers, Floats, Strings) and expressions like CURRENT_TIMESTAMP.
Sources: src/sqlite/def/column.rs:37-56, src/sqlite/def/table.rs:182-200

Writing Schemas

The write() method on TableDef converts the internal representation back into a TableCreateStatement for sea-query.

// Simplified example of the writing logic
pub fn write(&self) -> TableCreateStatement {
    let mut new_table = Table::create();
    new_table.table(Alias::new(&self.name));

    self.columns.iter().for_each(|column_info| {
        let mut new_column = ColumnDef::new_with_type(
            Alias::new(&column_info.name), 
            column_info.r#type.clone()
        );
        if self.auto_increment && column_info.primary_key {
            new_column.primary_key().auto_increment();
        }
        // ... applies defaults and nullability
        new_table.col(&mut new_column);
    });
    new_table
}

Sources: src/sqlite/def/table.rs:166-180, sea-schema-sync/src/sqlite/def/table.rs:166-180

Synchronous vs. Asynchronous Support

SeaSchema provides two modes for SQLite interaction based on the enabled features:

1. Async (sqlx-sqlite): Uses sqlx::SqliteRow and async methods in TableDef (e.g., async fn get_column_info).
2. Sync (rusqlite): Found in sea-schema-sync, using RusqliteRow and synchronous blocking methods.

The project uses a build script (make-sync.sh) to transform asynchronous source files into synchronous equivalents by removing async/await keywords and mapping Sqlx types to Rusqlite types.
Sources: src/lib.rs:17-21, build-tools/make-sync.sh:13-20, Cargo.toml:70-74

Summary

SQLite support in SeaSchema bridges the gap between SQLite's flexible, PRAGMA-heavy metadata system and a structured, type-safe Rust representation. By handling nuances like hidden columns, constraint-originated indexes, and dynamic type parsing, it enables developers to perform reliable schema discovery and generation for SQLite databases in both synchronous and asynchronous Rust environments.

Sources: README.md:1-10, CHANGELOG.md:1-15