monstrous

a lightweight SQL composer for Node.js and PostgreSQL

await db.select(
  db.libraries
    .join(db.holdings) // implicit join on foreign key holdings.library_id
    .join(db.books)    // implicit join on foreign key holdings.book_id
    .join(db.authors, db.$join.left, {[db.authors.$id]: db.books.$author_id})
    .filter({
      [db.libraries.$postcode]: '12345',
      [`${db.authors.$name} ilike`]: 'Lauren%Ipsum'
    })
    .project({
      $key: db.libraries.$id,
      $fields: [...db.libraries],
      authors: [{
        $key: db.authors.$id,
        $fields: [
          db.authors.$name,
          db.expr(
            `extract(year from age(coalesce(${db.authors.$death}, now()), ${db.authors.$birth}))`
          ).as('age')
        ],
        // notice `books` is a collection on authors, even though we join authors to books!
        books: [{
          $key: db.books.$id,
          $fields: [...db.books]
        }]
      }]
    })
);

npm i monstrous

what is this?

monstrous helps you build composable, reusable SQL functionality without models by deriving an API from your running database schema. It is a successor to MassiveJS that departs significantly from Massive's interface and syntax to afford a lighter and far more flexible translation layer for interacting with PostgreSQL from Node.

Then it gets out of the way and lets you write and run your own SQL. That part hasn't changed.

relations, composition, and reuse

monstrous is additionally inspired by the Clojure library Penkala and the "Relations as First-Class Citizen" principle elucidated in Ruby by alf/bmg (although, like Penkala, it sticks to SQL instead of implementing a full relational algebra).

Unlike many database object- or model-oriented tools, data access with monstrous starts at the action: db.select() rather than Massive's db.libraries.select(), a Library.find_by(), or other variations on the subject-then-verb theme.

db.select() has one required argument: the bare minimum query is db.select(db.libraries). Inside those parentheses is a relation, with a db.libraries.primary_key and qualified columns, which can be transformed into (essentially) another relation, and another, and another, through fluent functions like join, filter, limit, and project. Each relation-builder function returns a new relation -- intermediary relations can be used and built off of repeatedly.

Relations' columns are dollar-prefixed properties: db.libraries.$id, db.authors.$name. Use these properties instead of strings -- while you can get away with un- or partially-qualified names much of the time, you also lose out on some of monstrous' automatic resolution on the fully-qualified and quoted name returned by db.libraries.$id, and risk ambiguous column names appearing in the final query. Keys as in criteria objects can be specified {[db.libraries.$id]: 2}. Spreading an object ([...db.libraries]) returns all columns, useful in projections.

Fluent functions come in two flavors:

additive

Every time these are invoked,

• join will declare a single new join relation and its join conditions
• filter will augment an existing or create a new where clause
• group will append its field(s) to the group by clause

idempotent

• project will overwrite the output record shape definition
• order will replace the order by clause
• offset and limit will reset their respective values

Functions can be used in any order and combination as long as references are preserved: you can't filter a column in authors before you join(db.authors) into your statement.

await db.select(db.libraries
  .join(db.holdings)
  .join(db.books)
  .filter({[db.libraries.$postcode]: '12345'})
  .join(db.authors)
  .filter({[`${db.authors.$name} ilike`]: 'lauren%ipsum'})
  .limit(10)
  .limit(5)
  // both filters will be applied when the statement is executed, and the
  // results will be nested libraries -> holdings -> books -> authors. Up
  // to five results will be returned.
);

Statement execution independent from composition means that a statement can be instantiated as an ordinary variable; used, expanded, and reused within that variable's scope; and even stored on the monstrous connection itself with db.attach(statement) (but be careful about when and where you do that).

const ipsum = db.libraries
  .join(db.holdings)
  .join(db.books)
  .join(db.authors)
  .filter({[`${db.authors.$name} ilike`]: 'lauren%ipsum'});

// don't do this on an application hot path!
db.attach(ipsum, ['saved_queries', 'ipsum']);

// elsewhere....
const result = await db.select(
  db.saved_queries.ipsum.filter({[db.libraries.$postcode]: '12345'})
);

Furthermore, other attached or ad-hoc queries can use ipsum as a base without altering its future executions. Every fluent function returns an updated clone of its base statement.

connect

const db = await monstrous({
  host: 'localhost',
  port: 5432,
  database: 'librarysystem',
  user: 'postgres',
  password: 'supersecure',
  scripts: 'lib/sql'
});

raw SQL and custom types/records

db.query runs raw SQL statements. Pass ordinal arguments inline, or use a map of named parameters. A query target may be passed as the final argument to specify results handling.

