Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.turso.tech/llms.txt

Use this file to discover all available pages before exploring further.

Change Data Capture (CDC) tracks all data changes (inserts, updates, deletes) made to your database tables. Changes are recorded in a local table that you can query like any other table. This is useful for building reactive applications, syncing data between systems, auditing, and more.

​
Enable CDC

Enable CDC per connection using a PRAGMA: 
PRAGMA capture_data_changes_conn('full');
To disable: 
PRAGMA capture_data_changes_conn('off');
CDC cannot be used together with MVCC. They are mutually exclusive on the same connection.

​
Capture Modes

ModeDescription
idCapture only the primary key/rowid of changed rows
beforeCapture row state before changes (for updates/deletes)
afterCapture row state after changes (for inserts/updates)
fullCapture both before and after states, plus per-column update details

​
CDC Table

Changes are stored in the turso_cdc table by default. You can specify a custom table name: 
PRAGMA capture_data_changes_conn('full,my_changes_table');
The CDC table has the following schema:
ColumnTypeDescription
change_idINTEGERAuto-incrementing unique identifier
change_timeINTEGERTimestamp (Unix epoch)
change_txn_idINTEGERTransaction ID (groups rows into transactions)
change_typeINTEGER1 = INSERT, 0 = UPDATE, -1 = DELETE, 2 = COMMIT
table_nameTEXTName of the table that was changed
idvariesPrimary key/rowid of the changed row
beforeBLOBRow data before the change (modes: before, full)
afterBLOBRow data after the change (modes: after, full)
updatesBLOBPer-column change details (mode: full)

​
Querying Changes

-- All changes
SELECT * FROM turso_cdc;

-- Only inserts
SELECT * FROM turso_cdc WHERE change_type = 1;

-- Only updates
SELECT * FROM turso_cdc WHERE change_type = 0;

-- Only deletes
SELECT * FROM turso_cdc WHERE change_type = -1;

-- Changes for a specific table
SELECT * FROM turso_cdc WHERE table_name = 'users';

-- Changes in the last hour
SELECT * FROM turso_cdc WHERE change_time > unixepoch() - 3600;

​
Decoding Binary Records

The before, after, and updates columns store data in a binary format. Use the built-in helper functions to decode them: 
SELECT
  change_type,
  table_name,
  id,
  bin_record_json_object(table_columns_json_array('users'), after) AS after_state,
  bin_record_json_object(table_columns_json_array('users'), before) AS before_state
FROM turso_cdc
WHERE table_name = 'users' AND change_type != 2;
FunctionDescription
table_columns_json_array(table_name)Returns a JSON array of column names for a table (e.g. ["id","name","email"])
bin_record_json_object(columns_json, blob)Decodes a binary record into a JSON object using the given column names

​
Transactions

CDC respects transaction boundaries. Changes are only recorded when a transaction commits. If a transaction rolls back, no CDC entries are created. All rows from the same transaction share the same change_txn_id, with a single COMMIT record (change_type = 2) at the end: 
BEGIN;
INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');
INSERT INTO users VALUES (2, 'Bob', 'bob@example.com');
UPDATE users SET name = 'Charles' WHERE id = 1;
COMMIT;
-- All 3 changes + 1 COMMIT record share the same change_txn_id

​
Schema Changes

CDC also tracks DDL operations (CREATE TABLE, DROP TABLE, CREATE INDEX, etc.) as changes to the sqlite_schema table: 
PRAGMA capture_data_changes_conn('full');

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT);
-- Recorded in turso_cdc as a change to sqlite_schema

​
Multiple Connections

Each connection has its own independent CDC configuration. Different connections can capture to different tables: 
-- Connection 1: Capture to 'audit_log'
PRAGMA capture_data_changes_conn('full,audit_log');

-- Connection 2: Capture to 'sync_queue'
PRAGMA capture_data_changes_conn('id,sync_queue');

​
Example

-- Create a table
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

-- Enable full CDC
PRAGMA capture_data_changes_conn('full');

-- Make some changes
INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');
INSERT INTO users VALUES (2, 'Bob', 'bob@example.com');
UPDATE users SET email = 'alice@newdomain.com' WHERE id = 1;
DELETE FROM users WHERE id = 2;

-- View the captured changes with decoded data
SELECT
  change_id,
  CASE change_type
    WHEN 1 THEN 'INSERT'
    WHEN 0 THEN 'UPDATE'
    WHEN -1 THEN 'DELETE'
    WHEN 2 THEN 'COMMIT'
  END AS operation,
  table_name,
  id,
  bin_record_json_object(table_columns_json_array('users'), after) AS after_state
FROM turso_cdc
WHERE change_type != 2
ORDER BY change_id;