MoosicBox Schema

Database migration system for the MoosicBox ecosystem. This package provides a thin wrapper around switchy_schema for automated SQL migration management with PostgreSQL and SQLite databases, featuring version tracking and comprehensive testing utilities.

Features

• Database Migrations: Automated SQL migration execution with rollback support
• Version Tracking: Track applied migrations in __moosicbox_schema_migrations table
• Multi-Database Support: Both PostgreSQL and SQLite via switchy_database
• Environment Control: Skip migrations via MOOSICBOX_SKIP_MIGRATION_EXECUTION=1
• Partial Migrations: Run migrations up to a specific version
• Comprehensive Testing: Advanced test utilities for migration validation
• Built on switchy_schema: Modern, generic migration engine

Installation

Add this to your Cargo.toml:

[dependencies]
moosicbox_schema = { version = "0.1.4", features = ["sqlite", "postgres"] }

# Or rely on default features (includes "sqlite" and "postgres")
# moosicbox_schema = "0.1.4"

Usage

Running Library Migrations

use moosicbox_schema::{migrate_library, MigrateError};
use switchy_database::Database;

#[tokio::main]
async fn main() -> Result<(), MigrateError> {
    // Initialize your database connection
    // (use your preferred database connection method)
    let db: Box<dyn Database> = todo!("Initialize your database connection");

    // Run all library migrations
    migrate_library(&*db).await?;

    println!("Library migrations completed successfully");
    Ok(())
}

Running Config Migrations

use moosicbox_schema::migrate_config;

// Initialize your database connection
let db: Box<dyn Database> = todo!("Initialize your database connection");

// Run configuration-related migrations
migrate_config(&*db).await?;

Partial Migrations

use moosicbox_schema::migrate_library_until;

// Initialize your database connection
let db: Box<dyn Database> = todo!("Initialize your database connection");

// Run migrations up to a specific version
migrate_library_until(&*db, Some("2023-10-14-031701_create_tracks")).await?;

Environment Variables

Drop Migration Tracking Table

export MOOSICBOX_DROP_MIGRATIONS_TABLE=1

When this environment variable is set to "1", the migration tracking table (__moosicbox_schema_migrations) will be dropped before running migrations. This removes all migration history.

Warning: This is a destructive operation. Use only for:

• Fresh setup: Resetting a development database
• Testing: Creating clean test environments
• Recovery: Rebuilding corrupted tracking tables

This is typically followed by running migrations with MOOSICBOX_SKIP_MIGRATION_EXECUTION=1 to rebuild the tracking table.

Skip Migration Execution

export MOOSICBOX_SKIP_MIGRATION_EXECUTION=1

When this environment variable is set to "1", migration functions will populate the migration tracking table (__moosicbox_schema_migrations) with all migrations marked as completed WITHOUT executing their SQL. This is useful for:

• Initialization: Setting up tracking for existing databases with matching schema
• Read-only deployments: Applications that shouldn't modify schema
• Recovery: When schema table needs to be rebuilt after corruption

Behavior:

• ✅ Creates the migration tracking table if it doesn't exist
• ✅ Records all migrations as completed with timestamps
• ✅ Logs summary of marked migrations (newly marked, updated, already completed)
• ❌ Does NOT execute any migration SQL

Previous Behavior: Completely skipped all migration operations. The new behavior ensures proper migration state tracking even when SQL execution is skipped.

Example:

use moosicbox_schema::migrate_library;

// Set environment variable
std::env::set_var("MOOSICBOX_SKIP_MIGRATION_EXECUTION", "1");

// Initialize your database connection
let db: Box<dyn Database> = todo!("Initialize your database connection");

// This will populate the table without executing SQL
migrate_library(&*db).await?;
// Logs: "marked 45 migrations as completed (45 newly marked, 0 failed skipped, 0 in-progress skipped)"

Scope Behavior:

The MOOSICBOX_SKIP_MIGRATION_EXECUTION environment variable uses the safest scope (PendingOnly):

• ✅ Only marks untracked migrations as completed
• ⏭️ Preserves failed migration states (they remain failed)
• ⏭️ Preserves in-progress migration states (they remain in-progress)

This ensures that if you have failed migrations tracked, they won't be incorrectly marked as completed. The environment variable is designed for initialization scenarios, not recovery from failed migrations.

If you need to mark failed migrations as completed, use the CLI instead:

switchy-migrate mark-all-completed --include-failed -d DATABASE_URL

Migration Testing

For testing migrations, use the provided test utilities:

use moosicbox_schema::{get_sqlite_library_migrations};
use switchy_schema_test_utils::MigrationTestBuilder;

#[tokio::test]
async fn test_library_migrations() {
    // Initialize test database connection
    let db: Box<dyn Database> = todo!("Initialize test database connection");
    let migrations = get_sqlite_library_migrations().await.unwrap();

    // Run all migrations and verify they work
    MigrationTestBuilder::new(migrations)
        .with_table_name("__moosicbox_schema_migrations")
        .run(&*db)
        .await
        .unwrap();
}

For more advanced migration testing patterns, see the Migration Testing Guide section below.

Error Handling

use moosicbox_schema::MigrateError;

// Initialize your database connection
let db: Box<dyn Database> = todo!("Initialize your database connection");

match migrate_library(&*db).await {
    Ok(()) => println!("Migrations completed successfully"),
    Err(MigrateError::Database(db_err)) => {
        eprintln!("Database error during migration: {}", db_err);
    }
    Err(MigrateError::Schema(schema_err)) => {
        eprintln!("Schema migration error: {}", schema_err);
    }
}

