Queries
Since Massive doesn't use models, data is retrieved as plain old JavaScript objects where keys correspond to column names. With the obvious exceptions of findOne
and count
, most query functions return arrays where each object represents a row in the resultset even if there is only one result whether naturally or from a LIMIT
clause applied by query options.
The find
, findOne
, and count
functions form a consistent API for data retrieval with criteria and options. where
offers total flexibility if you need to hand-write a WHERE
clause for cases where criteria objects aren't sufficient (for example, testing concatenation or math on a field). search
handles full-text search across multiple fields.
All query functions except count
may take options objects. Valid options are those for SELECT
statements and general results processing. One especially useful option with query functions is stream
, which, when true, will return results as a stream instead of an array. This allows you to start reading and handling data immediately, but the connection will remain open until the stream is terminated.
find
find
is the workhorse of the query functions. Given criteria and options (both optional, in which case it queries the full table) it returns a Promise for a results array. If no results are found, the array will be empty.
db.tests.find({
is_active: true
}, {
offset: 20,
limit: 10
}).then(tests => {
// active tests 21-30
});
findOne
findOne
is a convenient alias for find
with options.single
set. An options object may still be passed. It returns an object corresponding to the first resulting record, or null
if no records match the criteria.
db.tests.findOne({
id: 1
}, {
fields: ['name', 'is_active']
}).then(result => {
// an object with the name and active status for test #1
});
You can use a primary key value instead of a criteria object with findOne
if desired. If your table has a compound primary key you must use the criteria object.
db.tests.findOne(1, {
fields: ['name', 'is_active']
}).then(result => {
// an object with the name and active status for test #1
});
count
count
returns the number of rows matching the criteria object. Since PostgreSQL uses 64-bit integers and JavaScript's Number type only has 53 bits of precision, the result will actually come back as a String rather than a Number.
count
does not take an options object, since there are no useful options a user might set.
db.tests.count({
is_active: true
}).then(total => {
// the number of active tests
});
count
may also be invoked with a where
-style prepared statement and parameters.
where
where
lets you write your own prepared statement-style WHERE
clause. While the criteria object is extremely flexible, it does have limitations: it won't perform operations such as LEFT()
or SUBSTRING()
, it can't set up subqueries, and so forth. For those contingencies, or if you just really want to write it yourself (we don't judge), there's where
. Like find
, where
always returns a results array, even if it's empty.
db.tests.where(
'id IN (SELECT test_id FROM user_tests WHERE user_id = $1)',
[1]
).then(tests => {
// all of user 1's tests
});
where
can use named parameters; just pass in an object instead of an array for the function's second argument.
db.tests.where(
'id IN (SELECT test_id FROM user_tests WHERE user_id = ${id})',
{id: 1}
).then(tests => {
// all of user 1's tests
});
search
search
enables full-text searching across multiple fields. The first argument is a search plan with an array of fields
, a term
to search for, and an optional where
criteria object to filter matches further. The function also takes a query options object as an optional second argument. search
returns a results array.
db.users.search(
{
fields: ['email', 'name'],
term: 'rob',
where: {
is_active: true
}
},
{stream: true}
).then(stream => {
// a readable stream of users matching the full-text
// search condition
});
Raw SQL
Important note: db.run
is deprecated as of version 5.0.0. Update your code to use db.query
instead.
Massive offers a lot of features for interacting with your database objects in abstract terms which makes bridging the JavaScript-Postgres divide much easier and more convenient, but sometimes there's no way around handcrafting a query. If you need a prepared statement, consider using the scripts directory (see below) but if it's a one-off, there's always db.query
.
db.query(
'select * from tests where id > $1',
[1]
).then(tests => {
// all tests matching the criteria
});
query
takes named parameters as well:
db.query(
'select * from tests where id > ${something}',
{something: 1}
).then(tests => {
// all tests matching the criteria
});
And options:
db.query(
'select * from tests where id > ${something}',
{something: 1},
{build: true}
).then(query => {
// an object with sql and params
});