Beyond the Database: Practical CQRS Implementation for Read/Write Separation

CQRS Overview: Not a New Concept

CQRS (Command Query Responsibility Segregation) is the architectural version of Bertrand Meyer’s Command‑Query Separation principle. A Command changes state and returns nothing (e.g., void doSomething(...)), while a Query returns data without changing state (e.g., SomeType query(...)).

At the architectural level CQRS means separating the model that handles commands from the model that handles queries.

Lightweight CQRS in a Single Database (L1)

The following interfaces already demonstrate a CQRS split:

public class SalaryCalculationAppService {
    public PaySlipId calculateMonthlyPaySlip(...){ ... }
    public void approve(PaySlipId id){ ... }
    public void revoke(PaySlipId id){ ... }
}

public interface PaySlipQueryService {
    Page<PaySlipListItem> query(PaySlipQuery q, Pageable p);
    PaySlipMonthlySummary summarize(PayCycle cycle);
}

Even though both sides use the same database, commands and queries are isolated at the interface level.

Evolution Stages (L1‑L4)

L1 Interface Separation : Same database, Command uses aggregates/repositories, Query uses DTO/SQL. Suitable for most business systems.

L2 Data‑Source Separation : Query reads from a read‑only replica or view, Command writes to the primary. Applies when read/write pressure differs.

L3 Dedicated Read Model : Query uses Elasticsearch, Redis, ClickHouse, etc., for complex list searches and reports. Ideal for read‑heavy, write‑light scenarios.

L4 Event‑Driven Synchronization : Command side emits domain events, which are asynchronously consumed to update the read model. Fits micro‑services, cross‑BC queries, and high concurrency.

Why Separate Read and Write

Write operations need strong consistency and transactional guarantees; read operations need flexibility and high performance. For example, HR may request "employees with net salary > 20 000". Using the write model forces an N+1 query, loading entire aggregates into memory and causing performance explosion.

// Anti‑pattern: query via aggregate, N+1
List<PaySlip> all = paySlipRepository.findAllByCycle(cycle);
return all.stream()
    .filter(p -> p.getNetSalary().isGreaterThan(Money.ofYuan("20000")))
    .map(p -> {
        Employee e = employeeRepository.findById(p.getEmployeeId()).orElseThrow();
        Department d = departmentRepository.findById(e.getDeptId()).orElseThrow();
        return new PaySlipListItem(p.getId().getValue(), e.getName(), d.getName(), p.getNetSalary(), p.getStatus(), p.getIssuedAt());
    })
    .collect(Collectors.toList());

The model mismatch (aggregate vs flat list) is the root cause.

Command Side Guidelines

One Command → one Handler (e.g., CalculatePaySlipHandler, ApprovePaySlipHandler).

Commands are immutable data carriers; use value objects (e.g., EmployeeId, PayCycle).

Handlers return either void or the aggregate ID; never return DTOs.

public class CalculatePaySlipHandler {
    @Transactional
    public PaySlipId handle(CalculatePaySlipCommand cmd) {
        // business logic unchanged
        paySlip.markAsCalculated();
        paySlipRepository.save(paySlip);
        // domain event emitted inside save
        return paySlip.getId();
    }
}

Query Side Guidelines

The query side works with DTOs only, without aggregates or business rules.

public class PaySlipListQuery {
    private String employeeName; // fuzzy search
    private String departmentId;
    private Money minNetSalary;
    private Money maxNetSalary;
    private PayCycle cycle;
    private PaySlipStatus status;
}

public interface PaySlipQueryService {
    Page<PaySlipListItem> query(PaySlipListQuery q, Pageable pageable);
    PaySlipDetailDTO detail(PaySlipId id);
    PaySlipMonthlySummary summarize(PayCycle cycle);
}

Implementation can use plain JDBC/MyBatis for L1/L2 or a dedicated client for L3/L4.

@Repository
public class JdbcPaySlipQueryService implements PaySlipQueryService {
    private final JdbcTemplate jdbc;
    @Override
    public Page<PaySlipListItem> query(PaySlipListQuery q, Pageable pageable) {
        String sql = """
            SELECT ps.id, e.name AS employee_name, d.name AS dept_name,
                   ps.net_amount, ps.status, ps.issued_at
            FROM pay_slip ps
            JOIN employee e ON e.id = ps.employee_id
            JOIN department d ON d.id = e.department_id
            WHERE ps.pay_cycle = ?
              AND (? IS NULL OR e.name LIKE CONCAT('%', ?, '%'))
              AND (? IS NULL OR e.department_id = ?)
              AND (? IS NULL OR ps.net_amount >= ?)
              AND (? IS NULL OR ps.net_amount <= ?)
              AND (? IS NULL OR ps.status = ?)
            ORDER BY ps.issued_at DESC
            """;
        // map result to PaySlipListItem
    }
}

