1

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.

8
  • Have you considered simply using the PK from the command database as PK for the query database?
    – Rik D
    Apr 29 at 19:20
  • Thank you @RikD, 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. Apr 29 at 20:11
  • 3
    Why are there separate databases for Command and Query? That's not what CQRS means. Apr 29 at 23:00
  • Well, different databases is not mandatory for reads and writes separation, but I'm using CQRS that way. May 3 at 10:57
  • 1
    @RobertHarvey: Specific implementation of OP's particular example aside, one of the major reasons to implement CQRS (nowadays, which IIRC wasn't the driving incentive when CQRS was first designed) is to enable the codebase to gracefully handle the separation of the write store and read store(s). It's not particularly confusing to mention CQRS and the separation of data stores in the same breath.
    – Flater
    May 31 at 10:26
0

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.

1
  • Probably I'm going to keep using the natural key, but maybe I will consider to use a GUID, although I don't want to add more fields/relationships to the models. Thank you so much! May 3 at 11:01
0

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.

2
  • Hi, thanks for your response. The read model has more columns than the other one because it works as a view. That way allows me to query information (about a player) without performing a JOIN between tables. That's why it has more columns: it doesn't mirror one table, but two or more. Jun 2 at 8:32
  • @PabloCumpiánDíaz: If it were a view, then you'd have access to the same ID value. The fact that a different PK is being generating suggests that you're using a different table (with its own PK sequence). Regardless, I understand why you're transforming your data, and there's definitely value to doing things like that (cutting down on repeated calculations etc), but this is simply not something that CQ(R)S entails, and you're not going to find specific information on this. There's nothing wrong with what you're doing but you need to separately tackle the replication and the transformation.
    – Flater
    Jun 2 at 8:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.