Netmash.Infrastructure.DatabaseMigration

A comprehensive database migration library for .NET applications that supports multiple database providers and flexible metadata storage options.

Features

• Multi-Database Support: SQL Server and SQLite
• Flexible Metadata Storage: XML files or database tables
• Multi-Schema Support: Separate migration metadata for multiple services
• Version-Based Migrations: Organized script execution based on semantic versioning
• Table Prefixing: Support for prefixed table names to avoid conflicts
• Automatic Schema Creation: Creates migration tables and schemas automatically
• Logging Integration: Comprehensive logging of migration activities
• ASP.NET Core Integration: Easy dependency injection setup

Installation

Add the package to your project:

dotnet add package Netmash.Infrastructure.DatabaseMigration

Quick Start

1. Basic Setup

For Web Applications

// In Startup.cs or Program.cs
services.AddMigration();

// In Configure method or app startup
app.RunMigration();

For Worker Services

// In Program.cs
var host = builder.Build();
host.RunMigration();
host.Run();

2. Configuration

Add connection string to appsettings.json:

{
  "ConnectionStrings": {
    "DatabaseConnection": "Server=localhost;Database=MyApp;Trusted_Connection=true;"
  }
}

3. Organize Migration Scripts

Create directory structure:

Scripts/
├── 1.0.0/
│   ├── 01.CreateUserTable.sql
│   └── 02.CreateIndexes.sql
├── 1.0.1/
│   ├── 01.AddUserRoles.sql
│   └── 02.UpdateUserData.sql
└── 1.1.0/
    └── 01.AddAuditFields.sql

Configuration Options

AddMigration Parameters

Parameter	Type	Default	Description
databaseType	DatabaseType	SQLite	Database provider (SQLite, SQLServer)
metadataStorage	MetadataStorage	XmlFile	Where to store migration metadata
connectionName	string	"DatabaseConnection"	Connection string name in configuration
workspace	string	"Workspace"	Working directory for XML files
scriptsDirectoryPath	string	"Scripts"	Directory containing migration scripts
metadataSchema	string	"migration"	Database schema for migration tables (SQL Server only)
metadataPrefix	string	null	Prefix for table names or XML file names

Usage Examples

SQL Server with Database Metadata Storage

services.AddMigration(
    databaseType: DatabaseType.SQLServer,
    metadataStorage: MetadataStorage.Database,
    connectionName: "DefaultConnection",
    scriptsDirectoryPath: "Database/Migrations"
);

Multi-Service Setup with Schema Separation

// Service 1
services.AddMigration(
    databaseType: DatabaseType.SQLServer,
    metadataStorage: MetadataStorage.Database,
    metadataSchema: "service1_migrations"
);

// Service 2
services.AddMigration(
    databaseType: DatabaseType.SQLServer,
    metadataStorage: MetadataStorage.Database,
    metadataSchema: "service2_migrations"
);

SQLite with Table Prefixing

services.AddMigration(
    databaseType: DatabaseType.SQLite,
    metadataStorage: MetadataStorage.Database,
    metadataPrefix: "myservice_"
);
// Creates tables: myservice_MigrationSignature, myservice_MigratedVersion, myservice_MigratedScript

XML File Storage with Prefixing

services.AddMigration(
    metadataStorage: MetadataStorage.XmlFile,
    metadataPrefix: "userservice_",
    workspace: "C:\\MyApp\\Data"
);
// Creates file: C:\MyApp\Data\userservice_MigrationSignatures.xml

Database Schema Creation

SQL Server

The library automatically creates:

• Schema (if specified): CREATE SCHEMA [your_schema]
• Migration tables in the specified schema
• Proper foreign key relationships

SQLite

The library automatically creates:

• Migration tables (with optional prefix)
• Proper foreign key relationships

Migration Script Organization

Scripts are organized by version directories and executed in alphabetical order:

Scripts/
├── 1.0.0/           # Version 1.0.0 scripts
│   ├── 01.Users.sql
│   └── 02.Roles.sql
├── 1.0.1/           # Version 1.0.1 scripts
│   └── 01.Permissions.sql
└── 2.0.0/           # Version 2.0.0 scripts
    ├── 01.RefactorTables.sql
    └── 02.MigrateData.sql

Connection String Workspace Placeholder

Use {Workspace} placeholder in connection strings for dynamic paths:

{
  "ConnectionStrings": {
    "DatabaseConnection": "Data Source={Workspace}\\myapp.db"
  }
}

Metadata Storage Options

XML File Storage

