drift Docs
Pages
drift Docs
Start
What is Drift?
The tour, if you are new here.
Use cases
Whether Drift does your thing.
Getting started
Nothing to deployed, in one command.
Architecture
How a slice is put together.
What it costs
The free grant, four unit prices, two rules.
Moving off Azure
Estimate, snapshot and transform a resource group.
Build
Canvas
Static sites, same origin as your API.
Tools
Operate
Auth
Route gates, API keys and your account.
Teams
More than one person on one account.
Security
Boundaries, sandboxing and hardening.
Troubleshooting
Error codes
What went wrong, and what to do about it.
Legal
Acceptable use
What a slice may not be used for.
Data processing
The DPA, and every sub-processor.

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.

Driftfile
slice "shop" {}

backbone {
  sql "app" {
    size   = "128MiB"
    schema = "backbone/app.sql"
  }
}
Go
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.

Go
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:

Driftfile
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.sql

Each 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, so 10_ runs after 9_, and gaps are fine. Only .sql files 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 BEGIN or COMMIT. 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

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