Examples

Python: also in Node · 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."""

import tempfile
from pathlib import Path

import potluckdb

with tempfile.TemporaryDirectory() as scratch:
    root = Path(scratch)
    remote = potluckdb.Remote.from_directory(root / "remote")
    manual = potluckdb.OpenOptions(sync_policy=potluckdb.SyncPolicy.manual())

    writer = potluckdb.open(root / "writer", remote, options=manual)
    writer.execute("CREATE TABLE item(id INTEGER PRIMARY KEY, value TEXT)")
    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.
    rival = potluckdb.open(root / "rival", remote, options=manual)
    rival.execute("INSERT INTO item(value) VALUES('from rival')")
    assert type(rival.sync()).__name__ == "Pushed"

    # The writer races the same head with its own local work.
    writer.execute("INSERT INTO item(value) VALUES('from writer')")
    conflict = writer.sync()
    assert type(conflict).__name__ == "Conflict"

    resolution = writer.resolve_conflict()
    assert resolution.revision is not None
    # Replayed, not quarantined: nothing was given up to resolve this.
    assert resolution.quarantined == ()
    # Pushed, not "Pushed or UpToDate": resolving stages the replayed
    # statement, so there is always something left to publish.
    assert type(writer.sync()).__name__ == "Pushed"

    # Both sides' rows survive: the winner was adopted and the safe local
    # statement replayed on top of it.
    rows = writer.query("SELECT id, value FROM item ORDER BY id")
    assert list(rows) == [(1, "from rival"), (2, "from writer")]

    writer.close()
    rival.close()
    remote.close()

print("conflict_resolution: ok")

Quickstart local

"""Local use needs no configuration: open, write, read, close."""

import tempfile
from pathlib import Path

import potluckdb

with tempfile.TemporaryDirectory() as scratch:
    db = potluckdb.open(Path(scratch) / "app.potluckdb")
    db.execute("CREATE TABLE note(id INTEGER PRIMARY KEY, body TEXT NOT NULL)")
    result = db.execute("INSERT INTO note(body) VALUES(?)", ("hello",))
    assert result.rows_affected == 1
    assert result.last_insert_id == 1

    rows = db.query("SELECT id, body FROM note ORDER BY id")
    assert list(rows) == [(1, "hello")]
    assert rows.columns == ("id", "body")
    assert rows[0]["body"] == "hello" and rows[0][0] == 1
    db.close()
    db.close()  # close is idempotent

print("quickstart_local: ok")

Remote sync

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

import tempfile
from pathlib import Path

import potluckdb

with tempfile.TemporaryDirectory() as scratch:
    root = Path(scratch)
    remote = potluckdb.Remote.from_directory(root / "remote")
    manual = potluckdb.OpenOptions(sync_policy=potluckdb.SyncPolicy.manual())

    laptop = potluckdb.open(root / "laptop", remote, options=manual)
    laptop.execute("CREATE TABLE task(id INTEGER PRIMARY KEY, title TEXT)")
    laptop.execute("INSERT INTO task(title) VALUES(?)", ("ship the release",))
    outcome = laptop.sync()
    assert type(outcome).__name__ == "Pushed"

    phone = potluckdb.open(root / "phone", remote, options=manual)
    rows = phone.query("SELECT title FROM task")
    assert list(rows) == [("ship the release",)]

    # An unchanged head is reported as exactly that.
    assert type(phone.sync()).__name__ == "UpToDate"

    laptop.close()
    phone.close()
    remote.close()

print("remote_sync: ok")

Second device

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

import tempfile
from pathlib import Path

import potluckdb

with tempfile.TemporaryDirectory() as scratch:
    root = Path(scratch)
    remote = potluckdb.Remote.from_directory(root / "remote")
    manual = potluckdb.OpenOptions(sync_policy=potluckdb.SyncPolicy.manual())

    # The first device publishes before any other device opens.
    laptop = potluckdb.open(root / "laptop", remote, options=manual)
    laptop.execute("CREATE TABLE note(id INTEGER PRIMARY KEY, body TEXT)")
    laptop.execute("INSERT INTO note VALUES(1, 'from the laptop')")
    assert type(laptop.sync()).__name__ == "Pushed"

    # Opening now bootstraps from that published head, so the phone
    # already has the table.
    phone = potluckdb.open(root / "phone", remote, options=manual)
    assert phone.query("SELECT body FROM note")[0][0] == "from the laptop"
    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.
    early_remote = potluckdb.Remote.from_directory(root / "early-remote")
    first = potluckdb.open(root / "first", early_remote, options=manual)
    tablet = potluckdb.open(root / "tablet", early_remote, options=manual)
    assert tablet.status().pending_writes == 0
    first.execute("CREATE TABLE note(id INTEGER PRIMARY KEY, body TEXT)")
    first.execute("INSERT INTO note VALUES(1, 'from the first device')")
    assert type(first.sync()).__name__ == "Pushed"

    assert type(tablet.sync()).__name__ == "Pulled"
    assert tablet.query("SELECT body FROM note")[0][0] == "from the first device"

    first.close()
    tablet.close()
    early_remote.close()
    laptop.close()
    remote.close()

print("second_device: ok")

Transactions

"""Managed transactions: commit on success, rollback on failure."""

import tempfile
from pathlib import Path

import potluckdb

with tempfile.TemporaryDirectory() as scratch:
    db = potluckdb.open(Path(scratch) / "app.potluckdb")
    db.execute("CREATE TABLE ledger(id INTEGER PRIMARY KEY, amount INTEGER)")

    with db.transaction() as transaction:
        transaction.execute("INSERT INTO ledger(amount) VALUES(?)", (100,))
        transaction.execute("INSERT INTO ledger(amount) VALUES(?)", (-40,))
        (total,) = transaction.query("SELECT SUM(amount) FROM ledger")[0]
        assert total == 60
    # The scope completed, so both rows committed.
    assert db.query("SELECT COUNT(*) FROM ledger")[0][0] == 2

    try:
        with db.transaction() as transaction:
            transaction.execute("INSERT INTO ledger(amount) VALUES(?)", (999,))
            raise RuntimeError("abort this batch")
    except RuntimeError:
        pass
    # The failed scope rolled back: still two rows.
    assert db.query("SELECT COUNT(*) FROM ledger")[0][0] == 2

    db.close()

print("transactions: ok")

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 and
says so.
"""

