Event Sourced MSSql
Published: 2021-11-11 20:53:25

---
title: Event Sourced MSSql
published: true
description: 
tags: 
cover_image: https://images.unsplash.com/photo-1483736762161-1d107f3c78e1?ixlib=rb-1.2.1&ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&auto=format&fit=crop&w=1074&q=80
---

## Single events table approach

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

```sql
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

```sql
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.

```sql
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.

```sql
...

  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.

```sql
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.

```sql
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.

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