Switchy Database

Database abstraction layer with support for multiple database backends, schema management, and transactions.

Overview

The Switchy Database package provides:

• Multi-Database Support: SQLite (rusqlite and sqlx), PostgreSQL (raw and sqlx), MySQL (sqlx), DuckDB, and Turso
• Schema Management: Create/alter tables, indexes with portable definitions
• Schema Introspection: Query existing database structure programmatically
• Transaction Support: ACID transactions with savepoint capabilities for nested transaction-like behavior
• Query Builder: Type-safe query construction for common operations

Features

Database Backends

• SQLite (rusqlite): File-based database using rusqlite driver with ? placeholders
• SQLite (sqlx): File-based database using sqlx driver with ? placeholders
• PostgreSQL (raw): Production PostgreSQL using tokio-postgres and deadpool-postgres
• PostgreSQL (sqlx): Production PostgreSQL using sqlx with connection pooling
• MySQL (sqlx): MySQL database using sqlx driver
• DuckDB: Embedded analytical database using duckdb driver with ? placeholders
• Turso: Turso (local libSQL/SQLite-compatible) database support
• Simulator: Testing database (delegates to underlying backend)

Schema Features

• Schema Creation: Create tables, indexes, and alter existing schema
• Schema Introspection: Check table/column existence, get table metadata
• Type Portability: Common data type abstraction across backends
• Foreign Keys: Define and introspect foreign key relationships
• Auto-increment: Backend-specific auto-increment handling

Transaction Features

• ACID Transactions: Full transaction support across all backends
• Savepoints: Nested transaction-like behavior with rollback points
• Connection Pooling: Efficient connection management (backend-dependent)

Usage

Basic Query Operations

use switchy_database::{Database, DatabaseError, query::FilterableQuery};

async fn query_examples(db: &dyn Database) -> Result<(), DatabaseError> {
    // SELECT query
    let rows = db.select("tracks")
        .columns(&["id", "title", "artist"])
        .where_eq("artist", "The Beatles")
        .execute(db)
        .await?;

    // Get first row
    let row = db.select("tracks")
        .where_eq("id", 42)
        .execute_first(db)
        .await?;

    // INSERT
    let new_row = db.insert("tracks")
        .value("title", "Come Together")
        .value("artist", "The Beatles")
        .value("duration", 259)
        .execute(db)
        .await?;

    println!("Inserted track with ID: {:?}", new_row.id());

    // UPDATE
    db.update("tracks")
        .value("artist", "The Beatles (Remastered)")
        .where_eq("id", 42)
        .execute(db)
        .await?;

    // DELETE
    db.delete("tracks")
        .where_eq("id", 42)
        .execute(db)
        .await?;

    Ok(())
}

Transactions

use switchy_database::{Database, DatabaseError};

async fn transaction_example(db: &dyn Database) -> Result<(), DatabaseError> {
    // Begin transaction
    let tx = db.begin_transaction().await?;

    // Execute operations within transaction
    let user_row = tx.insert("users")
        .value("username", "music_lover")
        .value("email", "user@example.com")
        .execute(&*tx)
        .await?;

    let user_id = user_row.id().and_then(|v| v.as_i64()).unwrap();

    let playlist_row = tx.insert("playlists")
        .value("user_id", user_id)
        .value("name", "My Favorites")
        .execute(&*tx)
        .await?;

    // Commit transaction
    tx.commit().await?;

    println!("Created user {} with playlist", user_id);

    Ok(())
}

async fn transaction_with_rollback(db: &dyn Database) -> Result<(), DatabaseError> {
    let tx = db.begin_transaction().await?;

    // This will succeed
    tx.insert("artists")
        .value("name", "New Artist")
        .execute(&*tx)
        .await?;

    // This might fail (e.g., duplicate key)
    let result = tx.insert("artists")
        .value("name", "New Artist") // Same name, might violate unique constraint
        .execute(&*tx)
        .await;

    match result {
        Ok(_) => {
            tx.commit().await?;
            println!("Transaction committed successfully");
        },
        Err(e) => {
            tx.rollback().await?;
            println!("Transaction rolled back due to error: {}", e);
        }
    }

    Ok(())
}

Savepoints (Nested Transactions)

Savepoints allow partial rollback within a transaction, enabling complex error recovery:

use switchy_database::{Database, DatabaseError};

