Docs
CR-SQLite
Quickstart

Quickstart

Make sure youve acquired, installed and loaded the cr-sqlite extension. Read about this in installation.

CR-SQLite exposes three main APIs.

crsql_as_crr

A function to upgrade existing tables to "conflict free replicated relations", or crrs, for short.

SELECT crsql_as_crr('table_name');

crsql_changes

A virtual table to ask the database for changesets or to apply changesets from another database.

  1. Get local changes after version v
SELECT "table", "pk", "cid", "val", "col_version", "db_version", COALESCE("site_id", crsql_site_id()), "cl", "seq" FROM crsql_changes WHERE db_version > :v AND site_id IS NULL
  1. Get all changes excluding those synced from some_site after version v
SELECT "table", "pk", "cid", "val", "col_version", "db_version", COALESCE("site_id", crsql_site_id()), "cl", "seq" FROM crsql_changes WHERE db_version > :v AND site_id IS NOT :some_site
  1. Merge changes in from another peer
INSERT INTO crsql_changes VALUES [patches received from select on another peer]

crsql_begin/commit_alter

Utilities to aid in schema migrations.

-- begin altering `table_name`
SELECT crsql_begin_alter(:table_name);
-- any number of schema alterations against `table_name`
ALTER TABLE table_name ...;
SELECT crsql_commit_alter(:table_name);

Application code uses the function extension to enable crr support on tables.

Networking code uses the crsql_changes virtual table to fetch and apply changes.

Why are SELECT statements modifying DB state? SQLite doesn't allow us to extend its grammar. This is the simplest way to get what we need given the constraint. libSQL, however, will allow us to extend their grammar. Be on the lookout for a dedicated syntax to create crrs (CREATE TABLE ... AS CRR) in the future.

30k Foot View

Usage looks like:

-- load the extension if it is not statically linked
-- this should always be the first operation on the connection
.load crsqlite
.mode column
-- create tables as normal
create table foo (a primary key NOT NULL, b);
create table baz (a primary key NOT NULL, b, c, d);
 
-- update those tables to be crrs / crdts
select crsql_as_crr('foo');
select crsql_as_crr('baz');
 
-- insert some data / interact with tables as normal
insert into foo (a,b) values (1,2);
insert into baz (a,b,c,d) values ('a', 'woo', 'doo', 'daa');
 
 
-- ask for a record of what has changed
select "table", "pk", "cid", "val", "col_version", "db_version", COALESCE("site_id", crsql_site_id()), "cl", "seq" from crsql_changes;
 
┌───────┬─────────────┬─────┬───────┬─────────────┬────────────┬──────────────────────────────────────┬────┬─────┐
│ table │     pk      │ cid │  val  │ col_version │ db_version │ COALESCE("site_id", crsql_site_id()) │ cl │ seq │
├───────┼─────────────┼─────┼───────┼─────────────┼────────────┼──────────────────────────────────────┼────┼─────┤
│ 'foo' │ x'010901'   │ 'b' │ 2     │ 1           │ 1          │ x'049c48eadf4440d7944ed9ec88b13ea5'  │ 1  │ 0   │
│ 'baz' │ x'010b0161' │ 'b' │ 'woo' │ 1           │ 2          │ x'049c48eadf4440d7944ed9ec88b13ea5'  │ 1  │ 0   │
│ 'baz' │ x'010b0161' │ 'c' │ 'doo' │ 1           │ 2          │ x'049c48eadf4440d7944ed9ec88b13ea5'  │ 1  │ 1   │
│ 'baz' │ x'010b0161' │ 'd' │ 'daa' │ 1           │ 2          │ x'049c48eadf4440d7944ed9ec88b13ea5'  │ 1  │ 2   │
└───────┴─────────────┴─────┴───────┴─────────────┴────────────┴──────────────────────────────────────┴────┴─────┘
 
-- merge changes from a peer
insert into crsql_changes
  ("table", "pk", "cid", "val", "col_version", "db_version", "site_id", "cl", "seq")
  values
  ('foo', x'010905', 'b', 'thing', 5, 5, X'7096E2D505314699A59C95FABA14ABB5', 1, 0);
insert into crsql_changes ("table", "pk", "cid", "val", "col_version", "db_version", "site_id", "cl", "seq")
  values
  ('baz', x'010b0161', 'b', 123, 101, 233, X'7096E2D505314699A59C95FABA14ABB5', 1, 0);
 
-- check that peer's changes were applied
sqlite> select * from foo;
┌───┬─────────┐
│ a │    b    │
├───┼─────────┤
│ 1 │ 2       │
│ 5 │ 'thing' │
└───┴─────────┘
 
select * from baz;
┌─────┬─────┬───────┬───────┐
│  a  │  b  │   c   │   d   │
├─────┼─────┼───────┼───────┤
│ 'a' │ 123 │ 'doo' │ 'daa' │
└─────┴─────┴───────┴───────┘
 
-- tear down the extension before closing the connection
-- https://sqlite.org/forum/forumpost/c94f943821
select crsql_finalize();

That was a whirlwind. You can see a line-by-line breakdown of using cr-sqlite (opens in a new tab) as well as api documentation.

InstallationYour First App