MassiveJS

npm install massive --save

Once installed, require the library and connect to your database with a parameter object or connection string:

const massive = require('massive');

const db = await massive({
  host: '127.0.0.1',
  port: 5432,
  database: 'appdb',
  user: 'appuser',
  password: 'apppwd'
});

The returned db object constitutes an API for your schema, with tables, views, functions, and scripts attached. Read on to walk through using them, or consult the documentation for specifics.

A Brief Example

Let's take a (heavily simplified) software testing application as our example. The application's database contains a tests table and an issues table, the latter having a test_id so multiple issues may be raised per test. An auth schema contains a users table; back in the default public schema, a user_tests view associates users with their tests. A copy_test function clones a test for reuse. Finally, there's a db/resetTest.sql file included in the application's source code which returns a test in progress to an as-newly-initialized state.

The application uses Massive to query and persist information in this database, and interested parties can also use the interactive Massive REPL to experiment or perform one-off tasks.

• Persistence
• Retrieval
• Deletion
• Functions and Scripts
• Joins
• Resultset Decomposition
• Documents
• Arbitrary Queries

Persistence

The db object returned by the massive() call has all the above entities attached. Our first task is to create a user:

let alice = await db.auth.users.save({
  username: 'alice',
  password: 'supersecure'
});

alice is a JavaScript object containing the username and password we specified. But our users table has more columns than that: first and foremost, there's a primary key, an id column, which uniquely identifies a single user record. A user also has a role, a created_at timestamp defaulting to the current date on insert, and an updated_at timestamp to track when the record was last modified. alice therefore also has an id, a role, a created_at, and an updated_at. Omitted fields without database defaults -- role and updated_at here -- are null.

To reset Alice's password, we issue a save again, including alice's primary key value:

alice = await db.auth.users.save({
  id: 1,
  password: 'evenmoresecure'
});

The save will search by the primary key in the object and modify only those fields we include. Since Alice's username isn't changing, it doesn't have to be included. However, including it won't hurt anything either, so we could just as well have modified and passed in the original alice object.

Now that alice exists in the system, she can start a test. However, tests shouldn't be updated from the same code path, so we use insert instead:

const test = await db.tests.insert({
  name: 'application homepage',
  url: 'http://www.example.com',
  user_id: alice.id
});

Last bit of housekeeping: alice still doesn't have a role, and we may have added more users without roles as well. Let's perform a bulk update to ensure that we're giving people the right permissions:

const users = await db.auth.users.update({
  'role is': null
}, {
  role: 'default'
});

Retrieval

It's time to see what Alice has been up to. We don't have the saved test anymore, but it'd be out of date anyway. What we do have, though, is the primary key:

const test = await db.tests.findOne(1);

It turns out Alice has discovered several problems which are now stored in the issues table. We can see how many she's found with count:

const total = await db.issues.count({test_id: 1});

Since Postgres' count returns a 64-bit integer while JavaScript integers max out at 53 bits, total will be a string, although thanks to JavaScript's weak typing this doesn't often matter. Next, let's actually pull out the issue data:

const issues = await db.issues.find({
  test_id: 1
}, {
  order: [{field: 'created_at', direction: 'desc'}]
});

The first argument to find is a criteria object. Records must satisfy all such criteria; the key or is a special case, and at least one of its array of sub-criteria objects must likewise be fully matched.

The second argument defines query options; here, we're sorting the issues most recent first. There are many other options which affect both the generation of SQL statements and the processing of results, and almost all retrieval and persistence functions take an options object as the final argument.

find yields an array, here issues, which contains all records in that table matching the criteria and sorted according to options.order.

There are other retrieval functions: where allows us to write more complex WHERE clauses than those find can generate based on the criteria object, and search performs a full-text search against multiple fields in a table. The documentation has more information on these.

Deletion

After review, it turns out that one of the issues Alice discovered was actually the application working as designed, so she needs to delete the issue. She can do that with destroy:

const removed = await db.issues.destroy(3);

The issue has been deleted and the record returned. Since Alice passed the primary key, it's a single object. But destroy, like find, can also accept a criteria object, in which case it would yield an array of issues.

Functions and Scripts

Alice's colleague Bob wants to start testing the homepage, but doesn't want to go through the entire setup process. Fortunately, there's a copy_test function which will let him build on her work, if he passes in the test id and his userid to assign the clone to himself:

const test = await db.copy_test(test.id, bob.id);

There's an important note here: this example assumes that Massive has been initialized with enhancedFunctions. With this flag enabled, Massive detects the shape of database functions' output, and will return a single record object -- or even a scalar value -- as appropriate. Since copy_test only makes one copy, it does return the record object. Without enhancedFunctions, this invocation would return an array containing the single record.

Shortly after Bob starts in, the system he's testing is redeployed underneath him, invalidating the results he's gathered so far. He could delete issues with destroy either individually or in bulk, but it's faster to use the resetTest script. This works exactly as if it were a database function, except that enhancedFunctions does not perform any result type introspection, so the results will always be an array of record objects:

const resetTests = await db.resetTest(test.id);

