Examples

Node: also in Python · Go · Rust · Swift · Dart · Java and Kotlin · .NET · C++ · Objective-C

Every program here is executed against the published package on each release, so an example that stopped working would fail the release rather than mislead you.

Conflict resolution

// Conflict is data, not an exception: detect it, resolve it, lose nothing.

const assert = require("node:assert/strict");
const fs = require("node:fs");
const os = require("node:os");
const path = require("node:path");
const potluckdb = require("@potluckdb/potluckdb");

async function main() {
  const scratch = fs.mkdtempSync(path.join(os.tmpdir(), "potluckdb-"));
  const remote = await potluckdb.Remote.fromDirectory(path.join(scratch, "remote"));
  const manual = new potluckdb.OpenOptions({ syncPolicy: potluckdb.SyncPolicy.manual() });

  const writer = await potluckdb.open(path.join(scratch, "writer"), remote, manual);
  await writer.execute("CREATE TABLE item(id INTEGER PRIMARY KEY, value TEXT)");
  await writer.sync();

  // No id is named: the database assigns one. That matters more than it
  // looks. Resolving a conflict replays local statements on top of the
  // winner, and a statement whose key the winner already used cannot be
  // replayed -- it is quarantined instead, and its write does not land.
  // Two devices that both pick their own ids collide the moment they
  // pick the same one. Left to the database, they cannot.
  const rival = await potluckdb.open(path.join(scratch, "rival"), remote, manual);
  await rival.execute("INSERT INTO item(value) VALUES('from rival')");
  assert.equal((await rival.sync()).kind, "pushed", "the rival publishes first");

  // The writer races the same head with its own local work.
  await writer.execute("INSERT INTO item(value) VALUES('from writer')");
  assert.equal((await writer.sync()).kind, "conflict", "the writer is told it lost the race");

  const resolution = await writer.resolveConflict();
  assert.notEqual(resolution.revision, null, "resolution reports the revision it adopted");
  // Pushed, not "pushed or up_to_date": resolving stages the replayed
  // statement, so there is always something left to publish.
  assert.equal((await writer.sync()).kind, "pushed", "the writer publishes what it resolved");

  // Both sides' rows survive: the winner was adopted and the safe local
  // statement replayed on top of it.
  const { rows } = await writer.query("SELECT id, value FROM item ORDER BY id");
  assert.deepEqual(
    rows,
    [{ id: 1, value: "from rival" }, { id: 2, value: "from writer" }],
    "nothing was lost",
  );

  await writer.close();
  await rival.close();
  await remote.close();
  fs.rmSync(scratch, { recursive: true, force: true });
  console.log("conflict_resolution: ok");
}

main().catch((error) => { console.error(error); process.exit(1); });

Quickstart local

// Local use needs no configuration: open, write, read, close.
//
// A query answers with `columns`, the select list's names in order, and
// `rows`, one plain object per row keyed by column name. SQL integers
// arrive as numbers. A number holds an integer exactly only within
// ±2^53; a stored value beyond that is refused with `unsafe_integer`
// rather than rounded, and a database opened with `readBigInts: true`
// returns every integer as a bigint instead: `1n`, not `1`. SQL BLOBs
// arrive as `Uint8Array` values, and `Uint8Array` (including Node's
// `Buffer`) binds as a BLOB.

const assert = require("node:assert/strict");
const fs = require("node:fs");
const os = require("node:os");
const path = require("node:path");
const potluckdb = require("@potluckdb/potluckdb");