await db.query(
  `select * from books where author_id = $1`,
  3
);

await db.query(
  `select * from books where author_id = $(author_id) and title ilike $(title)`,
  {
    author_id: 3,
    title: 'the placeholder'
  },
  db.$target.one
);

exprs, tuples, and literals

Exprs and descendant types interpolate SQL text into a constructed query. They're a thin layer over pg-promise's custom type formatting and accept the SQL with an optional array of indexed or map of named parameters.

db.expr(
  `extract(years from justify_interval($1 - ${db.employees.$hired_on}))`,
  new Date() // argument $1
).as('tenure') // alias only required in projection!

Exprs can be used in many places: as criteria values in join and filter (or in place of criteria!), as output fields in project, or as change values in insert and update.

Composite types can be represented with exprs in persistence methods with SQL formatted as a record ($1, $2) and appropriate parameters.

In projections, exprs must be aliased: db.expr(...).as('name').

Exprs can be assigned pg-promise formatting options via db.expr(...).options({capSQL: true})

Tuples are a subclass of Expr which represent composite types or records. These do not include an SQL snippet, instead transforming their arguments -- which can include other exprs in order to reference columns in the query -- into record values.

db.tuple(1, 2, 3, 'some text')

Finally, literals allow the specification of text, numeric, or boolean values in projections:

db.literal(`'this' will be properly escaped`)

Tuples, and exprs provided as tuple arguments, are always interpolated directly into the final SQL statement; user input is not sanitized against SQL injection.

joins

statement.join() adds a single relation to the query under construction (this differs dramatically from Massive, where join() is used a single time to attach all desired relations at once). The full specification looks like this, with the target relation, the join type, and the on criteria:

const values = db.values('v', {author_id: 1, extra: 'text'});
const result = await db.select(
  db.libraries
    .join(db.holdings, db.$join.left, {[db.holdings.$library_id]: db.libraries.$id})
    .join(
      // left-joining the filtered subquery means some holdings will be empty!
      db.books.filter({[`${db.books.$id} <`]: 5}),
      db.$join.left,
      {[db.holdings.$book_id]: db.books.$id}
    )
    .join(
      values,
      db.$join.left,
      {[values.$author_id]: db.books.$author_id}
    )
);

The join type defaults to inner and may be omitted.

The on criteria map the columns of the newly joined relation to, usually, fully qualified columns of any already-joined relation. There is no "handedness" to criteria, so the division into criteria keys and values is a matter of taste. Literals and exprs are also valid on criteria values only.

Results are automatically projected as new_relname: [record object, record object] to the most recently joined relation referenced in the on, but you can also override with your own projection.

If on criteria are not specified, monstrous will search for a foreign key relationship between the target table and any other table involved in the query. If it finds one and only one such foreign key, it will generate the appropriate on clause automatically; otherwise, it will raise an error on execution telling you to specify on criteria.

filtering

statement.filter() restricts results with a where clause. It accepts either criteria objects mapping column names to values and exprs or, for tables, an integer or UUID primary key.

projection

statement.project() defines the shape of your query output, articulating nested collections and objects. It unifies Massive's resultset decomposition (aka join definitions) and the fields and exprs options.

project() takes a single argument, which represents the shape of a single output record. This may be a flat array of column names:

[
    // include all columns by spreading a relation
    ...db.employees,
    // add a column by name
    db.libraries.$postcode,
    // expressions can be added as long as they're aliased with `as`
    db.expr(`extract(year from ${db.libraries.founded_at})`).as('founding_year')
]

Or a complex object:

const directors = db.employees
  .filter({position: 'director'})
  .as('directors'); // set the default projection name

await db.select(
  db.libraries
    .join(directors, {[directors.$library_id]: db.libraries.$id})
    .join(db.patrons)
    .project({
      $key: db.libraries.primary_key,
      $fields: [...db.libraries],
      director: { // override the subquery alias to singular
        $key: directors.$id,
        name: directors.$name,
        tenure: db.expr(`now() - ${directors.$hired_on}`)
      },
      patrons: [db.patrons] // project entire patron records into an array
    })
  );

Here, director is a nested object (a library has only one director; the subquery filters employee records appropriately). It's also named arbitrarily, since the records are coming from the employees table. Meanwhile, patrons is a nested array named for its table.

The right-hand side determines the output shape of the nested projection. An object representation yields an object, while a single-element array containing the same object representation yields an array.

There are two special object keys being used here:

• $key uniquely identifies a record-node at the current level of the projection tree
• $fields specifies a list of fields to be included in this record-node without renaming, similar to the simple array form of projection