• Pros: Simple, file-based, easy to backup
• Cons: Not suitable for distributed deployments
• Use Case: Single-instance applications, development environments

Database Storage

• Pros: Centralized, supports distributed deployments, transactional
• Cons: Requires database access, slightly more complex
• Use Case: Production environments, multiple service instances

Validation Rules

The library enforces several validation rules:

1. Prefix Validation: metadataPrefix can only contain letters, numbers, and underscores
2. SQLite Schema Limitation: SQLite doesn't support custom schemas - use metadataPrefix instead
3. XML File Schema Limitation: Custom schemas only work with database storage
4. Provider Compatibility: Custom schemas only supported with SQL Server

Migration Tables Schema

MigrationSignature

• Id (int, primary key)
• MigrationDate (datetime)
• MachineName (varchar)
• LastVersion (varchar)

MigratedVersion

• Id (int, primary key)
• SignatureId (int, foreign key)
• Version (varchar)

MigratedScript

• Id (int, primary key)
• VersionId (int, foreign key)
• Script (varchar)

Logging

The library provides comprehensive logging:

// Configuration logging
[INFO] Database migration configured with DatabaseType: SQLServer, MetadataStorage: Database
[INFO] Using database schema: microservice1
[INFO] Using metadata prefix: ms1_

// Migration execution logging
[INFO] Starting migration...
[INFO] Running script pack '1.0.1' with 3 scripts:
[INFO] Running sql script: 'Scripts\1.0.1\01.CreateTable.sql'

Error Handling

Common exceptions and solutions:

Invalid Prefix

ArgumentException: MetadataPrefix can only contain letters, numbers, and underscores.

Solution: Use only alphanumeric characters and underscores in prefix.

SQLite Schema Error

NotSupportedException: SQLite provider does not support multiple schemas.

Solution: Use metadataPrefix instead of metadataSchema with SQLite.

XML Schema Error

NotSupportedException: MetadataSchema can only be used when metadataStorage is Database.

Solution: Use database storage or remove custom schema parameter.

Advanced Configuration

Custom Workspace with Dynamic Connection String

// appsettings.json
{
  "ConnectionStrings": {
    "DatabaseConnection": "Data Source={Workspace}\\myapp.db"
  }
}

// Startup
services.AddMigration(
    databaseType: DatabaseType.SQLite,
    workspace: "C:\\MyAppData"
);
// Results in: Data Source=C:\MyAppData\myapp.db

Multiple Services Sharing Database

// User Service
services.AddMigration(
    databaseType: DatabaseType.SQLServer,
    metadataStorage: MetadataStorage.Database,
    metadataSchema: "user_migrations",
    scriptsDirectoryPath: "UserService/Migrations"
);

// Order Service
services.AddMigration(
    databaseType: DatabaseType.SQLServer,
    metadataStorage: MetadataStorage.Database,
    metadataSchema: "order_migrations",
    scriptsDirectoryPath: "OrderService/Migrations"
);

Best Practices

1. Version Naming: Use semantic versioning (1.0.0, 1.0.1, 1.1.0)
2. Script Naming: Use numbered prefixes (01.CreateTable.sql, 02.AddIndex.sql)
3. Idempotent Scripts: Write scripts that can be run multiple times safely
4. Backup Strategy: Always backup databases before running migrations
5. Testing: Test migrations on development/staging environments first
6. Rollback Planning: Plan rollback strategies for complex migrations

Migration Process Flow

1. Initialization: Check and create migration metadata storage
2. Version Detection: Determine last applied migration version
3. Script Discovery: Find unapplied script versions
4. Execution: Run scripts in version and alphabetical order
5. Tracking: Record successful migrations in metadata
6. Logging: Log all activities and results

Troubleshooting

Migration Not Running

• Verify connection string is correct
• Check that Scripts directory exists and contains version folders
• Ensure proper permissions on database/file system

Metadata Table Missing

• Verify database connection
• Check that the application has database creation permissions
• Review logs for schema creation errors

Version Skipping

• Ensure version folders follow semantic versioning
• Check for missing or incorrectly named version directories
• Verify script execution order and dependencies

License

This project is part of the Netmash framework. See the main repository for license information.

Contributing

Contributions are welcome! Please follow the established patterns and include appropriate tests.

Version History

• 1.4.1: API improvements - better extension method naming and safety
• 1.4.0: Multi-schema support, table prefixing
• 1.3.0: .NET 8 upgrade
• 1.2.0: .NET 6 upgrade
• 1.1.0: Database metadata storage support
• 1.0.0: Initial release with XML file storage