Change Data Capture is an early preview feature. The API and behavior may change in future releases.
Enabling CDC
Enable CDC for the current connection using the capture_data_changes_conn pragma:
PRAGMA capture_data_changes_conn('<mode>[,custom_cdc_table]');
turso_cdc).
Modes
| Mode | Captures | Description |
|---|
off | Nothing | Disable CDC for the connection |
id | rowid only | Most compact; logs only the affected row ID |
before | Row state before the change | Captures state before updates and deletes |
after | Row state after the change | Captures state after inserts and updates |
full | Both before and after states | Complete audit trail |
Custom CDC table
You can direct CDC output to a custom table instead of turso_cdc:
PRAGMA capture_data_changes_conn('full,my_audit_log');
The turso_cdc table
When CDC is first enabled, the turso_cdc table is created automatically. Each row records one change event.
| Column | Type | Description |
|---|
change_id | INTEGER | Monotonically increasing integer. Always strictly increasing. Serves as the primary key. |
change_time | INTEGER | Local Unix timestamp (seconds) when the change was recorded. Not guaranteed to be strictly increasing — it can drift or repeat. |
change_type | INTEGER | 1 = INSERT, 0 = UPDATE (also used for ALTER TABLE), -1 = DELETE (also covers DROP TABLE, DROP INDEX) |
table_name | TEXT | Name of the affected table. For DDL changes (CREATE TABLE, etc.), this is always sqlite_schema. |
id | INTEGER | Rowid of the affected row. For DDL operations, the rowid of the sqlite_schema entry. |
before | BLOB | Full row state before an UPDATE or DELETE. NULL for INSERT. For DDL changes, may contain the object definition before modification. |
after | BLOB | Full row state after an INSERT or UPDATE. NULL for DELETE. For DDL changes, may contain the object definition after modification. |
updates | BLOB | Granular column-level details for UPDATE operations. |
WITHOUT ROWID tables are not supported with CDC.
CDC with DDL changes
CDC also records schema changes. When you create or drop a table, the change is logged with table_name = 'sqlite_schema':
CREATE TABLE → change_type = 1 (INSERT into sqlite_schema)ALTER TABLE → change_type = 0 (UPDATE of sqlite_schema)DROP TABLE, DROP INDEX → change_type = -1 (DELETE from sqlite_schema)
Behavior notes
- CDC records are visible even before a transaction commits.
- Operations that fail due to constraint violations are not recorded.
- Changes to the CDC table itself are also logged if CDC is enabled for that connection.
In full mode, each update of size N bytes is written three times to disk: once for the before state, once for the after state, and once for the actual value in the WAL. Frequent updates in full mode can significantly increase disk I/O.
- Use
id mode when you only need to know which rows changed.
- Use
before or after mode when you need one side of the change.
- Use
full mode only when you need a complete audit trail and can tolerate the additional I/O.
Schema evolution and CDC
If you modify a table’s schema (adding or dropping columns), the table_columns_json_array() function returns the current schema, not the historical one at the time a CDC record was written. This can cause incorrect results when decoding older CDC records. Manually track schema versions by storing the output of table_columns_json_array() before making schema changes.
Example
-- Enable full CDC for this connection
PRAGMA capture_data_changes_conn('full');
-- Create a table (logged as a DDL change)
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT
);
-- Perform data changes
INSERT INTO users VALUES (1, 'John'), (2, 'Jane');
UPDATE users SET name = 'John Doe' WHERE id = 1;
DELETE FROM users WHERE id = 2;
-- Read the CDC log
SELECT change_id, change_type, table_name, id
FROM turso_cdc
ORDER BY change_id;
turso_cdc table will contain:
| change_id | change_type | table_name | id | Description |
|---|
| 1 | 1 | sqlite_schema | 2 | CREATE TABLE users |
| 2 | 1 | users | 1 | INSERT id=1 |
| 3 | 1 | users | 2 | INSERT id=2 |
| 4 | 0 | users | 1 | UPDATE id=1 |
| 5 | -1 | users | 2 | DELETE id=2 |