Functions and Scripts

Object-relational mappers tend to ignore database functions and procedures. For many, the database exists solely as a repository, with the manipulation of data a task reserved for application logic and external jobs.

To be fair, this setup is perfectly sufficient for many use cases. But when it isn't, it hurts. With functions, you can perform complex operations on your data at a scope and speed unrivaled by anything else. Why go to the trouble of querying bulk data into another system and manipulating it -- only to make a second trip across the wire to put it right back where it was? Especially when there's a powerful, flexible language purpose-built for set operations right there? You wouldn't work that way, and Massive won't make you: functions are first-class citizens as far as it's concerned.

Database Functions and Procedures

All functions and (as of Postgres 11) procedures visible to the connecting role are attached to the Massive instance, unless the configuration restricts function loading.

The Scripts Directory

Massive doesn't stop at the code stored in the database itself: on startup, it looks for script files in your project and loads them up too. By default, Massive searches the /db directory, but this can be customized by setting the scripts property in the Massive configuration object. The scripts directory can contain further subdirectories; like schemas, these are treated as namespaces. Unlike schemas, they can be nested to arbitrary depths.

Like SQL passed to db.query, prepared statements in script files can use named parameters instead of $1-style indexed parameters. Named parameters are formatted ${name}. Other delimiters besides braces are supported; consult the pg-promise docs for a full accounting.

Script files may use the stream option with some constraints:

  • Named parameters are not supported when streaming.
  • You may need to add explicit type casts in places: SELECT $1 + $2 has problems, but SELECT $1::INT + $2::INT runs.

Invocation and Examples

Massive treats functions, procedures, and scripts (almost) identically. Each is attached to the connected Massive instance as a function which may be invoked directly. Parameters may be passed in one by one or as an array. An options object may be passed as the very last argument to a function or script invocation. Results are returned in the usual Massive style as an array of objects.

If enhancedFunctions is set to true in the Massive configuration object, functions and procedures returning scalars or single records will be intercepted and the results massaged into scalars or objects, as appropriate. Since this departs from the consistent form, it must be explicitly enabled on initialization. enhancedFunctions does not apply to scripts, which will continue to return arrays of records no matter what.

The final argument to Postgres functions and procedures may be declared a VARIADIC array, similar to JavaScript rest parameters: all extra values are rolled up into a single array parameter of the same type. Options objects may still be appended to the invocation after the last variadic argument. Don't enclose your variadic arguments in an array or Postgres will not be able to identify the function you mean to execute.

Functions

// an array containing the generated UUID (the uuid-ossp
// extension must be enabled)
const arr = await db.uuid_generate_v1mc();

// options objects may be used with functions
const stream = await db.get_processed_records(123, {
  stream: true
});

Procedures

// procedures do not return values; while options
// objects are recognized, they are not very useful
await db.myProcedure(1, 2, 3, 4);

Scripts

// this runs the prepared statement in
// db/myScripts/restartTests.sql and returns any output
// from a RETURNING clause
const results = await db.myScripts.someScript(5, true);

// as above; the prepared statement should use
// ${category} and ${force} instead of $1 and $2
const results = await db.myScripts.anotherScript({
  category: 5,
  force: true
});

// options objects may also be used with scripts
const stream = await db.myScripts.someScript(1, true, {
  stream: true
});