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
- Looking Into the Instance
- Schemas
- Refreshing the API
- Refreshing Materialized Views
- Loader Configuration and Filtering
- Document Table Primary Key Types
- Driver Configuration
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.audit.find(...)
as you need to.
There are two cases in which collisions will result in an error:
- 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 afind()
function, so a script file namedmytable/find.sql
cannot be loaded. - When a script file has the same path as a database function.
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.
If you ever need to run the introspection again, use db.reload()
to get a promise for an up-to-date instance.
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 for non-public database entities, and nested directory names for script files). listTables
includes normal and foreign tables. listFunctions
includes both database functions and script files.
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(...);
Refreshing 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 be 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.
db.reload().then(refreshedInstance => {...});
Refreshing Materialized Views
refresh
can be used with materialized views, which cache the view query results to sacrifice realtime updates for performance. Materialized views must be refreshed whenever you need to ensure the information in them is up to date.
Materialized views ordinarily block reads while refreshing. To avoid this, invoke the function passing true
to specify a concurrent refresh.
refresh
returns an empty query result.
db.cached_statistics.refresh(true) // concurrently
.then(() => {...});
Loader Configuration and Filtering
If you don't want to load every table, view, or function your user can access, Massive lets you restrict which objects are loaded through a set of white- and blacklist options on initialization. Any, all, or none of the loader configuration fields may be specified, or the object may be omitted entirely as long as driver configuration is also omitted.
Blacklists and whitelists may be comma-separated strings or an array of strings (which will be separated by commas). Either type can use SQL LIKE
(_
and %
placeholders) wildcarding. Consistent with PostgreSQL naming, they are case-sensitive.
Document Table Primary Key Types
Please see the document table documentation's Primary Key Default Data Type for more information about what a UUID is and why you may want to use it. However, to use the loader documentPkType
option, the connected database will need the extension 'uuid-ossp' installed.
If this extension is not installed, follow these steps, using the 'postgres' account as 'superuser' privileges are required.
$ psql -d postgres -h localhost -p 5432 -U postgres
psql (10.4 (Ubuntu 10.4-2.pgdg16.04+1))
Type "help" for help.
postgres=# \c YOUR_DB_NAME;
You are now connected to database "YOUR_DB_NAME"
as user "postgres".
YOUR_DB_NAME=# CREATE EXTENSION "uuid-ossp";
CREATE EXTENSION
YOUR_DB_NAME=# \q
The default/recommended loader uuidVersion
option is set to 'v4' and will suit most use cases. However, 'v1' and 'v1mc' offer higher performance but are considered less secure, as they may reveal the network card MAC address. Before changing this setting make sure you carry out your own research.
More information: PostgreSQL extension 'uuid-ossp'
massive(connectionInfo, {
// change the scripts directory
scripts: './myscripts',
// override default 'serial' data type, used for new document
// tables id/primary key, i.e. 'serial' or 'uuid'
documentPkType: 'serial',
// applies if documentPkType is set to 'uuid'. Override default
// 'v4' UUID variation, i.e. 'v1', 'v1mc', 'v3', 'v4' or 'v5'
uuidVersion: 'v4',
// 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: 'authorizeUser,disableUser',
// 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,
// don't load materialized views (required for Postgres < 9.3)
excludeMatViews: true
}).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, loaderConfig, {
// use native bindings (must be installed separately)
pgNative: true,
// don't log any warnings from the driver
noWarnings: true
}).then(instance => {...});