crsql_changes

crsql_changes is a virtual table that can be used to pull a changeset out of the database and/or apply a changeset to the database. crsql_changes only stores the current state of the database and metadata required to merge with any peer rather than the full history of every change ever made to the database. This design was chosen to keep the database compact, even if there are thousands of changes per second.

Networking layers may optionally write site_id values to this table. If your networking layer does write site_id, this can allow you to pull changes that were not made by a given site.

Table Structure

CREATE TABLE crsql_changes(
  [table] TEXT NOT NULL,
  [pk] TEXT NOT NULL,
  [cid] TEXT NOT NULL,
  [val] ANY,
  [col_version] INTEGER NOT NULL,
  [db_version] INTEGER NOT NULL,
  [site_id] BLOB, -- id of the peer database that made the change
  [cl] INTEGER NOT NULL,
  [seq] INTEGER NOT NULL
) STRICT;

table - this is the name of the CRR that the current change applies to
pk - the primary key columns for that table, encoded in a format that can be sent over the wire and validated on receipt.
cid - the name of the column that was modified. Special values are used in the case of deletes and creates.
val - the value of the column. Encoded for safe wire transport and validation on receipt.
col_version - version / lamport clock of the column. Used for merging.
db_version - the lamport clock of the database. Used to track whether or not a site has seen changes from another database.
site_id - the site responsible for the change
cl - "causal length" or whether or not the row is deleted
seq - the order within the given db_version the mutation was made. Useful for splitting up very large transactions into many packets.

Local writes will have a site_id equal to crsql_site_id(). This allows you to pull only local changes if desired. E.g., SELECT * FROM crsql_changes WHERE db_version > ? AND site_id = crsql_site_id()

See:

Note: if you migrate the schema of a crr without using crsql_alter_begin you can end up in a case where tables and columns in a changeset do not exist. The changeset applicator will ignore these but this can lead to a divergence between databases. See the migrations section.

Pulling Changesets

To retrieve a changeset from the database you can query the crsql_changes table like any other table. The preferred way to do this, however, is as follows:

SELECT "table", "pk", "cid", "val", "col_version", "db_version", "site_id", "cl", "seq"
  FROM "crsql_changes"
  WHERE "db_version" > ?
  AND "site_id" IS NOT ?

This query will return all changes from the databse after a provided version and not applied by a given peer.

The site_id IS NOT ? is included as an optimization that reduces total sync roundtrips by one by preventing sites from receiving their own changes back.
Passing site_id IS NULL will only fetch changes that were made locally and not received through data sync (assuming the network layer is writing site ids which is optional).

You may also query against other columns -- such as the table name -- if you are only interested in a subset of changes like those to a given table.

See the sync guide to learn more about pulling changes from this table.

Applying Changesets

The output of

SELECT "table", "pk", "cid", "val", "col_version", "db_version", COALESCE("site_id", crsql_site_id()), "cl", "seq"
  FROM "crsql_changes"
  WHERE "db_version" > ?
  AND "site_id" IS NOT ?

can be directly fed back into a crsql_changes table on another database.

Example:

INSERT INTO "crsql_changes"
  ("table", "pk", "cid", "val", "col_version", "db_version", "site_id", "cl", "seq")
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);

Note that you MUST use SQL bind parameters and NOT include the values of crsql_changes directly in a SQL string.

crsqlite will process the bound values and ensure they are safe before applying them against the database. If the values are interpolated into the string, undefined behavior may result.

crsql_finalize crsql_begin_alter