In this article, we will use the student enrollment management system created in a previous article to explore how Materialized Views work and how to implement them.
In this system, actions such as creating a student, updating a profile, and enrolling in a class are stored as events. We will create read-side tables from these events so that we can retrieve an enrollment list containing student names and class names using SQL.
A Materialized View (MV) stores read-side data calculated from the original data. In this implementation, Sekiban reads events and reflects them in SQL tables.
The stack used in this article is .NET 10 / Sekiban DCB 10.19.0 / Orleans / PostgreSQL. The code examples include only the parts needed for the explanation.
Why Use a Materialized View?
The event store contains events such as "a student was created" and "a student enrolled in a class." However, what we want to retrieve in an enrollment list is data that combines student names and class names.
| Student Name | Class Name |
|---|---|
| Alice | Math |
| Alice | English |
| Bob | Math |
One option is to retrieve the student list and class list separately and match them in the application. However, if the MV contains tables for students, classes, and enrollments, we can retrieve the required data with SQL JOINs.
Enrollment JOIN Student JOIN Class
→ Filter by conditions
→ Return an enrollment list containing student and class names
The role of the MV is to provide a data structure optimized for reading, separately from the format used to store events. Filtering and aggregation can also be written directly in SQL.
Overall Architecture
In this system, we use one database for events and another for the MV.
flowchart TD
A[Create student / Update profile / Enroll in class] --> B[Validate business rules in the command]
B --> C[(Event Store)]
C --> D[Successful command response]
C --> E[Sekiban MV Runtime]
E --> F[Projector<br/>Convert events into SQL]
F --> G[(MV Database<br/>Student / Class / Enrollment tables)]
H[Enrollment List API] --> I[Get the currently active MV tables<br/>JOIN them with SQL]
G --> I
I --> J[Enrollment list containing student and class names]The event store keeps the event history, while the MV database stores read-side data built from that history.
For example, when a student's name is updated, the process works as follows:
- The command validates the requested change.
- A
StudentProfileUpdatedevent is stored. - The MV runtime reads the event.
- The student table is updated using SQL generated by the Projector.
- The enrollment list API returns the updated student name.
Event persistence and MV updates are separate processes, so there is a delay between a successful command response and the update becoming visible in the MV.
MV synchronization may complete within a few seconds, but when there are many events, it may take several minutes or longer. Because of this behavior, the MV should not be used to validate consistency or enforce business rules.
To guarantee consistency when writing data, retrieve the current state for each tag (aggregate) and perform validation on the command side.
The events remain the source of truth. The MV should be treated as read-side data that can be rebuilt from those events.
In this article, the MV is implemented as a set of read-side tables that are updated based on events.
Packages and Database Connections
The EventSource project, which contains the MV definitions, references Sekiban.Dcb.MaterializedView.
The API host also uses packages for PostgreSQL and Orleans integration.
According to the official Sekiban documentation, providers are available not only for PostgreSQL but also for databases such as SQL Server, MySQL, and SQLite.
<PackageReference Include="Sekiban.Dcb.MaterializedView" Version="10.19.0" />
<PackageReference Include="Sekiban.Dcb.MaterializedView.Postgres" Version="10.19.0" />
<PackageReference Include="Sekiban.Dcb.MaterializedView.Orleans" Version="10.19.0" />
This is the same configuration used in the existing sample.
Dapper is used to execute SQL and map query results to objects.
In AppHost, the MV database is passed to the API.
var materializedViewPostgres = postgresServer
.AddDatabase("DcbMaterializedViewPostgres");
apiService = apiService
.WithReference(materializedViewPostgres)
.WaitFor(materializedViewPostgres);
In this sample, the same PostgreSQL server contains two databases: DcbPostgres for storing events and DcbMaterializedViewPostgres for storing the MV.
Implementing the Projector
The system already had an MV implementation. I added support for the StudentProfileUpdated event so that changes to a student's name and enrollment limit are also reflected in the read-side tables.
A Projector is a class that converts events into read-side data.
In this example, ClassRoomEnrollmentMvV2 implements IMaterializedViewProjector.
public sealed class ClassRoomEnrollmentMvV2 : IMaterializedViewProjector
{
public string ViewName => "ClassRoomEnrollment";
public int ViewVersion => 2;
// Implement InitializeAsync and ApplyToViewAsync.
}
The two main methods are:
| Method | Role |
|---|---|
InitializeAsync |
Registers and creates the MV tables |
ApplyToViewAsync |
Generates SQL for each event |
Defining the Tables
The enrollment list uses the following three tables.
| Table | Main Data |
|---|---|
students |
Student ID, name, enrollment limit, number of enrollments |
classrooms |
Class ID, name, capacity, number of enrolled students |
enrollments |
Student ID, class ID, enrollment date and time |
In InitializeAsync, we register a logical table name and then create the table using the physical name provided by the runtime.
Students = ctx.RegisterTable(StudentsLogicalTable);
The following is an excerpt from the DDL for the student table.
{Students.PhysicalName} is the table name inserted by C# string interpolation.
CREATE TABLE IF NOT EXISTS {Students.PhysicalName} (
student_id UUID PRIMARY KEY,
name TEXT NOT NULL,
max_class_count INT NOT NULL,
enrolled_count INT NOT NULL DEFAULT 0,
_last_sortable_unique_id TEXT NOT NULL,
_last_applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
This DDL is executed with ctx.ExecuteAsync.
By obtaining the physical table name from the runtime instead of hard-coding it, we can work with tables that correspond to different MV versions.
Returning SQL for Each Event
In ApplyToViewAsync, the logic is selected according to the event type, and a list of MvSqlStatement objects is returned.
The student-related part looks like this:
StudentCreated created =>
[InsertStudent(created, ctx.CurrentSortableUniqueId)],
StudentProfileUpdated updated =>
[UpdateStudent(updated, ctx.CurrentSortableUniqueId)],
The relationship between events and MV updates is as follows:
| Event | Update to the MV |
|---|---|
StudentCreated |
Insert a student |
ClassRoomCreated |
Insert a class |
StudentProfileUpdated |
Update the student's name and enrollment limit |
StudentEnrolledInClassRoom |
Insert an enrollment and update enrollment counts for the student and class |
StudentDroppedFromClassRoom |
Delete an enrollment and update enrollment counts for the student and class |
Business rules, such as the enrollment limit, are validated on the command side.
The Projector converts stored events into SQL, and the MV runtime applies that SQL.
Reflecting Profile Updates
When a profile is updated, the student's name and enrollment limit are changed.
UPDATE {Students.PhysicalName}
SET name = @Name,
max_class_count = @MaxClassCount,
_last_sortable_unique_id = @SortableUniqueId,
_last_applied_at = NOW()
WHERE student_id = @StudentId
AND _last_sortable_unique_id < @SortableUniqueId;
The values are passed as SQL parameters.
This update does not modify the enrollment count or enrollment records, so the existing enrollment information is preserved even when the student's name changes.
_last_sortable_unique_id represents the position of the latest event applied to the row.
The update is performed only when the incoming event is newer. This condition helps prevent duplicate processing and older events from overwriting newer data, while assuming that the MV runtime applies events in order.
Registering the MV Runtime
In the API's Program.cs, we register the Projector and the MV storage.
The following is an excerpt from the registration code:
builder.Services.AddSekibanDcbMaterializedView(options =>
{
options.AllowDefaultServiceId = true;
options.BatchSize = 100;
options.PollInterval = TimeSpan.FromSeconds(1);
});
builder.Services.AddMaterializedView<ClassRoomEnrollmentMvV2>();
builder.Services.AddSekibanDcbMaterializedViewPostgres(
builder.Configuration,
connectionStringName: "DcbMaterializedViewPostgres",
registerHostedWorker: false);
builder.Services.AddSekibanDcbMaterializedViewOrleans();
In this example, Orleans manages MV processing, so registerHostedWorker is set to false.
AllowDefaultServiceId is configured for the single-service setup used in this sample.
Retrieving the Enrollment List from the MV
On the read side, IMvOrleansQueryAccessor is used to obtain the database connection information and table definitions.
var context = await mvQueryAccessor.GetAsync(
projector, cancellationToken: cancellationToken);
var studentsTable = context.GetRequiredTable(
ClassRoomEnrollmentMvV2.StudentsLogicalTable);
The class and enrollment tables are retrieved in the same way.
The main part of the SQL query uses the physical table names to perform JOINs:
var sql = $"""
SELECT e.student_id AS "StudentId", s.name AS "StudentName",
e.class_room_id AS "ClassRoomId", c.name AS "ClassName",
e.enrolled_at AS "EnrolledAt",
e._last_sortable_unique_id AS "LastSortableUniqueId"
FROM {enrollmentsTable.PhysicalTable} e
INNER JOIN {studentsTable.PhysicalTable} s
ON s.student_id = e.student_id
INNER JOIN {classRoomsTable.PhysicalTable} c
ON c.class_room_id = e.class_room_id
""";
The actual implementation also adds filtering by student ID and class ID, as well as sorting.
Dapper maps the result to the response type.
var rows = (await connection.QueryAsync<EnrollmentListMvItem>(
new CommandDefinition(
sql,
parameters,
cancellationToken: cancellationToken))).ToList();
connection is an Npgsql connection to the MV database, and parameters contains the filter conditions.
The endpoint is:
GET /api/mv/enrollments/
Because the result already contains both the student name and class name, the caller does not need to retrieve separate lists and match them manually.
Waiting for the MV After an Update
If we query the MV immediately after an update, it may still return the previous data.
In this sample, the command API returns a sortableUniqueId. By passing this value to the query API, we can wait until the MV has processed events up to that position.
Update the profile
→ Get sortableUniqueId from the response
→ Pass it to the enrollment API as waitForSortableUniqueId
→ Wait until the MV reaches that event position
→ Retrieve the data
The waiting logic checks the processing position with the following call:
await context.Grain.IsSortableUniqueIdReceived(sortableUniqueId);
The implementation also handles timeouts and cancellation.
What we are waiting for here is the event processing position. Therefore, if the Projector does not contain logic for the profile update event, waiting for the event to be processed will not update the student's name.
Managing Versions with ViewVersion
An MV has its own read-model version, separate from the Sekiban package version.
public string ViewName => "ClassRoomEnrollment";
public int ViewVersion => 2;
| Property | Meaning |
|---|---|
ViewName |
Name of the read model |
ViewVersion |
Version of the table structure and projection logic |
The existing version 1 did not process StudentProfileUpdated.
If events have already been processed without handling this event, simply adding the new logic does not automatically cause previously processed events to be read again.
Version 1
Student created → Insert student
Profile updated → Skip without processing
Student enrolled → Insert enrollment
Version 2
Student created → Insert student
Profile updated → Update name and enrollment limit
Student enrolled → Insert enrollment
For this reason, after adding support for StudentProfileUpdated, I changed ViewVersion to 2.
This allows the MV to be rebuilt from historical events using the new projection logic.
Even if the table columns do not change, changing the version is useful when the data produced from historical events changes.
On the other hand, if we only add more fields to a query by JOINing existing tables, rebuilding the MV is usually unnecessary.
Getting the Currently Active MV Tables
Because the tables are separated by version, the API needs to determine which version is currently active.
This is handled by IMvOrleansQueryAccessor.
Use version 1
→ Build the data for version 2
→ Once version 2 is ready, switch queries to version 2
By obtaining the table names through the Accessor instead of hard-coding physical table names in the API, the query destination can be switched between versions.
It is not enough to rename the class to V2. ViewVersion and the registered Projector must also be updated together.
Verifying the API Behavior
First, create a student named "Alice" and a class named "Math", enroll Alice in the class, and retrieve the enrollment list.
Then change the student's name to "Alice Updated", wait for the update event to be reflected in the MV, and retrieve the list again.
| Item | Before Update | After Update |
|---|---|---|
| Student name | Alice | Alice Updated |
| Class name | Math | Math |
| Enrollment | Alice is enrolled in Math | The same enrollment is preserved |
If we get this result, we can confirm that the profile update has been reflected in the student table and that the updated name is also used in the enrollment list generated through the JOIN.
When the student drops the class, we also verify that the corresponding enrollment disappears from the list and that the enrollment counts for both the student and class are updated.
Verification Results
Using the actual API, Orleans, and PostgreSQL, I verified the following behavior:
- Updates to the student's name and enrollment limit were reflected in the MV, and the updated name appeared in the joined enrollment list.
- The existing enrollment information and enrollment timestamp were preserved after the profile update, and additional enrollments were reflected correctly.
- Updates that violated business rules were rejected, and the MV was not changed.
- Filtering by student and class, as well as dropping an enrollment, was reflected correctly in the query results.
- After restarting with version 2 instead of version 1, historical profile updates were reflected, and the API also switched its query target to version 2.
- I re-projected 13 events into an empty MV database and confirmed that the student names, enrollment limits, enrollment relationships, and counts matched the data before the rebuild. The original events were not modified.
Summary
In this article, we used Sekiban DCB's Materialized View functionality to build an enrollment list that can be queried with SQL from stored events.
In addition to reflecting updates, we also confirmed version switching and rebuilding the MV from historical events.
Materialized Views are useful when you want to join or aggregate multiple pieces of information with SQL, or when you want to expose read-side data as database tables.
A good place to start is a familiar list endpoint like this one. Try updating tables from events and see how the read model changes as new events are processed.
Top comments (0)