Examples

Swift: also in Python · Node · Go · Rust · 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.
//
// A losing sync is a SyncResult case, not a thrown error. Errors are for
// things that went wrong; losing a race is an ordinary outcome with a
// defined next step.

import Foundation
import potluckdb

let scratch = URL(fileURLWithPath: NSTemporaryDirectory())
    .appendingPathComponent("potluckdb-conflict-\(ProcessInfo.processInfo.processIdentifier)")
try FileManager.default.createDirectory(at: scratch, withIntermediateDirectories: true)
defer { try? FileManager.default.removeItem(at: scratch) }

let remote = try Remote.fromDirectory(scratch.appendingPathComponent("remote").path)
// OpenOptions validates its settings, so constructing it can fail
// rather than deferring an invalid combination to the first open.
let manual = OpenOptions(syncPolicy: .manual())

let writer = try open(
    scratch.appendingPathComponent("writer").path, remote: remote, options: manual
)
_ = try writer.execute("CREATE TABLE item(id INTEGER PRIMARY KEY, value TEXT)")
_ = try 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.
let rival = try open(
    scratch.appendingPathComponent("rival").path, remote: remote, options: manual
)
_ = try rival.execute("INSERT INTO item(value) VALUES('from rival')")
switch try rival.sync() {
case .pushed: break
case let other: preconditionFailure("expected the rival to publish, got \(other)")
}

// The writer races the same head with its own local work.
_ = try writer.execute("INSERT INTO item(value) VALUES('from writer')")
switch try writer.sync() {
case .conflict: break
case let other: preconditionFailure("expected the writer to be told it lost, got \(other)")
}

let resolution = try writer.resolveConflict()
precondition(!resolution.revision.bytes.isEmpty, "resolution reports what it adopted")
// Pushed, not "pushed or upToDate": resolving stages the replayed
// statement, so there is always something left to publish.
switch try writer.sync() {
case .pushed: break
case let other: preconditionFailure("expected the writer to publish, got \(other)")
}

// Both sides' rows survive: the winner was adopted and the safe local
// statement replayed on top of it.
let rows = try writer.query("SELECT id, value FROM item ORDER BY id")
precondition(
    rows == [[.integer(1), .text("from rival")], [.integer(2), .text("from writer")]],
    "nothing was lost"
)

try writer.close()
try rival.close()
try remote.close()
print("conflict_resolution: ok")

Quickstart local

// Local use needs no configuration: open, write, read, close.
//
// Values are an SQLValue enum in both directions, so a column that is not
// the type you expected is a `case` you have not written rather than a
// surprise at run time.

import Foundation
import potluckdb

let scratch = URL(fileURLWithPath: NSTemporaryDirectory())
    .appendingPathComponent("potluckdb-\(ProcessInfo.processInfo.processIdentifier)")
try FileManager.default.createDirectory(at: scratch, withIntermediateDirectories: true)
defer { try? FileManager.default.removeItem(at: scratch) }

let database = try open(scratch.appendingPathComponent("app.potluckdb").path)

_ = try database.execute("CREATE TABLE note(id INTEGER PRIMARY KEY, body TEXT NOT NULL)")

let inserted = try database.execute(
    "INSERT INTO note(body) VALUES(?)",
    .positional([.text("hello")])
)
precondition(inserted.rowsAffected == 1, "one row inserted")
// lastInsertID is optional: a statement that inserted nothing has no
// identifier to report, and nil says so without inventing a zero.
precondition(inserted.lastInsertID == 1, "first row has id 1")

let rows = try database.query("SELECT id, body FROM note ORDER BY id")
precondition(rows.columns == ["id", "body"], "the result names its columns")
precondition(rows == [[.integer(1), .text("hello")]], "the row reads back")
precondition(rows[0]["body"] == .text("hello"), "and by column name")

try database.close()
try database.close()  // close is idempotent

print("quickstart_local: ok")

Remote sync

// Two devices share one remote: write on one, sync, read on the other.
//
// SyncResult is an enum, so every outcome is a `case` and the compiler
// objects when a new one is added and left unhandled.

import Foundation
import potluckdb

