Event Sourcing inside a RDBMS

Question

I'm thinking about using an Event Sourcing / Event Store pattern in my RDBMS designs.

What I mean by Event Store is a pattern where each data mutation (INSERT / UPDATE / DELETE) is persisted as an INSERT into a suitable table of events. At the same time, hopefully in the same transaction, some piece of logic updates a state table with the result of applying the mutation.

This is similar, but conceptually the opposite of what is traditionally done: first executing the mutation (INSERT / UPDATE / DELETE) on the state table; and then executing an INSERT into a log or audit table.

The advantage of this inverted or Event Store approach, as I understand, is that by construction it allows you to query any past state of the system, at any point in time; to undo most types of changes that might have been wrongly applied, for instance by an application bug, without affecting later events performed by users; and so on.

I've read a few articles about this pattern, but all of them seem to use an opaque JSON blob as the event data. This IMHO violates all that is good about relational databases.

I'm more inclined to model events as dedicated tables, using common sense to group similar events into the same table, and using something like DB triggers to perform the mutations on the state table.

Here is an example of what I'm thinking.

Let's take a "shopping cart" example, where the possible events are:

1. The user adds a certain quantity of a product to their shopping cart. (ADD)
2. The user updates the quantity of one of the products in their shopping cart. (UPDATE)
3. The user removes all quantity of one of the products in their shopping cart. (REMOVE)
4. The user's shopping cart is emptied, for instance after successfully creating an order. (EMPTY)

In this trivial example, all 4 events may be successfully modeled with a single cart_event table (in most real-world cases, I suppose they would not):

Column	Type	Null	Notes
event_id	int	N	Global sequential id, common to all event tables
timestamp	timestamp	N	Event timestamp
user_id	?	N	FK to users
event_type	enum	N	ADD, UPDATE, REMOVE, EMPTY
product_id	?	Y	FK to products, required iff. ADD, UPDATE, REMOVE
quantity	int	Y	Quantity to add or update, required iff. ADD, UPDATE

I'm thinking that the event_id should come from a global sequence, so that all events (in all event tables) can be read in a unique sequential order.

A view may be written with all event types in UNION ALL from all event tables, to browse them in that order. Alternatively, table inheritance may be used, from a global event table, if the RDBMS supports it (they usually don't, or at least they don't support polymorphic queries.)

In any case, the application code would only be allowed to perform SELECT and INSERT into event tables.

Then, AFTER INSERT triggers for all event tables would "apply" the event to a "state" table, which would take the place of the traditional mutable table in SELECT queries. Application code would never be allowed to modify this table, only using it for querying the current state of the system. (The DBA would be allowed to perform mutations on event tables to fix bugs, and then rebuild the state tables.)

Alternatively, a VIEW may be used instead of triggers to create the state table virtually, if the RDBMS is powerful enough to provide decent performance when querying (I doubt it.)

Is this approach well-known and/or supported by major RDBMS?

Are there recognized guidelines on how to apply this design pattern (Event Sourcing) inside the persistence layer, without falling into the schemaless "JSON soup" way of doing things?

Answer 1

It surprises me that this is so uncommon. I've used it in past projects with great results. Anecdotally, I can confirm that this approach works perfectly fine. It may have some additional complexities but they are one-offs. Once you establish your event sourcing format, it works precisely the same way.

I can't speak to RDBMS features around it. We worked it out using a regular old SQL database and it worked perfectly fine for us.

The rest of this answer is a collection of tips and tricks on quirks that I ran into. This is all written from the point of view of a SQL database, .NET Core application, with standard onion/clean architecture in place.

0 - Vocabulary

This is just to make my explanation easier to understand. We used the following vocabulary:

• Aggregate: a particular stream, represented as if it were a single identifiable entity. For example, a single Person. Bob and John are two different aggregates (instaces of Person), but they have the same aggregate type (Person)
• Event: a data manipulation on an aggregate. We used generics here (Event<Person>) to denote specific aggregates because we had to reuse our logic across many kinds of aggregates; but you could just stick to a PersonEvent type that is not generic.
• Snapshot: A state capture. Our snapshots were different from what Event Sourcing is normally used for, and I'll skip the long explanation. Regardless of how you use it, there was a snapshot table that contains the current state of a Person. Note that you could also just add new snapshots and keep the old ones if you wanted to - this is a contextual consideration on whether it yields you any benefit.

