Auto-Migration

Flaggy includes a built-in migration system that automatically manages database schema creation and updates. No manual SQL scripts required - just configure your provider and Flaggy handles the rest.

Overview

The auto-migration system provides:

• Automatic Schema Creation: Tables created on first run
• Version Tracking: Migration history stored in database
• Incremental Updates: Only runs pending migrations
• Zero Downtime: Safe for production deployments
• Idempotent: Safe to run multiple times
• Multi-Provider: Works with MySQL, PostgreSQL, and MS SQL Server
• Customizable: Optional manual control

How It Works

When you start your application with auto-migration enabled:

1. Check Version Table: Looks for flaggy_migrations table
2. Create If Missing: Creates version table if it doesn't exist
3. Get Current Version: Queries current database version
4. Find Pending Migrations: Compares with available migrations
5. Run Migrations: Executes migrations in order (1, 2, 3, etc.)
6. Record Version: Updates version table with timestamp

Application Start
       │
       ▼
┌──────────────────┐
│ Check Version    │
│ Table Exists?    │
└────────┬─────────┘
         │
         ├─No──► Create flaggy_migrations table
         │
         ▼
┌──────────────────┐
│ Get Current      │
│ Version (0)      │
└────────┬─────────┘
         │
         ▼
┌──────────────────┐
│ Check Pending    │
│ Migrations       │
│ (1, 2, 3)        │
└────────┬─────────┘
         │
         ▼
┌──────────────────┐
│ Run Migration 1  │
│ Record Version   │
└────────┬─────────┘
         │
         ▼
┌──────────────────┐
│ Run Migration 2  │
│ Record Version   │
└────────┬─────────┘
         │
         ▼
┌──────────────────┐
│ Run Migration 3  │
│ Record Version   │
└────────┬─────────┘
         │
         ▼
   Application Ready

Configuration

Enable Auto-Migration (Default)

Auto-migration is enabled by default for all database providers:

using Flaggy.Extensions;

var builder = WebApplication.CreateBuilder(args);

// Auto-migration enabled by default
builder.Services.AddFlaggy(options =>
{
    options.UseMySQL(
        connectionString: "Server=localhost;Database=myapp;User=root;Password=pass;"

    );
});

var app = builder.Build();
app.Run();

Explicit Configuration

// Explicitly enable auto-migration
builder.Services.AddFlaggy(options =>
{
    options.UseMySQL(
        connectionString: "Server=localhost;Database=myapp;User=root;Password=pass;",
        autoMigrate: true // Default: true
    );
});

// PostgreSQL
builder.Services.AddFlaggy(options =>
{
    options.UsePostgreSQL(
        connectionString: "Host=localhost;Database=myapp;username=postgres;Password=pass",
        autoMigrate: true
    );
});

// MS SQL Server
builder.Services.AddFlaggy(options =>
{
    options.UseMsSql(
        connectionString: "Server=localhost;Database=myapp;User Id=sa;Password=pass;TrustServerCertificate=True",
        autoMigrate: true
    );
});

Disable Auto-Migration

For manual control:

builder.Services.AddFlaggy(options =>
{
    options.UseMySQL(
        connectionString: "Server=localhost;Database=myapp;User=root;Password=pass;",
        autoMigrate: false // Disable automatic migration
    );
});

Note: When disabled, you must manually create tables before using Flaggy.

Migration Tables

Version Tracking Table

The flaggy_migrations table tracks which migrations have been applied:

MySQL

CREATE TABLE flaggy_migrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    version INT NOT NULL,
    description VARCHAR(500) NOT NULL,
    applied_at DATETIME NOT NULL,
    UNIQUE KEY UK_Version (Version)
);

PostgreSQL

CREATE TABLE flaggy_migrations (
    id SERIAL PRIMARY KEY,
    version INT NOT NULL UNIQUE,
    description VARCHAR(500) NOT NULL,
    applied_at TIMESTAMP NOT NULL
);