Event‑Driven Read‑Model Synchronization (L4)

When the read model is external (e.g., Elasticsearch), the write side publishes a domain event after persisting the aggregate.

@Service
public class CalculatePaySlipHandler {
    @Transactional
    public PaySlipId handle(CalculatePaySlipCommand cmd) {
        // calculate pay slip
        paySlip.markAsCalculated();
        paySlipRepository.save(paySlip); // event written to outbox
        return paySlip.getId();
    }
}

The read‑model listener consumes the event and updates the ES index.

@Service
@RocketMQMessageListener(topic = "payroll-events", selectorExpression = "PAY_SLIP_CALCULATED", consumerGroup = "payroll-readmodel-payslip-consumer")
public class PaySlipCalculatedESUpdater implements RocketMQListener<String> {
    private final ElasticsearchRestTemplate esTemplate;
    private final EmployeeQueryClient employeeClient;
    private final DepartmentQueryClient deptClient;
    @Override
    public void onMessage(String message) {
        PaySlipCalculated event = parse(message);
        PaySlipListItem doc = PaySlipListItem.builder()
            .paySlipId(event.getPaySlipId().getValue())
            .employeeId(event.getEmployeeId().getValue())
            .employeeName(employeeClient.getName(event.getEmployeeId()))
            .departmentName(deptClient.getNameByEmployee(event.getEmployeeId()))
            .netSalary(event.getNetSalary())
            .status(PaySlipStatus.CALCULATED)
            .issuedAt(LocalDate.now())
            .build();
        IndexQuery query = new IndexQueryBuilder()
            .withId(doc.getPaySlipId())
            .withObject(doc)
            .build();
        esTemplate.index(query, IndexCoordinates.of("payslip_list"));
    }
}

Read queries now hit Elasticsearch directly, achieving millisecond‑level response times.

@Repository
public class ESPaySlipQueryService implements PaySlipQueryService {
    private final ElasticsearchRestTemplate esTemplate;
    @Override
    public Page<PaySlipListItem> query(PaySlipListQuery q, Pageable pageable) {
        NativeQuery query = NativeQuery.builder()
            .withQuery(bool(b -> {
                if (q.getEmployeeName() != null) b.must(match("employeeName", q.getEmployeeName()));
                if (q.getDepartmentId() != null) b.must(term("departmentId", q.getDepartmentId()));
                if (q.getMinNetSalary() != null) b.must(range("netSalary").gte(q.getMinNetSalary().getAmount()));
                // ... other criteria
                return b;
            }))
            .withPageable(pageable)
            .build();
        SearchHits<PaySlipListItem> hits = esTemplate.search(query, PaySlipListItem.class);
        return SearchHitSupport.searchPageFor(hits, pageable).map(SearchHit::getContent);
    }
}

Benefits and Trade‑offs

Write fast, read fast : Writes stay transactional on the primary DB; reads benefit from ES’s full‑text and aggregation capabilities.

Independent scaling : Scale the write DB for payroll calculation bursts and scale ES nodes for HR reporting bursts without interference.

Cross‑BC queries become natural : Redundant fields (employee name, department) are stored in the read model, eliminating N+1 calls.

Eventual consistency : There is a millisecond‑to‑second lag between write and read visibility, acceptable for payroll list pages. Critical immediate‑visibility cases can still query the primary DB.

When to Adopt CQRS

Adopt a more complex CQRS stage only if at least two of the following hold:

Read/write imbalance : >90% reads, <10% writes, and queries are becoming complex.

Model shape mismatch : Write aggregate (e.g., PaySlip + PaySlipLineItem) differs significantly from the flat list DTO needed for UI.

Cross‑BC query needs : Data needed for a list spans multiple bounded contexts.

If none apply, stick to a simple CRUD approach.

Common CQRS Anti‑Patterns

Forcing every query into the read model, even simple ID lookups.

Read model joins the write DB directly instead of consuming events.

Introducing full event sourcing from the start; event sourcing is separate from CQRS and adds unnecessary complexity.

Mapping read‑model DTOs with an ORM, re‑introducing aggregate‑level overhead.

Conclusion

CQRS is a powerful tactical pattern but easy to misuse. Start with zero‑cost interface separation (L1), then progress to data‑source separation, dedicated read models, and finally event‑driven synchronization as the system grows. The three decision criteria help avoid over‑engineering.