# db

> Store data in SQLite with SQL queries and transactions, or as JSON documents in collections you search by example.

This is the Markdown version of https://osl-lang.org/packages/db. Every page of the OSL docs has one: add .md to its path. https://osl-lang.org/llms.txt lists them all.

```osl
import "std:db"
```

`std:db` is a database in a file. It is built on SQLite, so there is no server to install or run: `db.open("app.db")` creates the file if it needs to and you start storing. Use it when data has outgrown a settings file, when you need to search, sort or update part of it, or when several changes must happen together or not at all. For a handful of named strings, [save](/packages/save.md) is lighter.

A database offers two ways to work, and you can mix them in one file:

- **Tables and SQL** for data with a fixed shape: users, orders, scores.
- **Collections of documents** for data that varies: each document is an object, and you find them by example, with no schema.

SQLite is the only engine in this package, so every example on this page runs as written. Each one deletes its database file first, because the examples run more than once.

## Open a database

`db.open(path)` returns a result holding the connection. Use `":memory:"` for a database that lives only as long as the program, which suits tests:

```osl
import "std:db"
import "std:fs"

fs.remove("first.db", {force: true}).unwrap()

auto conn = db.open("first.db").unwrap()
log conn.tables().unwrap()
conn.close().unwrap()
log fs.exists("first.db")

auto scratch = db.open(":memory:").unwrap()
log scratch.tables().unwrap()
scratch.close().unwrap()

fs.remove("first.db", {force: true}).unwrap()
```

Output:

```text
[]
true
[]
```

Close a connection when you are done with it. A closed connection throws a `StateError` if you use it.

## Create a table and insert rows

`exec` runs one SQL statement and returns a result. Use `?` placeholders for values and pass them after the SQL, so the database never mistakes data for code. `run` is like `exec` but tells you what happened, and `insert` builds the statement from an object:

```osl
import "std:db"

auto conn = db.open(":memory:").unwrap()
conn.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER)").unwrap()

auto first = conn.run("INSERT INTO users (name, age) VALUES (?, ?)", "Ada", 36).unwrap()
log first.lastInsertID first.rowsAffected

int id = conn.insert("users", {name: "Grace", age: 45}).unwrap()
log id

log conn.tables().unwrap()
log conn.columns("users").unwrap()
conn.close().unwrap()
```

Output:

```text
1 1
2
["users"]
["id","name","age"]
```

`insert` returns the new row's id. `createTable(name, columns)` builds a table from an object of column definitions when you don't want to write the SQL:

```osl
import "std:db"

auto conn = db.open(":memory:").unwrap()
conn.createTable("notes", {id: "INTEGER PRIMARY KEY", body: "TEXT"}).unwrap()
log conn.columns("notes").unwrap().sort()
conn.close().unwrap()
```

Output:

```text
["body","id"]
```

> **Warning**
>
> Never build SQL by joining user input into the string. `"... WHERE name = '" ++ name ++ "'"` lets a name such as `x' OR '1'='1` change the query. Pass values as `?` arguments every time.

## Reading rows

`queryMap` returns the rows as objects keyed by column name, which is the easiest to use. `queryMapOne` returns the first row or `null`:

```osl
import "std:db"

auto conn = db.open(":memory:").unwrap()
conn.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)").unwrap()
conn.insert("users", {name: "Ada", age: 36}).unwrap()
conn.insert("users", {name: "Grace", age: 45}).unwrap()
conn.insert("users", {name: "Linus", age: 28}).unwrap()

for user in conn.queryMap("SELECT name, age FROM users WHERE age > ? ORDER BY age", 30).unwrap() (
  log user["name"] user["age"]
)

log conn.queryMapOne("SELECT name FROM users WHERE id = ?", 3).unwrap()
log conn.queryMapOne("SELECT name FROM users WHERE id = ?", 99).unwrap()
conn.close().unwrap()
```

Output:

```text
Ada 36
Grace 45
{"name":"Linus"}
null
```

