Backbone SQL

When you need relational queries and transactions, declare a SQLite database in your Driftfile (with an optional schema and seed) and reach it by name from the SDK:

rows, _ := drift.Backbone.SQL("app").Query("SELECT * FROM orders WHERE status = ?", "open")
res,  _ := drift.Backbone.SQL("app").Execute("INSERT INTO orders (item, qty) VALUES (?, ?)", "widget", 3)

A single statement over 64 KiB is rejected with 413.

Transactions

Begin() returns a separate handle carrying a server-issued token. Statements join the transaction only when you run them on that handle. Anything run on SQL("app") executes outside it.

tx, _ := drift.Backbone.SQL("app").Begin()
if _, err := tx.Execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", 100, 1); err != nil {
    tx.Rollback()
    return err
}
tx.Execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", 100, 2)
tx.Commit()

Close every transaction with Commit() or Rollback(). A janitor sweeps every 5 seconds and rolls back any transaction idle for more than 30. A token used against a different database name is rejected with 400.

SQL from the CLI

drift backbone sql list           # every database and its on-disk size
drift backbone sql drop ledger    # deletes the database, its tables and its rows