$key is usually the table's primary key, and indeed each table has a primary_key property as seen with libraries. A composite $key may be specified explicitly with an array of constituent fields.

libraries also uses the $fields property to specify relation columns without renaming them. Meanwhile, the director instead specifies only a subset of fields one by one, and tenure is a raw SQL expression which will be interpolated into the select list. Finally, patrons is articulated as an array without any changes or additions, by supplying the relation itself.

These three variations on patrons are all equivalent:

{
  patrons: [db.patrons]
}

{
  patrons: [{
    $key: db.patrons.primary_key, // or db.patrons.$patron_id!
    $fields: [...db.patrons]
  }]
}

{
  patrons: [{
    $key: db.patrons.$id,
    name: db.patrons.$name,
    address: db.patrons.$address
  }]
}

db.exprs may be included in a $fields array, but must be aliased. In other words, a list of library directors could be constructed thus:

await db.select(
  db.libraries
    .join(directors, {[directors.$library_id]: db.libraries.$id})
    .project({
      $key: directors.$id,
      $fields: [
        db.libraries.$name,
        directors.$name,
        db.expr(`now() - ${directors.$hired_on}`).as('tenure')
      ]
    })
);

As always, raw SQL is a potential vector for SQL injection; use it carefully!

counting

You can use exprs to project aggregates, but db.count is a convenience method that builds a count(*) expression for you. No arguments are necessary, in which case the returned column is also named count. You can also build and pass an expr in order to customize the expression, such as by adding distinct, specifying target relations, or aliasing the expr.

await db.select(
db.libraries
  .join(db.holdings)
  .project([
    db.libraries.$id,
    db.count(db.expr('holdings.*').as('collection_size'))
  ])
  .group(db.libraries.$id)
);

aliasing relations

db.libraries.as('bookplaces') lets you rename relations within the context of a query. This helps with generating correct names in the default projection of a join, but is also required for self-joins since each instance of a relation in a join must have a unique name.

Once introduced into a query, aliases must be used consistently: it is generally an error if you select records from libraries as bookplaces but filter where libraries.postcode = '12345' (Postgres wants bookplaces.postcode). Declaring your alias as a variable in JavaScript -- const bookplaces = db.libraries.as('bookplaces') -- can help ensure consistency.

Aliases carry through to projections: nested properties will be named for the alias rather than the original relation.

ordering

await db.select(
  db.libraries.order(
    db.$order.desc(db.libraries.$founded),
    db.libraries.$name
  )
);

Any SQL expression may be written in an expr and passed to db.$sort.asc or db.$sort.desc.

Here as in many other places raw text is accepted but can increase your risk of SQL injection, and may generate SQL that Postgres cannot execute, for example because of an ambiguous column name.

json

