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)

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