Rusqlite and Postgres Crate Integrations

Relevant source files

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

• sea-query-rusqlite/src/lib.rs
• sea-query-postgres/src/lib.rs
• sea-query-rusqlite/Cargo.toml
• sea-query-postgres/Cargo.toml
• README.md
• src/lib.rs

Introduction

SeaQuery provides specialized integration crates, sea-query-rusqlite and sea-query-postgres, to bridge the gap between its dynamic SQL AST and popular Rust database drivers. These integrations act as "binders," allowing developers to build queries using SeaQuery's ergonomic API and execute them directly using the native types and protocols of rusqlite and postgres.

The primary goal of these integrations is to handle the conversion of SeaQuery's internal Values—which represent SQL parameters—into the specific trait objects or data structures required by the target driver's binary protocol. This ensures type safety and prevents common issues like SQL injection by utilizing the drivers' native parameter binding mechanisms.

Sources: README.md:17-21, sea-query-rusqlite/Cargo.toml:11, sea-query-postgres/Cargo.toml:11

Shared Architecture and Workflow

Both integrations follow a similar architectural pattern: they provide trait implementations or helper structures that take a built SeaQuery statement and its associated values, then format them for the specific database driver.

Query Execution Flow

The following diagram illustrates the typical lifecycle of a query from construction to execution through these integrations.

The workflow begins with the core sea-query AST and culminates in driver-specific execution.
Sources: src/lib.rs:240-255, README.md:240-255

Rusqlite Integration (sea-query-rusqlite)

The sea-query-rusqlite crate enables seamless interaction with SQLite databases. It primarily focuses on mapping SeaQuery's Value variants to types that implement rusqlite::ToSql.

Key Components and Features

Feature	Description
Binder Traits	Traits that allow converting sea-query::Values into a format compatible with rusqlite parameter lists.
Type Support	Extensive support for optional types like Chrono, UUID, JSON, and Time via feature flags.
Fallible UInt	Utilizes rusqlite features like fallible_uint for safer integer handling.

Sources: sea-query-rusqlite/Cargo.toml:19-35, sea-query-rusqlite/src/lib.rs

Extension Support

The integration provides specific feature flags to enable support for complex types within SQLite:

• with-chrono: Maps Chrono types to SQLite-compatible strings or integers.
• with-uuid: Supports UUID storage and retrieval.
• with-json: Integrates serde_json for JSONB-like functionality in SQLite.

Sources: sea-query-rusqlite/Cargo.toml:23-31

Postgres Integration (sea-query-postgres)

The sea-query-postgres crate provides the necessary logic to bind SeaQuery values to the postgres and native-tls drivers. It is designed to work with the binary protocol, which is more efficient and secure than text-based injection.

Data Mapping and Types

Postgres integration is complex due to the variety of specialized types supported by the database. The crate utilizes postgres-types and bytes to handle low-level serialization.

The integration maps high-level SeaQuery values into the specific byte formats expected by the Postgres wire protocol.
Sources: sea-query-postgres/src/lib.rs, sea-query-postgres/Cargo.toml:21-25

Specialized Postgres Features

Feature Flag	Description
postgres-array	Supports native PostgreSQL array types (e.g., TEXT[]).
postgres-vector	Integration for pgvector to support vector embeddings and similarity searches.
postgres-range	Support for range types via sea-query-postgres-types/range.
with-ipnetwork	Support for inet and cidr types.

Sources: sea-query-postgres/Cargo.toml:34-42

Parameter Binding Logic

SeaQuery handles the $N sequencing required by PostgreSQL. The integration ensures that when a query is built with PostgresQueryBuilder, the resulting Values vector matches the indices in the SQL string.

// Example of built query for Postgres
assert_eq!(
    Query::select()
        .expr(Expr::col("id").eq(1))
        .build(PostgresQueryBuilder),
    (
        r#"SELECT "id" FROM "table" WHERE "id" = $1"#.to_owned(),
        Values(vec![1.into()])
    )
);

Sources: src/lib.rs:98-115, README.md:98-115

Configuration and Dependencies

Both crates are designed to be lightweight, with most driver-specific type support being optional to keep binary sizes small.

Dependency Summary Table

Crate	Core Dependencies	Optional Driver Extensions
sea-query-rusqlite	sea-query, rusqlite	chrono, serde_json, uuid, time, jiff
sea-query-postgres	sea-query, postgres-types, bytes	pgvector, rust_decimal, ipnetwork, mac_address

Sources: sea-query-rusqlite/Cargo.toml:19-21, sea-query-postgres/Cargo.toml:21-32

Summary

The Rusqlite and Postgres integrations for SeaQuery provide the essential "glue" code needed to execute dynamically built SQL. By implementing the necessary binder traits, these crates allow SeaQuery to support the native features of each database—such as Postgres arrays or SQLite's flexible typing—while maintaining a consistent, type-safe API for the user. These modules are critical for production environments where binary protocols and parameter binding are required for performance and security.