async function main() {
  const scratch = fs.mkdtempSync(path.join(os.tmpdir(), "potluckdb-"));
  const db = await potluckdb.open(path.join(scratch, "app.potluckdb"));
  await db.execute("CREATE TABLE note(id INTEGER PRIMARY KEY, body TEXT NOT NULL)");
  const result = await db.execute("INSERT INTO note(body) VALUES(?)", ["hello"]);
  assert.equal(result.rowsAffected, 1, "one row inserted");
  assert.equal(result.lastInsertId, 1, "first row has id 1");

  const { columns, rows } = await db.query("SELECT id, body FROM note ORDER BY id");
  assert.deepEqual(columns, ["id", "body"], "the result names its columns");
  assert.deepEqual(rows, [{ id: 1, body: "hello" }], "a row is an object keyed by column");

  // Binding: a plain integer binds as an integer, and so does a bigint.
  const byNumber = await db.query("SELECT body FROM note WHERE id = ?", [1]);
  assert.deepEqual(byNumber.rows, [{ body: "hello" }], "a plain integer binds as an integer");
  const byBigInt = await db.query("SELECT body FROM note WHERE id = ?", [1n]);
  assert.deepEqual(byBigInt.rows, [{ body: "hello" }], "and so does a bigint");

  // Past what a number holds exactly, an integral Number binds as REAL:
  // it is the value's own representation. BigInt stays the way to bind a
  // large exact INTEGER.
  const wide = await db.query("SELECT ?1 AS value, typeof(?1) AS kind", [Number.MAX_SAFE_INTEGER + 1]);
  assert.equal(wide.rows[0].kind, "real", "an inexact integer number binds as REAL");
  assert.equal(wide.rows[0].value, 2 ** 53, "and reads back as that number");

  // Counting comes back a number too.
  const counted = await db.query("SELECT count(*) AS notes FROM note");
  assert.equal(counted.rows[0].notes, 1, "count(*) comes back a number");

  // A stored integer beyond ±2^53 cannot arrive as a number. Reading it
  // is refused, and the error's action names the option that can.
  await db.execute("CREATE TABLE reading(name TEXT PRIMARY KEY, value INTEGER NOT NULL)");
  await db.execute("INSERT INTO reading(name, value) VALUES(?, ?)", ["far away", 2n ** 60n]);
  await assert.rejects(
    db.query("SELECT value FROM reading WHERE name = ?", ["far away"]),
    (error) => error.code === "unsafe_integer" && error.action.includes("readBigInts"),
    "an integer beyond 2^53 is refused as a number",
  );
  await db.close();
  await db.close(); // close is idempotent

  // Opened with readBigInts: true, every SQL integer is a bigint.
  const exact = await potluckdb.open(
    path.join(scratch, "app.potluckdb"),
    new potluckdb.OpenOptions({ readBigInts: true }),
  );
  const ids = await exact.query("SELECT id FROM note ORDER BY id");
  assert.deepEqual(ids.rows, [{ id: 1n }], "integers arrive as bigints");
  const far = await exact.query("SELECT value FROM reading WHERE name = ?", ["far away"]);
  assert.deepEqual(far.rows, [{ value: 2n ** 60n }], "including the one a number could not hold");
  await exact.close();

  // BLOBs arrive as Uint8Arrays -- a Node Buffer, which is one -- and both
  // bind back as BLOBs, including an empty one.
  const binary = await potluckdb.open(path.join(scratch, "blobs.potluckdb"));
  await binary.execute("CREATE TABLE payload(name TEXT PRIMARY KEY, data BLOB NOT NULL)");
  await binary.execute("INSERT INTO payload(name, data) VALUES(?, ?)", ["bytes", new Uint8Array([0, 255])]);
  await binary.execute("INSERT INTO payload(name, data) VALUES(?, ?)", ["empty", Buffer.alloc(0)]);
  const blobs = await binary.query("SELECT name, data FROM payload ORDER BY name");
  assert.ok(blobs.rows[0].data instanceof Uint8Array, "a BLOB arrives as a Uint8Array");
  assert.deepEqual(blobs.rows[0].data, Buffer.from([0, 255]), "a BLOB round-trips as binary");
  assert.deepEqual(blobs.rows[1].data, Buffer.alloc(0), "including an empty one");
  await binary.close();

  fs.rmSync(scratch, { recursive: true, force: true });
  console.log("quickstart_local: ok");
}

main().catch((error) => { console.error(error); process.exit(1); });

Remote sync

// Two devices share one remote: write on one, sync, read on the other.

const assert = require("node:assert/strict");
const fs = require("node:fs");
const os = require("node:os");
const path = require("node:path");
const potluckdb = require("@potluckdb/potluckdb");

