Options

The options object modifies query behavior, either by applying additional clauses to the query itself or by changing how Massive handles results.

Options can be passed to most query and persistence functions as the final argument.

db.tests.find({
  is_active: true
}, {
  fields: ['name', 'started_at'],
  exprs: {
    lowername: 'lower(name)',
    total: 'passes + failures'
  },
  offset: 20,
  limit: 10,
  only: true,
  stream: true
}).then(stream => {
  // a stream returning the name, start date, lower-
  // cased name, and pass + failure total for active
  // tests 21-30, omitting rows from any descendant
  // tables
});

Filtering and Shaping Results

These options affect data retrieval queries. Some, such as fields and exprs, are generally applicable to all such calls; others, such as limit and pageLength, are only useful when multiple records will be returned.

The fields option may be used with persistence queries to restrict the returned results. This can be useful in the case of column security where the Postgres user does not have permissions on the full table.

Option key Description
fields Specify an array of column names to include in the resultset. The names will be quoted; use exprs to invoke functions or operate on columns.
exprs Specify a map of aliases to expressions to include in the resultset. Do not send user input directly into exprs unless you understand the risk of SQL injection!
limit Set the number of rows to take.
offset Set the number of rows to skip.
order An array of order objects.
pageLength Number of results to return with keyset pagination. Requires order.

nb. The exprs option and the corresponding expr key in order objects interpolate values into the emitted SQL. Take care with raw strings and ensure that user input is never directly passed in through the options, or you risk opening yourself up to SQL injection attacks.

Ordering Results

The order option sets an array of order objects which are used to build a SQL ORDER BY clause. An order object must contain a field or an expr; all other properties are optional.

Key Description
field The name of the column being sorted on. May be a JSON path if sorting by an element nested in a JSON field or document table body.
expr A raw SQL expression. Will not be escaped or quoted and is potentially vulnerable to SQL injection.
direction The sort direction, ASC (default) or DESC.
nulls Sort null field values first or last.
type Define a cast type for values. Useful with JSON fields.
last If using keyset pagination, this attribute's value from the final record on the previous page. 
db.tests.find({
  is_active: true
}, {
  order: [{
    field: 'started_at',
    direction: 'desc',
    nulls: 'first'
  }, {
    expr: 'passes + failures',
    direction: 'asc'
  }]
}).then(stream => {
  // all tests, ordered first by most recent start
  // date (nulls first), then by pass + failure
  // total deferring to the Postgres default null
  // positioning (last when ascending)
});

Keyset Pagination

When query results are meant to be displayed to a user, it's often useful to retrieve and display them one page at a time. This is easily accomplished by setting limit to the page length and offset to the page length times the current page (counting from zero). However, as result sets grow larger, this method starts to perform poorly as the first n rows must be retrieved and discarded each time.

Keyset pagination offers a trade: consistent performance, but you don't know how many pages there are and can't reliably sort by columns containing null values. It does require a slightly different user interface metaphor which avoids numbering and jumping to arbitrary pages, but the performance gains can be worth it. For a detailed technical breakdown, see Markus Winand's post on the topic.

Although enabling keyset pagination is a matter of a single field, it does require some setup:

Once these prerequisites are satisfied, set the pageLength option to the number of records you want back per page.

To retrieve subsequent pages, inspect the last record on the current page. When you make the query for the next page, add a last key to each element of the order array containing that attribute's value for the final record.

db.issues.find(
  {},
  {
    order: [{
      field: 'test_id',
      last: 1500
    }, {
      field: 'issue_id',
      last: 10256
    }],
    pageLength: 25
  }
).then(next25Results => ...);

Persisting Data

These options modify data persistence calls.

Option key Use in Description
onConflictIgnore insert If the inserted data would violate a unique constraint, do nothing.
deepInsert insert Specify true to turn on deep insert.
body updateDoc Specify in order to override the default body field affected by updateDoc.

Table Inheritance

By default, queries against tables having descendant tables affect and/or return records from those descendant tables. Use the only option to prevent this, but it's superfluous if the target has no descendant tables.

Option key Use in Description
only any Set to true to restrict the query to the table specified, if any others inherit from it.

Results Processing

Results processing options are generally applicable to all query types, although stream is principally useful with query functions.

Option key Description
build Set to true to return the query text and parameters without executing anything.
document Set to true to invoke document table handling.
single Set to true to return the first result as an object instead of a results array.
stream Set to true to return results as a stream instead of an array. Streamed results cannot be decomposed.
decompose Provide a schema to transform the results into an object graph. Not compatible with stream.

Tasks and Transactions

db.withConnection may take a single option:

Option key Description
tag A tag which will be visible in pg-monitor.

db.withTransaction as well:

Option key Description
mode A TransactionMode object defining a new isolation level, readonly mode, and/or deferrable mode.