Separate Command And Query

Overview

FlexBase implements Command Query Responsibility Segregation (CQRS) as a core architectural pattern, providing clear separation between data modification operations (Commands) and data retrieval operations (Queries). This separation enables independent scaling, optimization, and maintenance of read and write operations.

Architecture Pattern

Command-Query Separation

┌─────────────────┐    ┌─────────────────┐
│   COMMANDS      │    │    QUERIES      │
│   (Write Ops)   │    │   (Read Ops)    │
├─────────────────┤    ├─────────────────┤
│ • Insert        │    │ • GetById       │
│ • Update        │    │ • GetSingle     │
│ • Delete        │    │ • GetList       │
│ • SoftDelete    │    │ • GetPagedList  │
└─────────────────┘    └─────────────────┘
         │                       │
         ▼                       ▼
┌─────────────────┐    ┌─────────────────┐
│ Write Repository│    │ Read Repository │
│ (Write DB)      │    │ (Read DB)       │
└─────────────────┘    └─────────────────┘

Command Operations

Purpose

Commands handle data modification operations that change the state of the system.

Characteristics

• Write Operations: Create, Update, Delete, SoftDelete

• State Changes: Modify data in the system

• Event Publishing: Trigger events after successful operations

• Transaction Support: Full ACID compliance

• Audit Trail: Track who made changes and when

Command Flow

Controller → Service → Command Handler → Domain Model → Write Repository → Write Database → Event Publishing

Command Examples

• Insert: AddOrder, CreateProduct, RegisterUser

• Update: UpdateOrder, EditProduct, ModifyUser

• Delete: DeleteOrder, RemoveProduct, DeactivateUser

• SoftDelete: SoftDeleteOrder, ArchiveProduct, DeactivateUser

Command Implementation

// Command Handler
public class AddOrderHandler : IAddOrderHandler
{
    public virtual async Task Execute(AddOrderCommand cmd, IFlexServiceBusContext serviceBusContext)
    {
        _repoFactory.Init(cmd.Dto);
        
        _model = _flexHost.GetDomainModel<Order>().AddOrder(cmd);
        _repoFactory.GetRepo().InsertOrUpdate(_model);
        int records = await _repoFactory.GetRepo().SaveAsync();
        
        await this.Fire(EventCondition, serviceBusContext);
    }
}

Query Operations

Purpose

Queries handle data retrieval operations that read data without modifying the system state.

Characteristics

• Read Operations: GetById, GetSingle, GetList, GetPagedList

• No State Changes: Read-only operations

• No Events: No side effects or event publishing

• Optimized for Performance: Tuned for fast data retrieval

• Caching Friendly: Can be easily cached

Query Flow

Controller → Service → Query Handler → Read Repository → Read Database → Response

Query Examples

• GetById: GetOrderById, GetProductById, GetUserById

• GetSingle: GetOrderByNumber, GetProductBySku, GetUserByEmail

• GetList: GetOrders, GetProducts, GetUsers

• GetPagedList: GetOrdersPaged, GetProductsPaged, GetUsersPaged

Query Implementation

// Query Handler
public class GetOrderById : FlexiQueryBridge<Order, GetOrderByIdDto>
{
    public override GetOrderByIdDto Fetch()
    {
        var result = Build<Order>().SelectTo<GetOrderByIdDto>().FirstOrDefault();
        return result;
    }

    protected override IQueryable<T> Build<T>()
    {
        _repoFactory.Init(_params);
        IQueryable<T> query = _repoFactory.GetRepo().FindAll<T>().Where(t => t.Id == _params.Id);
        return query;
    }
}

Repository Separation

Write Repository (Commands)

• Purpose: Handles all data modification operations

• Database: Connected to the primary/write database

• Operations: Insert, Update, Delete, SoftDelete

• Transaction Support: Full ACID compliance

• Audit Trail: Tracks all changes

Read Repository (Queries)

• Purpose: Handles all data retrieval operations

• Database: Can be connected to read-only replica or separate read database

• Operations: Select, Filter, Search, Pagination

• Performance Optimized: Tuned for fast queries

• Caching Support: Can be easily cached

Repository Configuration

// Write Repository Registration
services.AddTransient<IWriteDbConnectionProviderBridge, AppSettingsWriteDbConnectionProvider>();

// Read Repository Registration
services.AddTransient<IReadDbConnectionProviderBridge, AppSettingsReadDbConnectionProvider>();

Production Scalability Benefits

1. Independent Database Scaling

Write Database Scaling

• Vertical Scaling: Increase CPU, memory, and storage for write operations

• Optimized for Writes: Tuned for insert, update, delete operations

• Transaction Performance: Optimized for ACID compliance

• Backup Strategy: Full backup and point-in-time recovery

Read Database Scaling

• Horizontal Scaling: Multiple read replicas for high availability

• Read-Only Optimizations: Indexes optimized for query patterns

• Caching Layer: Redis or in-memory caching for frequently accessed data

• Geographic Distribution: Read replicas in different regions

