Query builder

Orm.from(schema) starts a query. The steps take the query first and give a new one, so they chain with |>. The query ends in all, one, count, update_all or delete_all. A query is a value: every step gives a new one.

StepDoes
Orm.where(query, sql, params)adds sql of your program, with ? for the params
Orm.where_eq(query, column, value)column = ?; Db.Value.Null is column is null
Orm.where_in(query, column, values)column in (?, ...); no value keeps no row
Orm.order_by(query, column), Orm.order_by_desc(query, column)order by a column, after the ones already given
Orm.limit(query, n), Orm.offset(query, n)page the rows; an offset with no limit works on every database
EndGives
Orm.all(query, conn)Result[List[T]]
Orm.one(query, conn)Result[Optional[T]], the first row or none
Orm.count(query, conn)Result[Int], the rows the conditions keep (order and page do not count)
Orm.update_all(query, conn, changes) and Orm.delete_all(query, conn)see writes
import Db from "pkg/db/db"
import Orm from "pkg/db/orm"
import Sqlite from "pkg/db/sqlite"

struct User
  id: Int = 0
  name: String
  age: Optional[Int] = none
  admin: Bool
end

def run() -> Result[Void]
  conn = try Db.open("sqlite::memory:", [Sqlite.driver()])
  try Db.execute(conn, "create table users (id integer primary key, name text not null, age integer, admin integer not null)", [])
  try Db.execute(conn, "insert into users (name, age, admin) values ('Ana', 31, 1), ('Bob', 27, 0), ('Cy', 40, 1), ('Dee', null, 1)", [])
  users = Orm.schema("users", User.codec)
  admins = try (Orm.from(users)
    |> Orm.where_eq("admin", Db.Value.Bool(true))
    |> Orm.where("age > ?", [Db.Value.Int(28)])
    |> Orm.order_by_desc("age")
    |> Orm.limit(10)
    |> Orm.all(conn))
  for user in admins
    puts(user.name)
  end

  total = try (Orm.from(users) |> Orm.where_in("name", [Db.Value.Text("Ana"), Db.Value.Text("Bob")]) |> Orm.count(conn))
  puts(total)
  Db.close(conn)
  return ok()
end

def main() -> Void
  case run()
    ok(_) -> puts("done")
    error(message) -> IO.warn(message)
  end
end
Output
Cy
Ana
2
done

A line cannot start with |> unless the chain is in parentheses, as above.

Conditions #

  • All the conditions must hold (and).
  • A column that is not in the schema, or a value of another kind than its column (age is an Int, so Db.Value.Text is wrong), is not an error of the step. The query keeps the first problem, and the call that ends it gives the error, before any sql runs. So a column name from a request is safe in where_eq, where_in and order_by.
  • The sql of where is yours, so keep it a constant of your program and pass values as params. Do not build it from a request.
  • where_eq and where_in refuse a Float that is NaN or an infinity.
  • where_in takes no Null item, since an in list never matches NULL: use where_eq(column, Db.Value.Null). A very long list may pass the limit of placeholders of the database, which is an error of the database.

Order and page #

Rows come in the order of the database unless order_by says otherwise, for all and one alike. Where NULLs sort differs between the databases (SQLite and MySQL: first when ascending; PostgreSQL: last), so do not count on it, or order by a column that is never NULL. limit and offset take a count of zero or more.

one keeps a limit smaller than 1, so a limit of 0 gives none. Orm.find is where_eq on the key plus one.