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 put it back where it was with a second trip across the wire? 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 documentation 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.

Prepared statement scripts must consist of one and only one SQL statement. Common table expressions or CTEs can take some of the sting out of this requirement, but if you need to execute multiple statements with arbitrary parameters it's time to turn it into a proper function.

Invocation

Massive treats functions and scripts (almost) identically. Each is attached 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 flat arrays will be intercepted and the results massaged into scalars or flat arrays, as appropriate. Since this represents a departure from the consistent form, it must be explicitly enabled on initialization.

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

db.uuid_generate_v1mc().then(arr => {
  // an array containing the generated UUID (requires
  // the uuid-ossp extension)
});

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

Procedures

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

Scripts

db.myScripts.someScript(5, true).then(results => {
  // this runs the prepared statement in
  // db/myScripts/restartTests.sql with the above
  // parameters and returns any output from a RETURNING
  // clause
});

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

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