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.
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 always have a
NULL site_id. This allows you to pull only local changes if desired. Note that in ANSI SQL, NULL has strange semantics. You must use
IS NULL instead of
= NULL and
IS NOT NULL instead of
!= NULL to check for NULL values.
- https://sqlite.org/forum/forumpost/4507acec4a8f0dc6 (opens in a new tab)
- https://stackoverflow.com/questions/5658457/not-equal-operator-on-null (opens in a new tab)
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.
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.
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.
site_id IS NULLwill 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.
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.
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.