Connecting

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

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

const massive = require('massive');

const db = await massive({
  host: 'localhost',
  port: 5432,
  database: 'appdb',
  user: 'appuser',
  password: 'apppwd',
  ssl: false,
  poolSize: 10
});

Introspection

When you instantiate Massive, it analyzes 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 attached at the same path. 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. See the framework examples for some common patterns.

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 default 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
const tests = await db.tests.find(...);

// query a table on the auth schema
const users = await db.auth.users.find(...);

Looking Into the Instance

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

const tables = db.listTables();
const views = db.listViews();
const functions = db.listFunctions();

Each returns synchronously with 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, to some extent, a moving 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

Massive's introspection and certain other behavioral parameters can be adjusted by passing a second configuration argument during instantiation:

const db = await massive(connectionInfo, config);

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, or v4.

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:

const db = await 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,

  // set to true to ignore database functions entirely
  excludeFunctions: false,

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

  // specifically, v1mc UUIDs (semi-deterministic, better
  // for indexing and partitioning than the default v4)
  uuidVersion: 'v1mc'
});

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.

const db = await massive(connectionInfo, config, {
  // use native bindings (must be installed separately)
  pgNative: true,

  // don't log any warnings from the driver
  noWarnings: true
});

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');

const db = await massive(connectionInfo, {}, {
  promiseLib: promise
});

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');

const db = await massive(
  'postgres://localhost:5432/massive'
);

monitor.attach(db.driverConfig);

const data = await db.query('select 1');

// 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.

await db.reload();

Terminating Connections

The Massive instance ordinarily lives and dies with your served application, but if you're using Massive in another context (such as a command-line script) it can take several seconds to spin down. To terminate the connection instantly, issue the following:

db.instance.$pool.end();

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});

find in this instance still returns a Promise, but the REPL attaches a resolver which logs the results to make things simpler. You may still then off it; as of this writing, top-level await has yet to land.

Pass a connection string with massive -c or --connection instead of -d/--database.

Exit the REPL by pressing Ctrl-C twice.