Spell

Spell

Spell is the query builder of Leoric which has several important characters that made a powerful querying interface possible.

  • Deferred
  • Method chaining
  • Parsing SQL expressions

Some of the methods of Spell follow such pattern:

  • Methods that begin with $, such as Spell#$group, are methods that support chaining. For example, we can do new Spell().$select('foo').$group('bar') to construct an SQL query.
  • These methods have twin methods without the $, which have the same functionalities but differ in only one thing, every call of these methods makes changes on a duplicate of current spell and returns the duplicated one. For example, new Spell().select('foo').group('bar') works and each call, new Spell(), .select('foo'), and .group('bar'), returns a new spell.

Spell adapts this pattern to make following usage possible:

const query = Post.find('createdAt > ?', new Date(2012, 4, 15));
const [ { count } ] = query.count();
const posts = query.offset(page * pageSize).limit(pageSize);
this.body = { count, posts }

query.count() and query.offset() won't interfere with each other because new Spell gets returned every time these methods were called. For brevity, only the methods start with $ are documented.

For performance reason, Bone use the prefixed with $ ones mostly.

Constructor

new Spell(Model, opts)

Create a spell.

Parameters:
Name Type Description
Model

A sub class of Bone.
opts

Extra columnAttributes to be set.

Members

Model

A sub-class of Bone.

all

Mark the spell as resolving to a collection of model instances.

Example
Post.all

dup

Get a duplicate of current spell. The duplicate is independent, so further chained calls on the duplicate do not mutate the original spell. Note that joins and sets are shared by reference with the original.

first

Return a duplicated spell ordered by the primary key ascending and limited to the first record. Resolves to a single model instance or null.

Example
Post.find({ title: 'x' }).first

last

Return a duplicated spell ordered by the primary key descending and limited to the last record. Resolves to a single model instance or null.

Example
Post.find({ title: 'x' }).last

unparanoid

Return a duplicated spell with the soft delete scope (e.g. deleted_at IS NULL) removed only, keeping other scopes intact, so the query includes soft deleted rows.

Example
Post.find({ title: 'x' }).unparanoid.all

unscoped

Return a duplicated spell with all scopes removed, including the default soft delete scope, so the query includes soft deleted rows as well.

Example
Post.find({ title: 'x' }).unscoped.all

Methods

$delete()

Set query command to DELETE.

$forceIndex()

Example:
.forceIndex('idx_id')
.forceIndex('idx_id', 'idx_title_id')
.forceIndex('idx_id', { orderBy: ['idx_title', 'idx_org_id'] }, { groupBy: 'idx_type' })

$from(table)

Set the table of the spell. If an instance of Spell is passed, it will be used as a derived table.

Parameters:
Name Type Description
table

$get(index)

Get nth record.

Parameters:
Name Type Description
index

$group(…names)

Set GROUP BY columnAttributes. select_expr with AS is supported, hence following expressions have the same effect:

.select('YEAR(createdAt)) AS year').group('year');
Parameters:
Name Type Attributes Description
names <repeatable>
Example:
.group('city');
.group('YEAR(createdAt)');

$having(conditions, …values)

Set the HAVING conditions, which usually appears in GROUP queries only.

Parameters:
Name Type Attributes Description
conditions
values <repeatable>
Example:
.having('average between ? and ?', 10, 20);
.having('maximum > 42');
.having({ count: 5 });

$ignoreIndex()

Example:
.ignoreIndex('idx_id')
.ignoreIndex('idx_id', 'idx_title_id')
.ignoreIndex('idx_id', { orderBy: ['idx_title', 'idx_org_id'] }, { groupBy: 'idx_type' })

$join(Model, onConditions, …values)

LEFT JOIN arbitrary models with specified ON conditions.

Parameters:
Name Type Attributes Description
Model
onConditions
values <repeatable>
Example:
.join(User, 'users.id = posts.authorId');
.join(TagMap, 'tagMaps.targetId = posts.id and tagMaps.targetType = 0');

$joinMany(Model, onConditions, …values)

LEFT JOIN an arbitrary model and mount all matching rows as a collection.

Parameters:
Name Type Attributes Description
Model
onConditions
values <repeatable>
Returns:

The current query with matching rows mounted as a collection.

Example:
.joinMany(Comment, 'comments.postId = posts.id');

$limit(rowCount)

Set the LIMIT of the query.

Parameters:
Name Type Description
rowCount

$offset(skip)

Set the OFFSET of the query.

Parameters:
Name Type Description
skip

$optimizerHints(…hints)

add optimizer hints to query

Parameters:
Name Type Attributes Description
hints <repeatable>
Example:
.optimizerHints('SET_VAR(foreign_key_checks=OFF)')
.optimizerHints('SET_VAR(foreign_key_checks=OFF)', 'MAX_EXECUTION_TIME(1000)')

$order(name, direction)

Set the ORDER of the query

Parameters:
Name Type Description
name
direction
Example:
.order('title');
.order('title', 'desc');
.order({ title: 'desc' });
.order('id asc, gmt_created desc')

$select(…names)

Whitelist columnAttributes to select. Can be called repeatedly to select more columnAttributes.

Parameters:
Name Type Attributes Description
names <repeatable>
Example:
.select('title');
.select('title', 'createdAt');
.select('IFNULL(title, "Untitled")');

$useIndex()

Example:
.useIndex('idx_id')
.useIndex('idx_id', 'idx_title_id')
.useIndex('idx_id', { orderBy: ['idx_title', 'idx_org_id'] }, { groupBy: 'idx_type' })

$where(conditions, …values)

Set WHERE conditions. Both string conditions and object conditions are supported.

Parameters:
Name Type Attributes Description
conditions
values <repeatable>

only necessary when using templated string conditions
Example:
.where({ foo: null });
.where('foo = ? and bar >= ?', null, 42);

$with(…qualifiers)

LEFT JOIN predefined associations in model.

Parameters:
Name Type Attributes Description
qualifiers <repeatable>
Example:
.with('attachment');
.with('attachment', 'comments');
.with({ comments: { select: 'content' } });

(async, generator) batch()

Get the query results by batch. Returns an async iterator which can then be consumed with an async loop or the cutting edge for await. The iterator is an Object that contains a next() method:

const iterator = {
  i: 0,
  next: () => Promise.resolve(this.i++);
}

See examples to consume async iterators properly. Currently async iterator is proposed and implemented by V8 but hasn't made into Node.js LTS yet.

Example:
async function consume() {
  const batch = Post.all.batch();
  while (true) {
    const { done, value: post } = await batch.next();
    if (value) handle(post);
    if (done) break
  }
}
// or
for await (const post of Post.all.batch()) {
   handle(post);
}

catch(reject)

Attach a rejection handler to the spell, just like Promise#catch.

Parameters:
Name Type Description
reject

called with the reason when the query rejects
Example:
Post.find({ title: 'x' }).catch(err => handle(err));

finally(onFinally)

Attach a callback invoked when the spell settles, whether it resolved or rejected, and return a new Promise, just like Promise#finally.

Parameters:
Name Type Description
onFinally

callback invoked after the query settles
Example:
await Post.find({ title: 'x' }).finally(cleanup);

(async) ignite()

Execute the query and resolve the result, applying all registered later callbacks in order. Awaiting a spell directly behaves the same as awaiting spell.ignite().

Returns:

the query result after all later callbacks

Example:
const post = await Post.first.ignite();

later(resolve)

Register a callback to transform the query result when the spell is ignited. Callbacks run in registration order and may return a Promise; the resolved value is passed to the next callback.

Parameters:
Name Type Description
resolve

transform applied to the query result
Example:
Post.first.later(post => post ? post.toJSON() : null)

then(resolve, reject)

Fake spell as a thenable object so it can be consumed like a regular Promise.

Parameters:
Name Type Description
resolve

called with the query result when the spell resolves
reject

called with the reason when the query rejects
Example:
const post = await Post.first
Post.last.then(post => handle(post));

toSqlString()

Format current spell to SQL string.

Example:
Post.find({ title: 'x' }).order('id', 'desc').limit(10).toSqlString();

toString()

Alias of Spell#toSqlString.

Example:
`${Post.find({ title: 'x' })}`