Architecture

This package is built on top of switchy_schema, providing MoosicBox-specific migration management while leveraging the generic, reusable migration engine. The architecture includes:

• Embedded Migrations: SQL files compiled into the binary at build time
• Automatic Discovery: Migrations loaded from /migrations/server/{library,config}/{sqlite,postgres}/ directories
• Version Tracking: Uses __moosicbox_schema_migrations table (customized from default __switchy_migrations)
• Environment Integration: Respects MOOSICBOX_SKIP_MIGRATION_EXECUTION environment variable

Migration Structure

Migrations are organized in the following directory structure:

migrations/server/
├── library/
│   ├── sqlite/
│   │   ├── 2023-10-13-195407_create_artists/
│   │   │   ├── up.sql
│   │   │   └── down.sql
│   │   └── 2023-10-14-031701_create_tracks/
│   │       ├── up.sql
│   │       └── down.sql
│   └── postgres/
│       ├── 2023-10-13-195407_create_artists/
│       │   ├── up.sql
│       │   └── down.sql
│       └── 2023-10-14-031701_create_tracks/
│           ├── up.sql
│           └── down.sql
└── config/
    ├── sqlite/
    └── postgres/

Migration Testing Guide

Basic Migration Testing

Use MigrationTestBuilder for comprehensive migration validation:

use moosicbox_schema::get_sqlite_library_migrations;
use switchy_schema_test_utils::MigrationTestBuilder;

#[tokio::test]
async fn test_all_migrations() {
    // Initialize test database connection
    let db: Box<dyn Database> = todo!("Initialize test database connection");

    MigrationTestBuilder::new(get_sqlite_library_migrations().await.unwrap())
        .with_table_name("__moosicbox_schema_migrations")
        .run(&*db)
        .await
        .unwrap();
}

Data Migration Testing

Test migrations that transform existing data:

#[tokio::test]
async fn test_data_migration() {
    // Initialize test database connection
    let db: Box<dyn Database> = todo!("Initialize test database connection");

    MigrationTestBuilder::new(get_sqlite_library_migrations().await.unwrap())
        .with_table_name("__moosicbox_schema_migrations")
        .with_data_before("2023-10-14-031701_create_tracks", |db| Box::pin(async move {
            // Insert test data in old format
            db.exec_raw("INSERT INTO artists (title) VALUES ('Test Artist')").await?;
            Ok(())
        }))
        .with_data_after("2023-10-14-031701_create_tracks", |db| Box::pin(async move {
            // Verify data is preserved and new structure works
            let result = db.select("artists").columns(&["id", "title"]).execute(db).await?;
            assert_eq!(result.len(), 1);
            Ok(())
        }))
        .run(&*db)
        .await
        .unwrap();
}

Testing with Rollback

Test migration rollback functionality:

#[tokio::test]
async fn test_migration_rollback() {
    // Initialize test database connection
    let db: Box<dyn Database> = todo!("Initialize test database connection");

    MigrationTestBuilder::new(get_sqlite_library_migrations().await.unwrap())
        .with_table_name("__moosicbox_schema_migrations")
        .with_rollback() // Enable rollback testing
        .run(&*db)
        .await
        .unwrap();

    // Verify database is back to initial state
}

Available Test Collection Functions

// Get migrations for testing - all return Vec<Arc<dyn Migration>>
use moosicbox_schema::{
    get_sqlite_library_migrations,
    get_sqlite_config_migrations,
    get_postgres_library_migrations,
    get_postgres_config_migrations,
};

Supported Databases

• SQLite: Via the sqlite feature flag
• PostgreSQL: Via the postgres feature flag

Both databases use the same migration table schema but have database-specific SQL in their migration files.

Migration Tracking

The system automatically creates a __moosicbox_schema_migrations table to track applied migrations:

CREATE TABLE __moosicbox_schema_migrations (
    id TEXT NOT NULL PRIMARY KEY,
    run_on DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finished_on DATETIME,
    up_checksum VARCHAR(64) NOT NULL,
    down_checksum VARCHAR(64) NOT NULL,
    status TEXT NOT NULL,
    failure_reason TEXT
);

The tracking table records:

• id: Unique migration identifier (e.g., 2023-10-13-195407_create_artists)
• run_on: When the migration started executing
• finished_on: When the migration completed (NULL if still in progress)
• up_checksum: SHA-256 checksum of the up migration SQL
• down_checksum: SHA-256 checksum of the down migration SQL
• status: Migration status (completed, failed, or in_progress)
• failure_reason: Error message if migration failed (NULL if successful)

Dependencies

• switchy_schema: Generic migration engine
• switchy_database: Database abstraction layer
• switchy_env: Environment variable handling
• moosicbox_assert: Runtime assertion helpers
• include_dir: Compile-time directory inclusion
• log: Logging for migration execution and status
• thiserror: Error handling utilities

Error Types

• MigrateError: Wraps both database and schema migration errors
  • MigrateError::Database(DatabaseError): Database connection/execution errors
  • MigrateError::Schema(SwitchyMigrationError): Migration logic errors

Development Notes

• Migrations are embedded at compile time for zero-config deployment
• The build.rs script ensures recompilation when migration files change
• Both PostgreSQL and SQLite migrations run when both features are enabled (intended for development/testing)
• In production deployments, typically only one database feature is enabled