DbEasy.Migrations 1.0.0-beta07

DbEasy.Migrations

Lightweight and developer-friendly database migration and schema management system for .NET.

DbEasy.Migrations extends the DbEasy ecosystem with database schema snapshots, model comparison, migration operations, SQL generation, seed data, custom SQL, stored procedures, constraints, and developer-oriented validation and logging.

---

🚀 Overview

DbEasy.Migrations provides a lightweight migration infrastructure designed to detect database model changes and generate the required SQL operations.

It supports:

• 📸 Database and model snapshots
• 🔍 Snapshot comparison
• 🏗️ Table and column migrations
• 🔄 Renamed tables and columns detection
• 🔐 Constraint management
• 🌱 Seed data
• 📝 Custom SQL operations
• ⚙️ Stored procedures
• 🛡️ Migration validation
• 📋 Migration logging
• 🗄️ SQL Server support
• 🐘 PostgreSQL support

---

📦 Installation

dotnet add package DbEasy.Migrations

---

🧩 Main Features

📸 Snapshot Management

DbEasy.Migrations can represent the structure of a database through snapshots.

Snapshots can contain:

• Tables
• Columns
• Primary keys
• Foreign keys
• Unique constraints
• Check constraints
• Schema information

Snapshots can also be serialized, saved, loaded, validated, and compared.

---

🔍 Snapshot Comparison

The migration system can compare two snapshots and identify structural differences.

Supported changes include:

• Added tables
• Removed tables
• Added columns
• Removed columns
• Modified columns
• Renamed tables
• Renamed columns
• Changed constraints

This allows migrations to be generated from differences between models or database schemas.

---

🔐 Constraints

DbEasy.Migrations supports several constraint types.

Unique Constraints

Unique constraints can be represented in snapshots and converted into migration operations and SQL.

Check Constraints

Check constraints can be represented using their SQL expression.

Example:

ALTER TABLE [Users]
ADD CONSTRAINT [CK_Users_Age]
CHECK ([Age] >= 0);

Foreign Keys

Foreign key relationships contain:

• Constraint name
• Principal table
• Principal column
• Dependent table
• Dependent column
• Relationship type

---

🌱 Seed Data

DbEasy.Migrations provides migration operations for inserting initial data.

Example:

var operation =
    new SeedOperation(
        "Users",
        new Dictionary<string, object?>
        {
            ["Id"] = 1,
            ["Name"] = "Seed User",
            ["Age"] = 25
        });

Seed operations can be:

• Generated as SQL
• Executed against the database
• Rolled back

---

📝 Custom SQL

Custom SQL operations allow migration authors to execute SQL that is not covered by the standard migration operations.

Example:

var operation =
    new SqlOperation(
        "CREATE INDEX IX_Users_Name ON Users(Name);");

Custom SQL operations support:

• SQL generation
• Execution
• Rollback

---

⚙️ Stored Procedures

DbEasy.Migrations supports stored procedure migration operations.

Supported operations include:

• Create procedure
• Drop procedure
• SQL generation
• SQL Server support
• PostgreSQL support

---

🛡️ Migration Validation

The Developer Experience feature introduces validation mechanisms designed to detect invalid migration definitions before they are executed.

Duplicate Tables

The validation system detects duplicate table definitions.

Example error:

Duplicate table detected.
Table: 'Users'.
Occurrences: 2.
Table names must be unique within the same schema.

---

Duplicate Columns

Duplicate column definitions are detected within a table.

Example:

Duplicate column detected.
Table: 'Users'.
Column: 'Name'.
Occurrences: 2.
Column names must be unique within a table.

---

Duplicate Primary Key Columns

Primary key column definitions are validated to prevent the same column from being declared multiple times.

---

Relationship Validation

Foreign key relationships are validated against the snapshot.

The validation checks:

• Foreign key name
• Dependent table
• Dependent column
• Principal table
• Principal column
• Existence of referenced tables
• Existence of referenced columns
• Column type compatibility

Example:

