Connecting

Connect by requiring or importing Massive and invoking the function with connection information. The resulting promise resolves into a connected database instance.

The connection process is fast but does take time. The instance is intended to be maintained and reused rather than regenerated for each query.

You can connect Massive to your database with a pg-promise configuration object or a connection string. Using the former is recommended for application code since connection strings provide no mechanism for configuring the pool size and other options.

const massive = require('massive');

massive({
  host: 'localhost',
  port: 5432,
  database: 'appdb',
  user: 'appuser',
  password: 'apppwd',
  ssl: false,
  poolSize: 10
}).then(instance => {...});

Introspection

When you instantiate Massive, it introspects your database to discover tables, views, and functions. If you have a /db directory in your project root, SQL script files there are also loaded. Together, all four become an API for your database attached to the connected Massive instance itself. Schemas (and folders in /db) organize objects in namespaces.

Most objects can coexist if they wind up in the same namespace. For example, you might have a table named companies and a schema named companies which contains more tables. In this scenario, db.companies will be a table and also a schema, so you might query db.companies.find(...) and db.companies.audits.find(...) as you need to.

There are two cases in which collisions cannot be resolved:

  • When a script file or database function would override a function belonging to a loaded table or view (or vice versa): for example, db.mytable already has a find() function, so a script file named mytable/find.sql cannot be loaded.
  • When a script file has the same path as a database function.

Massive will throw an exception on startup if it encounters either problem in order to avoid unexpected behavior.

The introspection process is fast, but not instantaneous, and you don't want to be doing it every time you run another query. Massive is designed to be initialized once, with the instance retained and used throughout the rest of your application. In Express, you can store the connected instance with app.set in your entry point and retrieve it with req.app.get in your routes; or with koa, using app.context. If no such mechanism is available, you can take advantage of Node's module caching to require the object as necessary.

Schemas

Massive understands database schemas and treats any schema other than the default public (or Postgres configured search_path) as a namespace. Objects bound to the public schema are attached directly to the database object, while other schemas will be represented by a namespace attached to the database object, with their respective tables and views bound to the namespace.

// query a table on the public schema
db.tests.find(...).then(...);

// query a table on the auth schema
db.auth.users.find(...).then(...);

Looking Into the Instance

To see everything Massive has discovered and loaded, use the three list functions:

db.listTables();
db.listViews();
db.listFunctions();

Each returns an unsorted array of dot-separated paths (including the schema if not public for database entities, and nested directory names for script files). listTables includes normal and foreign tables; listViews includes ordinary and materialized views. listFunctions includes both database functions and script files.

New Database Versions and Features

PostgreSQL is not a stationary target: for example, materialized views didn't exist prior to Postgres 9.3, and procedures only became supported with Postgres 11. Massive automatically detects the version of Postgres it's connecting to and enables or disables version-dependent features accordingly.

Massive Configuration

If you want to change the introspection parameters or adjust other elements of Massive's behavior, instantiate Massive with a second configuration argument:

massive(connectionInfo, configurationInfo)
  .then(instance => {...});

Any, all, or none of the following fields may be specified, or the object may be omitted entirely as long as driver configuration is also omitted.

Key Value Default Description
scripts String ./db Relative path to a scripts directory.
allowedSchemas Array/String [] Only load tables, views, and functions from the specified schemas.
whitelist Array/String [] Only load tables and views matching the whitelist.
blacklist Array/String [] Never load tables and views matching the blacklist.
exceptions Array/String [] Specify exceptions to a blacklist ruleset.
functionWhitelist Array/String [] Only load functions matching the whitelist.
functionBlacklist Array/String [] Never load functions matching the blacklist.
enhancedFunctions Boolean false Streamline function return values: a function returning a record will yield an object and a function returning a scalar will yield the value, instead of both returning an array of record objects.
excludeFunctions Boolean false Don't load database functions at all.
documentPkType String serial Set the type of document table primary key fields to serial or uuid.
uuidVersion String v4 Set the UUID version used by document table primary keys to v1, v1mc, v3, v4, or v5.

Blacklists and whitelists may be an array of strings or a single comma-separated string. Either form can use SQL LIKE wildcarding with _ and % placeholders. Consistent with PostgreSQL naming conventions, values are case-sensitive.

A fully-specified Massive configuration object might look like this:

massive(connectionInfo, {
  // change the scripts directory
  scripts: './myscripts',

  // only load tables, views, and functions in these
  // schemas
  allowedSchemas: ['public', 'auth'],   

  // only load tables and views matching the whitelist
  whitelist: ['test%', 'users'],

  // never load these tables or views...
  blacklist: 'device%, issue',

  // ...unless they appear here
  exceptions: ['device_sessions'],

  // only load functions matching the whitelist
  functionWhitelist: ['%user%'],

  // never load functions on the blacklist
  functionBlacklist: 'authorize_user,disable_user',

  // streamline function return values: a function with a
  // scalar value will return just the scalar instead of
  // an array, etc.
  enhancedFunctions: true,

  // don't load database functions at all
  excludeFunctions: true,

  // use UUID primary keys for document tables
  documentPkType: 'uuid',

  // use v1mc keys (semi-deterministic, better for
  // indexing and partitioning than the default v4)
  uuidVersion: 'v1mc'
}).then(instance => {...});

Driver Configuration

The third argument to the Massive constructor is a driverConfig object passed directly through to pg-promise. Please consult the pg-promise documentation for more information.

massive(connectionInfo, configurationInfo, {
  // use native bindings (must be installed separately)
  pgNative: true,

  // don't log any warnings from the driver
  noWarnings: true
}).then(instance => {...});

Changing the Promise Library

Massive uses ES6 promises by default. To use a different promise implementation such as Bluebird to enable long stack traces, pass the required promise module in the driver options.

const promise = require('bluebird');

massive(connectionInfo, {}, {
  promiseLib: promise
}).then(instance => {...});

Monitoring Queries

pg-monitor can help diagnose bugs and performance issues by logging all queries Massive emits to the database as they happen in realtime with more granularity than tailing the Postgres logfile. Note that while the driver options are not required while initializing Massive, db.driverConfig still contains the read-only pg-promise configuration.

const massive = require('massive');
const monitor = require('pg-monitor');

massive('postgres://localhost:5432/massive').then(db => {
  monitor.attach(db.driverConfig);

  db.query('select 1').then(data => {
    // monitor output appears in the console
  });
});

Reloading the API

If you're changing your database's schema on the go by issuing CREATE, ALTER, and DROP statements at runtime, the connected Massive instance will eventually fall out of date since it is generated at the time of connection. The reload function cleans out your database's API and performs the introspection again, ensuring you can access dynamically instantiated objects.

Massive functions like createDocumentTable, createSchema, dropSchema, and dropTable do not require a reload.

db.reload().then(refreshedInstance => {...});

REPL

Massive ships with a REPL (read-evaluate-print loop), an interactive console that lets you connect to a database and execute JavaScript code. The easiest way to run it is to install globally:

npm i -g massive

You can then fire up a connection and start writing JavaScript:

massive --database appdb

db > db.listTables();
[ 'tests',
  'users' ]

db > db.tests.find({user_id: 1}).then(tests => {...});

Or to use a connection string, invoke it with the -c or --connection option instead of -d or --database.

In addition to the tables collection, the views and functions collections are also exposed on the database object.

When invoking functions, you may omit the then if you just want to see output -- Massive provides a resolver which logs the results to make it easy to query with the REPL.

Exit the REPL by pressing Ctrl-C twice.