SQL : Synchronize Read/Write Databases in CQRS , Asp.Net Core

Question

I was reading about DDD and CQRS (using Asp.Net Core ,MSSQL), and their different approaches, then I read a topic about separating Read and Write Database ,so I started to search web about how to do so and how to sync those databases, but sadly(maybe I was searching wrong) I didn't find any good source to find how to do so.

So here is my question :

How should I separate those databases, and then how should I sync the data between them, e.g. I have a table called "User" which is in read and write separated dbs,now if I add a new row to the write table in write db, I have to tell the read db to sync itself with write db so I can have the new data there to query and use later,but how? I also read something about Event Sourcing Pattern or Event-Driven Architecture,but they didn't help me find out how to sync.

so anyone know how to do so or have any good resources about this topic which can help a dummy :)

(consider you're explaining for a guy who is learning it for the first time!).

Thanks!

Bigwig

Dino Esposito created a lot of learning materials about CQRS/EventSourcing in .NET, including samples on github. There are video courses on pluralsight, books, articles in MSDN Magazine, talks at conferences.... But be aware CQRS can be hard and especially Event Sourcing is very hard to get started. It's almost impossible not to get burned on your first project. Even the Dino's materials are somewhat naive and you will quickly find limitations. – Activate

Answer 1

I have a related answer that may provide some background on how to approach CQRS.

The main point to keep in mind is that the "write" side is concerned with changes/transaction (OLTP) and the "read" side is concerned with queries (OLAP).

How you update your "read" side (read model) is going to depend on how you make the "write" side changes. When using an Event Store things may be easier in that each event has a global sequence number and each projection (read model) tracks where it is in terms of the global sequence number. So when new events arrive (projection polls) then they can be actioned if the event applies to the projection.

If you simply update the "write" side with, say, a SQL query then things are going to be a bit different, and possibly tricky, since you don't have any mechanism to replay those changes into the read model should you wish to make changes. In such a case you could use messaging, and possibly store those, or make the changes to the "read" side together with the "write" side... which isn't ideal; unless you need 100% consistency.

As mentioned by @Levi Ramsey, the read model is usually quite a bit different from the write model in that it is optimised for reading so it may include denormalized data or simply be in a data store that is more suited to read models.

Asafetida

Thanks for your answer, as @Levi Ramsey and yours helped me understand this subject, now that I just learned useful information about the concept thanks to both of you, May I ask you if you know any good open source project or sample projects which would help in practicing this approach? – Bigwig

I have an Identity and Access Management (IAM) solution that I use for my own projects. It has a Vue front-end. It actually makes use of my Shuttle.Recall project for Event Sourcing/Projection. You are welcome to reference that as it implements these concepts. However, it may take some leg-work to get up-and-running since I haven't let it loose in the wild yet :) --- I'll dockerize it at some stage. – Asafetida

Thanks for mentioning your project, I will be pleased to see your codes in propose of learning ! And btw, Thanks for the help @Eben Roux. – Bigwig

Answer 2

The main benefit of CQRS is around being able to use different data models and/or different databases for queries vs. updates. If they are using the same data model, there's often not much benefit (at least not with a DB like SQL Server which is, at most scales, reasonable for both) to CQRS.

This in turn implies that it's generally not possible to just have the two databases automatically be in sync, because there's going to be some model translation involved (e.g. from a relational DB (with a normalized schema) like SQL Server to a denormalized document DB like Mongo).

One fairly common pattern is to have the software which writes to the DB also publish events describing what was updated to some event bus. Another piece of software subscribes to those events and performs the appropriate updates to the read DB. Note that this implies the existence of a period of time where queries against the read DB and the write DB will give different results.

Insignia

Thanks for the answer, and btw I didn't know you can have two different databases with different database technologies(e.g. Mongo and MSSQL) to achieve this approach. Anyways, Do you know or have any sample or open source projects which can help me understand and practice this approach? – Bigwig

Admittedly not .Net (though there's a .Net port of a large portion of Akka), but the Akka Platform guide walks through a sample system implemented using cqrs and event sourcing (es isn't required for cqrs: es tends to require cqrs and has a level of mechanical sympathy with cqrs). developer.lightbend.com/docs/akka-platform-guide/concepts/… – Insignia