let scratch = URL(fileURLWithPath: NSTemporaryDirectory())
    .appendingPathComponent("potluckdb-sync-\(ProcessInfo.processInfo.processIdentifier)")
try FileManager.default.createDirectory(at: scratch, withIntermediateDirectories: true)
defer { try? FileManager.default.removeItem(at: scratch) }

let remote = try Remote.fromDirectory(scratch.appendingPathComponent("remote").path)
// OpenOptions validates its settings, so constructing it can fail
// rather than deferring an invalid combination to the first open.
let manual = OpenOptions(syncPolicy: .manual())

let laptop = try open(
    scratch.appendingPathComponent("laptop").path, remote: remote, options: manual
)
_ = try laptop.execute("CREATE TABLE task(id INTEGER PRIMARY KEY, title TEXT)")
_ = try laptop.execute(
    "INSERT INTO task(title) VALUES(?)", .positional([.text("ship the release")])
)
switch try laptop.sync() {
case .pushed: break
case let other: preconditionFailure("expected the first sync to publish, got \(other)")
}

let phone = try open(
    scratch.appendingPathComponent("phone").path, remote: remote, options: manual
)
let rows = try phone.query("SELECT title FROM task")
precondition(rows == [[.text("ship the release")]], "the other device sees the row")

// An unchanged head is reported as exactly that.
switch try phone.sync() {
case .upToDate: break
case let other: preconditionFailure("expected an unchanged head, got \(other)")
}

try laptop.close()
try phone.close()
try remote.close()
print("remote_sync: ok")

Second device

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

import Foundation
import potluckdb

let scratch = URL(fileURLWithPath: NSTemporaryDirectory())
    .appendingPathComponent("potluckdb-second-device-\(ProcessInfo.processInfo.processIdentifier)")
try FileManager.default.createDirectory(at: scratch, withIntermediateDirectories: true)
defer { try? FileManager.default.removeItem(at: scratch) }

let remote = try Remote.fromDirectory(scratch.appendingPathComponent("remote").path)
let manual = OpenOptions(syncPolicy: .manual())

// The first device publishes before any other device opens.
let laptop = try open(
    scratch.appendingPathComponent("laptop").path, remote: remote, options: manual
)
_ = try laptop.execute("CREATE TABLE note(id INTEGER PRIMARY KEY, body TEXT)")
_ = try laptop.execute("INSERT INTO note VALUES(1, 'from the laptop')")
switch try laptop.sync() {
case .pushed: break
case let other: preconditionFailure("expected the first sync to publish, got \(other)")
}

// Opening now bootstraps from that published head, so the phone
// already has the table.
let phone = try open(
    scratch.appendingPathComponent("phone").path, remote: remote, options: manual
)
let phoneRows = try phone.query("SELECT body FROM note")
precondition(phoneRows == [[.text("from the laptop")]], "the other device sees the row")
try 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.
let earlyRemote = try Remote.fromDirectory(scratch.appendingPathComponent("early-remote").path)
let first = try open(
    scratch.appendingPathComponent("first").path, remote: earlyRemote, options: manual
)
let tablet = try open(
    scratch.appendingPathComponent("tablet").path, remote: earlyRemote, options: manual
)
let pendingAtOpen = try tablet.status().pendingWrites
precondition(pendingAtOpen == 0, "opening records nothing to publish")
_ = try first.execute("CREATE TABLE note(id INTEGER PRIMARY KEY, body TEXT)")
_ = try first.execute("INSERT INTO note VALUES(1, 'from the first device')")
switch try first.sync() {
case .pushed: break
case let other: preconditionFailure("expected the first device to publish, got \(other)")
}

switch try tablet.sync() {
case .pulled: break
case let other: preconditionFailure("expected a pristine device to pull, got \(other)")
}
let visible = try tablet.query("SELECT body FROM note")
precondition(visible == [[.text("from the first device")]], "the tablet sees the published row")

try first.close()
try tablet.close()
try earlyRemote.close()
try laptop.close()
try remote.close()
print("second_device: ok")

Transactions