2. Performance Optimization

Write Database Optimizations

-- Write-optimized indexes
CREATE INDEX IX_Orders_CreatedDate ON Orders (CreatedDate);
CREATE INDEX IX_Orders_LastModified ON Orders (LastModifiedDate);

-- Partitioning for large tables
CREATE PARTITION FUNCTION PF_OrdersByDate (datetime)
AS RANGE RIGHT FOR VALUES ('2024-01-01', '2024-02-01', '2024-03-01');

Read Database Optimizations

-- Read-optimized indexes
CREATE INDEX IX_Orders_CustomerId_OrderDate ON Orders (CustomerId, OrderDate);
CREATE INDEX IX_Orders_Status_CreatedDate ON Orders (Status, CreatedDate);

-- Covering indexes for common queries
CREATE INDEX IX_Orders_Covering ON Orders (CustomerId) 
INCLUDE (OrderDate, TotalAmount, Status);

3. High Availability Architecture

Write Database

• Primary Database: Single source of truth for all writes

• Synchronous Replication: Real-time data consistency

• Failover Support: Automatic failover to secondary

• Data Integrity: ACID compliance and consistency

Read Database

• Multiple Replicas: Several read-only replicas

• Asynchronous Replication: Near real-time data availability

• Load Balancing: Distribute read load across replicas

• Geographic Distribution: Replicas in different regions

4. Cost Optimization

Write Database Costs

• High-Performance Hardware: SSD storage, high CPU/memory

• Backup Storage: Full and incremental backups

• Monitoring: Comprehensive monitoring and alerting

• Security: Advanced security and compliance features

Read Database Costs

• Standard Hardware: Cost-effective hardware for read operations

• Reduced Backup Requirements: Less frequent backups

• Basic Monitoring: Essential monitoring for read operations

• Standard Security: Standard security measures

Configuration Examples

Single Database Configuration

// Both commands and queries use the same database
services.AddTransient<IWriteDbConnectionProviderBridge, AppSettingsWriteDbConnectionProvider>();
services.AddTransient<IReadDbConnectionProviderBridge, AppSettingsReadDbConnectionProvider>();

Separate Database Configuration

// Commands use write database
services.AddTransient<IWriteDbConnectionProviderBridge, AppSettingsWriteDbConnectionProvider>();

// Queries use read database
services.AddTransient<IReadDbConnectionProviderBridge, AppSettingsReadDbConnectionProvider>();

Connection String Configuration

{
  "FlexBase": {
    "AppDbConnection": "Data Source=WriteServer;Initial Catalog=YourDatabase;...",
    "AppReadDbConnection": "Data Source=ReadServer;Initial Catalog=YourReadDatabase;..."
  }
}

Real-World Scaling Scenarios

Scenario 1: High-Volume E-Commerce

• Write Database: Handles order creation, updates, and inventory changes

• Read Database: Serves product catalogs, search results, and analytics

• Scaling: Read replicas in multiple regions for global customers

Scenario 2: Financial Services

• Write Database: Processes transactions, account updates, and payments

• Read Database: Serves account balances, transaction history, and reports

• Scaling: Read replicas for compliance reporting and analytics

Scenario 3: Social Media Platform

• Write Database: Handles posts, comments, and user interactions

• Read Database: Serves feeds, timelines, and search results

• Scaling: Multiple read replicas for content delivery

Monitoring and Metrics

Write Database Metrics

• Transaction Rate: Commands per second

• Response Time: Average command execution time

• Error Rate: Failed command percentage

• Resource Utilization: CPU, memory, disk usage

Read Database Metrics

• Query Rate: Queries per second

• Response Time: Average query execution time

• Cache Hit Rate: Percentage of cached queries

• Replica Lag: Data freshness across replicas

Best Practices

1. Database Design

• Write Database: Normalized for consistency and integrity

• Read Database: Denormalized for query performance

• Indexing Strategy: Different indexes for write vs read patterns

2. Caching Strategy

• Write Operations: No caching (always fresh data)

• Read Operations: Aggressive caching for frequently accessed data

• Cache Invalidation: Smart invalidation on data changes

3. Monitoring

• Separate Monitoring: Different monitoring for write and read operations

• Performance Metrics: Track performance independently

• Alerting: Different alerting thresholds for each database

4. Backup and Recovery

• Write Database: Full backup and point-in-time recovery

• Read Database: Less frequent backups, can be rebuilt from write database

Key Benefits

• Independent Scaling: Scale read and write operations separately

• Performance Optimization: Optimize each database for its specific workload

• High Availability: Multiple read replicas for better availability

• Cost Optimization: Use appropriate hardware for each workload

• Maintenance: Perform maintenance on read replicas without affecting writes

• Geographic Distribution: Place read replicas closer to users

• Disaster Recovery: Better disaster recovery options

• Load Distribution: Distribute read load across multiple replicas

---

This Command-Query separation in FlexBase provides a robust foundation for building scalable, high-performance applications that can handle both high-volume writes and complex read operations efficiently! 🚀