Convergent, Replicated SQLite. Multi-writer and CRDT support for SQLite
A component of the vulcan project.
Example applications using cr-sqlite to sync state.
CR-SQLite is a run-time loadable extension for SQLite and libSQL. It allows merging different SQLite databases together that have taken independent writes.
In other words, you can write to your SQLite database while offline. I can write to mine while offline. We can then both come online and merge our databases together, without conflict.
In technical terms: cr-sqlite adds multi-master replication and partition tolerance to SQLite via conflict free replicated data types (CRDTs) and/or causally ordered event logs.
All of the above involve a merging of independent edits problem. If your database can handle this for you, you don't need custom code in your application to handle those 5 cases.
Discussions of these problems in the application space:
Individuals: robinvasan | iansinnott | davefowler | barbalex | MohannadNaj
Perf data: https://github.com/vlcn-io/cr-sqlite/blob/main/py/perf/perf.ipynb
The full documentation site is available here.
crsqlite
exposes three main APIs:
crsql_as_crr
) to upgrade existing tables to "crrs" or "conflict free replicated relations"
SELECT crsql_as_crr('table_name')
crsql_changes
) to ask the database for changesets or to apply changesets from another database
SELECT "table", "pk", "cid", "val", "col_version", "db_version", "site_id", cl, seq FROM crsql_changes WHERE db_version > x AND site_id = crsql_site_id()
-- to get local changesSELECT "table", "pk", "cid", "val", "col_version", "db_version", "site_id", cl, seq FROM crsql_changes WHERE db_version > x AND site_id != some_site_id
-- to get all changes excluding those synced from some actorINSERT INTO crsql_changes VALUES ([patches received from select on another peer])
crsql_begin_alter('table_name')
& crsql_alter_commit('table_name')
primitives to allow altering table definitions that have been upgraded to crr
s.
SELECT crsql_begin_alter('table_name');
-- 1 or more alterations to `table_name`
ALTER TABLE table_name ...;
SELECT crsql_commit_alter('table_name');
A future version of cr-sqlite may extend the SQL syntax to make this more natural.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.
Usage looks like:
-- load the extension if it is not statically linked
.load crsqlite
.mode qbox
-- 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", "site_id", "cl", "seq" from crsql_changes;
┌───────┬─────────────┬─────┬───────┬─────────────┬────────────┬──────────────────────────────────────┬────┬─────┐
│ table │ pk │ cid │ val │ col_version │ db_version │ "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();
Pre-built binaries of the extension are available in the releases section.
These can be loaded into sqlite
via the load_extension
command from any language (Python, NodeJS, C++, Rust, etc.) that has SQLite bindings.
The entrypoint to the loadable extension is sqlite3_crsqlite_init
so you'll either need to provide that to load_extension
or rename your binary to crsqlite.[dylib/dll/so]
. See the linked sqlite load_extension
docs.
load_extension(extension_path, 'sqlite3_crsqlite_init')
Note: if you're using
cr-sqlite
as a run time loadable extension, loading the extension should be the first operation you do after opening a connection to the database. The extension needs to be loaded on every connection you create.
For a WASM build that works in the browser, see the js directory.
For UI integrations (e.g., React) see the js directory.
There are two approaches with very different tradeoffs. Both will eventually be supported by cr-sqlite
. v1
(and current releases) support the first approach. v2
will support both approaches.
Approach 1 is characterized by the following properties:
Tables which should be synced are defined as a composition of other types of CRDTs.
Example table definition:
CREATE CLSet post (
id INTEGER PRIMARY KEY NOT NULL,
views COUNTER,
content PERITEXT,
owner_id LWW INTEGER
);
note: given that extensions can't extend the SQLite syntax this is notional. We are, however, extending the libSQL syntax so this will be available in that fork. In base SQLite you'd run the
select crsql_as_crr
function as seen earlier.
Under approach 1, merging two tables works roughly like so:
If a row was modified in multiple places, then we merge the row. Merging a row involves merging each column of that row according to the semantics of the CRDT for the column.
For more background see this post.
Notes:
To be implemented in v2 of cr-sqlite
Approach 2 has the following properties:
This is much more akin to git and event sourcing but with the drawback being that it is much more write heavy and much more space intensive.
For a table version, build against a release tag as main may not be 100% stable.
You'll need to install Rust.
Instructions on building a native library that can be loaded into SQLite in non-wasm environments.
rustup toolchain install nightly # make sure you have the rust nightly toolchain
git clone --recurse-submodules [email protected]:vlcn-io/cr-sqlite.git
cd cr-sqlite/core
make loadable
This will create a shared library at dist/crsqlite.[lib extension]
[lib extension]:
.so
.dylib
.dll
For a WASM build that works in the browser, see the js repository.
Instructions on building a sqlite3
CLI that has cr-sqlite
statically linked and pre-loaded.
In the core
directory of the project, run:
make sqlite3
This will create a sqlite3
binary at dist/sqlite3
core:
cd core
make test
py integration tests:
cd core
make loadable
cd ../py/correctness
./install-and-test.sh
JS APIs for using cr-sqlite
in the browser are not yet documented but exist in the js repo. You can also see examples of them in use here:
cr-sqlite was inspired by and built on ideas from these papers: