Backbone SQL
When you need relational queries and transactions, declare a SQLite database with a sql block in your Driftfile (a required size, and an optional schema, migrations and seed) and reach it by name from the SDK. size is the database's storage: the limit it enforces and what it is billed at. The slice's database count is the number of sql blocks the file declares, and a database past that cap answers 429.
slice "shop" {}
backbone {
sql "app" {
size = "128MiB"
schema = "backbone/app.sql"
}
}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 looks every second and rolls back any transaction idle for more than 30. A token used against a different database name is rejected with 400.
Migrations
A schema file is re-run on every deploy, which suits CREATE TABLE IF NOT EXISTS and nothing that changes a table already holding data. For that, point migrations at a directory of numbered SQL files:
slice "shop" {}
backbone {
sql "app" {
size = "128MiB"
schema = "backbone/app.sql"
migrations = "backbone/migrations"
seed = "backbone/app-seed.sql"
}
}backbone/migrations/
0001_create_users.sql
0002_add_email.sql
0003_index_email.sqlEach file is applied once, on the first deploy that sees it, in numeric order. A deploy takes the database's parts in a fixed order: the schema, then every migration not yet applied, then the seed. Each migration runs in its own transaction and is recorded, with a checksum of its contents, in a table inside the database called _drift_migrations. Because the record lives in the database, a snapshot carries it, and a restored database does not run anything again.
The rules, every one of them enforced by the slice:
- A file is named
<number>_<name>.sql, where the name is letters, digits,_and-. Numbers compare as numbers, so10_runs after9_, and gaps are fine. Only.sqlfiles directly inside the directory are sent; a README or a subdirectory is left alone. - Two files with one number are refused, because neither can be ordered before the other.
- A new file numbered below one already applied is refused. It would run after a migration it was numbered to precede. Renumber it above the highest applied one.
- An applied file whose contents changed is refused. An applied migration never runs again, so an edit would never take effect. Put the change in a new migration instead. A change of line endings alone is not an edit.
- Do not write
BEGINorCOMMIT. Each file already runs in a transaction. A file that ends it itself fails the deploy and is not recorded, and whatever it ran before that statement cannot be rolled back.
The name, number and contents checks run before any migration does, so those refusals apply none of the files. A migration that fails when it runs is rolled back, every migration before it stays applied, none after it is attempted, and the deploy fails naming the file. A refused migration always fails the deploy, and so does a schema that fails when migrations sit beside it, because then no migration ran.
drift apply reports what happened:
✓ sql migrations applied: app ← 0002_add_email.sql, 0003_index_email.sql (1 already applied)A slice whose runtime predates migrations runs the schema and ignores the files. The CLI recognises that answer and fails the deploy, saying none of the files was applied, rather than reporting success.
The seed runs after the schema and migrations, and only while the database holds no data yet: when it has no tables, or the first of its tables by name (the migration history aside) is empty. A database built entirely by migrations is therefore still seeded on its first deploy.
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