Query Building API reference

The factory functions defined here create instances of the corresponding *Query manager classes, which fit the following inheritance hierarchy:

  • BaseQuery
    • InsertQuery
    • SUDQuery
      • SelectQuery
      • UpdateQuery
      • DeleteQuery

These functions are the same ones re-exported by the main gesundheit module (where they bind queries to gesundheit.defaultEngine), and attached to engine/transaction objects (where they bind queries to the engine/transaction they are called on).

Each one accepts a table name or alias object (anything handled by toRelation), one or more extra parameters, and an optional visitor callback as a final parameter that will be called with it’s this context set to the newly constructed query instance.

function INSERT(table, fieldsOrRow)

Create a new InsertQuery that will add rows to table.

Parameters:
  • table – Name of the table that rows will be inserted into.
  • fieldsOrRow – Either an array of column names that will be inserted, or a plain object representing a row of data to insert, in which case the keys of the object will define the columns that are inserted.
  • visitor – (Optional) a function that will be called with it’s context set to the newly constructed query object.

Warning

The column names passed in fields will be printed into the query unescaped so do not use arbitrary input here. This is hopefully only temporary.

function SELECT(table, fields)

Create a new SelectQuery selecting from table.

Parameters:
  • table – Name or alias object of the first table to select rows from. More tables can be joined using SelectQuery.join.
  • fields – (Optional) Fields to project from table. If this is not given, all fields (*) will be projected until SelectQuery.fields is called.
  • visitor – (Optional) a function that will be called with it’s context set to the newly constructed query object.
function UPDATE(table)

Create a new UpdateQuery that will update table.

Parameters:
  • table – Name or alias of the table to update.
  • visitor – (Optional) a function that will be called with it’s context set to the newly constructed query object.
function DELETE(table)

Create a new DeleteQuery that will delete rows from table.

Parameters:
  • table – Name or alias of the table to delete rows from.
  • visitor – (Optional) a function that will be called with it’s context set to the newly constructed query object.
function exports.mixinFactoryMethods(invocant, getEngine)

Add wrappers methods for each of the query factory functions to invocant using lower, UPPER, and Camel cases. The new methods will retrieve an engine using getEngine and then create the query bound to that engine.

If getEngine is not given, queries will be bound to invocant instead.

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

Examples

Insert a single row:

var insert = require('gesundheit').insert

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

Add multiple rows to a single insert:

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

API

class InsertQuery extends BaseQuery

Insert queries are much simpler than most query types: they cannot join multiple tables.

method InsertQuery.addRows(rows, variadic)

Add multiple rows of data to the insert statement.

method InsertQuery.addRow(row)

Add a single row

method InsertQuery.from(query)

Insert from a select query.

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

Examples

Start a select query with SELECT:

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

Join another table with SelectQuery.join:

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

Note that joining a table “focuses” it, so .where({sex: 'M'}) refers to the people.sex column. You can avoid this implicit behaviour by using full column names (e.g. 'chairs.id') or switching focus back to a previous table using SelectQuery.focus:

men_with_light_recliners.focus('chairs')

Lets order the results by chairs.weight and get the top 5:

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

The entire query can also be written without needing a temp variable by using the third parameter to select (a callback function that will be passed to BaseQuery.visit):

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)
})

API

class SelectQuery extends SUDQuery

Adds a number of SELECT-specific methods to SUDQuery, such as fields and groupBy

method SelectQuery.fields(fields)

Adds one or more fields to the query. Fields can be strings (in which case they will be passed to SUDQuery.column) or pre- constructed nodes. (Such as those returned by column).

If no fields are given, clears all fields from the currently focused table.

To alias a field, use an object with a single key where the key is the alias name and the value is a string or node:

q.fields({employee_name: 'employees.name'})
method SelectQuery.func(fun, args)

Adds a SQL function to the column list for the query. This can be an aggregate function if you also use groupBy.

Parameters:
  • fun – name of SQL function.
  • args – arguments that will be passed to the function. Any argument that is not a Node object will be converted into a bound parameter.

Example:

# SELECT count(id) FROM t1
select('t1', function (q) { q.func('count', q.c('id')) })

# SELECT count(id) AS "counter" FROM t1
select('t1', function (q) { q.func({counter: 'count'}, q.c('id')) })
method SelectQuery.distinct(bool)

Make this query DISTINCT on all fields.

method SelectQuery.join(table, opts)

Join another table to the query.

Parameters:
  • table – A table name, or alias literal. An error will be thrown if the table/alias name is not unique. See toRelation for more information on the many things table could be.
  • opts.on – An object literal expressing join conditions. See where for more.
  • opts.type – A join type constant (e.g. INNER, OUTER)
  • opts.fields – Columns to be selected from the newly joined table.
  • opts.prefixFields

    If this is a string, it will be used to prefix the field names given in opts.fields. If this is a boolean true then the joined table (or alias) name plus an underscore will be used as the prefix. For example:

    query.join({p: 'people'}, {
      on: query.c('people.dep_id').eq(query.c('dep.id')),
      fields: ['first_name', 'last_name'],
      prefixFields: true
    })
    

    Will alias people.first_name AS p_first_name.

method SelectQuery.ensureJoin(table, opts)

The same as join, but will only join tbl if it is not joined already.

method SelectQuery.focus(alias)

Make a different table “focused”, this will use that table as the default for the fields, order and where methods.

Parameters:
  • alias – The table/alias name to focus. If the table or alias is not already part of the query an error will be thrown.
method SelectQuery.groupBy(fields)

Add a GROUP BY to the query.

method SelectQuery.having(constraint)

This method works similarly to SUDQuery.where, but the constraints are added the HAVING portion of a SQL clause.

Update

Examples

Updating rows that match a condition:

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

API

class UpdateQuery extends SUDQuery

The update query is a little underpowered right now, and can only handle simple updates of a single table.

method UpdateQuery.set(data)

Add fields to the SET portion of this query.

Parameters:
  • data – An object mapping fields to values. The values will be passed to toParam to be converted into bound paramaeters.
method UpdateQuery.setNodes(nodes)

Directly push one or more nodes into the SET portion of this query

Delete

Examples

Delete all rows that match a condition:

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

API

class DeleteQuery extends SUDQuery

Delete queries only add a ‘returning’ method to SUDQuery