SQL Event Store

Demonstration of a SQL event store with deduplication and guaranteed event ordering. The database rules are intended to prevent incorrect information from entering into an event stream. You are assumed to have familiarity with event sourcing. Three DDLs are provided – one for Postgres, one for SQLite, and one for SQL Server.

This project uses a node test suite to ensure the DDLs comply with the design requirements. The DDLs can also be ported to most SQL RDBMS and accessed from any number of writers, including high-load serverless functions, without a coordinating “single writer” process.

Installing

Good news! Nothing to install! Instead, take the DDLs in this project (Postgres, SQLite, SQL Server) and include them in your application's database definition set.

Usage Model

SQLite, Postgres, and SQL Server versions have similar SQL usage models but with some differences. Postgres and SQL Server provide functions/procedures, whereas SQLite only has views. The concepts and naming are similar between the databases, but slightly different in their use and capabilities.

Appending Events

In order to manage the business rules of an event-sourced application, one must append events to the ledger.

SQLite

Append new events by inserting into the append_event view. Here is an example:

-- Add an event. Note the RETURNING clause, which returns the generated event_id for the appended event. This is used to append the next event.
INSERT INTO append_event (entity, entity_key, event, data, append_key) -- first event in entity, omit previous_id
VALUES ('game', 'apr-7-2025', 'game started','true', 'an-append-key')
RETURNING (SELECT event_id FROM ledger WHERE append_key = 'an-append-key');

-- now insert another event, using the first event's id as the previous_id value
INSERT INTO append_event (entity, entity_key, event, data, append_key, previous_id)
VALUES ('game', 'apr-7-2025', 'game going','true', 'another-append-key', '019612a6-38ac-7108-85fd-33e8081cedaf')
RETURNING (SELECT event_id FROM ledger WHERE append_key = 'another-append-key');

Postgres

Append new events by calling the append_event function. Here is an example:

-- Add an event. This function returns the generated event_id for the appended event.
SELECT append_event ('game', 'apr-7-2025', 'game started','true', 'an-append-key', null);

-- now insert another event, using the first event's id as the previous_id value
SELECT append_event ('game', 'apr-7-2025', 'game going','true', 'another-append-key', '019612a6-38ac-7108-85fd-33e8081cedaf');

SQL Server

Append new events by calling the append_event stored procedure. Here is an example:

-- Add an event. This procedure returns the generated event_id via an OUTPUT parameter.
DECLARE @event_id UNIQUEIDENTIFIER;
EXEC append_event @entity = 'game', @entity_key = 'apr-7-2025', @event = 'game started', @data = 'true', @append_key = 'an-append-key', @previous_id = NULL, @event_id = @event_id OUTPUT;
SELECT @event_id;

-- now insert another event, using the first event's id as the previous_id value
DECLARE @event_id2 UNIQUEIDENTIFIER;
EXEC append_event @entity = 'game', @entity_key = 'apr-7-2025', @event = 'game going', @data = 'true', @append_key = 'another-append-key', @previous_id = @event_id, @event_id = @event_id2 OUTPUT;
SELECT @event_id2;

Replaying Events

One can replay events in order, without unhelpful data, by using the replay_events view.

SQLite / Postgres / SQL Server

-- Replay all the events
SELECT * FROM replay_events;

-- Replay events from a specific entity
SELECT * FROM replay_events
WHERE entity = 'game'
  AND entity_key = '2022 Classic';

-- Replay only certain events
SELECT * FROM replay_events
WHERE entity = 'game'
  AND entity_key = '2022 Classic'
  AND event IN ('game-started', 'game-finished');

-- BEWARE the last event_id in this result set may not be the last event for the entity instance, so it
-- cannot be used to append an event. To find the last event for an entity, use this query:

-- SQLite / Postgres
SELECT event_id FROM ledger
WHERE entity = 'game'
  AND entity_key = '2022 Classic'
ORDER BY sequence DESC LIMIT 1;

-- SQL Server
SELECT TOP 1 event_id FROM ledger
WHERE entity = 'game'
  AND entity_key = '2022 Classic'
ORDER BY sequence DESC;

