SQLite is a remarkable, versatile and widely adopted embedded relational database. SQLite’s lightweight nature, ease of integration and broad cross-platform support make it a go-to choice for developers seeking an efficient solution for storing data. Also, SQLite is very capable, but some features aren’t included out of the box. One of those not-included features is temporal tables. What that feature is, why it’s useful, and how to get it working with SQLite will be described in this post.
What are temporal tables?
Temporal tables, also known as temporal data tables, are a feature in database systems that allow you to keep track of states of data over time. These tables store historical versions of data, providing a way to query and analyze how data has evolved over different points in time. A mechanism to fly back in time, so to speak. Such a feature is especially useful when handling data that does not only consist of immutable facts, but also data that is mutable or somewhat mutable. There is more mutable data than one might think. Let’s look at an example, a table of employees:
| ID | fistName | lastName |
|---|---|---|
| 0 | Max | Mustermann |
| 1 | Erika | Musterfrau |
It looks simple enough, doesn’t it? It’s very immutable at first glance, one might think, but then life happens. Max and Erika get married, so both take the last name “Muster”. A year later, Max decides to be a stay-at-home dad and quits. Now, the table might look like this:
| ID | fistName | lastName |
|---|---|---|
| 1 | Erika | Muster |
Answers to the questions: “Did we have an employee called Mustermann?” or “Did we have an employee called Musterfrau” are lost.
One might work around this by adding “employment_start” and “employment_end” columns and maybe some column like “maiden name”, but all of this only covers the cases we thought about. Things like a name change caused by a divorce would not be covered. Furthermore, we haven’t looked at more fluid data like addresses, for example.
From this example, we can see that building a history on the application layer might be a solution, but having a history on the database layer might be more convenient.
Basic layout of a temporal table
Temporal tables contain time slices of each row of the original data table. Practically speaking, all the data with an added “valid_from” and “valid_to” column. By that, there can be multiple rows of our employee data that were valid at different times. The temporal table of our employee table might look like this:
| valid_from | valid_to | ID | fistName | lastName |
|---|---|---|---|---|
| 2020-01-01 | 2021-03-14 | 0 | Max | Mustermann |
| 2020-06-01 | 2021-03-14 | 1 | Erika | Musterfrau |
| 2021-03-15 | 2023-10-31 | 0 | Max | Muster |
| 2021-03-15 | 1 | Erika | Muster |
As we can see, all the states of our employee data are preserved in our history table:
We can see that there were once rows of Max Mustermann and Erika Musterfrau that changed to Max & Erik Muster. Afterward, the row of Max Muster stopped to exist.
We don’t have any reasons for these changes, but we have all the history, and we can have that without changing the application by letting SQLite take care of it by using triggers.
Database-Triggers
Database triggers are powerful and versatile tools in the world of database management. Essentially, a database trigger is a set of instructions that automatically execute in response to a specific event or action within a database. The most common events for triggers are data modifications, such as inserts, updates or deletes. We will use triggers to log the data changes to our history / temporal table. One might say we instruct the database to log all changes.
How to get there with SQLite – Technical steps
To get it working there are two steps needed:
1.) Create the history table that holds the historic data based on the original data structure
2.) Create triggers that fill or update the history table on insert, update and delete of the original data.
Both of these steps might be done by hand, but I’m lazy. Therefore, I created a Python script that takes a SQLite Database and a table name and creates SQL-Statements for 1.) & 2.).
The script can be found over on github: https://github.com/balu-/temporal-tables-in-SQLite
Appendix:
I’m not the first one thinking about something like this:
Simon Willison has created something in the same direction (https://simonwillison.net/2023/Apr/15/sqlite-history/). His storage format is more sophisticated and space-preserving, but, in my opinion, less easy to access.
Thatdevsherry (https://github.com/thatdevsherry/historia) has taken a different aproch to reach a similar result. He built a layer between the application and the database, that layer creates, updates history data.