Query Building API reference¶

BaseQuery¶

class BaseQuery extends EventEmitter¶

The base class for all queries. While this class itself is not part of gesundheits public API, the methods defined on it are.

method BaseQuery.constructor(engine, opts)¶

Parameters:	engine – The engine the query will be bound to. opts.table – Any object that can be converted by toRelation.

method BaseQuery.copy(fn)¶

Instantiate a new query with a deep copy of this ones AST

method BaseQuery.visit(fn)¶

Call the given function in the context of this query. This is useful with query factory functions where you can use it as a sort-of-DSL:

SELECT('people', ['name'], function(q) {
  // this === q
  this.join('addresses', {
    on: {person_id: q.project('people', 'id')},
    fields: ['city', 'region']
  })
})

method BaseQuery.bind(engine)¶

Bind this query object to a new engine. If no argument is given the query will be bound to the default engine.

method BaseQuery.render()¶

Render the query to a SQL string.

method BaseQuery.compile()¶

Compile this query object, returning a SQL string and parameter array.

method BaseQuery.execute(cb)¶

Execute the query using @engine and return a QueryAdapter.

Parameters:	cb – An (optional) node-style callback that will be called with any errors and/or the query results. If no callback is given, an AnyDB Query will be returned.

Insert¶

var insert = require('gesundheit').insert

insert('people', {name: 'Jerry'}).execute(function (err, result) {
    // Handle err
})

var q = insert('people', ['name'])
q.addRows([
  ['Jerry'],
  ['Joey'],
  ['Jimbob']
])
q.execute(function (err, result) {
    // Handle err
})

SUDQuery¶

class SUDQuery extends BaseQuery¶

SUDQuery is the base class for SELECT, UPDATE, and DELETE queries. It adds logic to BaseQuery for adding WHERE clauses, projecting columns, ordering, limits, and offsets.

method SUDQuery.where(constraint)¶

Adds a WHERE clause to the query. This method accepts wide range of input that can express very complex constraints. The examples below assume we are starting with this simple select query: q = select('t1')

The first kind of constraint is a comparison node as produced by the ComparableMixin methods on projected fields:

q.where(q.c('field1').eq(42))
q.where(q.c('field2').gt(42))
# WHERE t1.field1 = 42 AND t1.field2 > 42

We used an implied table name above, which is always the last table added to the query or focused with SUDQuery.focus. If you want to specify constraints on multiple tables at once (or just be more explicit) you can also specify the relation for a field by prepending it to the field name (e.g. q.c('t1.field1'). See SUDQuery.project for details.

The second kind of constraint is an object literal where each key is a field name and each value is a constraint. The last example expressed as a literal object looks like this:

q.where({field1: 42, field2: {gt: 42}})
# WHERE t1.field1 = 42 AND t1.field2 > 42

Internally this constructs the comparison nodes for you using a simple transformation: each key is passed to project (meaning you can specify the relation name as part of the key if you so desire) and each value is either used as the argument to ComparableMixin.eq or (in the case of object literals) converted into one or more calls to the corresponding comparison methods.

To compare two fields, use a projection as the value to be compared:

p = q.project.bind(q, 't1')
q.where({field1: {gt: p('field2')}})
# WHERE t1.field1 > t1.field2

If you use either of the special keys 'and' or 'or' in an object, the value will be treated as a nested set of constraints to be joined with the corresponding SQL operator. This process is recursive so you can nest constraints arbitrarily deep:

q.where({or: {a: 1, and: {b: 2, c: 3}}})
# WHERE (t1.a = 1 OR (t1.b = 2 AND t1.c = 3))

You can also acheive the same effect by chaining method calls on comparison nodes:

a = q.c('a')
b = q.c('b')
c = q.c('c')
q.where(a.eq(1).or(b.eq(2).and(c.eq(3))))
# WHERE (t1.a = 1 OR (t1.b = 2 AND t1.c = 3))

If you have the need to mix both styles (or simply find it more readable, You can use an array of constraints as the value for 'or' or 'and':

q.where({or: [{a: 1}, b.eq(2).and(c.eq(3))]})

Note that currently you cannot pass an object literal to the .and and .or methods:

# Will not work!!
q.where(a.eq(1).or({b: 2, c: 3}))

Finally, there are also shortcut methods SUDQuery.and and SUDQuery.or that treat multiple arguments like an array of constraints.

method SUDQuery.or(clauses)¶

Shortcut for .where({or: clauses})

method SUDQuery.and(clauses)¶

Shortcut for .where({and: clauses})

method SUDQuery.order(args)¶

Add one or more ORDER BY clauses to the query.

Each ordering can either be a string, in which case it must be a valid-ish SQL snippet like ‘some_table.some_field DESC’, (the field name and direction will still be converted to AST nodes) or an object, in which case each key will be treated as a field and each value as a direction.

method SUDQuery.limit(l)¶

Set the LIMIT on this query

method SUDQuery.offset(l)¶

Set the OFFSET of this query

method SUDQuery.project()¶

Backwards compatible alias for SUDQuery.column

method SUDQuery.column(relation, field)¶

Return a Column node representing <relation>.<field>.

The first argument is optional and specifies a table or alias name referring to a relation already joined to this query. If you don’t specify a relation, the table added or focused last will be used. Alternatively, you can specify the relation name and field with a single dot-separated string:

q.column('departments.name') == q.column('departments', 'name')

The returned object has a methods from ComparableMixin that create new comparison nodes usable in join conditions and where clauses:

# Find developers over the age of 45
s = select('people', ['name'])
s.join('departments', on: {id: s.column('people', 'department_id')})
s.where(s.column('departments', 'name').eq('development'))
s.where(s.column('people', 'age').gte(45))

project is also aliased as p for those who value brevity:

q.where(q.c('departments.name').eq('development'))

Note

this means you must specify a relation name if you have a field name with a dot in it, if you have dots in your column names, sorry.

method SUDQuery.rel(alias)¶

A shorthand way to get a relation by (alias) name

Select¶

var select = require('gesundheit').select
var lightRecliners = select('chairs', ['chair_type', 'size'])
  .where({chair_type: 'recliner', weight: {lt: 25}})

var malesWithLightRecliners = lightRecliners.copy()
  .join("people", {
    on: {chair_id: light_recliners.column('chairs.id')},
    fields: ['name']
  })
  .where({sex: 'M'})

men_with_light_recliners.focus('chairs')

men_with_light_recliners
  .order(weight: 'ASC)
  .limit(5)

men_with_light_recliners = select 'chairs', ['chair_type', 'size'], function (q) {
  q.where({chair_type: 'recliner', weight: {lt: 25}})
  q.join("people", {
    on: {chair_id: q.column('chairs.id')},
    fields: ['name']
  })
  q.where({gender: 'M'})
  q.order({'chairs.weight': 'ASC'})
  q.limit(5)
})

Update¶

update('tweeters')                # UPDATE tweeters
  .set({influential: true})       # SET tweeters.influential = true
  .where({followers: {gt: 1000}}) # WHERE tweeters.followers > 1000;
  .execute(function (err, res) { /* ... */ })

Delete¶

# DELETE FROM tweeters WHERE tweeters.followers < 10
delete('tweeters').where(followers: lt: 10)