DataMigratorToPostgres 0.0.2

MSSQL to PostgreSQL Migration Library

From a paid db to free

A comprehensive .NET library for migrating data from Microsoft SQL Server to PostgreSQL databases with support for multiple sources, dynamic table creation, and flexible configuration options.

Features

• Multiple Source Support: Migrate from one or multiple MSSQL databases
• Dynamic Table Creation: Automatically creates PostgreSQL tables based on MSSQL schema
• Flexible Migration Modes: Insert, Upsert, Overwrite, or Truncate existing data
• Table Prefixing: Add custom prefixes to target table names
• Batch Processing: Configurable batch sizes for optimal performance
• Schema Mapping: Automatic data type mapping from MSSQL to PostgreSQL
• Connection Testing: Built-in connection validation
• Comprehensive Logging: Detailed migration results and error reporting
• Unit Testing: Full test coverage with integration tests using TestContainers

Installation

dotnet add package DataMigratorToPostgres

Quick Start

using DataMigratorToPostgres.Models;
using DataMigratorToPostgres.Services;

var migrationService = new DataMigrationService();

var options = new MigrationOptions
{
    TablePrefix = "migrated_",
    Mode = MigrationMode.Insert,
    BatchSize = 1000
};

var result = await migrationService.MigrateAsync(
    "Server=localhost;Database=SourceDB;Trusted_Connection=true;",
    "Host=localhost;Database=TargetDB;Username=postgres;Password=password;",
    options);

Console.WriteLine($"Migration {(result.Success ? "successful" : "failed")}");
Console.WriteLine($"Migrated {result.TotalRowsMigrated} rows in {result.Duration}");

Configuration Options

MigrationOptions

Migration Modes

• Insert: Insert new data only (skip if table exists)
• Upsert: Update existing data and insert new data (requires primary key)
• Overwrite: Drop and recreate target tables
• Truncate: Clear target tables before inserting

Data Type Mapping

The library automatically maps MSSQL data types to PostgreSQL equivalents:

Multiple Source Migration

var sourceConnections = new[]
{
    "Server=server1;Database=DB1;Trusted_Connection=true;",
    "Server=server2;Database=DB2;Trusted_Connection=true;"
};

var result = await migrationService.MigrateAsync(
    sourceConnections,
    targetConnectionString,
    options);

Advanced Usage

Table Filtering

var options = new MigrationOptions
{
    // Exclude system tables
    ExcludeTables = new HashSet<string> { "SystemLog", "TempData" },
    
    // Only migrate specific tables
    IncludeTables = new HashSet<string> { "Users", "Orders", "Products" }
};

Custom Column Mappings

var options = new MigrationOptions
{
    ColumnMappings = new Dictionary<string, Dictionary<string, string>>
    {
        ["Users"] = new Dictionary<string, string>
        {
            ["user_id"] = "id",
            ["user_name"] = "username"
        }
    }
};

Connection Testing

var sourceValid = await migrationService.TestConnectionAsync(sourceConnectionString, false);
var targetValid = await migrationService.TestConnectionAsync(targetConnectionString, true);

if (!sourceValid || !targetValid)
{
    Console.WriteLine("Connection test failed");
    return;
}

Error Handling

The library provides comprehensive error reporting:

var result = await migrationService.MigrateAsync(/* ... */);

if (!result.Success)
{
    Console.WriteLine("Migration failed:");
    foreach (var error in result.Errors)
    {
        Console.WriteLine($"- {error}");
    }
}

// Check individual table results
foreach (var tableResult in result.TableResults.Values)
{
    if (!tableResult.Success)
    {
        Console.WriteLine($"Table {tableResult.SourceTableName} failed:");
        foreach (var error in tableResult.Errors)
        {
            Console.WriteLine($"  - {error}");
        }
    }
}

Testing

The library includes comprehensive unit and integration tests using TestContainers:

# Run all tests
dotnet test

# Run only unit tests
dotnet test --filter "Category=Unit"

# Run only integration tests
dotnet test --filter "Category=Integration"

Performance Considerations

• Batch Size: Adjust based on available memory and network latency
• Connection Pooling: Enable connection pooling for better performance
• Indexes: Consider disabling index creation during migration for faster inserts
• Parallel Processing: The library processes tables sequentially by design for data consistency

License

This project is licensed under the MIT License.

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.