`query` and `queryOne` return `db.Row` values instead. A row reads a column by name or by position (from 1), and keeps the column order:

```osl
import "std:db"

auto conn = db.open(":memory:").unwrap()
conn.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)").unwrap()
conn.insert("users", {name: "Ada", age: 36}).unwrap()

auto row = conn.queryOne("SELECT name, age FROM users").unwrap()
log row.get("name") row.get(2)
log row.columns() row.count()
log row.toMap()
conn.close().unwrap()
```

Output:

```text
Ada 36
["name","age"] 2
{"age":36,"name":"Ada"}
```

## Update, delete and count

`update`, `delete`, `count` and `exists` take a table, then a SQL condition with `?` placeholders and its values. They return the number of rows changed or counted:

```osl
import "std:db"

auto conn = db.open(":memory:").unwrap()
conn.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)").unwrap()
conn.insert("users", {name: "Ada", age: 36}).unwrap()
conn.insert("users", {name: "Grace", age: 45}).unwrap()

log conn.update("users", {age: 37}, "name = ?", "Ada").unwrap()
log conn.count("users", "age > ?", 40).unwrap()
log conn.exists("users", "name = ?", "Nobody").unwrap()
log conn.delete("users", "age > ?", 40).unwrap()
log conn.count("users", "1 = 1").unwrap()
conn.close().unwrap()
```

Output:

```text
1
1
false
1
1
```

## Errors

Every database call that can fail returns a result, so a mistake is a value you can look at. A syntax error is a `ParseError`, and a broken rule such as a duplicate in a `UNIQUE` column or a `NULL` in a `NOT NULL` one is a `ValueError`:

```osl
import "std:db"

auto conn = db.open(":memory:").unwrap()
conn.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE NOT NULL)").unwrap()
conn.insert("users", {email: "ada@example.com"}).unwrap()

auto syntax = conn.exec("SELEKT 1")
log syntax.unwrapErr().kind

auto duplicate = conn.insert("users", {email: "ada@example.com"})
log duplicate.unwrapErr().kind
log duplicate.unwrapErr().message

switch conn.exec("INSERT INTO users (email) VALUES (NULL)") (
  case ok(_)
    log "inserted"
  case err(e)
    log "rejected:" e.kind
)
conn.close().unwrap()
```

Output:

```text
ParseError
ValueError
db.DB.insert: UNIQUE constraint failed: users.email
rejected: ValueError
```

Let the database enforce the rules. A `UNIQUE` constraint can't be raced past, unlike "check first, then insert" in your code.

## Transactions

A transaction makes several changes happen together or not at all. `conn.transaction(fn)` runs `fn` with the connection and commits when it returns. If `fn` returns a failed result, everything it did is rolled back and that failure is the result of the call:

```osl
import "std:db"
import "std:result"

auto conn = db.open(":memory:").unwrap()
conn.exec("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER)").unwrap()
conn.insert("accounts", {name: "ada", balance: 100}).unwrap()
conn.insert("accounts", {name: "bob", balance: 20}).unwrap()

def transfer(*db.DB conn, string source, string dest, int amount) result<unit, error> (
  return conn.transaction(def(*db.DB tx) result<int, error> (
    tx.exec("UPDATE accounts SET balance = balance - ? WHERE name = ?", amount, source)!
    auto row = tx.queryMapOne("SELECT balance FROM accounts WHERE name = ?", source).unwrap()
    int left = row["balance"]
    if left < 0 (
      return result.err(error("insufficient funds"))
    )
    tx.exec("UPDATE accounts SET balance = balance + ? WHERE name = ?", amount, dest)!
    return result.ok(1)
  ))
)

log transfer(conn, "ada", "bob", 30).isOk()
log transfer(conn, "bob", "ada", 500).unwrapErr().message
for account in conn.queryMap("SELECT * FROM accounts ORDER BY name").unwrap() (
  log account["name"] account["balance"]
)
conn.close().unwrap()
```

Output:

```text
true
insufficient funds
ada 70
bob 50
```

