Using an RDBMS as event sourcing storage

If I were using an RDBMS (e.g. SQL Server) to store event sourcing data, what might the schema look like?

I've seen a few variations talked about in an abstract sense, but nothing concrete.

For example, say one has a "Product" entity, and changes to that product could come in the form of: Price, Cost and Description. I'm confused about whether I'd:

1. Have a "ProductEvent" table, that has all the fields for a product, where each change means a new record in that table, plus "who, what, where, why, when and how" (WWWWWH) as appropriate. When cost, price or description are changed, a whole new row as added to represent the Product.
2. Store product Cost, Price and Description in separate tables joined to the Product table with a foreign key relationship. When changes to those properties occur, write new rows with WWWWWH as appropriate.
3. Store WWWWWH, plus a serialised object representing the event, in a "ProductEvent" table, meaning the event itself must be loaded, de-serialised and re-played in my application code in order to re-build the application state for a given Product.

Particularly I worry about option 2 above. Taken to the extreme, the product table would be almost one-table-per-property, where to load the Application State for a given product would require loading all events for that product from each product event table. This table-explosion smells wrong to me.

I'm sure "it depends", and while there's no single "correct answer", I'm trying to get a feel for what is acceptable, and what is totally not acceptable. I'm also aware that NoSQL can help here, where events could be stored against an aggregate root, meaning only a single request to the database to get the events to rebuild the object from, but we're not using a NoSQL db at the moment so I'm feeling around for alternatives.

Solution 1:

The event store should not need to know about the specific fields or properties of events. Otherwise every modification of your model would result in having to migrate your database (just as in good old-fashioned state-based persistence). Therefore I wouldn't recommend option 1 and 2 at all.

Below is the schema as used in Ncqrs. As you can see, the table "Events" stores the related data as a CLOB (i.e. JSON or XML). This corresponds to your option 3 (Only that there is no "ProductEvents" table because you only need one generic "Events" table. In Ncqrs the mapping to your Aggregate Roots happens through the "EventSources" table, where each EventSource corresponds to an actual Aggregate Root.)

Table Events:
    Id [uniqueidentifier] NOT NULL,
    TimeStamp [datetime] NOT NULL,

    Name [varchar](max) NOT NULL,
    Version [varchar](max) NOT NULL,

    EventSourceId [uniqueidentifier] NOT NULL,
    Sequence [bigint], 

    Data [nvarchar](max) NOT NULL

Table EventSources:
    Id [uniqueidentifier] NOT NULL, 
    Type [nvarchar](255) NOT NULL, 
    Version [int] NOT NULL

The SQL persistence mechanism of Jonathan Oliver's Event Store implementation consists basically of one table called "Commits" with a BLOB field "Payload". This is pretty much the same as in Ncqrs, only that it serializes the event's properties in binary format (which, for instance, adds encryption support).

Greg Young recommends a similar approach, as extensively documented on Greg's website.

The schema of his prototypical "Events" table reads:

Table Events
    AggregateId [Guid],
    Data [Blob],
    SequenceNumber [Long],
    Version [Int]

Solution 2:

The GitHub project CQRS.NET has a few concrete examples of how you could do EventStores in a few different technologies. At time of writing there is an implementation in SQL using Linq2SQL and a SQL schema to go with it, there's one for MongoDB, one for DocumentDB (CosmosDB if you're in Azure) and one using EventStore (as mentioned above). There's more in Azure like Table Storage and Blob storage which is very similar to flat file storage.

I guess the main point here is that they all conform to the same principal/contract. They all store information in a single place/container/table, they use metadata to identify one event from another and 'just' store the whole event as it was - in some cases serialised, in supporting technologies, as it was. So depending on if you pick a document database, relational database or even flat file, there's several different ways to all reach the same intent of an event store (it's useful if you change you mind at any point and find you need to migrate or support more than one storage technology).

As a developer on the project I can share some insights on some of the choices we made.

