---
title: "Migrations"
description: "Manage database schema evolution with handwritten and auto migrations."
canonical: "https://roomsharp.dev/docs/v0.4.7/migrations"
source: "src/content/v0.4.7/migrations.mdx"
---

# Migrations

RoomSharp provides a flexible migration system that supports both declarative and handwritten migrations.

## Migration Types

### Manual Migrations (Recommended)

Implement `IRoomMigration` and use `MigrationContext` for transaction-safe migrations:

```csharp
public sealed class UserMigration_1_2 : IRoomMigration
{
    public int StartVersion => 1;
    public int EndVersion => 2;

    public string Id => "UserMigration_1_2:1->2";
    public string Name => nameof(UserMigration_1_2);

    public string Checksum => MigrationChecksum.Sha256(
        "ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'"
    );

    public async Task UpAsync(MigrationContext ctx)
    {
        await using var cmd = ctx.CreateCommand(
            "ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'");
        await cmd.ExecuteNonQueryAsync(ctx.CancellationToken);
    }
}
```

### Auto Migrations (Declarative)

Use `[AutoMigration]` with `IAutoMigrationSpec` for declarative schema changes:

```csharp
[Database(Version = 3, Entities = new[] { typeof(User) })]
[AutoMigration(From = 1, To = 2, Spec = typeof(UserTableSpec))]
[AutoMigration(From = 2, To = 3)]
public abstract class UserDatabase : RoomDatabase
{
    protected UserDatabase(IDatabaseProvider provider, ILogger? logger = null) 
        : base(provider, logger) { }
}

[RenameTable(FromTableName = "old_users", ToTableName = "users")]
[DeleteColumn(TableName = "users", ColumnName = "legacy_flag")]
[ColumnRename(TableName = "users", FromColumnName = "full_name", ToColumnName = "name")]
public sealed class UserTableSpec : IAutoMigrationSpec
{
    public async Task OnPostMigrate(MigrationContext ctx)
    {
        // Execute data migrations within the same transaction
        await ctx.ExecuteAsync("UPDATE users SET status='active' WHERE status IS NULL");
    }
}
```

### Legacy Migrations

Extend `Migration` and override `Migrate(DbConnection connection)` (supported via adapter, but deprecated):

```csharp
// Deprecated - use IRoomMigration instead
public class LegacyMigration : Migration
{
    public override void Migrate(DbConnection connection)
    {
        // Direct connection access
    }
}
```

### Upgrading from Legacy to IRoomMigration

<Note>
If you have existing `Migration` classes that override `Migrate(DbConnection)`, here's how to upgrade them to the modern `IRoomMigration` interface:
</Note>

**Before (Legacy):**
```csharp
public class AddStatusColumn : Migration
{
    public AddStatusColumn() : base(1, 2) { }

    public override void Migrate(DbConnection connection)
    {
        using var cmd = connection.CreateCommand();
        cmd.CommandText = "ALTER TABLE users ADD COLUMN status TEXT";
        cmd.ExecuteNonQuery();
    }
}
```

**After (Modern):**
```csharp
public sealed class AddStatusColumn : IRoomMigration
{
    public int StartVersion => 1;
    public int EndVersion => 2;
    public string Id => $"{nameof(AddStatusColumn)}:{StartVersion}->{EndVersion}";
    public string Name => nameof(AddStatusColumn);
    public string Checksum => MigrationChecksum.Sha256("ALTER TABLE users ADD COLUMN status TEXT");

    public async Task UpAsync(MigrationContext ctx)
    {
        // Option 1: Use extension method (recommended)
        await ctx.ExecuteAsync("ALTER TABLE users ADD COLUMN status TEXT");
        
        // Option 2: Use CreateCommand directly
        // await using var cmd = ctx.CreateCommand("ALTER TABLE users ADD COLUMN status TEXT");
        // await cmd.ExecuteNonQueryAsync(ctx.CancellationToken);
    }
}
```

