Skip to main content

Operators

Verse provides many query operators to help you write sophisticated queries. This section will describe the built-in operators and how to use them.

all​

The all operator is used to check if all of the elements in a sequence satisfy a given condition:

const all = await db.from.artists.all(a => a.name !== "AC/DC"); // false

any​

The any operator is used to check if a sequence contains any elements:

let any = await db.from.artists.where(a => a.name === "AC/DC").any(); // true

There is a shorthand for the common case above:

any = await db.from.artists.any(a => a.name === "AC/DC"); // true

array​

The array operator is an aggregate operator that collects all the elements in a sequence into an array:

const array = await db.from.artists.array().first(); // true

Aggregation happens on the database side, usually using a JSON array aggregation function such as json_agg in PostgreSQL.

avg​

The avg operator is an aggregate operator that calculates the average of a sequence of numbers:

let avg = await db.from.artists.select(a => a.artistId).avg();

There is a shorthand for the common case above:

avg = await db.from.artists.avg(a => a.artistId);

count​

The count operator is an aggregate operator that counts the number of elements in a sequence:

const count = await db.from.artists.count();

distinct​

The distinct operator is used to remove duplicate elements from a sequence:

const distinct = await db.from.artists
  .select(a => a.name)
  .distinct()
  .toArray();

first​

The first operator is used to get the first element of a sequence:

let first = await db.from.artists.where(a => a.name === "AC/DC").first();

There is a shorthand for the common case above:

first = await db.from.artists.first(a => a.name === "AC/DC");

groupBy​

The groupBy operator is used to group elements in a sequence by a key:

const groupBy = await db.from.albums.groupBy(a => a.artistId).toArray();

Each group is represented as a key-value pair where the key is the group key and the value is an array of the elements in the group.

You can also perform aggregation on the groups:

const albumCounts = await db.from.albums
  .groupBy(
    a => a.artistId,
    g => ({
      artist: g.key,
      count: g.count(),
    })
  )
  .toArray();

join​

The join operator is used to join two sequences based on a common key:

const joined = await db.from.artists
  .join(db.from.albums, (ar, al) => ar.artistId === al.artistId)
  .select((ar, al) => ({
    artist: ar.name,
    album: al.title,
  }))
  .toArray();

join returns a sequence of pairs where the first element is from the first sequence and the second element is from the second sequence. Subsequent operators (like the select operator above) will automatically destructure the pairs into separate parameters when the operator accepts an arrow function.

leftJoin​

The leftJoin operator is used to join two sequences based on a common key, returning all elements from the first sequence, and the matching elements from the second sequence:

const leftJoined = await db.from.artists
  .leftJoin(db.from.albums, (ar, al) => ar.artistId === al.artistId)
  .toArray();

Join returns a sequence of pairs where the first element is from the first sequence and the second element is from the second sequence, or undefined if there is no match. As with join, subsequent operators will automatically destructure the pairs into separate parameters when the operator accepts an arrow function.

limit​

The limit operator is used to limit the number of elements in a sequence:

const limit = await db.from.artists.limit(5).toArray();

Limit queries are automatically parameterized to improve performance and security.

max​

The max operator is an aggregate operator that calculates the maximum of a sequence of numbers:

let max = await db.from.artists.select(a => a.artistId).max();

There is a shorthand for the common case above:

avg = await db.from.artists.max(a => a.artistId);

maybeFirst​

The maybeFirst operator is used to get the first element of a sequence, or return undefined if the sequence is empty:

let maybeFirst = await db.from.artists
  .where(a => a.name === "Allan Holdsworth")
  .maybeFirst();

There is a shorthand for the common case above:

maybeFirst = await db.from.artists.maybeFirst(
  a => a.name === "Allan Holdsworth"
);

maybeSingle​

The maybeSingle operator is used to get the only element of a sequence, or return undefined if the sequence is empty:

let maybeSingle = await db.from.artists
  .where(a => a.name === "Allan Holdsworth")
  .maybeSingle();

This operator will throw an error if the sequence contains more than one element.

There is a shorthand for the common case above:

maybeSingle = await db.from.artists.maybeSingle(
  a => a.name === "Allan Holdsworth"
);

min​

The min operator is an aggregate operator that calculates the minimum of a sequence of numbers:

let min = await db.from.artists.select(a => a.artistId).min();

There is a shorthand for the common case above:

avg = await db.from.artists.min(a => a.artistId);

offset​

The offset operator is used to skip a number of elements in a sequence:

const offset = await db.from.artists.offset(5).toArray();

Offset queries are automatically parameterized to improve performance and security.

options​

The options operator is used to specify query options:

const options = await db.from.artists
  .options({ disabledConditions: "all" })
  .where(a => a.name === "AC/DC")
  .toArray();

It may only be used at the beginning of a query.

orderBy​

The orderBy operator is used to sort elements in a sequence:

const ordered = await db.from.artists.orderBy(a => a.name).toArray();

orderByDesc​

The orderByDesc operator is used to sort elements in a sequence in descending order:

const orderedDesc = await db.from.artists.orderByDesc(a => a.name).toArray();

select​

The select operator is used to project elements in a sequence:

const select = await db.from.artists.select(a => a.name).toArray();

Complex projections are supported:

const selectComplex = await db.from.albums
  .select(a => ({
    id: a.albumId,
    desc: `Title: ${a.title}`,
    props: [a.artistId],
  }))
  .toArray();

Verse will push down projection expressions to the database when possible (such as the template literal above). The example above will be translated to SQL as:

select "t1"."AlbumId", 'Title: ' || "t1"."Title" as "c0", "t1"."ArtistId"
from "Album" as "t1"

The rest of the required projection processing (i.e creating each result object) will be performed on the client side.

single​

The single operator is used to get the only element of a sequence:

let single = await db.from.artists.where(a => a.name === "AC/DC").single();

Single will throw if there are no elements in the sequence, or if there is more than one element.

There is a shorthand for the common case above:

single = await db.from.artists.single(a => a.name === "AC/DC");

sql​

The sql operator is used to execute a raw SQL query:

const name = "AC/DC"; // usually from user input, parameterized

const sql = await db.from.artists
  .sql`SELECT * FROM Artist WHERE Name = ${name}`.toArray();

It is a useful escape hatch for when you need to execute a query that is not supported by the Verse query API.

sum​

The sum operator is an aggregate operator that calculates the sum of a sequence of numbers:

let sum = await db.from.artists.select(a => a.artistId).sum();

There is a shorthand for the common case above:

sum = await db.from.artists.sum(a => a.artistId);

toArray​

Buffers the sequence into an array:

const toArray = await db.from.artists.toArray();

By default, sequences are lazy and will not execute until the query is iterated, or a terminal operator like toArray is called.

where​

The where operator is used to filter elements in a sequence:

const where = await db.from.artists.where(a => a.name === "AC/DC").toArray();

with​

The with operator is used to specify one or more navigation properties that should be eager loaded:

let eager = db.from.artists.limit(1).with(a => a.albums);

for await (const artist of eager) {
  console.log(artist);
}

See the Eager Loading section for more information.