The second transfer took 500 from `bob` before it noticed there wasn't enough, and the rollback put it back: balances are unchanged by it. When `fn` throws instead of returning an error, the transaction is rolled back and the throw continues. `begin()`, `commit()` and `rollback()` do the same by hand, for the cases where one function doesn't hold the whole job.

## Documents

A collection stores objects with no table to define first. `conn.collection(name)` returns it, creating it on first use. Each document gets an `_id` string. `insertOne` returns it, and `insertMany` returns all of them:

```osl
import "std:db"

auto conn = db.open(":memory:").unwrap()
auto people = conn.collection("people").unwrap()

string id = people.insertOne({name: "Ada", age: 36, tags: ["math"]}).unwrap()
log id.len > 0
people.insertMany([
  {name: "Grace", age: 45, tags: ["navy", "cobol"]},
  {name: "Linus", age: 28}
]).unwrap()

log people.all().unwrap().len
log people.findByID(id).unwrap()["name"]
log people.findOne({name: "Grace"}).unwrap()["tags"]
log people.findOne({name: "Nobody"}).unwrap()
log conn.collections().unwrap()
conn.close().unwrap()
```

Output:

```text
true
3
Ada
["navy","cobol"]
null
["people"]
```

## Finding by example

`find(filter)` takes an object that describes what you want. A plain value means equals, and an object of operators compares: `$gt`, `$gte`, `$lt`, `$lte`, `$ne`, `$in`, `$nin`, `$exists`, `$regex` and `$contains`. Options sort and page the answer:

```osl
import "std:db"

auto conn = db.open(":memory:").unwrap()
auto people = conn.collection("people").unwrap()
people.insertMany([
  {name: "Ada", age: 36, address: {city: "London"}},
  {name: "Grace", age: 45, address: {city: "New York"}},
  {name: "Linus", age: 28, address: {city: "Helsinki"}}
]).unwrap()

auto older = people.find({age: {"$gte": 30}}, {sort: {age: "desc"}}).unwrap()
log older.map(p -> p["name"])

log people.find({}, {sort: {name: "asc"}, skip: 1, limit: 1}).unwrap().map(p -> p["name"])
log people.count({age: {"$lt": 40}}).unwrap()
log people.exists({name: "Linus"}).unwrap()
log people.find({"address.city": "London"}).unwrap().map(p -> p["name"])
conn.close().unwrap()
```

Output:

```text
["Grace","Ada"]
["Grace"]
2
true
["Ada"]
```

A dot path such as `"address.city"` reaches into a nested object.

## Building queries

For anything longer than a filter, start a query with `where`, `sort`, `fields` or `query()` and chain more. Conditions combine with AND:

```osl
import "std:db"

auto conn = db.open(":memory:").unwrap()
auto people = conn.collection("people").unwrap()
people.insertMany([
  {name: "Ada", age: 36, tags: ["math"]},
  {name: "Grace", age: 45, tags: ["navy", "cobol"]},
  {name: "Linus", age: 28, tags: []}
]).unwrap()

auto names = people.where("age", ">", 30).sort("age", "asc").all().unwrap()
log names.map(p -> p["name"])

log people.where("tags", "contains", "cobol").count().unwrap()
log people.where("age", "<", 40).where("name", "!=", "Ada").first().unwrap()["name"]
log people.sort("name", "asc").limit(2).fields("name").all().unwrap().map(p -> p["name"])
conn.close().unwrap()
```

Output:

```text
["Ada","Grace"]
1
Linus
["Ada","Grace"]
```

The operators here are written as strings: `"=="`, `"!="`, `">"`, `">="`, `"<"`, `"<="`, `"in"`, `"notIn"`, `"contains"`, `"matches"` and `"exists"`.

## Changing documents

`updateOne` and `updateMany` set the fields you give on the documents a filter matches, and a dot path changes one nested field. A query can also change documents with `set`, `inc`, `push` and friends followed by `apply()`. `save` writes a whole document back under its `_id`:

```osl
import "std:db"

auto conn = db.open(":memory:").unwrap()
auto people = conn.collection("people").unwrap()
people.insertMany([
  {name: "Ada", age: 36, address: {city: "London"}, tags: []},
  {name: "Linus", age: 28, tags: []}
]).unwrap()

people.updateOne({name: "Ada"}, {"address.city": "Paris"}).unwrap()
log people.findOne({name: "Ada"}).unwrap()["address"]

people.where("name", "==", "Linus").inc("age", 1).push("tags", "kernel").apply().unwrap()
auto linus = people.findOne({name: "Linus"}).unwrap()
log linus["age"] linus["tags"]

linus["age"] = 50
people.save(linus).unwrap()
log people.findOne({name: "Linus"}).unwrap()["age"]

log people.deleteMany({age: {"$gt": 40}}).unwrap()
log people.count({}).unwrap()
conn.close().unwrap()
```

Output:

```text
{"city":"Paris"}
29 ["kernel"]
50
1
1
```

A filter with an operator the collection doesn't know, such as `$bogus`, is a programming error and throws a `ValueError`, instead of coming back as a failed result.

## Tables or collections

Use tables when every row has the same columns, when you join data across tables, or when you want the database to enforce types and uniqueness. Use collections when records vary or the data is a nested document. Both live in the same file and can share a transaction.

### Exercise: A to-do list

Make a `todos` table with an `id`, a `title` and a `done` column (0 or 1, default 0). Insert three to-dos, mark one done, and print the titles that are still open, in the order they were added.

Starting code:

```osl
import "std:db"

auto conn = db.open(":memory:").unwrap()

// create the table, add "write docs", "test docs" and "ship docs",
// finish "test docs", then print the open ones

conn.close().unwrap()
```

It should print:

```text
write docs
ship docs
```

Hint 1: `CREATE TABLE todos (id INTEGER PRIMARY KEY, title TEXT, done INTEGER DEFAULT 0)`, then `conn.insert("todos", {title: "write docs"})` for each.

Hint 2: `conn.update("todos", {done: 1}, "title = ?", "test docs")`, and `conn.queryMap("SELECT title FROM todos WHERE done = 0 ORDER BY id")` for the open ones.

A solution:

```osl
import "std:db"

auto conn = db.open(":memory:").unwrap()

conn.exec(
  "CREATE TABLE todos (id INTEGER PRIMARY KEY, title TEXT, done INTEGER DEFAULT 0)"
).unwrap()
for title in ["write docs", "test docs", "ship docs"] (
  conn.insert("todos", {title: title}).unwrap()
)
conn.update("todos", {done: 1}, "title = ?", "test docs").unwrap()

for todo in conn.queryMap("SELECT title FROM todos WHERE done = 0 ORDER BY id").unwrap() (
  log todo["title"]
)

conn.close().unwrap()
```

**Quiz.** Why pass values as `?` arguments instead of joining them into the SQL string?

- [ ] It is shorter.
- [x] The database treats them as data, so input like `x' OR '1'='1` can't change the query.
- [ ] Joined strings don't work with SQLite.

Answer: This is how SQL injection is prevented.

### Exercise: Find the OSL people

Store three documents in a `people` collection. Print the names, sorted, of those whose `tags` contain `"osl"`.

Starting code:

```osl
import "std:db"

auto conn = db.open(":memory:").unwrap()
auto people = conn.collection("people").unwrap()
people.insertMany([
  {name: "Ada", tags: ["osl", "math"]},
  {name: "Grace", tags: ["cobol"]},
  {name: "Linus", tags: ["osl", "kernel"]}
]).unwrap()

log "?"
conn.close().unwrap()
```

It should print:

```text
["Ada","Linus"]
```

Hint 1: `people.where("tags", "contains", "osl")` starts a query.

Hint 2: `.all().unwrap().map(p -> p["name"]).sort()`.

A solution:

```osl
import "std:db"

auto conn = db.open(":memory:").unwrap()
auto people = conn.collection("people").unwrap()
people.insertMany([
  {name: "Ada", tags: ["osl", "math"]},
  {name: "Grace", tags: ["cobol"]},
  {name: "Linus", tags: ["osl", "kernel"]}
]).unwrap()

log people.where("tags", "contains", "osl").all().unwrap().map(p -> p["name"]).sort()
conn.close().unwrap()
```

## API reference

## API reference

Every member of `std:db`, generated from the source of OSL.

### Functions

#### `db.open(string path) result<*db.DB, error>`

open opens the SQLite database file at path, creating it when it doesn't
exist; ":memory:" opens a private in-memory database. Fails with
NotFoundError when path's directory doesn't exist, ParseError when the
file isn't an SQLite database, and PermissionError or IOError when it
can't be opened. Close the handle with db.close() when done.

#### `db.openMemory() *db.DB`

**Deprecated.** Deprecated: use db.open(":memory:") instead.

openMemory opens a private in-memory database, or returns null on
failure.

### Constants

- `string db.asc`: Asc is the "asc" sort direction, for query.sort(field, db.asc).

- `string db.contains`: Contains is the $contains query operator, for query.where(field, db.contains, value).

- `string db.desc`: Desc is the "desc" sort direction, for query.sort(field, db.desc).

- `string db.eq`: Eq is the $eq query operator, for query.where(field, db.eq, value).

- `string db.equal`: Equal is the $eq query operator, for query.where(field, db.equal, value).

- `string db.greater`: Greater is the $gt query operator, for query.where(field, db.greater, value).

- `string db.greaterEqual`: GreaterEqual is the $gte query operator, for query.where(field, db.greaterEqual, value).

- `string db.gt`: Gt is the $gt query operator, for query.where(field, db.gt, value).

- `string db.gte`: Gte is the $gte query operator, for query.where(field, db.gte, value).

- `string db.in`: In is the $in query operator, for query.where(field, db.in, value).

- `string db.less`: Less is the $lt query operator, for query.where(field, db.less, value).

- `string db.lessEqual`: LessEqual is the $lte query operator, for query.where(field, db.lessEqual, value).

- `string db.lt`: Lt is the $lt query operator, for query.where(field, db.lt, value).

- `string db.lte`: Lte is the $lte query operator, for query.where(field, db.lte, value).

- `string db.matches`: Matches is the $regex query operator, for query.where(field, db.matches, value).

- `string db.ne`: Ne is the $ne query operator, for query.where(field, db.ne, value).

- `string db.nin`: Nin is the $nin query operator, for query.where(field, db.nin, value).

- `string db.notEqual`: NotEqual is the $ne query operator, for query.where(field, db.notEqual, value).

- `string db.notOneOf`: NotOneOf is the $nin query operator, for query.where(field, db.notOneOf, value).

- `string db.oneOf`: OneOf is the $in query operator, for query.where(field, db.oneOf, value).

- `string db.present`: Present is the $exists query operator, for query.where(field, db.present, value).

### `*db.DB`

#### `dB.begin() result<unit, error>`

begin starts a transaction: every statement on this database runs in it
until commit or rollback. Throws StateError when a transaction is already
open or the database is closed; fails like db.exec.

#### `dB.close() result<unit, error>`

close closes the database, rolling back a transaction that is still
open. Closing twice does nothing; any other method on a closed database
throws StateError.

#### `dB.collection(string name) result<*db.Collection, error>`

collection returns the document collection name, creating its table
(coll_<name>) when it doesn't exist. Throws ValueError unless name is
letters, digits, _ and -; fails like db.exec.

#### `dB.collections() result<string[], error>`

collections returns the names of the database's collections in
alphabetical order. Fails like db.exec.

#### `dB.columns(string table) result<string[], error>`

columns returns the column names of table in table order, or an empty
array when there is no such table. Throws ValueError for an invalid name;
fails like db.exec.

#### `dB.commit() result<unit, error>`

commit saves the open transaction's changes. Throws StateError when no
transaction is open; fails like db.exec, for example with ValueError for
a deferred constraint, and the transaction is then rolled back.