Firstly we found (even with unique UUIDs/GUIDs instead of integers) for many reasons sequential IDs occur for strategic reasons, thus just having an ID wasn't unique enough for a key, so we merged our main ID key column with the data/object type to create what should be a truly (in the sense of your application) unique key. I know some people say you don't need to store it, but that will depend on if you are greenfield or having to co-exist with existing systems.

We stuck with a single container/table/collection for maintainability reasons, but we did play around with a separate table per entity/object. We found in practise that meant either the application needed "CREATE" permissions (which generally speaking is not a good idea... generally, there's always exceptions/exclusions) or each time a new entity/object came into existence or was deployed, new storage containers/tables/collections needed to be made. We found this was painfully slow for local development and problematic for production deployments. You may not, but that was our real-world experience.

Another things to remember is that asking action X to happen may result in many different events occurring, thus knowing all the events generated by a command/event/what ever is useful. They may also be across different object types e.g. pushing "buy" in a shopping cart may trigger account and warehousing events to fire. A consuming application may want to know all of this, so we added a CorrelationId. This meant a consumer could ask for all events raised as a result of their request. You'll see that in the schema.

Specifically with SQL, we found that performance really became a bottleneck if indexes and partitions weren't adequately used. Remember events will needs to be streamed in reverse order if you are using snapshots. We tried a few different indexes and found that in practise, some additional indexes were needed for debugging in-production real-world applications. Again you'll see that in the schema.

Other in-production metadata was useful during production based investigations, timestamps gave us insight into the order in which events were persisted vs raised. That gave us some assistance on a particularly heavily event driven system that raised vast quantities of events, giving us information about the performance of things like networks and the systems distribution across the network.

Solution 3:

Well you might wanna give a look at Datomic.

Datomic is a database of flexible, time-based facts, supporting queries and joins, with elastic scalability, and ACID transactions.

I wrote a detailed answer here

You can watch a talk from Stuart Halloway explaining the design of Datomic here

Since Datomic stores facts in time, you can use it for event sourcing use cases, and so much more.

Solution 4:

I think solution (1 & 2) can become a problem very quickly as your domain model evolves. New fields are created, some change meaning, and some can become no longer used. Eventually your table will have dozens of nullable fields, and loading the events will be mess.

Also, remember that the event store should be used only for writes, you only query it to load the events, not the properties of the aggregate. They are separate things (that is the essence of CQRS).

Solution 3 what people usually do, there are many ways to acomplish that.

As example, EventFlow CQRS when used with SQL Server creates a table with this schema:

CREATE TABLE [dbo].[EventFlow](
    [GlobalSequenceNumber] [bigint] IDENTITY(1,1) NOT NULL,
    [BatchId] [uniqueidentifier] NOT NULL,
    [AggregateId] [nvarchar](255) NOT NULL,
    [AggregateName] [nvarchar](255) NOT NULL,
    [Data] [nvarchar](max) NOT NULL,
    [Metadata] [nvarchar](max) NOT NULL,
    [AggregateSequenceNumber] [int] NOT NULL,
 CONSTRAINT [PK_EventFlow] PRIMARY KEY CLUSTERED 
(
    [GlobalSequenceNumber] ASC
)

where:

• GlobalSequenceNumber: Simple global identification, may be used for ordering or identifying the missing events when you create your projection (readmodel).
• BatchId: An identification of the group of events that where inserted atomically (TBH, have no idea why this would be usefull)
• AggregateId: Identification of the aggregate
• Data: Serialized event
• Metadata: Other usefull information from event (e.g. event type used for deserialize, timestamp, originator id from command, etc.)
• AggregateSequenceNumber: Sequence number within the same aggregate (this is usefull if you cannot have writes happening out of order, so you use this field to for optimistic concurrency)

However, if you are creating from scratch I would recomend following the YAGNI principle, and creating with the minimal required fields for your use case.

Solution 5:

Possible hint is design followed by "Slowly Changing Dimension" (type=2) should help you to cover:

• order of events occurring (via surrogate key)
• durability of each state (valid from - valid to)

Left fold function should be also okay to implement, but you need to think of future query complexity.