**Key differences:**
| Aspect | Legacy | Modern |
|--------|--------|--------|
| Base | `Migration` class | `IRoomMigration` interface |
| Method | `Migrate(DbConnection)` | `UpAsync(MigrationContext)` |
| Transaction | Manual | Automatic via `ctx.Transaction` |
| Checksum | Auto from IL | Explicit property |
| Async | No | Yes |
| Cancellation | No | `ctx.CancellationToken` |

<Tip>
You can keep existing Legacy migrations running - RoomSharp wraps them in `LegacyMigrationAdapter` automatically. But new migrations should use `IRoomMigration`.
</Tip>

### Fluent Migrations

Use the fluent API to build schema changes programmatically. This approach is type-safe and generates appropriate SQL for each database provider:

```csharp
public sealed class CreateUsersTable : Migration
{
    public CreateUsersTable() : base(0, 1) { }

    public override Task UpAsync(MigrationContext ctx)
    {
        // Create a table with columns
        ctx.CreateTable("users", table =>
        {
            table.Column<int>("Id", c => c.PrimaryKey(autoIncrement: true));
            table.Column<string>("Email", c => c.NotNull().Unique());
            table.Column<string>("Name");
            table.Column<DateTime>("CreatedAt", c => c.NotNull());
        }, ifNotExists: true);

        // Create an index
        ctx.CreateIndex("users", "IX_users_email", unique: true, ifNotExists: true, "Email");

        return Task.CompletedTask;
    }
}
```

#### Fluent Operations

| Operation | Description | Example |
|-----------|-------------|---------|
| `CreateTable` | Create a new table | `ctx.CreateTable("users", t => { ... })` |
| `AlterTable` | Modify an existing table | `ctx.AlterTable("users", t => { ... })` |
| `RenameTable` | Rename a table | `ctx.RenameTable("old_name", "new_name")` |
| `DropTable` | Drop a table | `ctx.DropTable("users")` |
| `CreateIndex` | Create an index | `ctx.CreateIndex("table", "idx_name", unique, ifNotExists, "col1", "col2")` |
| `DropIndex` | Drop an index | `ctx.DropIndex("table", "idx_name")` |

#### Column Definition

```csharp
ctx.CreateTable("users", table =>
{
    // Primary key with auto-increment
    table.Column<int>("Id", c => c.PrimaryKey(autoIncrement: true));
    
    // Required unique string
    table.Column<string>("Email", c => c.NotNull().Unique());
    
    // Nullable string
    table.Column<string>("Bio");
    
    // With default value
    table.Column<bool>("IsActive", c => c.NotNull().Default(true));
    
    // Custom type affinity
    table.Column<DateTime>("CreatedAt", c => c.TypeAffinity("DATETIME").NotNull());
});
```

#### Alter Table

```csharp
ctx.AlterTable("users", table =>
{
    // Add a new column
    table.AddColumn<string>("Phone");
    
    // Add column with constraints
    table.AddColumn<bool>("IsVerified", c => c.NotNull().Default(false));
    
    // Rename a column
    table.RenameColumn("OldName", "NewName");
    
    // Drop a column (requires AllowDestructiveOperations)
    // table.DropColumn("LegacyColumn");
});
```

<Warning title="Destructive Operations">
Operations like `DropTable`, `DropColumn`, and `DropIndex` are blocked by default. Enable explicitly:

```csharp
ctx.AllowDestructiveOperations();
ctx.DropTable("legacy_table");
```
</Warning>

<Note>
Adding a `NOT NULL` column without a default to a non-empty table will throw an error:
`Migration rejected: adding NOT NULL column 'X' to non-empty table 'Y' without default value.`
</Note>

#### Convenience Methods

TableBuilder provides shortcuts for common column patterns:

