React to database changes
Patterns 4 · 3:47
A change tracking integration publishes every change to a database table's rows, including deletes, into a channel. A watcher route reacts to each change in the channel. The database administrator first turns on the database's own change feature.
This part of the video couldn't be loaded.
What it covers
- Scaffolding the change tracking with
weikio init --kind change-tracking - The document filled in for PostgreSQL and
public.invoice, and its placeholders from variables and a secret - The channel
invoice-changesfromweikio channel create, and one stored event per change - The kamelet
weikio-event-sourcethat starts the watcher route, and the route's log step - Reading three changes to invoice 1041 back with
weikio events showandweikio logs - The agent
devas the leading copy and each change on the admin UI's Changes tab
Commands in this video
Terminal
$ weikio init invoice-tracking --kind change-tracking
$ weikio channel create sandbox/invoice-changes
$ weikio apply -f ./invoice-tracking
$ weikio deploy sandbox/invoice-tracking --env devtest
$ weikio run ./invoice-watcher --no-logs
$ psql -d erp -c "INSERT INTO invoice (id, customer, amount, status) VALUES (1041, 'Lakeside Bakery', 640, 'open')"
$ weikio events show sandbox/invoice-changes --seq 1
$ psql -d erp -c "UPDATE invoice SET status = 'paid' WHERE id = 1041"
$ psql -d erp -c "DELETE FROM invoice WHERE id = 1041"
$ weikio logs sandbox/invoice-watcher --since 10mTranscript
- 0:01A row in someone else's database changes, and other systems have to hear of it.
- 0:11Invoice 1041 is added, changed and deleted, then the table reads empty.
- 0:17The database's administrator opens its change log.
- 0:23A change tracking reads that log, publishes to the invoice-changes channel,
- 0:28then a route reads the channel.
- 0:34A change tracking is a kind of integration that reads the database's change log and publishes each change into a channel.
- 0:42weikio init for a change tracking writes the invoice-tracking folder and its document.
- 0:49The scaffold arrives for another database product, so you fill it in for yours.
- 0:55The filled document names postgres, the table it watches and the channel.
- 1:00Host, user and password are placeholders: two devtest variables and a secret.
- 1:09A channel is the project's named topic for events,
- 1:13and it keeps what is published into it.
- 1:15The tracking brings no channel,
- 1:17so the channel create command for invoice-changes in sandbox declares one before anything publishes.
- 1:24Each change becomes one stored event,
- 1:26carrying its operation and the row before and after.
- 1:33A route of the same project watches that channel,
- 1:36starting on the weikio-event-source kamelet that comes with the platform.
- 1:41Its log step writes what the route took in: the operation,
- 1:45the row's id and the status before and after.
- 1:51The apply stores invoice-tracking in your Weik.io installation as a version.
- 1:56The deploy runs that version in devtest,
- 1:59then invoice-tracking reads the database's log.
- 2:02weikio run for the invoice-watcher folder applies and deploys it without waiting on the log.
- 2:13The change is made in the database's own tool.
- 2:20weikio events show reads the first stored event, the insert.
- 2:25The tracking read the log and published the change into the channel.
- 2:33The tool sets the row's status to paid.
- 2:38Then it deletes the row.
- 2:43The channel keeps all three changes, one event each.
- 2:50weikio logs reads the watcher's log from the installation.
- 2:55The watcher took in all three changes.
- 3:01The Details panel names the database and the channel.
- 3:04One copy runs on agent dev.
- 3:09The Changes section gives one row per published change, newest first.
- 3:14Opening one shows the row before the change and the row after.
- 3:22The change tracking read the database's log and sent every insert,
- 3:27update and delete to the channel.
- 3:29The channel kept each as a stored event, so the Weik.io CLI or a route can read it.
- 3:36The route on the kamelet took each event and logged it.
Next in this track
Patterns 5 · 3:01 One copy at a time Watched