> For the complete documentation index, see [llms.txt](https://docs.flexbase.in/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.flexbase.in/core-concepts/separate-command-and-query.md). # 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** ```csharp // Command Handler public class AddOrderHandler : IAddOrderHandler { public virtual async Task Execute(AddOrderCommand cmd, IFlexServiceBusContext serviceBusContext) { _repoFactory.Init(cmd.Dto); _model = _flexHost.GetDomainModel().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** ```csharp // Query Handler public class GetOrderById : FlexiQueryBridge { public override GetOrderByIdDto Fetch() { var result = Build().SelectTo().FirstOrDefault(); return result; } protected override IQueryable Build() { _repoFactory.Init(_params); IQueryable query = _repoFactory.GetRepo().FindAll().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** ```csharp // Write Repository Registration services.AddTransient(); // Read Repository Registration services.AddTransient(); ``` ## 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** ```sql -- 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** ```sql -- 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** ```csharp // Both commands and queries use the same database services.AddTransient(); services.AddTransient(); ``` ### **Separate Database Configuration** ```csharp // Commands use write database services.AddTransient(); // Queries use read database services.AddTransient(); ``` ### **Connection String Configuration** ```json { "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!** 🚀