# MoosicBox Schema
Database migration system for the MoosicBox ecosystem. This package provides a thin wrapper around [switchy_schema](../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`:
```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
```rust
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
```rust
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
```rust
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
```bash
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
```bash
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:**
```rust
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:
```bash
switchy-migrate mark-all-completed --include-failed -d DATABASE_URL
```
### Migration Testing
For testing migrations, use the provided test utilities:
```rust
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](#migration-testing-guide) section below.
### Error Handling
```rust
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](../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:
```rust
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:
```rust
#[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:
```rust
#[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
```rust
// 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:
```sql
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