1 - Database tables

We ended up with three tables for such a type, e.g. Person, PersonEvents and PersonSnapshots. Both the events and snapshots tables had a FK to the aggregate table.

Note that the structure of these tables was not identical to the domain concepts of an aggregate, event and snapshot. There are subtle differences based on what was needed to be stored and what wasn't. I can't answer this conclusively for you, but make sure to evaluate the table structure separately from the domain model structure.

Note that table Person doesn't really contain any data other than the person's unique ID. It is a fairly superfluous table that is only being used to set an AggregateId FK on the events and snapshots tables.

In our case, there were some data values that were immutable once an aggregate had been created. These values were added to the aggregate table. Any values that could be changed would be added to the events table. This neatly aligned with how the entries in these tables were already being handled in the system.

Most likely, unless you have such immutable values, the aggregate table is not particularly necessary. However, I would personally still add it, in case you might want to start using such immutable values in the future, and because it gives you some PK-FK referential integrity to help keep your other tables consistent.

2 - Dynamic JSON columns

Because a SQL database doesn't allow you the same structural freedom that a JSON blob does, it's not as evident on how to host all kinds of events in a single table (in a way that you can add more types in the future without breaking the table).

Our solution to this was to use two database columns: Type and Data (both strings). Data would contain the JSON-serialized event data, and Type would contain the class name of the specific event type in question. This allowed us to store any kind of event (a string is just a string) and convert it back to the correct type when loading it in your runtime.

3 - Base event type

Tip 2 has a secondary consequence. It's all fine that you can deserialize the event data into its specific type, but you need to be able to treat your stream as a single collection of events.

Because of this, all our events implemented the same interface. Since we used generics, this was IEvent<Person> but if you don't need generics IPersonEvent would work just as well.

public interface IEvent<TSnapshot>
{
    TSnapshot ApplyTo(TSnapshot snapshot);
}

Notice that we are using the snapshot as the mutation model. Effectively, you build a snapshot by applying events on top of it, and since a snapshot already contains all the properties that we are interested in, it made a lot of sense to reuse it as the model here.

Also notice that we don't know anything about this event, we only know that we can pass an instance of TAggregate into it and get it back. This means that when you create the event and put it into the stream, you know which kind of event it is; but when you fetch events from the stream, you have lost the ability to distinguish one event type from another. This is fine, as you only really use your events for replaying them anyway.

This means that your person event repository will be returning you an IEnumerable<IEvent<Person>> - plan your domain logic accordingly

If you want access to other event-based values, such as a user-readable message that describes the event (e.g. "Changed last name to Jones", "Happy birthday! Age increased by 1", ...), which user added this event to the stream, when this event was created, the version number, ...; you can expand the interface and force the event type classes to provide this information, e.g.:

public interface IEvent<TSnapshot>
{
    TSnapshot ApplyTo(TSnapshot snapshot);

    int Version { get; }
    string EventDescription { get; }
    string CreatedBy { get; }
    DateTime CreatedOn { get; }
}

4 - Repository methods

Because all of these tables are used in a very particular way, the repositories that wrapped them could be kept very simple.

• Aggregates can be added or deleted - no updates allowed. Deletes would ensure a cascaded delete of events and snapshots.
• Events could only be added to.
• Generally speaking, event sourcing does not allow you to delete entries from a stream. However, in our particular scenario we specifically allowed administrators to remove illegal (literally against the law) operations, so a delete method was implemented for that purpose alone. Regular users could not delete anything. However, this is not the usual scenario and I would not add it unless I had ample justification that it was necessary.
• Snapshots would only have a CreateOrUpdate method, not individual creates or updates. Also, no delete was developed.

In short, the only way to delete anything (barring the illegal operations as a fringe exceptions) would be by deleting the aggregate in its entirety.

