CQRS for Databases — Separate Read/Write Stores

In this tutorial, you'll learn how CQRS separates database read models from write models for independent optimization.

What You'll Learn

how CQRS separates database read models from write models for independent optimization.

Why It Matters

The same database design rarely optimizes both reads and writes. CQRS optimizes each separately.

Real-World Use

Read replicas, materialized views, Elasticsearch for reads + PostgreSQL for writes.

The CQRS for Databases Pattern

The CQRS for Databases pattern addresses a specific recurring design problem by providing a reusable solution structure. Understanding when and how to apply it is essential for writing maintainable, scalable code.

Key Concepts

• Registry/Tracking: CQRS for Databases maintains a registry of objects or operations.
• Atomicity: Changes are grouped into units that succeed or fail together.
• Isolation: Each unit operates independently.
• Consistency: The pattern ensures data integrity across operations.

Structure

The following diagram shows the structure of this pattern:

classDiagram
    class CQRSforDatabases {
        +findById()
        +findAll()
        +save()
        +delete()
    }
    class Database { +query() }
    Client --> CQRSforDatabases
    CQRSforDatabases --> Database

Implementation

from typing import List
from dataclasses import dataclass

@dataclass
class Migration:
    version: int
    name: str
    sql: str

class CQRSforDatabases:
    def __init__(self):
        self._migrations: List[Migration] = []
        self._applied: set = set()

    def register(self, m: Migration):
        self._migrations.append(m)

    def migrate(self):
        for m in sorted(self._migrations, key=lambda x: x.version):
            if m.version not in self._applied:
                print(f"Applying v{m.version}: {m.name}")
                print(f"  SQL: {m.sql}")
                self._applied.add(m.version)

    def rollback(self, version: int):
        if version in self._applied:
            print(f"Rolling back v{version}")
            self._applied.remove(version)

migrator = CQRSforDatabases()
migrator.register(Migration(1, "Create users", "CREATE TABLE users (...)"))
migrator.register(Migration(2, "Add email", "ALTER TABLE users ADD email TEXT"))
migrator.migrate()
print("---")
migrator.rollback(1)

Expected output:

Applying v1: Create users
  SQL: CREATE TABLE users (...)
Applying v2: Add email
  SQL: ALTER TABLE users ADD email TEXT
---
Rolling back v1

Key Participants

• CQRS for Databases: Coordinates tracking and persistence of changes.
• Entity: The domain object being tracked.
• Client: Code that uses the CQRS for Databases.
• Data Mapper: Handles actual database operations.

Real-World Examples

• DodaTech uses this pattern internally for consistent cross-cutting concerns.
• Major frameworks and libraries implement this pattern as a core architectural element.
• Production systems at scale depend on this pattern for reliability.

Related Patterns

• Cqrs

• Event Sourcing

• Sharding

• Design Patterns — the complete patterns catalog.

Pros and Cons

Common Mistakes

1. **Over-engineering: Applying CQRS for Databases where a simpler solution suffices, adding unnecessary complexity.

2. **Wrong granularity: Implementing CQRS for Databases at the wrong level of abstraction.

3. **Thread Safety ignored: Using CQRS for Databases in concurrent context without proper synchronization.

4. **Tight coupling: Violating the pattern intent by creating hidden dependencies.

5. **Premature optimization: Introducing CQRS for Databases before there is evidence it is needed.

Practice Questions

1. What problem does the CQRS for Databases pattern solve? Describe a real-world scenario where using it improves code quality.

2. How does CQRS for Databases differ from alternative approaches? What are the trade-offs?

3. What testing Strategy would you use for code that implements CQRS for Databases?

4. How would you refactor legacy code to introduce CQRS for Databases?

5. When should you NOT use CQRS for Databases? Describe scenarios where it adds unnecessary complexity.

Challenge

Implement a complete CQRS for Databases example in Python with unit tests. Include error handling, edge cases (empty data, null values, concurrent access), and a performance comparison against a simpler alternative. Document your design decisions.

Real-World Task

Find a section of code in your current project that could benefit from the CQRS for Databases pattern. Refactor it, write tests, and measure the improvement in testability, coupling, and cohesion.

Security Tip: When implementing CQRS for Databases, ensure proper input validation, avoid exposing internal state, and follow Least Privilege. At DodaTech, all implementations undergo security review.

Built by the developers of Doda Browser, DodaZIP, and Durga Antivirus Pro.