Public
Edited
Dec 16, 2023
6 forks
4 stars
Insert cell
Insert cell
Insert cell
sqliteWasm = import("https://esm.sh/@vlcn.io/crsqlite-wasm@0.16.0")
Insert cell
Insert cell
sqlite = sqliteWasm.default(
(file) => "https://esm.sh/@vlcn.io/crsqlite-wasm@0.16.0/dist/crsqlite.wasm"
)
Insert cell
Insert cell
db = sqlite.open(":memory:")
Insert cell
Insert cell
created = db.execMany([
`CREATE TABLE IF NOT EXISTS todo_list ("name" primary key not null, "creation_time");`,
`CREATE TABLE IF NOT EXISTS todo ("id" primary key not null, "list", "text", "complete");`
// primary keys act as the identifier by which to merge.
// if two things have different primary keys, they'll both exist.
// if two things have the same primary key, their contents are merged.
])
Insert cell
Insert cell
upgraded = created == OK && // "created == OK" is to tell observable not to run this cell until the one above has run
db.execMany([
`SELECT crsql_as_crr('todo_list');`,
`SELECT crsql_as_crr('todo');`
])
Insert cell
Insert cell
inserted = {
upgraded == OK;
let list = [
"milk",
"potatos",
"avocado",
"butter",
"cheese",
"broccoli",
"spinach"
];
// `insert or ignore` given this is a notebook and ppl will re-run cells.
await db.exec(`INSERT OR IGNORE INTO todo_list VALUES ('groceries', ?)`, [
Date.now()
]);
await Promise.all(
list.map((item) =>
db.exec(`INSERT INTO todo VALUES (?, 'groceries', ?, 0)`, [
nanoid.nanoid(),
item
])
)
);

list = ["test", "document", "explain", "onboard", "hire"];
await db.exec(`INSERT OR IGNORE INTO todo_list VALUES ('work', ?)`, [
Date.now()
]);
await Promise.all(
list.map((item) =>
db.exec(`INSERT INTO todo VALUES (?, 'work', ?, 0)`, [
nanoid.nanoid(),
item
])
)
);
return OK;
}
Insert cell
Insert cell
{
inserted == OK;
const groceries = await db.execO(
`SELECT "list", "text" FROM "todo" WHERE "list" = 'groceries'`
);
return Inputs.table(groceries);
}
Insert cell
{
inserted == OK;
const work = await db.execO(
`SELECT "list", "text" FROM "todo" WHERE "list" = 'work'`
);
return Inputs.table(work);
}
Insert cell
Insert cell
changesets = {
inserted == OK;
return await db.execA("SELECT * FROM crsql_changes where db_version > -1");
}
Insert cell
Insert cell
db2 = {
changesets;
const db2 = await sqlite.open(":memory:");
await db2.execMany([
`CREATE TABLE IF NOT EXISTS todo_list ("name" primary key not null, "creation_time");`,
`CREATE TABLE IF NOT EXISTS todo ("id" primary key not null, "list", "text", "complete");`,
`SELECT crsql_as_crr('todo_list');`,
`SELECT crsql_as_crr('todo');`
]);

return db2;
}
Insert cell
Insert cell
merged = {
await db2.tx(async (tx) => {
for (const cs of changesets) {
await tx.exec(
`INSERT INTO crsql_changes VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
cs
);
}
});
return OK;
}
Insert cell
Insert cell
{
merged == OK;
const groceries = await db2.execO(
`SELECT "list", "text" FROM "todo" WHERE "list" = 'groceries'`
);
return Inputs.table(groceries);
}
Insert cell
{
merged == OK;
const work = await db2.execO(
`SELECT "list", "text" FROM "todo" WHERE "list" = 'work'`
);
return Inputs.table(work);
}
Insert cell
Insert cell
Insert cell
db1_version = {
merged == OK;
return (await db.execA(`SELECT crsql_db_version()`))[0][0];
}
Insert cell
db2_version = {
db1_version;
return (await db2.execA(`SELECT crsql_db_version()`))[0][0];
}
Insert cell
Insert cell
concurrent_modification = {
// dependency tracking
db1_version;
db2_version;
// Both dbs create a `home` list
// given `name` is the pk, the lists will merge rather than duplicate.
await db.exec(`INSERT OR IGNORE INTO todo_list VALUES (?, ?)`, [
"home",
Date.now()
]);
await db2.exec(`INSERT OR IGNORE INTO todo_list VALUES (?, ?)`, [
"home",
Date.now()
]);
// both dbs add some stuff to that list
await db.exec(`INSERT INTO todo VALUES (?, ?, ?, ?)`, [
nanoid.nanoid(),
"home",
"paint",
0
]);
await db2.exec(`INSERT INTO todo VALUES (?, ?, ?, ?)`, [
nanoid.nanoid(),
"home",
"mow",
0
]);
await db.exec(`INSERT INTO todo VALUES (?, ?, ?, ?)`, [
nanoid.nanoid(),
"home",
"water",
0
]);
// given each item is a nanoid for primary key, `weed` will show up twice
await db2.exec(`INSERT INTO todo VALUES (?, ?, ?, ?)`, [
nanoid.nanoid(),
"home",
"weed",
0
]);
await db.exec(`INSERT INTO todo VALUES (?, ?, ?, ?)`, [
nanoid.nanoid(),
"home",
"weed",
0
]);
// and complete things on other lists
await db.exec(`UPDATE todo SET complete = 1 WHERE list = 'groceries'`);
return OK
}
Insert cell
Insert cell
db1_changesets = {
concurrent_modification == OK;
return await db.execA(`SELECT * FROM crsql_changes WHERE db_version > ?`, [
db1_version
]);
}
Insert cell
db2_changesets = {
concurrent_modification == OK;
return await db2.execA(`SELECT * FROM crsql_changes WHERE db_version > ?`, [
db2_version
]);
}
Insert cell
Inputs.table(db1_changesets)
Insert cell
Inputs.table(db2_changesets)
Insert cell
Insert cell
both_merged = {
await db2.tx(async (tx) => {
for (const cs of db1_changesets) {
await tx.exec(
`INSERT INTO crsql_changes VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
cs
);
}
});
await db.tx(async (tx) => {
for (const cs of db2_changesets) {
await tx.exec(
`INSERT INTO crsql_changes VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
cs
);
}
});
return OK;
}
Insert cell
Insert cell
{
both_merged == OK;
return Inputs.table(await db2.execO(`SELECT * FROM todo ORDER BY text ASC`));
}
Insert cell
{
both_merged == OK;
return Inputs.table(await db.execO(`SELECT * FROM todo ORDER BY text ASC`));
}
Insert cell
nanoid = import("https://esm.sh/nanoid@4.0.0")
Insert cell
OK = 0
Insert cell
siteid = (await db.execA(`SELECT crsql_site_id()`))[0][0]
Insert cell
db2_siteid = (await db2.execA(`SELECT crsql_site_id()`))[0][0]
Insert cell

One platform to build and deploy the best data apps

Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.
Learn more