@reiver

Event Sourcing

Event Sourcing is a way of storing data as a series of events.

Those who, when designing, (explicitly, or implicitly) use event modeling, and event storming to model their data, will sometimes use event sourcing (along with other techniques such as CQRS, and event messaging) when constructing an implementation.

I.e., event sourcing is a construction technique used in software engineering.

Examples

Examples often help to make things more clear. So we will go over a number of examples to try while going over the details on event sourcing to make what is meant by event sourcing more clear.

Example Event Names

If we had an event source about user signups for an e-commerce app, then the events for this signups event source might include:

  • e-mail address provided,
  • shipping address given,
  • terms-of-service accepted.

Although in our code, we might instead write these in CONST_CASE as:

  • EMAIL_ADDRESS_PROVIDED,
  • SHIPPING_ADDRESS_GIVEN,
  • TERMS_OF_SERVICE_ACCEPTED.

Past Tense

Notice that all those event names are in the past tense.

A convention with event sourcing is that event names are always written in the past tense!

So “e-mail address provided” (EMAIL_ADDRESS_PROVIDED) is past tense. But “e-mail”, or “e-mail address”, or “provide e-mail address”, or anything like that, are not. So if you went along with the convention of event names always being written in the past tense, then you would use “e-mail address provided” (EMAIL_ADDRESS_PROVIDED) rather than the others.

And “item added” (ITEM_ADDED) is past tense. But “item”, or “add item”, or anything like that, are not. So if you went along with the convention of event names always being written in the past tense, then you would use “item added” (ITEM_ADDED) rather than the others.

More Example Event Names

If we had an event source about a shopping cart for an e-commerce app, then the events for this shopping cart event source might include:

  • item added,
  • item removed.

(And again, by convention, the event names are in the past tense.)

And again, in our code, we might instead write these in CONST_CASE as:

  • ITEM_ADDED,
  • ITEM_REMOVED.

Event Fields

Often an event has more that just an event name.

Often an event would have fields too.

So, for example, if we had an event source about user signups for an e-commerce app, then the events for this signups event source might include a “e-mail address provided” (EMAIL_ADDRESS_PROVIDED) event.

This event might have a single field: “e-mail address” (email_address).

I.e.,:


Event Name: EMAIL_ADDRESS_PROVIDED
Event Fields:
	• email_address
			

So, some example EMAIL_ADDRESS_PROVIDED events might be:


Event Name: EMAIL_ADDRESS_PROVIDED
Event Fields:
	• email_address = "joeblow@example.com"
			

Or:


Event Name: EMAIL_ADDRESS_PROVIDED
Event Fields:
	• email_address = "homersimpson@springfield.ny.us"
			

That is a very simple event, in that it only has one field (email_address).

Now let us look at an event with more than one field.

So, for example, again if we had an event source about user signups for an e-commerce app, then the events for this signups event source might include a “shipping address given” (SHIPPING_ADDRESS_GIVEN) event.

This event might have a fields: “country name” (country_name), “region” (region), “locality” (locality), “street address” (street_address), “postal code” (postal_code).

I.e.,:


Event Name: SHIPPING_ADDRESS_GIVEN
Event Fields:
	• country_name
	• region
	• locality
	• street_address
	• postal_code
			

So, some example SHIPPING_ADDRESS_GIVEN might be:


Event Name: SHIPPING_ADDRESS_GIVEN
Event Fields:
	• country_name   = "Canada"
	• region         = "BC"
	• locality       = "Vancouver"
	• street_address = "601 West Cordova Street"
	• postal_code    = "V6C 2R6"
			

Or:


Event Name: SHIPPING_ADDRESS_GIVEN
Event Fields:
	• country_name   = "USA"
	• region         = "NY"
	• locality       = "Springfield"
	• street_address = "742 Evergreen Terrace"
	• postal_code    = "49007"
			

Event Sourcing in SQL

Although event modeling, and the normalized forms typically used in relational databases, are alternate ways of modeling data.

It is possible to do event sourcing in a SQL based relational database.

The following describes how I do event sourcing in a SQL based relational database.

The core table for this is a table that looks something like the following:


CREATE TABLE SOMETHING_events (
	id            SERIAL PRIMARY KEY NOT NULL CHECK (id > 0),
	when_created  TIMESTAMPTZ        NOT NULL DEFAULT now(),

	prev_id       INTEGER            REFERENCES signup_events(id) CHECK (id > 0),

	iid           VARCHAR(126),

	when_happened TIMESTAMPTZ        NOT NULL DEFAULT now(),
	name          VARCHAR(126)       NOT NULL,
	version       VARCHAR(126)       NOT NULL,
	data          JSONB              NOT NULL,
);
CREATE UNIQUE INDEX idx_SOMETHING_events_prev_id ON SOMETHING_events (prev_id);
CREATE UNIQUE INDEX idx_SOMETHING_events_iid     ON SOMETHING_events (iid);
			

