Spell

Spell

Spell is the query builder of Leoric which has several important charactors 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();
cosnt 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 everytime 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 Model

A sub class of Bone.
opts Object

Extra attributes to be set.

Members

dup

Get a duplicate of current spell.

Model

A sub-class of Bone.

Methods

$delete() → {Spell}

Set query command to DELETE.

Returns:
Type:
Spell

$forceIndex(…hints) → {Spell}

Parameters:
Name Type Attributes Description
hints string | object <repeatable>
Returns:
Type:
Spell
Example:
.forceIndex('idx_id')
.forceIndex('idx_id', 'idx_title_id')
.forceIndex('idx_id', { orderBy: ['idx_title', 'idx_org_id'] }, { groupBy: 'idx_type' })

$from(table) → {Spell}

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 string | Spell
Returns:
Type:
Spell

$get(index) → {Bone}

Get nth record.

Parameters:
Name Type Description
index number
Returns:
Type:
Bone

$group(…names) → {Spell}

Set GROUP BY attributes. 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 string <repeatable>
Returns:
Type:
Spell
Example:
.group('city');
.group('YEAR(createdAt)');

$having(conditions, …values) → {Spell}

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

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

$ignoreIndex(…hints) → {Spell}

Parameters:
Name Type Attributes Description
hints string | object <repeatable>
Returns:
Type:
Spell
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) → {Spell}

LEFT JOIN arbitrary models with specified ON conditions.

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

$limit(rowCount) → {Spell}

Set the LIMIT of the query.

Parameters:
Name Type Description
rowCount number
Returns:
Type:
Spell

$offset(skip) → {Spell}

Set the OFFSET of the query.

Parameters:
Name Type Description
skip number
Returns:
Type:
Spell

$optimizerHints(…hints) → {Spell}

add optimizer hints to query

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

$order(name, direction) → {Spell}

Set the ORDER of the query

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

$select(…names)

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

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

$useIndex(…hints) → {Spell}

Parameters:
Name Type Attributes Description
hints string | object <repeatable>
Returns:
Type:
Spell
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) → {Spell}

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

Parameters:
Name Type Attributes Description
conditions string | Object
values * <repeatable>

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

$with(…qualifiers) → {Spell}

LEFT JOIN predefined associations in model.

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

(async, generator) batch(size) → {Object}

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.

Parameters:
Name Type Default Description
size number 1000
Returns:
Type:
Object
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)

Parameters:
Name Type Description
reject function

finally(onFinally)

Parameters:
Name Type Description
onFinally function

nodeify(callback)

  • https://nodejs.org/en/knowledge/errors/what-are-the-error-conventions/
Parameters:
Name Type Description
callback function

then(resolve, reject)

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

Parameters:
Name Type Default Description
resolve function null
reject function
Example:
const post = await Post.first
Post.last.then(post => handle(post));

toSqlString() → {string}

Format current spell to SQL string.

Returns:
Type:
string

toString() → {string}

Alias of Spell#toSqlString

Returns:
Type:
string