Database Migrations

​

Migration Architecture

​

Understanding the Migrator

public class DbMigrator
{
    private readonly string _connectionString;
    private readonly ILogger<DbMigrator> _logger;
    private readonly bool _skipDatabasePreparation;
    private readonly bool _noTransactionMigration;

    public bool MigrateMsSqlDatabaseWithRetries(
        bool enableLogging = true,
        bool repeatable = false,
        string folderName = MigratorConstants.DefaultMigrationsFolderName,
        bool dryRun = false,
        CancellationToken cancellationToken = default)
    {
        // Retry logic for SQL Server script upgrade mode
        var attempt = 1;
        while (attempt < 10)
        {
            try
            {
                if (!_skipDatabasePreparation)
                {
                    PrepareDatabase(cancellationToken);
                }
                return MigrateDatabase(enableLogging, repeatable, folderName, dryRun, cancellationToken);
            }
            catch (SqlException ex)
            {
                if (ex.Message.Contains("Server is in script upgrade mode."))
                {
                    attempt++;
                    Thread.Sleep(20000);
                }
                else
                {
                    throw;
                }
            }
        }
        return false;
    }
}

​

Key Features

• Automatic Database Creation - Creates the database if it doesn’t exist
• Retry Logic - Handles SQL Server upgrade mode automatically
• Transaction Support - Wraps migrations in transactions by default
• Dry Run Mode - Preview migrations without applying them
• Multiple Providers - SQL Server, MySQL, PostgreSQL support

​

Migration Folders

• DbScripts/ - Main migration scripts (SQL Server)
• DbScripts_transition/ - Transitional scripts for major changes
• DbScripts_finalization/ - Post-migration cleanup scripts
• MySql/ - MySQL-specific migrations
• Postgres/ - PostgreSQL-specific migrations

​

Running Migrations

​

Local Development

cd util/MsSqlMigratorUtility
dotnet run "Server=localhost;Database=vault_dev;User Id=SA;Password=your_password;Encrypt=True;TrustServerCertificate=True"

dotnet run --dry-run "connection_string_here"

SELECT * FROM dbo.Migration
ORDER BY ScriptName;

​

Production Deployment

1. During Application Startup - Via DatabaseMigrationHostedService in the Admin service:

public class DatabaseMigrationHostedService : IHostedService
{
    public async Task StartAsync(CancellationToken cancellationToken)
    {
        var migrator = new DbMigrator(_connectionString, _logger);
        var success = migrator.MigrateMsSqlDatabaseWithRetries(
            enableLogging: true,
            cancellationToken: cancellationToken
        );
        
        if (!success)
        {
            throw new Exception("Database migration failed");
        }
    }
}

2. As Part of CI/CD - Before deploying application containers

​

Creating New Migrations

YYYY-MM-DD-HH-MM_DescriptiveName.sql

-- Add new column to User table
IF COL_LENGTH('[dbo].[User]', 'Preferences') IS NULL
BEGIN
    ALTER TABLE [dbo].[User]
    ADD [Preferences] NVARCHAR(MAX) NULL
END
GO

-- Create index if it doesn't exist
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_User_Preferences')
BEGIN
    CREATE NONCLUSTERED INDEX [IX_User_Preferences]
    ON [dbo].[User]([Preferences])
END
GO

<ItemGroup>
  <EmbeddedResource Include="DbScripts\*.sql" />
</ItemGroup>

cd util/MsSqlMigratorUtility

# Dry run first
dotnet run --dry-run "your_test_connection_string"

# Apply to test database
dotnet run "your_test_connection_string"

​

Migration Best Practices

​

Make Migrations Idempotent

-- Good: Check before adding column
IF COL_LENGTH('[dbo].[Table]', 'NewColumn') IS NULL
BEGIN
    ALTER TABLE [dbo].[Table]
    ADD [NewColumn] NVARCHAR(50) NULL
END
GO

-- Bad: Will fail if run twice
ALTER TABLE [dbo].[Table]
ADD [NewColumn] NVARCHAR(50) NULL
GO

​

Use Transactions Appropriately

var migrator = new DbMigrator(
    connectionString,
    logger,
    noTransactionMigration: true  // Disable transaction for this migration
);

​

Handle Large Data Migrations

-- Process in batches
DECLARE @BatchSize INT = 10000;
DECLARE @RowsAffected INT = @BatchSize;

WHILE @RowsAffected = @BatchSize
BEGIN
    UPDATE TOP (@BatchSize) [dbo].[LargeTable]
    SET [NewColumn] = 'DefaultValue'
    WHERE [NewColumn] IS NULL;
    
    SET @RowsAffected = @@ROWCOUNT;
END
GO

​

Maintain Backward Compatibility

1. Step 1: Deploy code that no longer uses the column
2. Step 2: After deployment, create migration to drop the column

​

Document Complex Migrations

-- Migration: Add support for user preferences
-- Jira: BW-12345
-- Description: Adds a JSONB column to store user UI preferences
-- Author: [email protected]
-- Date: 2026-03-10

IF COL_LENGTH('[dbo].[User]', 'Preferences') IS NULL
BEGIN
    ALTER TABLE [dbo].[User]
    ADD [Preferences] NVARCHAR(MAX) NULL
END
GO

​

Migration Execution Order

1. DbScripts/ - Main schema changes
2. DbScripts_transition/ - Transitional logic
3. DbScripts_finalization/ - Cleanup and finalization

​

Troubleshooting

​

Migration Failed Mid-Execution

1. Check the migration journal:
   SELECT * FROM dbo.Migration ORDER BY ScriptName DESC;
   
2. Manually rollback changes if needed
3. Fix the migration script
4. The migrator will retry on next run

​

Script Upgrade Mode Error

# Check SQL Server status
docker logs bitwarden_mssql

# Restart SQL Server
docker restart bitwarden_mssql

​

Multiple Database Providers Out of Sync

# Compare migration counts
ls util/Migrator/DbScripts/*.sql | wc -l
ls util/MySqlMigrations/Migrations/*.sql | wc -l
ls util/PostgresMigrations/Migrations/*.sql | wc -l

​

Related Files

• util/Migrator/DbMigrator.cs:15 - Core migration logic
• util/Migrator/SqlServerDbMigrator.cs:1 - SQL Server-specific implementation
• src/Admin/HostedServices/DatabaseMigrationHostedService.cs:1 - Production migration service

​

See Also

• DbUp Documentation
• Contributing Guide - Submit migration changes
• Testing Guide - Test migrations with integration tests