async fn batch_import_with_recovery(
    db: &dyn Database,
    batches: Vec<Vec<String>>
) -> Result<(), DatabaseError> {
    let tx = db.begin_transaction().await?;

    // Process records in batches with savepoints
    for (batch_num, batch) in batches.iter().enumerate() {
        let sp = tx.savepoint(&format!("batch_{}", batch_num)).await?;

        match process_batch(&*tx, batch).await {
            Ok(_) => {
                // Batch successful, merge into transaction
                sp.release().await?;
            }
            Err(e) => {
                // Batch failed, rollback this batch only
                eprintln!("Batch {} failed: {}", batch_num, e);
                sp.rollback_to().await?;
                // Transaction continues with other batches
            }
        }
    }

    tx.commit().await?;
    Ok(())
}

async fn process_batch(tx: &dyn Database, batch: &[String]) -> Result<(), DatabaseError> {
    for item in batch {
        tx.insert("items").value("name", item).execute(tx).await?;
    }
    Ok(())
}

Backend Support

Common Use Cases

• Batch Processing: Process large datasets with per-batch recovery
• Migration Testing: Test schema changes with rollback capability
• Complex Business Logic: Multi-step operations with conditional rollback
• Error Recovery: Continue transaction after handling specific errors

Schema Management

use switchy_database::{Database, DatabaseError};
use switchy_database::schema::{create_table, Column, DataType};
use switchy_database::DatabaseValue;

async fn create_schema(db: &dyn Database) -> Result<(), DatabaseError> {
    // Check if table exists first
    if !db.table_exists("users").await? {
        // Create table
        create_table("users")
            .column(Column {
                name: "id".to_string(),
                nullable: false,
                auto_increment: true,
                data_type: DataType::BigInt,
                default: None,
            })
            .column(Column {
                name: "username".to_string(),
                nullable: false,
                auto_increment: false,
                data_type: DataType::VarChar(50),
                default: None,
            })
            .column(Column {
                name: "email".to_string(),
                nullable: true,
                auto_increment: false,
                data_type: DataType::VarChar(255),
                default: None,
            })
            .column(Column {
                name: "created_at".to_string(),
                nullable: false,
                auto_increment: false,
                data_type: DataType::DateTime,
                default: Some(DatabaseValue::Now),
            })
            .primary_key("id")
            .execute(db)
            .await?;
    }

    // Create index
    db.create_index("idx_users_email")
        .table("users")
        .column("email")
        .unique(true)
        .execute(db)
        .await?;

    Ok(())
}

Schema Introspection

use switchy_database::{Database, DatabaseError};

async fn inspect_schema(db: &dyn Database) -> Result<(), DatabaseError> {
    // List all tables
    let tables = db.list_tables().await?;
    println!("Tables: {:?}", tables);

    // Check if a table exists
    if db.table_exists("users").await? {
        println!("Users table exists");
    }

    // Check if a column exists
    if db.column_exists("users", "email").await? {
        println!("Email column exists");
    }

    // Get complete table information
    if let Some(table_info) = db.get_table_info("users").await? {
        println!("Table: {}", table_info.name);

        // Inspect columns
        for (col_name, col_info) in &table_info.columns {
            println!("  Column: {} {:?} {}",
                col_name,
                col_info.data_type,
                if col_info.nullable { "NULL" } else { "NOT NULL" }
            );

            if col_info.is_primary_key {
                println!("    (Primary Key)");
            }
        }

        // Inspect indexes
        for (idx_name, idx_info) in &table_info.indexes {
            println!("  Index: {} on {:?} {}",
                idx_name,
                idx_info.columns,
                if idx_info.unique { "(UNIQUE)" } else { "" }
            );
        }

        // Inspect foreign keys
        for (fk_name, fk_info) in &table_info.foreign_keys {
            println!("  FK: {}.{} -> {}.{}",
                table_info.name, fk_info.column,
                fk_info.referenced_table, fk_info.referenced_column
            );
        }
    }

    // Get just the columns
    let columns = db.get_table_columns("users").await?;
    for column in columns {
        println!("Column: {} ({})", column.name,
                 if column.nullable { "NULL" } else { "NOT NULL" });
    }

    Ok(())
}

Raw SQL Queries

use switchy_database::{Database, DatabaseError, DatabaseValue};

async fn raw_queries(db: &dyn Database) -> Result<(), DatabaseError> {
    // Raw query without parameters (string interpolation - use carefully!)
    let rows = db.query_raw("SELECT * FROM tracks WHERE artist = 'The Beatles'").await?;

    // Raw query with parameters (safe from SQL injection)
    // Note: Parameter syntax varies by backend:
    // - rusqlite: ? placeholders
    // - sqlx-sqlite: ? placeholders
    // - PostgreSQL (raw/sqlx): $1, $2 placeholders
    // - MySQL (sqlx): ? placeholders
    // - DuckDB: ? placeholders
    let params = vec![DatabaseValue::String("The Beatles".to_string())];
    let rows = db.query_raw_params("SELECT * FROM tracks WHERE artist = ?", &params).await?;

    // Raw execution (no results)
    db.exec_raw("CREATE INDEX idx_tracks_artist ON tracks(artist)").await?;

    // Raw execution with parameters
    let params = vec![
        DatabaseValue::String("Come Together".to_string()),
        DatabaseValue::String("The Beatles".to_string()),
    ];
    db.exec_raw_params("INSERT INTO tracks (title, artist) VALUES (?, ?)", &params).await?;

    Ok(())
}