#### `dB.count(string table, string where, any ...whereArgs) result<int, error>`

count returns how many rows of table match the SQL condition where (with
? placeholders bound to whereArgs); an empty where counts every row.
Throws ValueError for an invalid table name; fails like db.exec.

#### `dB.createTable(string table, object columns) result<unit, error>`

createTable creates table, unless it already exists, with one column per
key of columns, whose value is the column's SQL type and constraints
(such as "INTEGER PRIMARY KEY"). Columns are created in alphabetical
order; use db.exec with CREATE TABLE to choose the order. Throws
ValueError for an invalid name or no columns and TypeError for a type
that isn't a string; fails like db.exec.

#### `dB.delete(string table, string where, any ...whereArgs) result<int, error>`

delete removes every row of table matching the SQL condition where (with
? placeholders bound to whereArgs) and returns how many it removed.
Throws ValueError for an invalid table name or an empty where; pass "1" to
delete every row. Fails like db.exec.

#### `dB.dropTable(string table) result<unit, error>`

dropTable deletes table and its rows; a table that doesn't exist is not
an error. Throws ValueError for an invalid name; fails like db.exec.

#### `dB.exec(string query, any ...args) result<unit, error>`

exec runs one SQL statement, binding args to its ? placeholders. Use
db.run when you need the number of changed rows or the inserted rowid.
Used as a boolean (as in `if d.exec(...)`) it is true when the statement
succeeded. Fails with ParseError for invalid SQL, NotFoundError for a
missing table or column, ValueError for a constraint violation or the
wrong number of args, TypeError for an arg SQLite can't store, and
TimeoutError when the database is locked.

#### `dB.exists(string table, string where, any ...whereArgs) result<boolean, error>`

exists reports whether any row of table matches the SQL condition where,
like count. Fails like db.exec.

#### `dB.getColumns(any table) array`

**Deprecated.** Deprecated: use db.columns(table) instead.

getColumns returns the column names of table, or [] on failure.

#### `dB.getTables() array`

**Deprecated.** Deprecated: use db.tables() instead.

getTables returns the table names, or [] on failure.

#### `dB.insert(string table, object data) result<int, error>`

insert adds a row to table with the columns and values of data and
returns its rowid. Throws ValueError for an invalid table or column name
or empty data; fails like db.exec.

#### `dB.lastInsertID() result<int, error>`

lastInsertID returns the rowid of the most recent successful INSERT on
this database, or 0. Prefer the lastInsertID of db.exec or the result of
db.insert, which belong to one statement. Fails like db.exec.

#### `dB.query(string query, any ...args) result<*db.Row[], error>`

query runs an SQL query, binding args to its ? placeholders, and returns
its rows as *db.Row handles (use db.queryMap for objects). Fails like
db.exec.

#### `dB.queryMap(string query, any ...args) result<object[], error>`

queryMap runs an SQL query and returns its rows as objects keyed by
column name. Fails like db.exec.

#### `dB.queryMapOne(string query, any ...args) result<object?, error>`

queryMapOne runs an SQL query and returns its first row as an object
keyed by column name, or null when it returns no rows. Fails like
db.exec.

#### `dB.queryOne(string query, any ...args) result<*db.Row?, error>`

queryOne runs an SQL query and returns its first row as a *db.Row, or
null when it returns no rows. Fails like db.exec.

#### `dB.rollback() result<unit, error>`

rollback discards the open transaction's changes. Throws StateError when
no transaction is open.

#### `dB.rowsAffected(any query, any ...args) int`

**Deprecated.** Deprecated: use db.run(query, args...).rowsAffected instead.

rowsAffected runs a statement and returns how many rows it changed, or 0
on failure.

#### `dB.run(string query, any ...args) result<{lastInsertID: int, rowsAffected: int}, error>`

run runs one SQL statement like exec, binding args to its ? placeholders,
and returns a record with the keys rowsAffected and lastInsertID (the
rowid of the last inserted row, or 0). Fails like db.exec.

#### `dB.tables() result<string[], error>`