Invalid foreign key 'FK_Orders_Users'.
Relationship: 'Orders.UserId' → 'Users.Id'.
Principal table 'Users' does not exist in the snapshot.

---

📋 Migration Logging

DbEasy.Migrations provides a lightweight migration logging abstraction.

Logger

The logging system exposes:

IMigrationLogger

with support for:

LogInformation(...)
LogWarning(...)
LogError(...)
LogDebug(...)
LogSql(...)
LogExecutedSql(...)

---

Generated SQL Logging

Generated SQL can be logged before execution.

Example:

[DbEasy.Migrations SQL]
ALTER TABLE [Users] ADD [Email] NVARCHAR(255);

This makes it easier to inspect the SQL generated by the migration system.

---

Executed SQL Logging

SQL successfully executed against the database can also be logged.

Example:

[DbEasy.Migrations EXECUTED SQL]
ALTER TABLE [Users] ADD [Email] NVARCHAR(255);

The executed SQL is logged only after successful execution.

---

Error Logging

Migration execution errors can be logged together with the associated exception.

Example:

[DbEasy.Migrations ERROR] Migration execution failed.
Exception: SqlException
Message: ...

Errors are logged and then propagated to the caller so that migration failures are not silently ignored.

---

🗄️ Database Support

DbEasy.Migrations currently targets relational databases through SQL dialect implementations.

Supported database providers include:

SQL Server

SQL Server migration operations are supported.

PostgreSQL

PostgreSQL-specific SQL generation is supported through the PostgreSQL dialect.

---

🏗️ Architecture

The migration system is organized around several main concepts:

DbEasy.Migrations
│
├── Snapshot
│   ├── ModelSnapshot
│   ├── SchemaSnapshot
│   ├── TableSnapshot
│   ├── ColumnSnapshot
│   ├── ForeignKeySnapshot
│   ├── UniqueConstraintSnapshot
│   └── CheckConstraintSnapshot
│
├── Diff
│   └── ModelDifference
│
├── Operations
│   ├── Table operations
│   ├── Column operations
│   ├── Constraint operations
│   ├── Seed operations
│   ├── SQL operations
│   └── Procedure operations
│
├── Sql
│   ├── SQL generators
│   └── Database dialects
│
├── Validation
│   ├── DuplicateTableValidator
│   ├── DuplicateColumnValidator
│   └── RelationshipValidator
│
└── Logging
    ├── IMigrationLogger
    └── MigrationLogger

---

🔄 Migration Workflow

A typical migration workflow is:

Model / Database
       │
       ▼
   Snapshot
       │
       ▼
 Snapshot Comparison
       │
       ▼
 ModelDifference
       │
       ▼
Migration Operations
       │
       ▼
   Validation
       │
       ▼
   SQL Generation
       │
       ▼
 Generated SQL Logging
       │
       ▼
 SQL Execution
       │
       ├── Success → Executed SQL Logging
       │
       └── Error   → Error Logging

---

🧪 Validation Example

var validator =
    new RelationshipValidator();

validator.Validate(snapshot);

If the snapshot contains an invalid relationship, an informative InvalidOperationException is raised.

---

📋 Logging Example

var logger =
    new MigrationLogger();

logger.LogInformation(
    "Migration started.");

logger.LogSql(
    "ALTER TABLE [Users] ADD [Email] NVARCHAR(255);");

logger.LogExecutedSql(
    "ALTER TABLE [Users] ADD [Email] NVARCHAR(255);");

---

🎯 Developer Experience

The Developer Experience improvements are designed to make migrations easier to diagnose and maintain.

The system now provides:

• Clear validation errors
• Relationship validation
• Duplicate definition detection
• Generated SQL logging
• Executed SQL logging
• Exception logging
• Consistent migration diagnostics

---

🗺️ Roadmap

Future versions may extend:

• Advanced migration validation
• Additional constraint validation
• More database providers
• More migration operation types
• Advanced logging providers
• Migration history management
• Automatic migration execution
• Advanced schema comparison

---

📄 License

MIT License

---

📌 Status

DbEasy.Migrations is currently distributed as a beta package.

The API may evolve before the first stable release.