MassiveJS

npm i massive@next

Massive v6 is in prerelease right now. New features and changes are temporarily documented here to avoid confusion with v5 functionality. Please read at least the "Backwards Compatibility Breaks" section before upgrading.

Feedback is welcome! You can file an issue here to talk about the changes, report problems, or make suggestions. I'll be continuing to test, refine, and document it over the next weeks and hope to have v6 proper released around the end of August.

• Backwards Compatibility Breaks

  • Node Versions
  • Decomposition Behavior
  • Implicit Ordering
• Ordering by JSON fields
• The Type Option

• Readable.join()

  • Join Definitions
  • Persistence
  • Other Caveats

Backwards Compatibility Breaks

Node Versions

Pre-ES2017 versions of Node (< 7.6) are no longer supported.

Decomposition Behavior

Resultset decomposition should have defaulted to yielding arrays all along: the natural habitat of a record is in a set of other records. Thanks to the opportunity afforded by a major version bump, I'm correcting this oversight.

Resultset decomposition now yields arrays by default, and the array option has therefore been removed. You will need to remove array: true where it occurs in your decomposition schemas when upgrading.

To yield a flat object (as the former default) instead, set decomposeTo: 'object'.

Implicit Ordering

In previous versions, Massive would automatically order results by the primary key of the relation being queried, or in other circumstances the first column (using positional arguments). This is no longer done; results are returned in the order Postgres reads them, unless options.order has been explicitly specified.

You may find you have been depending on this implicit ordering behavior, especially in tests.

Ordering by JSON fields

JSON and JSONB properties are now sorted as their original type, instead of being converted to TEXT before sorting.

The Type Option

Improvements to the parser have rendered the type field in the order option superfluous. Use Postgres-style casting in the field instead:

{
  order: [{field: 'myfield::int', direction: 'desc'}]
}

type continues to function for now, but can be considered deprecated and subject to removal in a future major release.

Readable.join()

Instances of Readable -- i.e. tables and views -- now have a join method which builds a "compound" entity from a definition document (very similar to a decomposition schema). The compound entity's Readable and, if applicable, Writable methods target multiple relations by building queries with JOIN clauses.

// list East Virginian libraries having one or more of
// Italo Calvino's works in their collection; each
// library further has a list of Calvino's `books`.

const librariesWithBooks = await db.libraries.join({
  books: {
    type: 'INNER',
    on: {library_id: 'id'}
  }
}).find({
  state: 'EV',
  'books.author ILIKE': 'calvino, %'
});

Readable.join itself is a synchronous function. It does not need to be awaited and can be chained immediately into an API method such as find, insert, update, or destroy.

The compound entity returned by Readable.join is cached on the Database object. Future invocations with the same join schema will use the cached entity to avoid redundant processing.

If you're interested in a look behind the curtain, check out this post. For documentation and examples, read on.

Join Definitions

Join definitions are structurally similar to decomposition schemas, but a lot of the details that go into the latter -- column names, primary keys -- are automatically filled in from the results of Massive's initial database introspection. Each node in the definition tree has certain properties defining the behavior for that join; other properties are taken to be nested nodes. Depending on the circumstances, any or all of the join properties themselves may be optional.

Let's add to the join query above: we want to know who's checked these books out. This is what the full definition would look like, with even the optional properties specified:

{
  books: {
    type: 'INNER',
    relation: 'books',
    pk: 'id',
    on: {library_id: 'id'},
    omit: false,
    patron_books: {
      type: 'LEFT OUTER',
      relation: 'patron_books',
      pk: ['patron_id', 'book_id'],
      on: {book_id: 'books.id'},
      omit: true
    },
    who_checked_out: {
      type: 'LEFT OUTER',
      relation: 'patrons',
      pk: 'id',
      on: {id: 'patron_books.patron_id'},
      omit: false
    }
  }
}

Here, libraries is the origin relation, while books is a joined relation; libraries may also be thought of as books's parent. patron_books is a junction table, with a compound primary key; since it would be redundant if included in the output, it has been excluded with the omit option. Finally, who_checked_out is an alias for the patrons table. Patrons will be included in an array at the path books[n].who_checked_out.

