{"id":5947,"date":"2017-07-12T16:43:58","date_gmt":"2017-07-12T23:43:58","guid":{"rendered":"https:\/\/www.backerkit.com\/blog\/?p=5947"},"modified":"2020-02-21T00:40:50","modified_gmt":"2020-02-21T08:40:50","slug":"getting-started-with-event-sourcing-in-postgres","status":"publish","type":"post","link":"https:\/\/www.backerkit.com\/blog\/getting-started-with-event-sourcing-in-postgres\/","title":{"rendered":"Getting Started with Event Sourcing in Postgres"},"content":{"rendered":"

In a previous post<\/a> we explored how our application tracks sent emails. We set things up to update or insert a record for each email with the current state (delivered, dropped, etc). In this post we’ll look at an alternative data model where we’ll store each event as its own record and then roll up the events to get the current state of a particular email.<\/p>\n

With this different modeling we’ll be able to understand the full lifecycle of an email allowing us to debug issues better as well as have a better understanding of how our users interact with our emails. In our original setup we could only know about the most recent email event. We’ll use the concept of event sourcing<\/a> to maintain this history.<\/p>\n

Our Setup<\/h2>\n

Here’s what our table will look like:<\/p>\n

CREATE TABLE emails (\n  id SERIAL PRIMARY KEY,\n  message_id varchar NOT NULL,\n  status varchar NOT NULL,\n  created_at timestamp DEFAULT current_timestamp,\n  updated_at timestamp DEFAULT current_timestamp\n);\n\nCREATE INDEX ON emails (message_id);\n<\/code><\/pre>\n
Here is some data to get us started.<\/p>\n
INSERT INTO emails VALUES (\n  DEFAULT, 'first-message', 'sent', now(), now()\n);\nINSERT INTO emails VALUES (\n  DEFAULT, 'first-message', 'delivered', now(), now()\n);\nINSERT INTO emails VALUES (\n  DEFAULT, 'first-message', 'opened', now(), now()\n);\nINSERT INTO emails VALUES (\n  DEFAULT, 'second-message', 'sent', now(), now()\n);\n<\/code><\/pre>\n
We have two emails being tracked. One has been sent, delivered, and opened; the other has only been sent, but not yet delivered or opened.<\/p>\n
Check out this SQLFiddle<\/a> if you’d like to explore the data or queries yourself.<\/p>\n
Query for a Single Message<\/h2>\n
Given we have a single message_id<\/code> ('first-message'<\/code>, for example) and we want to find its current state, we could write a query like this one:<\/p>\n
SELECT\n  message_id,\n  status\nFROM emails\nWHERE message_id = 'first-message'\nORDER BY created_at DESC\nLIMIT 1;\n<\/code><\/pre>\n
Our output looks like:<\/p>\n
message_id     | status\n-----------------------\nfirst-message  | opened\n<\/code><\/pre>\n
Query Across All Messages<\/h2>\n
If we wanted to get the current state of all the emails, we would need to write a different query than we’re used to. We still want the most recent event but now we want it across a list of emails. As a result we want to get a single event (the most recent one) for each message. We can use Postgres’ DISTINCT ON<\/code><\/a> notation to do just that.<\/p>\n
SELECT DISTINCT ON (message_id)\n  message_id,\n  status\nFROM emails\nORDER BY message_id, created_at DESC;\n<\/code><\/pre>\n
Note<\/strong>: this query has been updated from the original post to be simpler as a result of feedback on Hacker News<\/a>.<\/p>\n
Our output looks like:<\/p>\n
message_id     | status\n-----------------------\nfirst-message  | opened\nsecond-message | sent\n<\/code><\/pre>\n
With this kind of data model and queries, we can now understand the current state of the world as well as interrogate our system to understand all the statuses through which an email went.<\/p>\n
Further Reading<\/h2>\n