Queries

Since Massive doesn't use models, data is retrieved as 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 form a consistent API for data retrieval with criteria and options. where offers total flexibility if you need to hand-write a WHERE clause for cases where criteria objects aren't sufficient (for example, testing concatenation or math on a field). search handles full-text search across multiple fields.

All query functions except count may take options objects as a final argument. Valid options are those for SELECT statements 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 queries the full table) it returns a Promise for a results array. If no results are found, the array will be empty.

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

findOne

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

db.tests.findOne({
  id: 1
}, {
  fields: ['name', 'is_active']
}).then(result => {
  // an object with the name and active status for
  // test #1
});

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.

db.tests.findOne(1, {
  fields: ['name', 'is_active']
}).then(result => {
  // an object with the name and active status for
  // test #1
});

count

count returns 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.

db.tests.count({
  is_active: true
}).then(total => {
  // the number of active tests
});

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

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

db.memos.search({
  fields: ["title", "summary", "body"],
  term: "Kauai"
}.then(memos => {
  // all memos with the token 'Kauai' in the title,
  // summary, or body
});

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 interpolated into the raw SQL string instead of bound via prepared statement parameter, you run the risk of SQL injection. Don't directly interpolate user input!

db.tests.where(
  'name ILIKE $1 OR id / 2 < $2',
  ['someth%ng', 3]
).then(tests => {
  // all tests matching some bizarre conditions invented
  // solely for the sake of an example (look into exprs
  // with the regular find functions if you actually
  // need to do math, invoke functions, etc)
});