async function main() {
  const scratch = fs.mkdtempSync(path.join(os.tmpdir(), "potluckdb-"));
  const remote = await potluckdb.Remote.fromDirectory(path.join(scratch, "remote"));
  const manual = new potluckdb.OpenOptions({ syncPolicy: potluckdb.SyncPolicy.manual() });

  const laptop = await potluckdb.open(path.join(scratch, "laptop"), remote, manual);
  await laptop.execute("CREATE TABLE task(id INTEGER PRIMARY KEY, title TEXT)");
  await laptop.execute("INSERT INTO task(title) VALUES(?)", ["ship the release"]);
  const pushed = await laptop.sync();
  assert.ok(pushed.kind === "pushed", `expected pushed, got ${pushed.kind}`);

  const phone = await potluckdb.open(path.join(scratch, "phone"), remote, manual);
  const { rows } = await phone.query("SELECT title FROM task");
  assert.ok(
    JSON.stringify(rows) === JSON.stringify([{ title: "ship the release" }]),
    "the other device sees the row",
  );

  // An unchanged head is reported as exactly that.
  const again = await phone.sync();
  assert.ok(again.kind === "up_to_date", `expected up_to_date, got ${again.kind}`);

  await laptop.close();
  await phone.close();
  await remote.close();
  fs.rmSync(scratch, { recursive: true, force: true });
  console.log("remote_sync: ok");
}

main().catch((error) => { console.error(error); process.exit(1); });

Second device

// Adding a second device, including one that opens before anything is published.

const assert = require("node:assert/strict");
const fs = require("node:fs");
const os = require("node:os");
const path = require("node:path");
const potluckdb = require("@potluckdb/potluckdb");

async function main() {
  const scratch = fs.mkdtempSync(path.join(os.tmpdir(), "potluckdb-"));
  const remote = await potluckdb.Remote.fromDirectory(path.join(scratch, "remote"));
  const manual = new potluckdb.OpenOptions({ syncPolicy: potluckdb.SyncPolicy.manual() });

  // The first device publishes before any other device opens.
  const laptop = await potluckdb.open(path.join(scratch, "laptop"), remote, manual);
  await laptop.execute("CREATE TABLE note(id INTEGER PRIMARY KEY, body TEXT)");
  await laptop.execute("INSERT INTO note VALUES(1, 'from the laptop')");
  const published = await laptop.sync();
  assert.ok(published.kind === "pushed", `expected pushed, got ${published.kind}`);

  // Opening now bootstraps from that published head, so the phone
  // already has the table.
  const phone = await potluckdb.open(path.join(scratch, "phone"), remote, manual);
  const phoneRows = await phone.query("SELECT body FROM note");
  assert.equal(phoneRows.rows[0].body, "from the laptop");
  await phone.close();

  // Devices may be opened in any order. One that opens against an
  // empty remote has nothing to copy and nothing of its own to
  // publish, so its first sync is an ordinary pull.
  const earlyRemote = await potluckdb.Remote.fromDirectory(
    path.join(scratch, "early-remote"),
  );
  const first = await potluckdb.open(path.join(scratch, "first"), earlyRemote, manual);
  const tablet = await potluckdb.open(path.join(scratch, "tablet"), earlyRemote, manual);
  const pending = (await tablet.status()).pendingWrites;
  assert.equal(pending, 0n);
  await first.execute("CREATE TABLE note(id INTEGER PRIMARY KEY, body TEXT)");
  await first.execute("INSERT INTO note VALUES(1, 'from the first device')");
  const firstSync = await first.sync();
  assert.ok(firstSync.kind === "pushed", `expected pushed, got ${firstSync.kind}`);

  const pulled = await tablet.sync();
  assert.ok(pulled.kind === "pulled", `expected pulled, got ${pulled.kind}`);
  const tabletRows = await tablet.query("SELECT body FROM note");
  assert.equal(tabletRows.rows[0].body, "from the first device");

  await first.close();
  await tablet.close();
  await earlyRemote.close();
  await laptop.close();
  await remote.close();
  fs.rmSync(scratch, { recursive: true, force: true });
  console.log("second_device: ok");
}

main().catch((error) => { console.error(error); process.exit(1); });

Transactions

// Managed transactions: commit on success, roll back on failure.
//
// The scope is a callback here rather than Python's `with` block, because
// that is how a JavaScript reader expects a scope that must always end.

const assert = require("node:assert/strict");
const fs = require("node:fs");
const os = require("node:os");
const path = require("node:path");
const potluckdb = require("@potluckdb/potluckdb");