Feature Flags

The following feature flags are available in Cargo.toml:

Backend Features

• sqlite-rusqlite - SQLite backend using rusqlite driver
• sqlite-sqlx - SQLite backend using sqlx driver
• postgres-raw - PostgreSQL backend using tokio-postgres
• postgres-sqlx - PostgreSQL backend using sqlx
• mysql / mysql-sqlx - MySQL backend using sqlx
• duckdb - DuckDB embedded analytical database
• duckdb-bundled - DuckDB with bundled library (no system install required)
• turso - Turso local database support (file-based or in-memory), without selecting a global allocator
• turso-mimalloc - Explicitly enables turso and Turso's mimalloc global allocator; not enabled by defaults or turso

Optional Turso allocator

Applications can opt in with:

switchy_database = { version = "0.4.0", default-features = false, features = ["turso-mimalloc"] }

This forwards to turso/mimalloc, which installs a Rust global allocator for the entire linked artifact, not only database allocations. Do not also declare a different global allocator in that artifact. Cargo features are additive: a transitive dependency enabling this feature enables the allocator for other consumers sharing that artifact too.

Keep this feature disabled in reusable libraries and independently linked native plugins. Multiple mimalloc instances can collide through fixed thread-local slots on macOS, causing memory corruption. Executable-owned allocator selection is preferred when an application loads plugins. Performance benefits are workload- and platform-dependent; benchmark before enabling it.

Additional Features

• schema - Schema management and introspection (enabled by default)
• cascade - CASCADE deletion support for schema operations
• auto-reverse - Auto-reverse migration support
• simulator - Database simulator for testing
• decimal - Decimal type support (rust_decimal)
• uuid - UUID type support
• api - Actix-web integration for web APIs

Placeholder Features

• all-placeholders - Support for all placeholder styles
• placeholder-question-mark - ? placeholder support
• placeholder-dollar-number - $1, $2 placeholder support
• placeholder-at-number - @1, @2 placeholder support
• placeholder-colon-number - :1, :2 placeholder support
• placeholder-named-colon - :name placeholder support

Error Handling

use switchy_database::{Database, DatabaseError, query::FilterableQuery};

// Within an async function with access to db: &dyn Database and track_id
match db.select("tracks").where_eq("id", track_id).execute_first(db).await {
    Ok(Some(row)) => println!("Found track: {:?}", row),
    Ok(None) => println!("Track not found"),
    Err(DatabaseError::NoRow) => {
        println!("No row returned");
    },
    Err(DatabaseError::InvalidSchema(msg)) => {
        eprintln!("Schema error: {}", msg);
    },
    Err(DatabaseError::AlreadyInTransaction) => {
        eprintln!("Already in a transaction");
    },
    Err(DatabaseError::TransactionCommitted) => {
        eprintln!("Transaction already committed");
    },
    Err(DatabaseError::TransactionRolledBack) => {
        eprintln!("Transaction already rolled back");
    },
    Err(e) => {
        eprintln!("Database error: {}", e);
    }
}

Backend-Specific Errors

Each backend has its own error variant:

• DatabaseError::Rusqlite(rusqlite::RusqliteDatabaseError) - rusqlite backend errors
• DatabaseError::SqliteSqlx(sqlx::sqlite::SqlxDatabaseError) - sqlx SQLite errors
• DatabaseError::Postgres(postgres::postgres::PostgresDatabaseError) - raw PostgreSQL errors
• DatabaseError::PostgresSqlx(sqlx::postgres::SqlxDatabaseError) - sqlx PostgreSQL errors
• DatabaseError::MysqlSqlx(sqlx::mysql::SqlxDatabaseError) - sqlx MySQL errors
• DatabaseError::DuckDb(duckdb::DuckDbDatabaseError) - DuckDB errors
• DatabaseError::Turso(turso::TursoDatabaseError) - Turso errors

Data Types

The DatabaseValue enum supports the following types:

• Strings: String, StringOpt
• Booleans: Bool, BoolOpt
• Integers: Int8, Int16, Int32, Int64 (and unsigned variants)
• Floating Point: Real32, Real64
• Decimal: Decimal (with decimal feature)
• UUID: Uuid (with uuid feature)
• DateTime: DateTime, Now, NowPlus
• Null: Null