import tempfile
from pathlib import Path

import potluckdb

with tempfile.TemporaryDirectory() as scratch:
    root = Path(scratch)
    remote = potluckdb.Remote.from_directory(root / "remote")
    manual = potluckdb.OpenOptions(sync_policy=potluckdb.SyncPolicy.manual())

    laptop = potluckdb.open(root / "laptop", remote, options=manual)
    laptop.execute("CREATE TABLE note(id INTEGER PRIMARY KEY, body TEXT)")
    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.
    phone = potluckdb.open(root / "phone", remote, options=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.
    laptop.execute("INSERT INTO note(body) VALUES('buy milk')")
    phone.execute("INSERT INTO note(body) VALUES('call the dentist')")

    assert type(laptop.sync()).__name__ == "Pushed"

    # The phone published second, so it finds the head has moved. This is
    # the expected result of concurrent writing, not a failure.
    assert type(phone.sync()).__name__ == "Conflict"
    resolution = phone.resolve_conflict()
    assert resolution.quarantined == ()
    assert type(phone.sync()).__name__ == "Pushed"

    # The laptop pulls what the phone resolved and published.
    assert type(laptop.sync()).__name__ == "Pulled"

    both = [("buy milk",), ("call the dentist",)]
    assert sorted(laptop.query("SELECT body FROM note")) == sorted(both)
    assert sorted(phone.query("SELECT body FROM note")) == sorted(both)

    laptop.close()
    phone.close()
    remote.close()

print("two_writers: ok")