CG.Infrastructure.Mediator.Data 1.1.1

Infrastructure.Mediator.Data

A .NET library that contains database migration scripts and schema definitions for the Infrastructure.Mediator library, specifically designed for event sourcing with StoredEvents.

Overview

Infrastructure.Mediator.Data is a .NET 9.0 library that provides database infrastructure for the Mediator library. It contains DbUp SQL scripts for creating and maintaining the database schema required for event sourcing functionality.

Features

• Database Schema Management: Automated schema creation and management
• Event Sourcing Support: Database structure for StoredEvents
• DbUp Integration: Seamless integration with DbUp for database migrations
• Clean Architecture: Separation of database scripts from business logic

Target Framework

• .NET 9.0

Package Information

• Package ID: CG.Infrastructure.Mediator.Data
• Version: 1.1.0
• Authors: Matthew Evans
• Company: Matthew Evans
• Product: CG.Infrastructure.Mediator.Data
• Description: Infra Mediator Data library that contains DbUp SQL scripts

Project Structure

Infrastructure.Mediator.Data/
├── Scripts/
│   ├── Schemas/
│   │   └── EnsureSchema.sql          # Schema creation script
│   └── Migrations/
│       └── 000002 - CreateTable - StoredEvents.sql  # StoredEvents table creation

Database Schema

StoredEvents Table

The main table for event sourcing:

CREATE TABLE [dbo].[StoredEvents]
(
    [Id] [uniqueidentifier] NOT NULL,
    [MessageType] [nvarchar](max) NULL,
    [AggregateId] [uniqueidentifier] NOT NULL,
    [Timestamp] [datetime2](7) NOT NULL,
    [Data] [nvarchar](max) NULL,
    [User] [nvarchar](max) NULL,
    CONSTRAINT [PK_StoredEvents] PRIMARY KEY CLUSTERED ([Id] ASC)
)

Columns:

• Id: Unique identifier for the event
• MessageType: Type of the event message
• AggregateId: Identifier for the aggregate root
• Timestamp: When the event occurred
• Data: JSON or serialized event data
• User: User who triggered the event

Usage

Database Migration

This library is designed to be used with DbUp for database migrations. The scripts are embedded as resources and can be executed using the DbUp framework.

Integration with Event.DbUp

The scripts are typically executed through the Event.DbUp project, which provides a command-line interface for database migrations.

Script Execution Order

1. EnsureSchema.sql: Creates the database schema if it doesn't exist
2. CreateTable - StoredEvents.sql: Creates the StoredEvents table

Dependencies

This library has no external dependencies and is designed to be lightweight, containing only the necessary SQL scripts for database setup.

Best Practices

1. Version Control: All database scripts are version-controlled
2. Idempotency: Scripts are designed to be run multiple times safely
3. Schema Variables: Uses DbUp variables for schema names
4. Clean Separation: Database scripts are separate from business logic

Migration Process

Using DbUp

var upgrader = DeployChanges.To
    .SqlDatabase(connectionString)
    .WithScriptsEmbeddedInAssembly(typeof(Program).Assembly)
    .WithVariable("schemaname", "dbo")
    .LogToConsole()
    .Build();

var result = upgrader.PerformUpgrade();

Manual Execution

Scripts can also be executed manually in SQL Server Management Studio or any other SQL client.

Contributing

This project is part of the CG Infrastructure Libraries. For contributions, please refer to the main infrastructure repository guidelines.

License

See the LICENSE file in the root directory for licensing information.

Support

For issues and questions related to this library, please contact the development team or create an issue in the project repository.