Queries

Since Massive doesn't use models, data is returned in the form of "plain old JavaScript objects" where keys correspond to column names. With the obvious exceptions of findOne and count, most query functions return arrays where each object represents a row in the resultset even if there is only one result, whether naturally or from a LIMIT clause applied by query options.

The find, findOne, and count functions all accept criteria objects. where allows hand-writing WHERE conditions when criteria objects aren't sufficient (for example, testing concatenation or math on a field). search handles full-text search across multiple fields. See Working With Documents for the document table variants.

All query functions except count may take options objects as a final argument. Valid options are those indicated for data retrieval and general results processing. One especially useful option with query functions is stream, which, when true, will return results as a stream instead of an array. This allows you to start reading and handling data immediately, but the connection will remain open until the stream is terminated.

find

find is the workhorse of the query functions. Given criteria and options (both optional, in which case it retrieves the entire table) it yields a results array. If no results are found, the array will be empty.

// retrieve active tests 21-30
const tests = await db.tests.find({
  is_active: true
}, {
  offset: 20,
  limit: 10
});

findOne

findOne is a convenient alias for find with options.single set. An options object may still be passed. It yields an object corresponding to the first resulting record, or null if no records match the criteria.

// retrieve the name and active status for test #1
const test = await db.tests.findOne({
  id: 1
}, {
  fields: ['name', 'is_active']
});

You can use a primary key value instead of a criteria object with findOne if desired. If your table has a compound primary key you must use the criteria object.

// retrieve the name and active status for test #1
const test = await db.tests.findOne(1, {
  fields: ['name', 'is_active']
});

count

count yields the number of rows matching the criteria object. Since PostgreSQL uses 64-bit integers and JavaScript's Number type only has 53 bits of precision, the result will actually come back as a String rather than a Number.

count does not take an options object, since there are no useful options a user might set.

// count active tests
const total = await db.tests.count({
  is_active: true
});

count may also be invoked with a where-style prepared statement conditions and parameters.

search performs a full-text search for a term within the specified fields.

PostgreSQL offers multiple term parsing functions. Pass a parser in the search plan to use one of the specialized functions.

Name Description
(default) Simple single-token (and 'single-quoted phrase') parser with some logical operations.
plain Input is tokenized, and all terms must be present.
phrase Input is tokenized, and all terms must be present in order.
websearch Search engine-style input with "double-quoted phrases", -omissions, and English "or". 
// retrieve memos with the token 'Kauai' in the title,
// summary, or body
const kauaiMemos = await db.memos.search({
  fields: ["title", "summary", "body"],
  term: "Kauai",
  parser: 'phrase'
});

where

where queries a table with a raw SQL WHERE clause, followed by prepared statement parameters and, if desired, an options object. The raw SQL may use $1 style or named ${name} style (consult the pg-promise docs for other accepted delimiters).

If user input is included in the raw SQL string instead of bound via prepared statement parameter, you open yourself up to SQL injection attacks. Don't directly interpolate user input!

// retrieve tests matching some bizarre conditions
// invented solely for the sake of an example
const tests = await db.tests.where(
  'name ILIKE $1 OR id / 2 < $2',
  ['someth%ng', 3]
);