In this instance, who_checked_out could have been nested inside patron_books instead of being placed under books. As long as the on is explicitly specified, it doesn't particularly matter which option you choose. However, Massive also analyzes foreign key relationships and can automatically fill in on values if there's a single, unambiguous relationship expressed in the database schema. If you're relying on this, the placement matters: who_checked_out should be nested under patron_books so it can be matched to the other side of the foreign key relationship correctly.

Relations in database schemas off your current search path (likely public) do not need to be aliased: names such as myschema.mytable are valid keys in the join definition. Records from myschema.mytable will appear under the name mytable in the final output.

Field references, whether in the join schema itself or in criteria or order objects passed to methods on a compound entity, must include the alias or name of the relation to which they belong, unless they belong to the origin. Aliases also take precedence over names. If you've aliased a relation in a similar manner to who_checked_out, reference it by the alias to avoid ambiguity. The records from an aliased relation appear under that alias in the final output.

Property	Type	Description
type	String	Set the JOIN type; defaults to INNER.
on	Object	Map fields in the joined relation to fields in the origin or parent relation.
relation	String	Specify the actual relation name, if using an alias.
pk	String/Array	Specify a field or fields uniquely identifying a single record for this relation, if it lacks an explicit primary key constraint (e.g. views).
decomposeTo	String	Joined records are ordinarily decomposed into arrays. Set to the string object to decompose into a singular object instead.
omit	Boolean	Set to true to exclude this relation from the output. Useful for junction tables.

The on property is optional if one and only one foreign key relationship exists between the relation and its parent. Both relations must be tables in order to create such a foreign key.

The same example join definition could thus be expressed more succinctly, assuming all primary and foreign keys are in place:

{
  books: {
    patron_books: {
      type: 'LEFT OUTER',
      omit: true
      who_checked_out: {
        type: 'LEFT OUTER',
        relation: 'patrons'
      }
    }
  }
}

The bare minimum for a simple inner join along a foreign key relationship is an empty object, accepting all defaults and introspected settings:

db.libraries.join({
  books: {}
}).find(...);

Or, for a single joined relation, the name alone suffices:

db.libraries.join('books').find(...);

Persistence

A compound entity produced by invoking join() on a Writable will possess the Writable suite of persistence methods including insert, update, save, and destroy. These all function, although with limitations: the origin relation is the primary or only target, and only tuples from the origin relation are returned -- there's no tree built from related records.

insert on a compound Writable functions exactly as deep insert. Match descendants to the join schema and provide an explicit undefined where the primary key of the origin record is to be filled in.

const librariesWithBooks = db.libraries.join('books');
const newLibrary = await librariesWithBooks.insert({
  name: 'Lichfield Public Library',
  state: 'EV',
  books: [{
    library_id: undefined,
    title: 'Jurgen: A Comedy of Justice',
    author: 'Cabell, James Branch'
  }, {
    library_id: undefined,
    title: 'If On a Winter\'s Night a Traveller',
    author: 'Calvino, Italo'
  }]
});

update and destroy modify only the origin relation, but can apply criteria which filter based on joined relations. However, there's a somewhat tricky limitation. If a joined relation other than the first has implicit or explicit on conditions which reference the origin table, Postgres will fail to process UPDATEs or DELETEs.

const librariesWithBooks = db.libraries.join('books');
const withCabell = await librariesWithBooks.update({
  'books.author ilike': 'cabell, %'
}, {
  has_cabell: true
});

// note that following a foreign key in the opposite
// direction -- deleting libraries based on a patron or
// book, where patrons or books have `library_id`s --
// will fail since the foreign key constraint would be
// violated

const libraryMembers = db.patrons.join('libraries');
const iplPatrons = await libraryMembers.destroy({
  'libraries.name ilike': 'Imaginary Public Library'
});

save continues to function either as insert or update, depending on whether a primary key value is supplied for the origin relation. However, it is less useful since inserts can target multiple tables à la deep insert while updates cannot.

Other Caveats

• INNER and LEFT OUTER are the only fully supported join types. Fortunately, they're also by far the most commonly used join types. The principal reason for this is that decomposition depends on having non-null tuples for the origin relation in all records, which neither RIGHT OUTER nor FULL OUTER joins guarantee. CROSS JOIN has simply not been implemented due to its extreme rarity.
• Readable.findOne() rejects outright since it's all but useless in a multi-relation context: the LIMIT clause it applies affects the entire resultset, so it would discard everything except a linear subtree of the desired output.