```csharp
ctx.CreateTable("users", table =>
{
    table.Id();                    // auto-increment int PK
    table.IdLong();               // auto-increment long PK
    table.IdGuid();               // GUID PK
    
    table.String("Email");         // required string (NOT NULL)
    table.NullableString("Bio");   // nullable string
    
    table.Int("Age");              // int column
    table.Bool("IsActive");        // bool column
    
    table.Timestamps();            // CreatedAt + UpdatedAt
    table.SoftDelete();            // DeletedAt for soft deletes
    table.IsActive();              // bool with default true
});
```

For AlterTable:

```csharp
ctx.AlterTable("users", table =>
{
    table.AddTimestamps();         // Add CreatedAt + UpdatedAt
    table.AddSoftDelete();         // Add DeletedAt
    table.AddIsActive();           // Add IsActive with default true
    table.AddString("Phone");      // Add required string
    table.AddNullableString("Bio"); // Add nullable string
});
```

#### Fluent Column Chaining

RoomSharp supports two fluent syntax styles for column configuration:

**Style 1: Direct chaining (new!)**
```csharp
// No callback needed - chain methods directly
table.Column<string>("Email").NotNull().Unique();
table.Column<DateTime>("DeletedAt").Nullable();
table.Column<int>("Age").NotNull().Default(0);

// Chain multiple columns together
table.Column<string>("Email").NotNull().Unique()
     .Column<int>("Age").NotNull()
     .Timestamps();
```

**Style 2: Callback configuration**
```csharp
// Use a callback for configuration
table.Column<string>("Email", c => c.NotNull().Unique().Default(""));
table.Column<DateTime>("CreatedAt", c => c.NotNull().TypeAffinity("DATETIME"));
```

<Tip>
Use Style 1 for cleaner syntax. Use Style 2 when you need complex configuration or conditional logic.
</Tip>

## Builder Configuration

Register migrations when building your database:

```csharp
var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UseSqlite("app.db")
    .SetVersion(2)
    .AddMigrations(new UserMigration_1_2())
    .AddCallback(new LoggingCallback(logger))
    .SetJournalMode(JournalMode.WAL)
    .EnableMultiInstanceInvalidation()
    .SetAutoCloseTimeout(TimeSpan.FromMinutes(2))
    .FallbackToDestructiveMigration() // Only for dev/test!
    .Build();
```

## Auto Migration Attributes

| Attribute | Description |
|-----------|-------------|
| `[RenameTable]` | Rename a table |
| `[DeleteTable]` | Drop a table |
| `[AddColumn]` | Add a new column |
| `[DeleteColumn]` | Remove a column |
| `[ColumnRename]` | Rename a column |

### AddColumn Options

You can use `AddColumnAttribute` in two ways:

**Option 1: Raw SQL (flexible but provider-specific)**
```csharp
[AddColumn(TableName = "users", ColumnName = "Age", 
    ColumnDefinition = "INTEGER NOT NULL DEFAULT 0")]
```

**Option 2: Type-safe (recommended)**
```csharp
// Simple int column
[AddColumn(TableName = "users", ColumnName = "Age", 
    ColumnType = typeof(int), Nullable = false, DefaultValue = 0)]

// String with max length
[AddColumn(TableName = "users", ColumnName = "Status", 
    ColumnType = typeof(string), MaxLength = 50, DefaultValue = "active")]

// Nullable DateTime
[AddColumn(TableName = "users", ColumnName = "LastLoginAt", 
    ColumnType = typeof(DateTime), Nullable = true)]
```

| Property | Type | Description |
|----------|------|-------------|
| `TableName` | string | Target table name |
| `ColumnName` | string | New column name |
| `ColumnDefinition` | string | Raw SQL definition (overrides other properties) |
| `ColumnType` | Type | CLR type (typeof(int), typeof(string), etc.) |
| `Nullable` | bool | Allow NULL values (default: true) |
| `DefaultValue` | object | Default value for the column |
| `MaxLength` | int | VARCHAR length for strings (-1 = TEXT) |


## Migrations in Production

<Warning title="Production Safety">