MS SQL Server

CREATE TABLE flaggy_migrations (
    id INT IDENTITY(1,1) PRIMARY KEY,
    version INT NOT NULL UNIQUE,
    description NVARCHAR(500) NOT NULL,
    applied_at DATETIME NOT NULL
);

Example data:

SELECT * FROM flaggy_migrations;

Id	Version	description	applied_at
1	1	Initial create feature_flags table	2025-01-15 10:30:00
2	2	Add Value column to feature_flags	2025-01-15 10:30:01
3	3	Create users table	2025-01-15 10:30:02

Migration History

Version 1: Initial Schema

Creates the core feature_flags table:

MySQL

CREATE TABLE feature_flags (
    `key` VARCHAR(255) PRIMARY KEY,
    is_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    description TEXT NULL,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
);

PostgreSQL

CREATE TABLE feature_flags (
    key VARCHAR(255) PRIMARY KEY,
    is_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    description TEXT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

MS SQL Server

CREATE TABLE feature_flags (
    [key] NVARCHAR(255) PRIMARY KEY,
    is_enabled BIT NOT NULL DEFAULT 0,
    description NVARCHAR(MAX) NULL,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
);

Version 2: Add Value Column

Adds the value column for storing flag values:

MySQL

ALTER TABLE feature_flags
ADD COLUMN `value` TEXT NULL AFTER is_enabled;

PostgreSQL

ALTER TABLE feature_flags
ADD COLUMN value TEXT NULL;

MS SQL Server

ALTER TABLE feature_flags
ADD [value] NVARCHAR(MAX) NULL;

Version 3: Create Users Table

Creates the users table for dashboard authentication:

MySQL

CREATE TABLE users (
    username VARCHAR(255) PRIMARY KEY,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(255) NULL,
    created_at DATETIME NULL
);

PostgreSQL

CREATE TABLE users (
    username VARCHAR(255) PRIMARY KEY,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(255) NULL,
    created_at TIMESTAMP NULL
);

MS SQL Server

CREATE TABLE users (
    username NVARCHAR(255) PRIMARY KEY,
    password_hash NVARCHAR(255) NOT NULL,
    email NVARCHAR(255) NULL,
    created_at DATETIME NULL
);

Custom Table Names

You can customize table names:

using Flaggy.Extensions;

builder.Services.AddFlaggy(options =>
{
    options.UseMySQL(
        connectionString: "Server=localhost;Database=myapp;User=root;Password=pass;",
    tableName: "app_feature_flags",        // Custom flag table name
    userTableName: "app_users",            // Custom user table name
    autoMigrate: true
    );
});

Migrations will use the custom names:

-- Custom table names
CREATE TABLE app_feature_flags (...);
CREATE TABLE app_users (...);
CREATE TABLE flaggy_migrations (...);  -- Version table name is always flaggy_migrations

Migration Safety

Idempotent Operations

Migrations use IF NOT EXISTS and similar checks to ensure they're safe to run multiple times:

-- Safe to run multiple times
CREATE TABLE IF NOT EXISTS feature_flags (...);

-- Checks before adding column
ALTER TABLE feature_flags
ADD COLUMN IF NOT EXISTS `value` TEXT NULL;

Version Checking

The migration system checks the current version before running:

// Gets current version from database
var currentVersion = await GetCurrentVersionAsync();  // Returns 0 if no migrations

// Only runs migrations with version > currentVersion
var pendingMigrations = _migrations
    .Where(m => m.Version > currentVersion)
    .OrderBy(m => m.Version);

Transaction Support

Each migration runs in a transaction (provider-dependent):

// Conceptual flow
using var transaction = connection.BeginTransaction();
try
{
    await migration.UpAsync(connection);
    await UpdateVersionAsync(migration.Version, migration.description);
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Troubleshooting

Migration Table Doesn't Exist

Problem: flaggy_migrations table not created.

Solution: Ensure auto-migration is enabled:

builder.Services.AddFlaggy(options =>
{
    options.UseMySQL(
        connectionString: connectionString,
        autoMigrate: true // Must be true
    );
});

Permission Errors

Problem: Database user lacks permission to create tables.

Solution: Grant CREATE and ALTER permissions:

-- MySQL
GRANT CREATE, ALTER ON myapp.* TO 'flaggy_user'@'localhost';

-- PostgreSQL
GRANT CREATE ON SCHEMA public TO flaggy_user;

-- MS SQL Server
GRANT CREATE TABLE TO flaggy_user;
GRANT ALTER TO flaggy_user;

Migration Version Mismatch

Problem: Application expects a newer schema version.

Solution: Check migration history:

SELECT * FROM flaggy_migrations ORDER BY Version;

If migrations are missing, ensure auto-migration is enabled or run manually.

Table Already Exists

Problem: Migration fails because table exists.

Solution: Migrations use IF NOT EXISTS by default. If you created tables manually, migrations should skip existing tables.

If the issue persists, check the version table:

-- Check current version
SELECT MAX(Version) FROM flaggy_migrations;

-- Manually insert missing version record if needed
INSERT INTO flaggy_migrations (Version, description, applied_at)
VALUES (1, 'Manual table creation', NOW());

Multiple Instances Running Simultaneously

Problem: Multiple application instances trying to migrate simultaneously.

Solution: The migration system uses database locks (via unique constraint on Version column). Only one instance will succeed; others will wait or skip.

For additional safety:

// Add retry logic
builder.Services.AddFlaggy(options =>
{
    options.UseMySQL(
        connectionString: connectionString: connectionString,
        autoMigrate: true
    );
});

// Wait for migrations to complete
await Task.Delay(TimeSpan.FromSeconds(5));

Manual Migration

If you prefer manual control, disable auto-migration and run migrations yourself:

Step 1: Disable Auto-Migration

builder.Services.AddFlaggy(options =>
{
    options.UseMySQL(
        connectionString: connectionString: connectionString,
        autoMigrate: false
    );
});

Step 2: Create Migration Scripts

Extract the SQL from migration classes or use the examples in this documentation.

Step 3: Run Migrations Manually

# MySQL
mysql -u root -p myapp < migration_001.sql
mysql -u root -p myapp < migration_002.sql
mysql -u root -p myapp < migration_003.sql

# PostgreSQL
psql -U postgres -d myapp -f migration_001.sql
psql -U postgres -d myapp -f migration_002.sql
psql -U postgres -d myapp -f migration_003.sql

# MS SQL Server
sqlcmd -S localhost -d myapp -U sa -P password -i migration_001.sql
sqlcmd -S localhost -d myapp -U sa -P password -i migration_002.sql
sqlcmd -S localhost -d myapp -U sa -P password -i migration_003.sql

Step 4: Update Version Table

-- Create version table
CREATE TABLE flaggy_migrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    version INT NOT NULL UNIQUE,
    description VARCHAR(500) NOT NULL,
    applied_at DATETIME NOT NULL
);

-- Record migrations
INSERT INTO flaggy_migrations (Version, description, applied_at)
VALUES
    (1, 'Initial create feature_flags table', NOW()),
    (2, 'Add Value column to feature_flags', NOW()),
    (3, 'Create users table', NOW());

Production Deployment

Option 1: Auto-Migration (Recommended)

Enable auto-migration for seamless deployments:

builder.Services.AddFlaggy(options =>
{
    options.UseMySQL(
        connectionString: builder.Configuration.GetConnectionString("FlaggyDb"
    );
}),
    autoMigrate: true
);

Benefits: - Zero manual intervention - Safe for rolling deployments - Automatic on startup

Considerations: - Requires database CREATE/ALTER permissions - First instance to start runs migrations

Option 2: Pre-Deployment Migration

Run migrations before deploying application:

# Step 1: Run migration tool/script
dotnet run --project MigrationTool

# Step 2: Deploy application with auto-migration disabled
# In appsettings.Production.json
{
  "Flaggy": {
    "AutoMigrate": false
  }
}

Benefits: - Full control over migration timing - Can test migrations in staging first - Suitable for strict change control environments

Considerations: - Requires separate migration process - More deployment steps

Option 3: Database Administrator Managed

DBA runs migrations manually:

-- DBA runs SQL scripts
-- Version 1
CREATE TABLE feature_flags (...);

-- Version 2
ALTER TABLE feature_flags ADD COLUMN `value` TEXT NULL;

-- Version 3
CREATE TABLE users (...);

-- Update version table
INSERT INTO flaggy_migrations (Version, description, applied_at)
VALUES (1, 'Manual migration', NOW());

Benefits: - Maximum control - Can be part of change management process - Suitable for highly regulated environments

Considerations: - Most manual work - Coordination required between DBA and developers

Best Practices

1. Enable Auto-Migration in Development

if (builder.Environment.IsDevelopment())
{
    builder.Services.AddFlaggy(options =>
{
    options.UseMySQL(
        connectionString: connectionString,
        autoMigrate: true // Auto-migration in dev
    );
});
}
else
{
    builder.Services.AddFlaggy(options =>
{
    options.UseMySQL(
        connectionString: connectionString,
        autoMigrate: false // Manual control in production
    );
});
}

2. Test Migrations in Staging

Always test migrations in a staging environment before production:

# Staging environment
export ASPNETCORE_ENVIRONMENT=Staging
dotnet run

# Verify migrations
psql -d staging_db -c "SELECT * FROM flaggy_migrations;"

3. Backup Before Migration

Backup database before running migrations:

# MySQL
mysqldump -u root -p myapp > backup_before_migration.sql

# PostgreSQL
pg_dump -U postgres myapp > backup_before_migration.sql

# MS SQL Server
sqlcmd -S localhost -Q "BACKUP DATABASE myapp TO DISK='backup_before_migration.bak'"

4. Monitor Migration Execution

Log migration execution:

// Migrations are logged automatically
// Check application logs for:
// "Running migration: Version 1 - Initial create feature_flags table"
// "Migration completed: Version 1"

5. Use Version Control

Keep migration scripts in version control even if using auto-migration:

project/
├── migrations/
│   ├── mysql/
│   │   ├── 001_initial_create.sql
│   │   ├── 002_add_value_column.sql
│   │   └── 003_create_users_table.sql
│   ├── postgresql/
│   │   ├── 001_initial_create.sql
│   │   ├── 002_add_value_column.sql
│   │   └── 003_create_users_table.sql
│   └── mssql/
│       ├── 001_initial_create.sql
│       ├── 002_add_value_column.sql
│       └── 003_create_users_table.sql

6. Document Migration Changes

Document what each migration does:

// Version 1: Initial schema creation
// - Creates feature_flags table with basic columns
// - Supports key, enabled state, description, timestamps

// Version 2: Add value support
// - Adds Value column to feature_flags
// - Enables typed flag values for configuration

// Version 3: User management
// - Creates users table for dashboard authentication
// - Supports username, password hash, email, created date

Future Migrations

As Flaggy evolves, new migrations will be added automatically. The system ensures:

• Backward Compatibility: Existing data is never lost
• Incremental Updates: Only new migrations run
• Version Tracking: Clear history of changes

Example future migration (Version 4):

-- Hypothetical future migration
ALTER TABLE feature_flags
ADD COLUMN Tags VARCHAR(500) NULL;

Your database will automatically upgrade from version 3 to version 4 on next startup.

Related Topics

• Providers - Database provider configuration
• User Management - Users table created by migrations
• Dashboard - Using the dashboard after schema creation
• Programmatic API - Managing flags after migration