Postgres supports JSON traversal either through subscripting(json_field['alpha']['beta']) or custom operators (json_field ->> 'alpha' or json_field #>> '{alpha,beta}'). monstrous supports both methods in filter criteria keys.

Projecting elements of JSON fields can be accomplished with exprs.

persistence

insert

Simple inserts declare a table and pass one or more individual value objects. Arrays are not supported; use the ... spread operator if you have an array of value objects.

await db.insert(db.authors, {name: 'Lauren Ipsum'}, {name: 'Daler S. Ahmet'});

Inserts into a single table can also pass a subquery:

await db.insert(db.holdings,
  db.books
    .filter({author_id: 1})
    .project({
      book_id: db.books.$id,
      library_id: 1
    })
);

Add records to multiple related tables by inserting against a join. You may only insert a single parent record, or any record with children; for example, the statement below could insert multiple books only if it did not also insert holdings.

await db.insert(db.authors.join(db.books), {
  name: 'Consuela Ctetur',
  books: [{
    title: 'The Placeholder',
    publisher: 'Aleatory Domicile',
    print_date: new Date(2001, 0),
    // only "leaf" tables can have multiple values!
    holdings: [{
      library_id: 1
    }, {
      library_id: 2
    }]
  }]
}, db.$target.one); // return Consuela as an object

The join fields (e.g. the foreign key) of dependent records are automatically filled, and extraneous fields not recognized as columns are ignored.

Keys in the value object referencing dependent tables may use the name as in the example, the fully qualified path ([db.books.$title]: [{...}]), or the qualified path from the table alias if applicable.

update

db.update expects the statement and an object mapping columns of the root relation to changes.

await db.update(db.authors.join(db.books).filter({publisher: 'Aleatory Domicile'}), {
  is_in_print: db.expr('case when print_date > $1 then true else false end', check_date)
});

save

db.save() passes a single value to db.insert or db.update depending on whether or not it contains values for the primary key. It automatically applies db.$target.one to return an object.

await db.save(db.authors, {
  id: 123,
  name: 'Lauren Ipsum',
  birth: new Date(1920, 1, 1)
});

delete

Deletes look a lot like selects except the resolved records aren't there afterwards.

await db.delete(
  db.holdings.join(db.books).filter({[db.books.$author_id]: 3})
);

query files and sql functions

monstrous loads QueryFiles from the /sql directory in your project (can be customized with the connection.scripts field). QueryFiles and introspected database functions are attached to the db object. Functions in a schema, or QueryFiles in nested paths, are namespaced accordingly.

QueryFiles and functions are both invoked with the db.execute function. Arguments are passed as second through nth arguments to execute; the final argument may be a query target.

Database functions can take exprs as arguments, but QueryFiles cannot. These exprs are interpolated directly -- do not pass user input, and beware of SQL injection!

// QueryFile (sql/collections/purge.sql) or database function with ordinal ($1
// style) parameters
await db.execute(
  db.collections.purge,
  1,
  123,
  'have 5 copies in better condition',
  db.$target.one
);

// QueryFile (sql/collections/purge.sql) with named parameter object
await db.execute(
  db.collections.purge,
  {
    library_id: 1,
    book_id: 123,
    reason: 'have 5 copies in better condition'
  },
  db.$target.one
);

tasks and transactions

db.task(callback, tag) provides a dedicated connection as the sole argument to callback. db.transaction(callback, mode, tag) does similarly, except that the connection opens a transaction which commits at the end of callback or rolls back if an error is thrown.

monstrous' split between statement construction and execution, or between database structure and connection, mean that you still work with db in the task/transaction callback! You use the dedicated task or tx connection to execute -- that is, issue the verbs -- while referencing db to build statements, since the database structure is not cloned to each connection.

Statements built outside the callback can be used within it.

await db.transaction(async tx => {
  await tx.insert(db.books,
    {author_id: 1, title: 'Makeshift Examples'},
    {author_id: 1, title: 'Inter/Inter'},
    {author_id: 1, title: 'Signifying Nothing'}
  );

  result = await tx.insert(db.holdings,
    db.books
      .filter({author_id: 1})
      .project({
        book_id: db.books.$id,
        library_id: 1
      })
  );
});

db.all(db.libraries, db.authors.filter(1)) selects each statement on a single dedicated connection. It's like a read-only task, except you can destructure the array of individual results into variables directly.

query targets

Result-manipulating functionality like Massive's findOne or stream methods, and the build option, is now provided by "query targets" passed as the final argument to any operation such as db.select or db.insert. Available targets:

• db.$target.one: return a single record as an object, or error if more than one record would be returned
• db.$target.unit: return a single value (one row, one column) as a primitive or object
• db.$target.list: return a single column as a simple array
• db.$target.stream: not implemented yet
• db.$target.log: return the SQL that would be emitted to Postgres

In a task or transaction, use task.$target.one/tx.$target.one.

how to get SQL injected

A non-exhaustive catalog.

Never interpolate user input into an expr:

const str = '; drop table patrons; --';
db.expr(`${libraries.$id} + 1 ${str}`).as('asking_for_trouble'),

It will not always bite you; exprs are ordinarily compiled using pg-promise's custom type formatting which sanitizes input. Sometimes, however, they must be directly interpolated, and the circumstances in which that's true are sufficiently arcane it's smarter to avoid the practice entirely.

Tuples are always unsafe.

other things monstrous does that Massive doesn't

• subqueries (join a statement just like a relation, use it as a value in a criteria object, pass it as a value to insert! in joins it's aliased to its root table name by default)
• join db.values(alias, ...objects)
• group by
• raw exprs in update changes
• raw exprs in join and filter criteria

things Massive does that monstrous doesn't

• ergonomic support for keyset pagination
• nulls first/last in order by
• locking in select
• full text search (currently doable with exprs)
• document tables
• attach compatible objects at the same target (e.g. table and function with same name)
• full (bracketless) JSON object subscripting in criteria

roadmap

• on conflict
• having
• window
• exists and correlated subqueries?
• ad-hoc CTEs?
• lateral join exprs?
• set operations (union [all], intersect, except)
• wrapped tasks with connection settings?
• PostGIS operators
• streaming
• merge?
• generate TypeScript definitions?

not on roadmap

• support older Postgres versions: most functionality should work back to Postgres 11 but has not been tested
• support Node < 16
• CommonJS
• migration management