tables returns the names of the database's tables in alphabetical order,
including the coll_ tables behind collections. Fails like db.exec.

#### `dB.transaction(any fn) result<unit, error>`

transaction runs fn(db) inside a transaction and commits it when fn
returns. When fn throws, the transaction is rolled back and the error
propagates; when fn returns an error or a failed result, the transaction
is rolled back and that error is this call's result. Throws StateError
when a transaction is already open or fn commits or rolls back itself;
fails like db.exec when the commit fails.

#### `dB.update(string table, object data, string where, any ...whereArgs) result<int, error>`

update sets the columns in data on every row of table matching the SQL
condition where (with ? placeholders bound to whereArgs) and returns how
many rows changed. Throws ValueError for an invalid name, empty data or an
empty where; pass "1" to update every row. Fails like db.exec.

### `*db.Row`

#### `row.columns() string[]`

columns returns the row's column names in order.

#### `row.count() int`

count returns the number of columns in the row.

#### `row.get(any column) any`

get returns a column's value: column is a 1-based position or a column
name (matched ignoring case). Returns null when there is no such column.
Throws TypeError when column is neither a number nor a string.

#### `row.getByName(any colName) any`

**Deprecated.** Deprecated: use row.get(name) instead.

getByName returns the value of the column named name, ignoring case.

#### `row.isEmpty() boolean`

isEmpty reports whether the row has no columns.

#### `row.toArray() array`

toArray returns the row's values in column order.

#### `row.toMap() object`

toMap returns the row as an object keyed by column name. When two columns
share a name the later one wins.

### `*db.Collection`

#### `collection.all() result<object[], error>`

all returns every document in the collection. Fails like db.exec.

#### `collection.count(object filter) result<int, error>`

count returns how many documents match filter. Fails like find.

#### `collection.deleteMany(object filter) result<int, error>`

deleteMany removes every document matching filter and returns how many
it removed. Fails like find.

#### `collection.deleteOne(object filter) result<int, error>`

deleteOne removes the first document matching filter and returns how
many it removed, 0 or 1. Fails like find.

#### `collection.drop() result<unit, error>`

drop deletes the collection's table and documents. Fails like db.exec.

#### `collection.exists(object filter) result<boolean, error>`

exists reports whether any document matches filter. Fails like find.

#### `collection.fields(any ...names) *db.Query`

fields starts a query that returns only the named fields; see
db.Query.fields.

#### `collection.find(object filter, object? options) result<object[], error>`

find returns the documents matching filter, an object such as
{age: {$gte: 18}, role: "admin"} (see db.Collection.where for the
operators). Options: sort (an object of field to "asc" or "desc", applied
in key order), skip and limit. Throws ValueError for an unknown operator
or a negative skip or limit; fails with ParseError for an invalid $regex
and otherwise like db.exec.

#### `collection.findByID(string id) result<object?, error>`

findByID returns the document whose _id is id, or null when there is
none. Fails like db.exec.

#### `collection.findOne(object filter) result<object?, error>`

findOne returns the first document matching filter, or null when none
does. Fails like find.

#### `collection.insertMany(array docs) result<string[], error>`

insertMany stores each object in docs like insertOne and returns their
_ids in order. Throws TypeError when an item isn't an object, before
storing anything; when an insert fails, the documents before it stay
stored.

#### `collection.insertOne(object doc) result<string, error>`

insertOne stores doc as a new document and returns its _id. doc's own _id
is used when it has one; otherwise a random id is generated and written
into doc. Fails with ValueError when a document with that _id exists, and
otherwise like db.exec.

#### `collection.query() *db.Query`

query starts an empty query over the whole collection.

#### `collection.replaceOne(object filter, object doc) result<int, error>`

replaceOne replaces the first document matching filter with doc, keeping
its _id, and returns how many documents changed, 0 or 1. Fails like find.

#### `collection.save(object doc) result<string, error>`

save writes doc under its _id, replacing the stored document with that
id or inserting doc when there is none (generating an _id and writing it
into doc when it has none), and returns the _id. Fails like db.exec.