The schema::DataType enum provides database-agnostic type definitions:

• Text - Variable-length text
• VarChar(n) - Fixed-length string
• Bool - Boolean
• Int - 32-bit integer
• SmallInt - 16-bit integer
• BigInt - 64-bit integer
• Real - 32-bit floating point
• Double - 64-bit floating point
• DateTime - Date and time
• Decimal(precision, scale) - Fixed-precision decimal

Architecture

Database Trait

The core Database trait provides:

• Query builder methods (select, insert, update, delete, upsert)
• Schema methods (create_table, drop_table, create_index, alter_table) - requires schema feature
• Execution methods (query, query_first, exec_update, exec_insert, etc.)
• Raw SQL methods (query_raw, query_raw_params, exec_raw, exec_raw_params)
• Introspection methods (table_exists, column_exists, get_table_info, list_tables) - requires schema feature
• Transaction method (begin_transaction)

DatabaseTransaction Trait

The DatabaseTransaction trait extends Database with:

• commit() - Commit the transaction
• rollback() - Rollback the transaction
• savepoint(name) - Create a savepoint within the transaction
• CASCADE operations (with cascade feature)

Savepoint Trait

The Savepoint trait provides:

• release() - Commit the savepoint
• rollback_to() - Rollback to the savepoint
• name() - Get the savepoint name

Global and Profile Database Access

The crate also exposes public APIs for resolving Database instances in applications:

• config::init(database) - Register a global Arc<Box<dyn Database>> singleton
• config::ConfigDatabase - Wrapper that dereferences to dyn Database (and supports Actix extraction with api feature)
• profiles::PROFILES - Global profile registry for multiple named databases
• profiles::LibraryDatabase - Profile-resolved wrapper that dereferences to dyn Database (and supports Actix extraction with api feature)

Backend Implementation Details

SQLite

Two SQLite implementations are available:

1. rusqlite (sqlite-rusqlite feature):

   • Uses ? placeholders
   • Blocking operations wrapped in async
   • Connection pooling for concurrent transactions

2. sqlx (sqlite-sqlx feature):

   • Uses ? placeholders
   • Native async support
   • Built-in connection pooling

PostgreSQL

Two PostgreSQL implementations are available:

1. Raw (postgres-raw feature):

   • Uses tokio-postgres and deadpool-postgres
   • Uses $1, $2 placeholders
   • Custom connection pool management

2. sqlx (postgres-sqlx feature):

   • Uses sqlx driver
   • Uses $1, $2 placeholders
   • Built-in connection pooling

MySQL

One MySQL implementation using sqlx:

• sqlx (mysql-sqlx feature):
  • Uses ? placeholders (via transformation)
  • Built-in connection pooling
  • Full transaction support

Turso

Turso (libSQL/SQLite-compatible) local database support:

• Uses ? placeholders
• Local file-based and in-memory database support
• Compatible with SQLite API

DuckDB

Embedded analytical database:

• DuckDB (duckdb / duckdb-bundled feature):
  • Uses ? placeholders
  • Connection pool of 5 connections behind Arc<Mutex<>>
  • Supports routing/consistency config (DuckDbConfig):
    • Deterministic mode (default): one shared connection topology for predictable behavior
    • Pooled mode: round-robin across independent connections
    • Strict consistency (default): serialized operation gate in pooled mode
    • Relaxed consistency: higher throughput, weaker cross-connection guarantees
  • Blocking operations wrapped in async
  • In-memory and file-backed databases supported
  • For cross-connection transaction behavior, prefer file-backed databases in tests
  • NOW() is cast to TIMESTAMP (DuckDB's NOW() returns TIMESTAMP WITH TIME ZONE)
  • Auto-increment uses CREATE SEQUENCE + DEFAULT nextval(...) instead of GENERATED ALWAYS AS IDENTITY
  • Savepoints are not supported (returns UnsupportedOperation)
  • Schema introspection via information_schema and duckdb_indexes()
  • DELETE ... RETURNING uses a SELECT-then-DELETE workaround (see source for details)
  • DROP TABLE ... CASCADE for FK-dependent tables is limited in DuckDB v1.4.4
  • ALTER TABLE ... DROP COLUMN with index/FK dependencies is limited in DuckDB v1.4.4

Limitations

• No ORM: This is a query builder, not a full ORM with automatic relationship mapping
• No Migration System: No built-in migration versioning or rollback system
• Manual Schema Management: Schema changes must be managed manually
• No Query Optimization: No automatic query analysis or optimization
• Backend-Specific Placeholder Syntax: Different backends require different placeholder styles (though some auto-transformation is provided)

See Also

• MoosicBox Config - Configuration management
• MoosicBox Server - Server with database integration