// Managed transactions: commit on success, roll back on failure.
//
// The scope is a closure. Returning normally commits; throwing rolls back
// and the error reaches the caller unchanged, so a failed batch cannot be
// mistaken for a successful one.

import Foundation
import potluckdb

struct AbortBatch: Error {}

let scratch = URL(fileURLWithPath: NSTemporaryDirectory())
    .appendingPathComponent("potluckdb-tx-\(ProcessInfo.processInfo.processIdentifier)")
try FileManager.default.createDirectory(at: scratch, withIntermediateDirectories: true)
defer { try? FileManager.default.removeItem(at: scratch) }

let database = try open(scratch.appendingPathComponent("app.potluckdb").path)
_ = try database.execute("CREATE TABLE ledger(id INTEGER PRIMARY KEY, amount INTEGER)")

try database.transaction { transaction in
    _ = try transaction.execute(
        "INSERT INTO ledger(amount) VALUES(?)", .positional([.integer(100)])
    )
    _ = try transaction.execute(
        "INSERT INTO ledger(amount) VALUES(?)", .positional([.integer(-40)])
    )
    let total = try transaction.query("SELECT SUM(amount) FROM ledger")
    precondition(total[0][0] == .integer(60), "the running total is visible inside the scope")
}

let committed = try database.query("SELECT COUNT(*) FROM ledger")
precondition(committed[0][0] == .integer(2), "two rows committed")

do {
    try database.transaction { transaction in
        _ = try transaction.execute(
            "INSERT INTO ledger(amount) VALUES(?)", .positional([.integer(999)])
        )
        throw AbortBatch()
    }
    preconditionFailure("the throwing scope should not have completed")
} catch is AbortBatch {
    // Expected: the error travels out unchanged.
}

let after = try database.query("SELECT COUNT(*) FROM ledger")
precondition(after[0][0] == .integer(2), "the failed batch left nothing behind")

try database.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.

import Foundation
import potluckdb

let scratch = URL(fileURLWithPath: NSTemporaryDirectory())
    .appendingPathComponent("potluckdb-two-writers-\(ProcessInfo.processInfo.processIdentifier)")
try FileManager.default.createDirectory(at: scratch, withIntermediateDirectories: true)
defer { try? FileManager.default.removeItem(at: scratch) }

let remote = try Remote.fromDirectory(scratch.appendingPathComponent("remote").path)
let manual = OpenOptions(syncPolicy: .manual())

let laptop = try open(
    scratch.appendingPathComponent("laptop").path, remote: remote, options: manual
)
_ = try laptop.execute("CREATE TABLE note(id INTEGER PRIMARY KEY, body TEXT)")
_ = try 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.
let phone = try open(
    scratch.appendingPathComponent("phone").path, remote: 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.
_ = try laptop.execute("INSERT INTO note(body) VALUES('buy milk')")
_ = try phone.execute("INSERT INTO note(body) VALUES('call the dentist')")

switch try laptop.sync() {
case .pushed: break
case let other: preconditionFailure("expected the laptop to publish, got \(other)")
}

// The phone published second, so it finds the head has moved. This is the
// expected result of concurrent writing, not a failure.
switch try phone.sync() {
case .conflict: break
case let other: preconditionFailure("expected the head to have moved, got \(other)")
}
let resolution = try phone.resolveConflict()
precondition(resolution.quarantined.isEmpty, "nothing was given up to resolve this")
switch try phone.sync() {
case .pushed: break
case let other: preconditionFailure("expected the phone to publish, got \(other)")
}

// The laptop pulls what the phone resolved and published.
switch try laptop.sync() {
case .pulled: break
case let other: preconditionFailure("expected the laptop to pull, got \(other)")
}

let both: [[SQLValue]] = [[.text("buy milk")], [.text("call the dentist")]]
for device in [laptop, phone] {
    // Queried outside the precondition: `try` cannot appear inside its
    // autoclosure, and the compiler is right to insist -- an assertion
    // that could throw is not an assertion.
    let rows = try device.query("SELECT body FROM note ORDER BY body")
    precondition(rows == both, "both devices see both notes")
}

try laptop.close()
try phone.close()
try remote.close()
print("two_writers: ok")