How to manage primary keys in CQRS

Question

I'm building a backend following the CQRS pattern and I don't know how to manage properly the primary keys (surrogate keys) between the command and query databases.

For example: I have a model with two different tables in each database. When I want to update the model in his different tables, each one doesn't have the same ID (this is not my real schema):

players (cmd db)
--------------------------------
| id | name | wins |   email   |
--------------------------------
  1    Mark   200    mk@kogames

players (query db)
-----------------------------------------------------
| id | name | wins | role_power | role_name | email |
-----------------------------------------------------
  12   Mark   200      2120         melee   mk@kogames

As you can see Mark has two different IDs: 1 and 12. What are the recommended strategies in order to service CRUD operations and reference both registries properly? I was wondering if store both keys in any kind of storage as follows would be well-suited:

---------------------------------
|   email   | cmd_id | query_id |
---------------------------------
  mk@kogames     1        12

But it doesn't seems to be a well designed solution.

Answer 1

There are a couple of options, the important thing to keep in mind is that the command database is the source of truth and the query database is eventually consistent. Therefor the command database cannot have a dependency on the query database. A separate table with both keys is not neccessary if the query db has some sort of reference to the data in the command database.

Options to use as reference in the query db:

• A natural key, in this case email.
• Generate a GUID and store that in both databases.
• Use the surrogate PK of the command db as a regular field in the query db.
• Use the surrogate PK of the command db as the PK in the query db.

My personal preference would be to use a natural key, but that's often tricky because of changing requirements.

My next choice would be to generate a GUID.

I consider auto-generated keys an implementation detail of the database and using those in another database tightly couples the two together. It can be the simplest solution, but can also cause headaches in the future.

Overall the safest option is to generate a GUID and couple the records that way.

Answer 2

The problem with this question is that you are naming specific concepts and then doing things that are different or unrelated. This makes it impossible to gauge whether you misunderstood the concept you're trying to use, or whether you're mislabeling your actual implementation with the wrong concept names.

As is the nature with questions posted here, I can only go off of what you mention, so this answer presumes that you have named the right concepts and that the issues are stemming from a misimplementation.

---

TL;DR

It's much better to sidestep these problems than try to live with and tackle them. Therefore, adjust your approach to allow for PK replication and data mirroring (as opposed to transformation).

---

Why separating the read/write stores?

The main goal of CQRS is to separate your read and write store, which is what your scenario seems to focus on. The main reason to separate the read/write stores is so that you can scale read/write stores independently, for performance reasons (i.e. when the queries vastly outnumber the mutations).

Well, different databases is not mandatory for reads and writes separation, but I'm using CQRS that way.

I actually agree here. I often pre-emptively separate my read and write stores even though I don't use distributed database servers yet, because it allows me to easily separate them if the need ever arises.
Similarly, the DEV environments I work in tend not to use distributed database servers even if the PROD environments do.

However, given that you are posting two completely different tables which are allegedly mirrored, that means you are working with at least two different databases (if not servers). Which means that these two (or more) stores need to keep up with one another.

---

Aligning the write and read stores

The key concept here is that the separated write and read stores are mirrored. Eventual consistency aside, the read store should be exactly equal to the write store. No data changes (again, eventual consistency aside) and most definitely no structural changes.

This is not the case for you. Not only are your PKs completely different, the read store table also has additional data fields that the write stores does not. That means that these are not mirrored databases.

I also noticed you mentioned:

I did, but the ORM doesn't allow setting the ID manually if I want to associate the PK as a FK on other relations.

I infer from this that you've implemented your data replication as a business logic algorithm, and are presumably calculating and adding these extra columns.

There is nothing wrong with scheduled data synchronization between tables, including data transformation, but CQRS and separate read/write stores are unrelated to this.

However, even in these kinds of transformations, the ability to replicate a PK value seems to be very much desireable, unless we're talking about replicating into a growing history table (rather than an overwritten "current state" table).
Given your example, it seems like your table is a "current state" table, rather than a growing historical log, so PK replication is very much desirable specifically to prevent all the synchronization issues that you currently find yourself battling.

---

How to replicate the write store to the read store?

In order to properly mirror a database, it actually helps to just to a dumb copy. Since the read store never changes (because no writes happen on it), you could effectively get away with taking a backup of the write store and overwriting the read store with that backed up copy.

It's not the most efficient procedure, but I'm mentioning it because it proves the point that a dumb copy is actually desirable here. Any smart logic you put into this process can actually detract from the viability of the design, as it changes what should be a perfect mirror image.

This is where it starts mattering which data provider you use, because more often than not, the mirroring happens outside of your application. SQL server allows you to set up a one-way replication between databases which does not require active effort from your side (i.e. the application logic). Similarly, cloud databases tend to have similar features or run with this out of the box when set up as a scaled set.

I can't account for every possible provider here. But in all cases, what you need to look into is the dumbest, most straightforward copy feature that you can find, preferably with minimal to no impact on availability.