Catching Up With New Events

Your application may want to "catch up" from a previously read event and avoid replaying already-seen events. SQLite, Postgres, and SQL Server have different mechanisms to do so.

Catching Up With SQLite

-- Catch up with events after a known event
SELECT * FROM replay_events
WHERE entity = 'game' 
  AND entity_key = '2022 Classic'
  AND sequence > (SELECT sequence
                  FROM ledger
                  WHERE event_id = '123e4567-e89b-12d3-a456-426614174000');

The last WHERE event_id portion will contain the most recent event processed by your application, and the point in the events where you want to continue from.

Catching Up With Postgres

Replaying events to catch up after a previous event is easier with Postgres since it has stored functions. The function replay_events_after accepts the event ID of the most recent event processed by your application. It returns the same fields as the replay_events view described above. The function includes ORDER BY internally, so results are already ordered.

-- Catch up on new events from a specific entity, after a specific event
-- Postgres
SELECT * FROM replay_events_after('123e4567-e89b-12d3-a456-426614174000')
WHERE entity = 'game' 
  AND entity_key = '2022 Classic';

Catching Up With SQL Server

SQL Server also provides a replay_events_after function similar to Postgres. However, since SQL Server's inline table-valued functions cannot include ORDER BY, you must add it in your query to guarantee ordering.

-- Catch up on new events from a specific entity, after a specific event
-- SQL Server (ORDER BY required)
SELECT * FROM replay_events_after('123e4567-e89b-12d3-a456-426614174000')
WHERE entity = 'game' 
  AND entity_key = '2022 Classic'
ORDER BY sequence;

Notice how your application can add WHERE clauses in the replay query to filter for relevant events.

Conceptual Model

An Event is an unalterable statement of fact that has occurred in the past. It has a name, like food-eaten, and it is scoped to an Entity, or an identifiable existence in the world. Entities are individually identified by business-relevant keys that uniquely identify one entity from another.

In this event store, an event cannot exist without an entity to apply it to. Events can create new entities, and in that first event, the entity key is presented as the identifying key for the newly created entity.

Events follow other events in a sequence. Within an entity instance, each event has a reference to the previous event, much like a backward-linked list. This expression of previous event ID enables SQL Event Store to guarantee that events are written sequentially, without losing any concurrent appends of other events in for the same entity.

Appends to other entities do not affect each other, so many events can be appended to many events concurrently without suffering serialization penalties that “single writer” systems can cause.

Design

• Append-Only Once events are created, they cannot be deleted, updated or otherwise modified. This includes entity event definitions.
• Insertion-Ordered Events must be consistently replayable in the order they were inserted.
• Event race conditions are Impossible The event store prevents a client from writing an event to an entity if another event has been inserted after the client has replayed an event stream.

Client Use Cases

Event store clients can use basic SQL statements to add and replay events. Clients follow the typical event sourcing pattern:

1. Receive a command
2. Replay events to compute current state
3. Validate entity state for command
4. Append a new event

Replay Events

Clients must always fetch, or replay, the events for an entity before inserting a new event. Replaying events assures the client that the state of the entity is known so that business rules can be applied for the command before an event is appended. For instance, if a command creates a new entity, the replay step will ensure no events have been appended to the entity's key.

SELECT event,
       data,
       event_id
FROM replay_events
    WHERE entity = ?
      AND entity_key = ?;

If a command produces a subsequent event for an existing entity, the event_id of the last event must be used as the previous_id of the next event. This design enforces the Event Sourcing pattern of building current read models before appending new events for an entity.

Append Events

Two types of events can be appended into the event log. The first event for an entity and subsequent events. The distinction is important for the database rules to control for incorrect events, like incorrect entity key or invalid previous event id.

First Event for an Entity

In this case, the previous_id does not exist, so it is omitted from the insert statement.

-- SQLite version, see above for Postgres
INSERT INTO append_event(entity,
                         entity_key,
                         event,
                         data,
                         append_key) 
VALUES (?, ?, ?, ?, ?);

Subsequent Events

The previous_id is the event_id of the last event recorded for the specific entity.

-- SQLite version, see above for Postgres
INS