Joins

Carol, an administrator, wants to get a list of users with the tests they've been working on. There are a couple of ways she could do this, first by joining the tests table to users:

const usersWithTests = await db.users.join({
  tests: {
    pk: 'id',
    type: 'INNER',
    on: { user_id: 'id' }
  }
}).find();

Since Carol is a stickler for proper database design and implementation and this is a very straightforward inner join on a foreign key relationship, she could rely on Massive's introspection and defaults to do most of the work for her:

const usersWithTests = await db.users.join('tests').find();

Massive can fill in much of the required information, even in complex join definitions, from table primary and foreign keys.

The output is decomposed automatically into nested objects:

[{
  id: 1,
  username: 'alice',
  tests: [{id: 1, name: 'first'}, {id: 2,name: 'second'}]
}, {
  id: 2,
  username: 'bob',
  tests: [{id: 3, name: 'third'}]
}]

Resultset Decomposition

Carol might also query the user_tests view, which handles the join in the database. But the records she receives from that look like this instead:

user_id	username	test_id	name
1	alice	1	first
1	alice	2	second
2	bob	3	third

Databases work with information in terms of flat tables and relationships, and the output of a single query is always tabular. So if Alice has two tests, that means there are two Alice rows in the output. In JavaScript, however, we're more accustomed to working with the sorts of nested object trees returned from joined entities above.

Massive can transform any result into nested structures with the decompose option, which takes a schema similar to the join definition. To generate the same structure above:

const usersWithTests = await db.user_tests.find({}, {
  decompose: {
    pk: 'user_id',
    columns: {user_id: 'id', username: 'username'},
    tests: {
      pk: 'test_id',
      columns: {test_id: 'id', name: 'name'}
    }
  }
});

Documents

The tests table represents a fairly limited picture of what exactly Alice and Bob are doing. An individual test may have a lot more information associated with it, and simply adding more columns to the tests table isn't an ideal solution for storing it since this data could be wildly different depending on what precisely is being evaluated.

Postgres' JSONB functionality allows for a more free-form approach than relational databases otherwise support. Working with JSONB fields is certainly possible with the suite of standard table functions, but Massive also allows the dynamic creation and usage of dedicated document tables with a separate set of functions based on the relational data persistence and retrieval functionality.

We can create a document table dynamically by calling saveDoc:

const attrs = await db.saveDoc('test_attributes', {
  productVersion: '1.0.5',
  testEnvironment: 'production',
  web: true,
  accessibilityStandards: ['wcag2a', 'wcag2aa']
});

The attributes document is exactly what we passed in, with the addition of an autogenerated primary key and other metadata fields. The metadata are never stored in the document body itself, but are automatically unwrapped when you persist the document.

Once the document table has been created, it's available just like any other table. You can retrieve the document again with the primary key, or query for an array of documents matching criteria:

const attrs = await db.test_attributes.findDoc(1);

const matches = await db.test_attributes.findDoc({web: true});

Count documents with criteria:

const total = await db.test_attributes.countDoc({web: true});

Perform a full-text search against the values in the document:

const matches = await db.test_attributes.searchDoc({
  fields : ["testEnvironment", "environment", "applicationStatus"],
  term : "production"
});

Persistence functions are also adapted for document tables. You can update/insert a document with saveDoc; if the argument contains an id field, it will update the existing document in the database. If the argument contains no id field then it will insert a new document into the database. Either way, it returns the current state of the document.

This is not a true upsert! saveDoc, like save, determines whether to emit an INSERT or an UPDATE based on whether the data you pass it contains a primary key. If you are generating primary keys manually, use insert instead -- if you specify a value for the primary key, it will execute an UPDATE whether or not there's anything there to modify.

attrs.requiresAuthentication = true;

attrs = await db.test_attributes.saveDoc(attrs);

Note that saveDoc replaces the entire document. To change fields without having to retrieve the document, use updateDoc:

const attrs = await db.test_attributes.updateDoc(1, {
  requiresAuthentication: false
});

updateDoc, like saveDoc, returns the current version of the entire document. updateDoc can also perform bulk operations with a criteria object and a changes object, just like the relational update:

const changed = await db.test_attributes.updateDoc({
  web: true
}, {
  browser: 'Chrome'
});

When used with a criteria object, updateDoc returns an array containing all updated documents.

Arbitrary Queries

Last but not least: sometimes you just need to write some SQL. Alice's and Bob's passwords are both stored as plain text, because we were originally more focused on getting up and running than we were on doing things right. But now, new users are being added through a system that hashes and salts passwords with a hash database function, and the application login expects passwords to be hashed. So Carol needs to ensure that all our users have hashed passwords, which she can do with an ad-hoc query in the REPL:

const users = await db.query(
  'update users set password = hash(password) where id < $1 returning *',
  [3]
);

The value returned is an array of records, assuming the query returns anything. query is most useful for one-offs like this, or for experimentation when you don't want to have to reload the database API to get changes to a script file. Once the query is ready for production usage, though, it's best off in a central location such as your scripts directory, or even a view or database function.