#### `collection.sort(string field, string direction) *db.Query`

sort starts a query ordered by field; see db.Query.sort.

#### `collection.updateMany(object filter, object changes) result<int, error>`

updateMany sets each field of changes on every document matching filter
and returns how many changed. Fails like find.

#### `collection.updateOne(object filter, object changes) result<int, error>`

updateOne sets each field of changes (dot paths such as "address.city"
reach into nested objects) on the first document matching filter and
returns how many documents changed, 0 or 1. Fails like find.

#### `collection.where(string field, string op, any value) *db.Query`

where starts a query with one condition; see db.Query.where.

### `*db.Query`

#### `query.all() result<object[], error>`

all runs the query and returns the matching documents. Fails with
ParseError for an invalid "matches" pattern and otherwise like db.exec.

#### `query.and(string field, string op, any value) *db.Query`

**Deprecated.** Deprecated: use query.where(field, op, value) instead.

and adds a condition like where.

#### `query.apply() result<int, error>`

apply runs the queued updates on every document matching the query's
conditions and returns how many documents it updated; with no queued
updates it changes nothing and returns 0. Fails like all.

#### `query.count() result<int, error>`

count returns how many documents match the query's conditions, ignoring
skip and limit. Fails like all.

#### `query.delete() result<int, error>`

delete removes every document matching the query's conditions (ignoring
sort, skip and limit) and returns how many it removed. Fails like all.

#### `query.exists() result<boolean, error>`

exists reports whether any document matches the query's conditions.
Fails like all.

#### `query.fields(any ...names) *db.Query`

fields makes this query return only the named fields (dot paths allowed)
plus _id, and returns the query. Throws TypeError for a name that isn't
a string.

#### `query.first() result<object?, error>`

first runs the query and returns the first matching document, or null
when none matches. Fails like all.

#### `query.get() result<object[], error>`

**Deprecated.** Deprecated: use query.all() instead.

get runs the query like all.

#### `query.inc(string field, number amount) *db.Query`

inc queues adding amount to the number in field (missing counts as 0)
for apply and returns the query.

#### `query.limit(int n) *db.Query`

limit makes this query return at most n documents and returns the query.
Throws ValueError for a negative n.

#### `query.max(string field, any value) *db.Query`

max queues raising field to value when value is larger (or field is
missing) for apply and returns the query.

#### `query.min(string field, any value) *db.Query`

min queues lowering field to value when value is smaller (or field is
missing) for apply and returns the query.

#### `query.mul(string field, number factor) *db.Query`

mul queues multiplying the number in field by factor for apply and
returns the query.

#### `query.pull(string field, any value) *db.Query`

pull queues removing every element equal to value from the array in
field for apply and returns the query.

#### `query.push(string field, any value) *db.Query`

push queues appending value to the array in field (creating it) for
apply and returns the query.

#### `query.rename(string field, string newField) *db.Query`

rename queues moving field to newField for apply and returns the query.

#### `query.set(string field, any value) *db.Query`

set queues setting field to value for apply and returns the query.

#### `query.skip(int n) *db.Query`

skip makes this query leave out its first n documents and returns the
query. Throws ValueError for a negative n.

#### `query.sort(string field, string direction) *db.Query`

sort orders this query's results by field, "asc" or "desc", after any
earlier sort fields, and returns the query. Values of different types
order null, booleans, numbers, strings, objects, arrays. Throws
ValueError for another direction.

#### `query.unset(string field) *db.Query`

unset queues removing field for apply and returns the query.

#### `query.where(string field, string op, any value) *db.Query`

where adds the condition "field op value" to this query and returns the
query, so calls chain; conditions combine with AND. field may be a dot
path such as "address.city". op is one of "==", "!=", ">", ">=", "<",
"<=", "in" and "notIn" (value is an array), "contains" (a substring of a
string or a member of an array), "matches" (value is a regular
expression) and "exists" (value is true or false); the filter forms
"$eq", "$gt" and so on work too. Throws ValueError for any other op.