- Migrations run under a real DB transaction via `MigrationContext` and are protected by a provider-specific migration lock.
- RoomSharp fails fast if `__room_state.dirty = 1` (previous migration attempt left the database in an unsafe state).
- Recovery: restore from backup or manually repair schema, then clear `dirty`.
- Rollbacks are not supported unless you implement your own "down" strategy.
- Use destructive fallback only in dev/test environments.

</Warning>

## Migration Tracking

RoomSharp tracks migrations in two tables:

| Table | Purpose |
|-------|---------|
| `__room_state` | Current schema version and dirty flag |
| `__room_migrations` | Applied migrations with ID and checksum |

<Tip>
Use `room migrate:verify` in CI/production to detect schema drift by comparing checksums.
</Tip>

## Understanding Checksums

Checksums are SHA256 hashes that uniquely identify a migration. RoomSharp uses them to detect if a migration was modified after being applied.

### Why Checksums Matter

- **Detect tampering**: If someone modifies an applied migration, the checksum mismatch will fail the next migration run
- **Ensure consistency**: All environments run the exact same migration code
- **Audit trail**: `__room_migrations` stores checksums for verification

### How to Calculate Checksums

There are three approaches:

#### 1. Manual Checksum (Simple)

Hash the SQL statement or a descriptive string:

```csharp
public sealed class AddStatusColumn : IRoomMigration
{
    public int StartVersion => 1;
    public int EndVersion => 2;
    public string Id => "AddStatusColumn:1->2";
    public string Name => nameof(AddStatusColumn);
    
    // Hash the SQL you're executing
    public string Checksum => MigrationChecksum.Sha256(
        "ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'"
    );

    public async Task UpAsync(MigrationContext ctx)
    {
        await ctx.ExecuteAsync("ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'");
    }
}
```

#### 2. Fluent Checksum (Recommended for Fluent Migrations)

Use `MigrationSignature.From()` to automatically compute checksum from fluent operations:

```csharp
public sealed class CreateUsersTable : IRoomMigration
{
    public int StartVersion => 0;
    public int EndVersion => 1;
    public string Id => "CreateUsersTable:0->1";
    public string Name => nameof(CreateUsersTable);
    
    // Define schema changes in a static method
    private static void Build(IMigrationBuilder m)
    {
        m.CreateTable("users", table =>
        {
            table.Column<int>("Id", c => c.PrimaryKey(autoIncrement: true));
            table.Column<string>("Email", c => c.NotNull().Unique());
            table.Column<DateTime>("CreatedAt", c => c.NotNull());
        });
        
        m.CreateIndex("users", "IX_users_email", true, true, "Email");
    }
    
    // Checksum is computed from the fluent operations
    public string Checksum => MigrationSignature.From(DatabaseType.SQLite, Build);

    public Task UpAsync(MigrationContext ctx)
    {
        Build(ctx);
        return Task.CompletedTask;
    }
}
```

#### 3. Auto-Computed (For Migration Base Class)

When extending `Migration`, the checksum is automatically computed from the IL bytecode:

```csharp
public sealed class AddEmailColumn : Migration
{
    public AddEmailColumn() : base(1, 2) { }
    
    // Checksum is auto-computed from UpAsync method body
    
    public override Task UpAsync(MigrationContext ctx)
    {
        ctx.AlterTable("users", t => t.AddColumn<string>("Email"));
        return Task.CompletedTask;
    }
}
```

<Warning title="Checksum Rules">

- **Never modify an applied migration** - The checksum will change and cause failures
- **If you need changes**, create a new migration with a new version
- **Use `MigrationSignature.From()`** for fluent migrations to ensure consistency across databases

</Warning>

## Destructive Fallback

<Danger title="Data Loss Warning">
`FallbackToDestructiveMigration()` drops all tables and restarts the metadata table when downgrading. Only use in development/testing!
</Danger>

```csharp
// ONLY for dev/test environments
var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UseSqlite("app.db")
    .FallbackToDestructiveMigration()
    .Build();
```

## CLI Commands