5 - Snapshots

We tackled it pretty much exactly like you're describing it. We kept more than one snapshot around per aggregate, but that's a side consideration that doesn't matter to you.

We opted for generating a new snapshot in the same request that added an event to the stream. This was a temporary implementation. Once we saw any issues with that solution, e.g. performance or race conditions, we were going to switch this over to a scheduled job that would update the snapshots once in a while.

We never had to move away from immediate snapshot generation because individual streams would generally stay under 1000 entries for the next decade. We had a lot of individual streams, and not a lot of events within a given stream.

6 - Separate databases for CQRS

Separate your event and snapshot databases. This is in preparation for needing to upscale your read database (snapshots) without breaking your event stream implementation.

Initially, we used the same database as a temporary solution but in our codebase we had already split them into two separate database contexts. The fact that they were hitting the same database was coincidental. We had separate connection strings and the integration tests even intentionally used separate databases to make sure that we were always compatible with splitting the databases once the time came for it.

Aggregate/Event goes into the write store, snapshots go into the read store.

One small consequence here, possibly related to Entity Framework which we were using, is that we could not set up a FK relation between the Aggregate and Snapshot tables. We added it anyway via a SQL script, because we wanted the referential integrity.

However, the odds of breaking integrity are tiny and I don't think it's particularly necessary to do so, unless you feel it is necessary.

7 - Generics

This is a very complex thing to tackle. However, we were dealing with an application that needed everything to be individually event-sourced; we are talking 40+ different aggregate types. The same CRUD operations, the same ES logic, over and over again. Reusability was a must for us.

I ended up building a massive collection of base aggregate/event/snapshot types (models, entities, repositories, interfaces, ...) and strung them all together with generic type constraints.

This was, in hindsight, a herculean effort. I had some classes with 9 generic type parameters and 15+ type constraints. This was not fun.

However, it worked beautifully. We created a unit template to generate these files, and we managed to make the template generic enough that the only thing you needed to do was add the specific properties that belonged to a particular aggregate type (Person, Order, Product, ...)

Do not do this unless you really need to. I managed to do it and it was worthwhile but it was horribly complex and required a lot of research on the topic.

Some direct responses

At the same time, hopefully in the same transaction

I wouldn't do it in the same transaction. If your read store fails to accept the update, for whatever reason, you've just lost your write operation as well.

It makes more sense to keep these separate, even if you intend to handle them both during the same request (see below, we did this as well). If the snapshot update fails, for any reason, you still have the update, and therefore can fix it using a simple snapshot restore (which I will inherently recommend you have access to whenever anything gets out of sync).

to undo most types of changes that might have been wrongly applied

Generally speaking, you're going to find the advice that you should not delete events, but rather introduce additional events that undo the result of the previous event.

I'm in two minds about this, and I think this is contextual whether you allow stream editing or not.

In case you allow stream editing, remember to regenerate your snapshots.

This IMHO violates all that is good about relational databases.

Wrong focus. The point of ES is to allow differently shaped events to fit into a single collection, which is orthogonal to the general "all rows will have the exact same fields" approach that your RDBMS is built upon.

You can work around it, but you can't expect it to be a natural fit. In our case, the company was unwilling to move away from SQL server and we had no standing to override it, so we rolled with it and once we overcame the structural difference, it all worked out.

I'm more inclined to model events as dedicated tables, using common sense to group similar events into the same table,

It is significantly more in line with ES to keep your stream in a single table. What you intend to do is not impossible but I suspect you're signing yourself up for more trouble than you're hoping to save yourself by doing so.

See above for my approach on how to fit an event stream into a single RDBMS table.

I'm thinking that the event_id should come from a global sequence, so that all events (in all event tables) can be read in a unique sequential order.

My suggestion is to stick to ES' usual versioning system. It also helps against race conditions and makes it easy to keep the order.

We did it differently, but in our case the user was supplying a specific date on which the entered change would take effect. Think of it like something that say "On [this date], [field] is now [new value]".
Because of this, our event stream was not in the same order as the events were being entered. This is very unusual for event sourcing. I can attest that it works perfectly fine, but it's not something you're going to find a lot of documentation on, and you have to consider your edge cases carefully.

