db

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

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

main.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()

Click the code to edit it

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:

main.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()

Click the code to edit it

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:

main.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()

Click the code to edit it

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:

main.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()

Click the code to edit it

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:

main.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()

Click the code to edit it

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:

main.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()

Click the code to edit it

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:

main.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: "[email protected]"}).unwrap()

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

auto duplicate = conn.insert("users", {email: "[email protected]"})
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()

Click the code to edit it

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:

main.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()

Click the code to edit it

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:

main.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()

Click the code to edit it

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:

main.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()

Click the code to edit it

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:

main.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()

Click the code to edit it

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:

main.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()

Click the code to edit it

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.

Your turn

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.

main.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()

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()

Ctrl Enter runs

Expected output

write docs
ship docs

Quick check

Why pass values as ? arguments instead of joining them into the SQL string?

Your turn

Find the OSL people

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

main.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()

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()

Ctrl Enter runs

Expected output

["Ada","Linus"]

API reference#

97 members, generated from the std:db source of OSL 9.3.0.

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.

Edit this page View as Markdown