Event Sourcing with SQLite

Designing Append-Only Data Systems

Event sourcing is a system design pattern where application state is derived from a sequence of events rather than stored as mutable records.

Instead of updating rows directly, the system records every change as an immutable event. The current state of the system is reconstructed by replaying those events.

SQLite works well for event sourcing because it provides:

• Durable append operations

• Transactional consistency

• Predictable performance

• A simple embedded deployment model

In this article we will build a small event store using SQLite and show how to:

• Design an append-only event table

• Replay events to rebuild state

• Create snapshots for faster recovery

• Query event streams efficiently

The focus is practical implementation.

What Event Sourcing Looks Like

Traditional database model:

Account Balance
---------------
account_id | balance
1          | 250

Updates overwrite state.

Event sourcing model:

Events
------
deposit 100
deposit 200
withdraw 50

The balance is derived by replaying events.

100 + 200 - 50 = 250

The database becomes a log of events instead of mutable state.

Designing the Event Store

The core of an event-sourced system is an append-only table.

CREATE TABLE events (
  event_id     INTEGER PRIMARY KEY AUTOINCREMENT,
  stream_id    TEXT NOT NULL,
  event_type   TEXT NOT NULL,
  payload      TEXT NOT NULL,
  created_at   TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Key concepts:

stream_id

Identifies an entity or aggregate such as an account or order.

event_type

Describes what happened.

payload

Stores event data, usually JSON.

Example Event Stream

Example events for a bank account:

INSERT INTO events (stream_id, event_type, payload)
VALUES
(’account_1’,’deposit’,’{”amount”:100}’),
(’account_1’,’deposit’,’{”amount”:200}’),
(’account_1’,’withdraw’,’{”amount”:50}’);

SQLite stores events sequentially.

This creates an immutable history of changes.

Querying Event Streams

To rebuild state we read the events for a specific stream.

SELECT event_type, payload
FROM events
WHERE stream_id = ‘account_1’
ORDER BY event_id;

Application code processes the events in order.

Example Python replay logic:

import sqlite3
import json

conn = sqlite3.connect(”events.db”)

balance = 0

rows = conn.execute(”“”
SELECT event_type, payload
FROM events
WHERE stream_id = ?
ORDER BY event_id
“”“, (”account_1”,))

for event_type, payload in rows:
    data = json.loads(payload)

    if event_type == “deposit”:
        balance += data[”amount”]

    if event_type == “withdraw”:
        balance -= data[”amount”]

print(balance)

This reconstructs the account state.

---

Optimizing Event Stream Queries

Event streams must be retrieved quickly.

Create an index:

CREATE INDEX idx_events_stream
ON events(stream_id, event_id);

This ensures fast sequential reads.

Adding Event Versioning

Real systems need ordering guarantees.

Add a version column:

CREATE TABLE events (
  event_id     INTEGER PRIMARY KEY AUTOINCREMENT,
  stream_id    TEXT NOT NULL,
  version      INTEGER NOT NULL,
  event_type   TEXT NOT NULL,
  payload      TEXT NOT NULL,
  created_at   TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Each stream increments its version.

Example:

stream_id | version
account_1 | 1
account_1 | 2
account_1 | 3

This prevents concurrent writers from corrupting order.

Preventing Write Conflicts

Add a constraint:

CREATE UNIQUE INDEX idx_stream_version
ON events(stream_id, version);

When inserting events, the application checks the expected version.

Example insert:

INSERT INTO events(stream_id, version, event_type, payload)
VALUES(’account_1’,4,’withdraw’,’{”amount”:20}’);

If another event already used version 4, SQLite will reject it.

Implementing Snapshots

Replaying thousands of events can become expensive.

Snapshots store the current state periodically.

Snapshot table:

CREATE TABLE snapshots (
  stream_id   TEXT PRIMARY KEY,
  version     INTEGER,
  state       TEXT,
  created_at  TEXT
);

Example snapshot insert:

INSERT INTO snapshots(stream_id, version, state)
VALUES(
‘account_1’,
3,
‘{”balance”:250}’
);

Loading State with Snapshots

State recovery now works like this:

1. Load the latest snapshot

2. Replay events after the snapshot version

Query snapshot:

SELECT version, state
FROM snapshots
WHERE stream_id = ‘account_1’;

Query remaining events:

SELECT event_type, payload
FROM events
WHERE stream_id = ‘account_1’
AND version > ?
ORDER BY version;

This dramatically reduces replay time.

Practical SQLite Performance Tips

Event sourcing workloads are append-heavy.

Enable WAL mode:

PRAGMA journal_mode = WAL;

This improves concurrent reads.

Backlink:

For more on WAL behavior see

https://www.sqliteforum.com/p/mastering-transactions-and-concurrency

Cleaning Up Event Streams

Event stores normally keep all history.

However, some systems archive old streams.

Example archival query:

DELETE FROM events
WHERE stream_id = ?
AND version < ?;

Only do this if historical audit is not required.

Where Event Sourcing Works Well

SQLite event sourcing is ideal for:

• Financial ledgers

• Audit trails

• Device state history

• Workflow engines

• Configuration change logs

These systems benefit from immutable history.

When Event Sourcing Is Not Ideal

Event sourcing may not fit when:

• Events grow extremely large

• Full history is unnecessary

• State updates are simpler than replay

Use it when history and traceability matter.

Closing Notes

Event sourcing changes how data is modeled.

Instead of storing state, you store the history of how state evolved.

SQLite’s transactional consistency and simple deployment make it an excellent foundation for append-only event systems.

With proper indexing and snapshot strategies, SQLite can power reliable event-sourced applications.

Subscribe Now

If you want practical, real-world SQLite architecture tutorials, subscribe to SQLite Forum.

Upcoming topics include:

• Building offline-first sync systems with SQLite

• SQLite replication strategies

• Distributed data ownership patterns

• SQLite B-tree storage internals

Subscribe to receive new articles directly.