async function main() {
  const scratch = fs.mkdtempSync(path.join(os.tmpdir(), "potluckdb-"));
  const db = await potluckdb.open(path.join(scratch, "app.potluckdb"));
  await db.execute("CREATE TABLE ledger(id INTEGER PRIMARY KEY, amount INTEGER)");

  await db.transaction(async (transaction) => {
    await transaction.execute("INSERT INTO ledger(amount) VALUES(?)", [100]);
    await transaction.execute("INSERT INTO ledger(amount) VALUES(?)", [-40]);
    const { rows } = await transaction.query("SELECT SUM(amount) AS total FROM ledger");
    // Integers arrive as numbers: 60, not 60n.
    assert.equal(rows[0].total, 60, "the running total is visible inside the scope");
  });
  // The scope completed, so both rows committed.
  const committed = await db.query("SELECT COUNT(*) AS entries FROM ledger");
  assert.equal(committed.rows[0].entries, 2, "two rows committed");

  await assert.rejects(
    db.transaction(async (transaction) => {
      await transaction.execute("INSERT INTO ledger(amount) VALUES(?)", [999]);
      throw new Error("abort this batch");
    }),
    "a throwing scope rejects rather than committing",
  );
  // The failed scope rolled back: still two rows.
  const after = await db.query("SELECT COUNT(*) AS entries FROM ledger");
  assert.equal(after.rows[0].entries, 2, "the failed batch left nothing behind");

  await db.close();
  fs.rmSync(scratch, { recursive: true, force: true });
  console.log("transactions: ok");
}

main().catch((error) => { console.error(error); process.exit(1); });

Two writers

// Two devices that both write, and end up agreeing.
//
// This is what a local-first database is for, and it is the one sequence
// worth learning: write on each device, publish, and let the second one
// resolve. Concurrent writes are the ordinary case, not the exceptional
// one -- whichever device publishes second finds the head has moved.

const assert = require("node:assert/strict");
const fs = require("node:fs");
const os = require("node:os");
const path = require("node:path");
const potluckdb = require("@potluckdb/potluckdb");

async function main() {
  const scratch = fs.mkdtempSync(path.join(os.tmpdir(), "potluckdb-"));
  const remote = await potluckdb.Remote.fromDirectory(path.join(scratch, "remote"));
  const manual = new potluckdb.OpenOptions({ syncPolicy: potluckdb.SyncPolicy.manual() });

  const laptop = await potluckdb.open(path.join(scratch, "laptop"), remote, manual);
  await laptop.execute("CREATE TABLE note(id INTEGER PRIMARY KEY, body TEXT)");
  await laptop.sync();

  // A second device bootstraps from the published head, schema and all.
  // Creating the table again here is not a no-op -- it fails with
  // table already exists, because it does.
  const phone = await potluckdb.open(path.join(scratch, "phone"), remote, manual);

  // Both write before either publishes. Neither names an id, so each
  // side assigns its own -- and both pick 1, because both are looking at
  // the same head. That is fine precisely because the statement does not
  // name it: replaying it on top of the winner assigns a fresh one.
  await laptop.execute("INSERT INTO note(body) VALUES(?)", ["buy milk"]);
  await phone.execute("INSERT INTO note(body) VALUES(?)", ["call the dentist"]);

  assert.equal((await laptop.sync()).kind, "pushed", "the laptop publishes first");

  // The phone published second, so it finds the head has moved. This is
  // the expected result of concurrent writing, not a failure.
  assert.equal((await phone.sync()).kind, "conflict", "the head moved under the phone");
  const resolution = await phone.resolveConflict();
  assert.deepEqual(resolution.quarantined, [], "nothing was given up to resolve this");
  assert.equal((await phone.sync()).kind, "pushed", "the phone publishes what it resolved");

  // The laptop pulls what the phone resolved and published.
  assert.equal((await laptop.sync()).kind, "pulled", "the laptop takes the phone's work");

  const both = [{ body: "buy milk" }, { body: "call the dentist" }];
  for (const device of [laptop, phone]) {
    const { rows } = await device.query("SELECT body FROM note ORDER BY body");
    assert.deepEqual(rows, both);
  }

  await laptop.close();
  await phone.close();
  await remote.close();
  fs.rmSync(scratch, { recursive: true, force: true });
  console.log("two_writers: ok");
}

main().catch((error) => { console.error(error); process.exit(1); });