Storage

All examples use in-memory SQLite (sqlite::memory:), so they leave no files and need no server. To work with a file or another database change the connection string: sqlite://notes.db, postgres://…, mysql://… - the code stays the same. ORM operations need the db effect.

CRUD#

Task. Create, read, update and delete records.

oxl✓ checked in CI

use std.orm { Db }

@table("notes")
data Note {
  @pk @auto id: i64  // the primary key, generated by the database
  title: str
  done: bool
}

fn main() io, db {
  let db = Db.open("sqlite::memory:")!  // in memory; for a file: sqlite://notes.db
  defer db.close()
  db.insert(Note{ id: 0, title: "write docs", done: false })
  let first = db.lastInsertId()
  db.insert(Note{ id: 0, title: "ship it", done: false })
  print("{db.count(Note)} notes, first id {first}")
  match db.find(Note, first) {
    Note n => {
      db.update(Note{ id: n.id, title: n.title, done: true })
      print("done: {db.find(Note, first)!.done}")
    }
    _ => print("no such note")
  }
  db.deleteById<Note, i64>(first)!
  print("left: {db.all(Note).len()}")
  print("missing: {db.find(Note, 99) is none}")
}

Output

2 notes, first id 1
done: true
left: 1
missing: true

@table("notes") turns a data into a table; @pk is the primary key, @auto means the database generates the value (pass 0 as a placeholder).
db.insert(…), db.update(…), db.find(Note, id) (T?), db.all(Note), db.count(Note), deleteById are the main operations. The try… variants (tryInsert) return a DbError instead of a panic, for example on a key conflict.
defer db.close() - the connection is closed on any exit.
The schema is created and updated by a migration (oxl db, §12.8); for the sqlite::memory: examples the table is created automatically.

Queries#

Task. Select, sort and paginate.

oxl✓ checked in CI

use std.orm { Db }

@table("products")
data Product {
  @pk id: i64
  name: str
  price: i64
  category: str
}

fn main() io, db {
  let db = Db.open("sqlite::memory:")!
  defer db.close()
  db.insert(Product{ id: 1, name: "pen", price: 3, category: "office" })
  db.insert(Product{ id: 2, name: "desk", price: 250, category: "furniture" })
  db.insert(Product{ id: 3, name: "lamp", price: 40, category: "furniture" })
  db.insert(Product{ id: 4, name: "ink", price: 15, category: "office" })
  let cheap = from p in db.Product where p.price < 50 order by p.price desc select p.name  // the query is compiled to SQL at build time
  print("{cheap}")
  let furniture = from p in db.Product where p.category == "furniture" order by p.name select (p.name, p.price)
  for (name, price) in furniture { print("{name}: {price}") }
  let page = from p in db.Product order by p.id skip 1 take 2 select p.id
  print("{page}")
}

Output

["lamp", "ink", "pen"]
desk: 250
lamp: 40
[2, 3]

from p in db.Product where p.price < 50 order by p.price desc select p.name is a query expression. For a table it is compiled to SQL at build time: values are passed as parameters, and an expression that cannot be translated to SQL is a compile error (query-not-sql).
select (p.name, p.price) returns tuples; skip/take give pagination.
The same syntax works over in-memory lists (step 6 of the tutorial): you can debug a query on a List first and then point it at a table.
The "N+1" anti-pattern (a query inside a loop over the results of another query) is detected by the analyzer - n-plus-one; relations are loaded with load(field).

Transactions#

Task. Transfer money between accounts so that on any failure no "half" of the operation remains.

oxl✓ checked in CI

use std.orm { Db }

@table("accounts")
data Account { @pk id: i64; owner: str; cents: i64 }

fn transfer(db: &Db, from: i64, to: i64, amount: i64) -> bool  io, db {
  db.transaction(() => {  // everything inside is one transaction
    let a = db.find(Account, from)
    let b = db.find(Account, to)
    match (a, b) {
      (Account x, Account y) => {
        if x.cents < amount { return false }
        db.update(Account{ id: x.id, owner: x.owner, cents: x.cents - amount })
        db.update(Account{ id: y.id, owner: y.owner, cents: y.cents + amount })
        true
      }
      _ => false
    }
  })
}

fn main() io, db {
  let db = Db.open("sqlite::memory:")!
  defer db.close()
  db.insert(Account{ id: 1, owner: "ann", cents: 1000 })
  db.insert(Account{ id: 2, owner: "bob", cents: 0 })
  print("{transfer(&db, 1, 2, 300)} {transfer(&db, 1, 2, 5000)} {transfer(&db, 1, 9, 1)}")
  for a in db.all(Account) { print("{a.owner}: {a.cents}") }
}

Output

true false false
ann: 700
bob: 300

db.transaction(() => { … }) runs the whole body as one transaction: the changes are committed together.
An early return false inside the body is fine: the failed transfers (false) changed nothing, the accounts keep 700 and 300 after the single successful one.
For operations that touch several nodes of a cluster, transactions are replaced by sagas with compensations - the bank-saga example.

Key-value#

Task. Keep small records by key with prefix search.

oxl✓ checked in CI

use std.kv { Kv }

fn main() io, db {
  let kv = Kv.open("recipes")!
  kv.put("user:1", b"ann")!
  kv.put("user:2", b"bob")!
  kv.put("order:1", b"pen")!
  let one = kv.get("user:1")!
  print("user:1 present: {one is bytes}")
  let users = kv.scan("user:", 10)!
  print("{users.len()} users")
  kv.delete("user:2")!
  print("{kv.scan("user:", 10)!.len()} left")
}

Output

user:1 present: true
2 users
1 left

Kv.open("recipes") opens a named built-in store; values are bytes, so serialize the data yourself (toJson, encode).
scan(prefix, limit) walks the keys with a common prefix in order; putIf(key, value, version) is a conditional write for optimistic locking; watch(prefix) is a stream of changes.
The built-in store is replicated across the cluster (Raft), so the same program in a cluster gains fault tolerance without code changes.

Back: cookbook.

Storage

CRUD#

Queries#

Transactions#

Key-value#

See also