Of course you would replace the string “SOMETHING” with whatever your events are about.

For example, if you wanted an event source about signups, (i.e., people signing up for you app) then you could have:


CREATE TABLE signup_events (
	id            SERIAL PRIMARY KEY NOT NULL CHECK (id > 0),
	when_created  TIMESTAMPTZ        NOT NULL DEFAULT now(),

	prev_id       INTEGER            REFERENCES signup_events(id) CHECK (id > 0),

	iid           VARCHAR(126),

	when_happened TIMESTAMPTZ        NOT NULL DEFAULT now(),
	name          VARCHAR(126)       NOT NULL,
	version       VARCHAR(126)       NOT NULL,
	data          JSONB              NOT NULL,
);
CREATE UNIQUE INDEX idx_signup_events_prev_id ON signup_events (prev_id);
CREATE UNIQUE INDEX idx_signup_events_iid     ON signup_events (iid);
			

Or, for example, if you wanted an event source about shopping carts e-commerce app, then you could have:


CREATE TABLE shopping_cart_events (
	id            SERIAL PRIMARY KEY NOT NULL CHECK (id > 0),
	when_created  TIMESTAMPTZ        NOT NULL DEFAULT now(),

	prev_id       INTEGER            REFERENCES signup_events(id) CHECK (id > 0),

	iid           VARCHAR(126),

	when_happened TIMESTAMPTZ        NOT NULL DEFAULT now(),
	name          VARCHAR(126)       NOT NULL,
	version       VARCHAR(126)       NOT NULL,
	data          JSONB              NOT NULL,
);
CREATE UNIQUE INDEX idx_shopping_cart_events_prev_id ON shopping_cart_events (prev_id);
CREATE UNIQUE INDEX idx_shopping_cart_events_iid     ON shopping_cart_events (iid);
			

With these events tables, the event name is stored in the name field.

And the event fields are stored in the data field.

Note that the data field is a JSONB, so each even can potentially have different event fields. But more on that later.

Event Immutability

Typically events in an event source are immutable, in that old events are never modified, or deleted. And only new events are added.

Event Sourcing in SQL: INSERTing New Events

So because the events in an event source are immutable, typically we only INSERT INTO these events tables.

If, for example, we were working with our example user signups event source for our example e-commerce app, and we wanted to INSERT a new “e-mail address provided” (EMAIL_ADDRESS_PROVIDED) event, then we would do something such as the following:


INSERT INTO signup_events
(iid, name, version, data)
VALUES
('FvRY4.Czhek_4BAl5GNT7k',                  -- iid
 'EMAIL_ADDRESS_PROVIDED',                  -- name
 '1.0.0',                                   -- version
 '{"email_address":"joeblow@example.com"}'  -- data
);
			

Note that we did not set a value for prev_id, and instead let it be NULL. As we will see later, events that have a value of NULL for their prev_id as the beginning of a chain (within an event source).

Now, for example, if we wanted to continue with this signup chain, and this same user then provided a shipping address, then we would do something such as the following:


INSERT INTO signup_events
(iid, name, version, data, prev_id)
VALUES
('j231epSV8hrTIsjsgO_wpt',                                           -- iid
 'SHIPPING_ADDRESS_GIVEN',                                           -- name
 '1.0.0',                                                            -- version
 '{"country_name":"Canada", "region":"BC", "locality":"Vancouver", "street_address":"601 West Cordova Street", "postal_code":"V6C 2R6"}',  -- data
 COALESCE((SELECT id FROM signup_events WHERE iid = 'FvRY4.Czhek_4BAl5GNT7k'), 0) -- prev_id
);
			

Note that this time we did set the value for prev_id. In this case we set its value to the result of this SQL code:


SELECT id
FROM signup_events
WHERE iid = 'FvRY4.Czhek_4BAl5GNT7k'
			

Note that in the WHERE clause, we are looking for the signup event where iid has a value of “FvRY4.Czhek_4BAl5GNT7k”.

The iid value of “FvRY4.Czhek_4BAl5GNT7k” (in the signup events) is the iid we used for the EMAIL_ADDRESS_PROVIDED event that we created before!

This makes it so we chain these two events together.

Why?... Because signup_events will contain events for all the users of the app, and not just one. And we need a way of being able to determine which EMAIL_ADDRESS_PROVIDED event, a SHIPPING_ADDRESS_GIVEN is related to.

We accomplish this by putting chaining together all the events for a single user signup.

Event Sourcing in SQL: Chains

The prev_id field in these types of tables creates a linked list.

This linked list is called a chain.

In our user signup example, a chain repsents a single user signing up.

To make chains easier to work with in SQL, we create a view that looks something like the following:


CREATE RECURSIVE VIEW SOMETHING_chains (
	cid,
	ordinal,

	iid,

	id,
	when_created,
	prev_id,

	when_happened,
	name,
	version,
	data,

	merged_data
) AS (
	(
		SELECT events.iid AS cid
		     , 0 AS ordinal

		     , events.iid

		     , events.id
		     , events.when_created
		     , events.prev_id

		     , events.when_happened
		     , events.name
		     , events.version
		     , events.data

		     , events.data AS merged_data

		FROM SOMETHING_events events
		WHERE events.prev_id IS NULL
	)
	UNION ALL
	(
		SELECT SOMETHING_chains.cid
		     , SOMETHING_chains.ordinal + 1 AS ordinal

		     , events.iid

		     , events.id
		     , events.when_created
		     , events.prev_id

		     , events.when_happened
		     , events.name
		     , events.version
		     , events.data

		     , SOMETHING_chains.merged_data || events.data AS merged_data

		FROM SOMETHING_events events
		JOIN SOMETHING_chains
		  ON SOMETHING_chains.id = events.prev_id
	)
);

			

So, for example, for our signup_events table, our chains view would be:


CREATE RECURSIVE VIEW signup_chains (
	cid,
	ordinal,

	iid,

	id,
	when_created,
	prev_id,

	when_happened,
	name,
	version,
	data,

	merged_data
) AS (
	(
		SELECT events.iid AS cid
		     , 0 AS ordinal

		     , events.iid

		     , events.id
		     , events.when_created
		     , events.prev_id

		     , events.when_happened
		     , events.name
		     , events.version
		     , events.data

		     , events.data AS merged_data

		FROM signup_events events
		WHERE events.prev_id IS NULL
	)
	UNION ALL
	(
		SELECT signup_chains.cid
		     , signup_chains.ordinal + 1 AS ordinal

		     , events.iid

		     , events.id
		     , events.when_created
		     , events.prev_id

		     , events.when_happened
		     , events.name
		     , events.version
		     , events.data

		     , signup_chains.merged_data || events.data AS merged_data

		FROM signup_events events
		JOIN signup_chains
		  ON signup_chains.id = events.prev_id
	)
);

			

And, for example, for our shopping_cart_events table, our chains view would be:


CREATE RECURSIVE VIEW shopping_cart_chains (
	cid,
	ordinal,

	iid,

	id,
	when_created,
	prev_id,

	when_happened,
	name,
	version,
	data,

	merged_data
) AS (
	(
		SELECT events.iid AS cid
		     , 0 AS ordinal

		     , events.iid

		     , events.id
		     , events.when_created
		     , events.prev_id

		     , events.when_happened
		     , events.name
		     , events.version
		     , events.data

		     , events.data AS merged_data

		FROM shopping_cart_events events
		WHERE events.prev_id IS NULL
	)
	UNION ALL
	(
		SELECT shopping_cart_chains.cid
		     , shopping_cart_chains.ordinal + 1 AS ordinal

		     , events.iid

		     , events.id
		     , events.when_created
		     , events.prev_id

		     , events.when_happened
		     , events.name
		     , events.version
		     , events.data

		     , shopping_cart_chains.merged_data || events.data AS merged_data

		FROM shopping_cart_events events
		JOIN shopping_cart_chains
		  ON shopping_cart_chains.id = events.prev_id
	)
);

			

This allows us to do SQL SELECT queries like the following to pull out a single chain:


SELECT *
FROM signup_chains
WHERE cid = 'FvRY4.Czhek_4BAl5GNT7k'
			

Note that cid (i.e., “chain ID”) is the iid for the first event in the chain.

Event Sourcing in SQL: Read Models

Part of the point in creating the chains views is to make creating read models easier.

For example, imagine that you wanted to


CREATE VIEW signups AS
SELECT cid
     , when_happened
     , (merged_data->>'email_address')::text    AS email_address
     , (merged_data->>'country_name')::text     AS country_name
     , (merged_data->>'region')::text           AS region
     , (merged_data->>'locality')::text         AS locality
     , (merged_data->>'street_address')::text   AS street_address
     , (merged_data->>'postal_code')::text      AS postal_code
FROM signup_chains
WHERE (cid, ordinal) IN (
	SELECT cid
	     , MAX(ordinal)
	FROM signup_chains
	GROUP BY cid
)
AND merged_data->>'email_address' IS NOT NULL
AND merged_data->>'country_name'  IS NOT NULL
;
			

This signups database view will feel like more typical tables in a SQL database modeled in a relational database style.

For example, one could get the signup information for by e-mail address with:


SELECT *
FROM signups
WHERE email_address = 'homersimpson@springfield.ny.us'
			

And this would return something such as:

cid email_address country_code region locality street_address postal_code
2QNz.2DT73gQVkemeXC_li homersimpson@springfield.ny.us USA NY Springfield 742 Evergreen Terrace 49007
-- Mirza Charles Iliya Krempeaux
See Other Topics