Track timestamped changes to a SQLite table using triggers#

This is more of a “today I figured out” than a TIL.

I have an idea to implement the Denormalized Query Engine design pattern using SQLite triggers.

My goal is that any time an insert or update occurs on a table, a matching row will be written to a _dqe_changes table recording the rowid of the affected row and the unix timestamp (in milliseconds) that the change occurred.

If a row is deleted, a record will be written into tha table with a 1 in the deleted column.

This will allow subscribing scripts to poll that table for changes made since their last poll, based on the milliseconds timestamp field. They can then apply those changes to external data stores, such as an Elasticsearch index.

Here’s the recipe I came up with:

1
-- This table exists just for table -> integer lookups,
2
-- to save space in the _dqe_changes table
3
CREATE TABLE _dqe_tables(
4
  id integer primary key,
5
  [table] text unique
6
);
7
-- This table records the timestamped changes
8
CREATE TABLE _dqe_changes(
9
  table_id integer,
10
  rowid integer,
11
  deleted integer, -- treated as a null or 1 boolean
12
  updated_ms integer,
13
  PRIMARY KEY (table_id, rowid),
14
  FOREIGN KEY (table_id) REFERENCES _dqe_tables(id)
15
);
16
-- Create an index for polling against
17
CREATE INDEX _dqe_changes_updated_ms ON _dqe_changes(updated_ms);
18

19
-- An example table:
20
CREATE TABLE foo (name text);
21

22
-- Each table needs to have these triggers created on it:
23
CREATE TRIGGER IF NOT EXISTS [foo_dqe_insert] AFTER INSERT ON [foo]
24
BEGIN
25
  INSERT OR IGNORE INTO _dqe_tables([table]) VALUES ('foo');
26
  INSERT OR REPLACE INTO _dqe_changes(table_id, rowid, updated_ms)
27
     VALUES (
28
        (select id from _dqe_tables where [table] = 'foo'),
29
        new.rowid,
30
        -- This is a recipe for timestamp in milliseconds, from
31
        -- https://stackoverflow.com/a/56895050/6083
32
        strftime('%s','now') || substr(strftime('%f','now'),4)
33
    );
34
END;
35
CREATE TRIGGER IF NOT EXISTS [foo_dqe_update] AFTER UPDATE ON [foo]
36
BEGIN
37
  INSERT OR IGNORE INTO _dqe_tables([table]) VALUES ('foo');
38
  INSERT OR REPLACE INTO _dqe_changes(table_id, rowid, updated_ms)
39
     VALUES (
40
        (select id from _dqe_tables where [table] = 'foo'),
41
        new.rowid,
42
        strftime('%s','now') || substr(strftime('%f','now'),4)
43
    );
44
END;
45
CREATE TRIGGER IF NOT EXISTS [foo_dqe_delete] AFTER DELETE ON [foo]
46
BEGIN
47
  INSERT OR IGNORE INTO _dqe_tables([table]) VALUES ('foo');
48
  INSERT OR REPLACE INTO _dqe_changes(table_id, rowid, deleted, updated_ms)
49
     VALUES (
50
        (select id from _dqe_tables where [table] = 'foo'),
51
        old.rowid,
52
        1,
53
        strftime('%s','now') || substr(strftime('%f','now'),4)
54
    );
55
END;
56

57
INSERT INTO foo VALUES ('hello');
58
INSERT INTO foo VALUES ('hello2');
59
INSERT INTO foo VALUES ('hello3');
60
INSERT INTO foo VALUES ('hello4');

To test this I ran sqlite3 (with no arguments, which provides an in-memory database to play with), pasted in the above and then ran this:

1
sqlite> .headers on
2
sqlite> select rowid, * from foo;
3
rowid|name
4
1|hello
5
2|hello2
6
3|hello3
7
4|hello4
8
sqlite> select * from _dqe_changes;
9
table_id|rowid|deleted|updated_ms
10
1|1||1629399072866
11
1|2||1629399072867
12
1|3||1629399072867
13
1|4||1629399072974
14
sqlite> select * from _dqe_tables;
15
id|table
16
1|foo
17
sqlite> delete from foo where name = 'hello3';
18
sqlite> select * from _dqe_changes;
19
table_id|rowid|deleted|updated_ms
20
1|1||1629399072866
21
1|2||1629399072867
22
1|4||1629399072974
23
1|3|1|1629399103169
24
sqlite> update foo set name = 'hello6' where name = 'hello';
25
sqlite> select * from _dqe_changes;
26
table_id|rowid|deleted|updated_ms
27
1|2||1629399072867
28
1|4||1629399072974
29
1|3|1|1629399103169
30
1|1||1629399115886
31
sqlite> select rowid, * from foo;
32
rowid|name
33
1|hello6
34
2|hello2
35
4|hello4

There’s one catch with the above recipe: if you delete ALL of the rows from a table (delete * from foo) SQLite defaults to reusing rowids in that table, starting again from 1. This means that your accompanying records in the _dqe_changes table will have stale rowids, which could lead to surprising behaviour.

If tables have a id integer primary key column SQLite does NOT reuse rowids, as explained here.

Initializing for a new table#

The above recipe expects the triggers to be installed on the table before any rows are written to it.

If applying this recipe to an already-populated table, the following SQL should initialize _dqe_changes with a row for every row:

1
INSERT OR IGNORE INTO _dqe_tables([table]) VALUES ('foo');
2
INSERT OR REPLACE INTO _dqe_changes(table_id, rowid, deleted, updated_ms)
3
     with table_id as (select id from _dqe_tables where [table] = 'foo')
4
     select
5
        table_id.id,
6
        foo.rowid,
7
        null as deleted,
8
        strftime('%s','now') || substr(strftime('%f','now'),4) as updated_ms
9
    from foo, table_id;

Newsletter

Track timestamped changes to a SQLite table using triggers#

Initializing for a new table#