```bash
# Check migration status
room migrate:status -b

# Apply pending migrations
room migrate:up -b

# Preview migration plan
room migrate:plan --to=3 -b

# Verify checksums in production
room migrate:verify -b
```

## Extension Methods

`MigrationContext` provides extension methods for common operations:

```csharp
public override async Task UpAsync(MigrationContext ctx)
{
    // Execute simple SQL
    await ctx.ExecuteAsync("UPDATE users SET status='active'");
    
    // Execute with parameters
    await ctx.ExecuteAsync("UPDATE users SET status=@status", new { status = "active" });
    
    // Check if table exists
    if (await ctx.TableExistsAsync("legacy_users"))
    {
        ctx.AllowDestructiveOperations();
        ctx.DropTable("legacy_users");
    }
    
    // Check if column exists
    if (!await ctx.ColumnExistsAsync("users", "email"))
    {
        ctx.AlterTable("users", t => t.AddColumn<string>("email"));
    }
```

## Programmatic Seeding

For MAUI/mobile apps where CLI seeding isn't possible, use the programmatic API:

### Basic Usage

```csharp
// Run all discovered seeders
await db.SeedAsync();

// Or with specific options
await db.SeedAsync(new SeederOptions
{
    Environment = "Development",
    AllowProduction = false
});

// Or with explicit seeders
await db.SeedAsync(new SeederOptions
{
    Seeders = new IRoomSeeder[] { new CurrencySeeder(), new CategorySeeder() }
});
```

### Creating Seeders

Use the strongly-typed base class for the best developer experience:

```csharp
[Seeder(Order = 1)]
public class CurrencySeeder : RoomSeeder<MyAppDatabase>
{
    protected override async ValueTask SeedCoreAsync(SeederContext ctx, MyAppDatabase db)
    {
        await ctx.SeedIfEmptyAsync("currencies", db.CurrencyDao.InsertAsync,
            new Currency { Code = "USD", Name = "US Dollar" },
            new Currency { Code = "EUR", Name = "Euro" },
            new Currency { Code = "SAR", Name = "Saudi Riyal" });
    }
}

[Seeder(Order = 2, DependsOn = new[] { typeof(CurrencySeeder) })]
public class AccountSeeder : RoomSeeder<MyAppDatabase>
{
    protected override async ValueTask SeedCoreAsync(SeederContext ctx, MyAppDatabase db)
    {
        if (!ctx.IsDevelopment) return;
        
        await ctx.SeedIfEmptyAsync("accounts", db.AccountDao.InsertAsync,
            new Account { Name = "Cash", Type = AccountType.Asset });
    }
}
```

### SeederContext Helpers

```csharp
// Environment checks
ctx.IsDevelopment  // true for "Development" or "Dev"
ctx.IsProduction   // true for "Production" or "Prod"
ctx.IsEnvironment("Staging")

// SQL execution
await ctx.ExecuteAsync("UPDATE users SET is_active = 1");
await ctx.ExecuteAsync("INSERT INTO logs VALUES (@msg)", new { msg = "Seeded" });

// Table helpers
await ctx.IsTableEmptyAsync("currencies");
await ctx.TableExistsAsync("legacy_table");
await ctx.GetTableRowCountAsync("users");

// Conditional seeding
await ctx.SeedInDevelopmentAsync(async () => { /* dev-only */ });
await ctx.SeedInEnvironmentsAsync(new[] { "Dev", "Staging" }, async () => { });
```

<Tip>
Use `RoomSeeder<TDatabase>` for strongly-typed access to your database and DAOs without manual casting.
</Tip>

## Best Practices

1. **Always use checksums** - Helps detect if migrations were modified after being applied
2. **Test migrations on a copy** - Never test migrations directly on production data
3. **Keep migrations small** - One logical change per migration
4. **Use descriptive names** - `UserMigration_1_2` is better than `Migration1`
5. **Never modify applied migrations** - Create a new migration instead
6. **Back up before migrating** - Especially important for production databases