Event Sourced MSSql

Single events table approach

The basic idea is that we have an events table as defined here.

create table [events] (
    id int not null primary key identity(1,1),
    uuid varchar(40) not null,
    [type] varchar(40) not null,
    body varchar(max) not null,
    created datetime2 not null default getutcdate()
)

• id is the identity and provides a way to order events
• uuid is the unique identifier for a particular entity
• type is the type of event (think redux action)
• body will contain json data matching the type. The combinations of type with body form a union, or sum, type.
• created defaults to the utc date and can also provide a way to order events. There are some examples where the clock on a machine could be reset or altered and thus force this date to be out of sequence with the id column, but having both will allow us to know if any such shenanigans took place.

A sample data table

create table customer (
  id int not null primary key identity(1,1),
  uuid varchar(40) not null unique,
  [name] varchar(40) not null
)

The basic premise is that we want to be able to keep all of the data in all of the states it has ever existed. This table will house the "current state" of the individual customers and the events table will store all mutative events so that we can have an audit log and recreate the state of the customers from any time in the past. Reads will come from the customer table, but inserts, updates, and deletes will be handled via a trigger on the events table. The uuid is what syncs this table with the events table and could possibly have an explicit foreign key. I've also added the surrogate id column which may not be necessary but the smaller size of the column might help with foreign keys to the customer table. Some fields that you might expect to be here like created or updated are not required with this approach.

Creating a trigger

Our trigger will live on the events table and will handle the various type + body data values that are inserted into the table. Here's the beginning of the trigger where we pull the values from the inserted row.

create trigger customer-events 
on [events]
after INSERT
AS
    DECLARE
        @uuid varchar(40),
        @type varchar(40),
        @body varchar(max)
    SELECT @uuid = INSERTED.[uuid], 
        @type = INSERTED.[type],
        @body = INSERTED.[body]
    FROM INSERTED

And now for the specific code dealing with each event. These could each be pulled into their own triggers if you prefer. My guess is that the larger your team the more split up you'll want these. If it's just you or a small team then you might just keep it all in a single trigger per data table.

...

  IF @type = 'customer-insert'
  BEGIN
    insert into customer (uuid, [name])
    values(@uuid, JSON_VALUE(@body, '$.name'))
  END

  IF @type = 'customer-update'
  BEGIN
    update customer
    set [name] = JSON_VALUE(@body, '$.name')
    where uuid = @uuid
  END

  IF @type = 'customer-delete'
  BEGIN
    delete from customer
    where uuid = @uuid
  END

Affecting change on the database

Now that we have our tables and trigger created we can make changes to the database. If we want to insert a customer row we can now insert a row into the events table with a newly supplied uuid value.

insert into [events](uuid, [type], body) values (
  '123e4567-e89b-12d3-a456-426652340000', 'customer-create', '{"name": "Reid Evans"}'
)

When we run that command we'll see 1 row inserted into the events table and because of our trigger, we'll also have a row in our customer table.

If we want to update or delete a customer we can issue similar inserts into the events table.

insert into [events](uuid, [type], body) values (
  '123e4567-e89b-12d3-a456-426652340000', 'customer-update', '{"name": "Reid N Evans"}'
)

insert into [events](uuid, [type], body) values (
  '123e4567-e89b-12d3-a456-426652340000', 'customer-delete', ''
)

Having our event log now means that we no longer need things like created and updated columns on our core entity tables because determining those is trivial.

select * from customer c
outer apply (
  select min(created) as created
  , max(created) as updated
  from [events] e
  where e.uuid = c.uuid
) timestamps