A view may be written with all event types in UNION ALL from all event tables, to browse them in that order. Alternatively, table inheritance may be used, from a global event table

Event sourcing is a relatively heavy system when you start replaying things or trying to make large scale reports about the state of the data. Anything you can do to reduce the complexity is going to be a good thing to do.

I again refer to my suggestion above on how to keep things in a single RDBMS table (per aggregate type)

Then, AFTER INSERT triggers for all event tables

I really, really, REALLY dislike logic in database providers, and I'll always argue against it. It's messy, hard to debug, and significantly more complex to migrate up/down during releases, compared to doing the same in code.

Consider your solution to be application-first, and only using the RDBMS as a dumb storage with some FK referential integrity. The kind of logic you are talking about is significantly easier to develop and maintain in a codebase rather than a database.

I can tell you horror stories of legacy applications that leaned hard into stored procs and table triggers. They were the single-handed cause of dozens of high value employees leaving the company.

without falling into the schemaless "JSON soup" way of doing things?

I think my above suggestion limits the use of JSON's lack of strong typing only to the specific use case in which it's actually useful (the ability to store different event structures in a single collection), while otherwise retaining the benefits of strong typing in all other cases.

I'm not a purist, I'm a pragmatist, and in this specific regard, JSON is actually superior - so why not leverage it?

Answer 2

Is this approach well-known and/or supported by major RDBMS?

As you've noticed, this is rather untypical.

I've read a few articles about this pattern, but all of them seem to use an opaque JSON blob as the event data. This IMHO violates all that is good about relational databases.

Kind of, but an event store really only needs two operations:

• Append an event for a particular entity
• Get all events of a particular entity in order

An RDBMS is not ideal for this use case, because you have to query different tables (one for each event type, essentially) and then put them in the correct order on the client-side.

What I mean by Event Store is a pattern where each data mutation (INSERT / UPDATE / DELETE) is persisted as an INSERT into a suitable table of events. At the same time, hopefully in the same transaction, some piece of logic updates a state table with the result of applying the mutation.

The second sentence goes a bit beyond the basic idea of event sourcing. Conceptually, you never store any state - you load the ordered sequence of past events and use them to recreate the state every time. The events are not just logs, they are the source of truth for your application.

This can become a performance problem, if your entities accumulate very long event sequences - which is not the case in all applications. Storing "snapshots" of the state in periodic intervalls (or even every time) is one way to remedy this problem.

Relational databases are good at storing state, but even here they may not be necessary: typically, you have separate, independent snapshots of each entity without a lot of cross-referencing.

In some cases, it is useful to have a separate representation of your data for reading^*. Here, you might want to query complex relationships between multiple different entities. In this situation, storing your state in an RDBMS could be advantagous, although there are also other possibilities - it just depends on the specific use case.

^{* see: CQRS}

Answer 3

In a persistent disk storage context we call this CoW or sometimes LFS.

For datastructures in RAM we tend to refer to it as functional programming.

To satisfy your needs, in both cases we should preserve a reference to each historic snapshot before we move on to some modified version.

---

Old version control systems would replay diffs from the beginning, while more recent ones store diffs in the reverse order since most queries ask for current version. And then there's git hashes, which put all versions on an equal footing.

An almost plausible implementation of what you seek would be frequent .CSV exports of tables to a git repo, with frequent commits.

---

To achieve read consistency, postgres routinely implements some of what you describe. Both INSERTs and SELECTs pay attention to transaction IDs, ensuring that overlapping writers and readers will see snapshots of the world that are sensible. MariaDB and others take a similar approach.

A practical implementation of what you describe would want to store periodic snapshots, perhaps daily. It's a time-space tradeoff, so you can satisfy "as of" queries in reasonable time. A system that sees fewer queries for ancient dates could safely devote less storage to years-old rows. DBAs would likely partition such a table, so ancient rows can be pruned or can be placed on a cheaper storage technology.