Getting Started

Installation

gesundheit is installed via npm just as you’d expect:

npm install --save gesundheit

In addition to gesundheit itself, you will need to install any database drivers you plan on using:

npm install --save any-db-{mysql,postgres,sqlite3}

Creating a Query object

The main interface for building queries with gesundheit are the query manager classes. They provide an API designed to make most query building operations concise and fluent, while under the hood they manage an abstract syntax tree for the query.

Creating a query manager

All of the query managers are created with functions named after the query type that take a table (or alias) as their first parameter. To demonstrate we will create a simple select query:

select = require('gesundheit').select
departments = select('departments')

This creates a new SelectQuery query instance that generates the SQL string SELECT * FROM departments. To refine the field list we call SelectQuery.fields:

departments.fields('name', 'manager_id')

It’s important to note that all of the query manager methods modify the query in-place [1] so departments will now render to SELECT departments.name, departments.manager_id FROM departments.

Compiling & Executing

To turn the query object into a SQL string and array of bound parameters, we .compile the query:

assert.deepEqual(
  departments.compile(),
  [ 'SELECT name, manager_id FROM departments', [] ]
)

(there are no parameters to our query yet)

Most often you don’t really care about the SQL string and params themselves, but want result of performing the query on an actual database. In that case you simply use the .execute method:

query.execute(function (err, res) { console.log(err, res) })

“but...” you might be saying, “gesundheit can’t know how connect to my database all on it’s own!” and you are 100% correct. In order to execute against a real database the query must be bound to an Engine. Queries are bound to an engine when they are first created, and will rely on that engine when asked to render and/or execute. [2]

Using a real database

So far, we have been using the built-in fake engine, which does nothing but render SQL strings. In order to use a real database, we need to create our own engine object to use:

var gesundheit = require('gesundheit')

var db = gesundheit.engine('postgres://localhost/test')

The database URL above can point to any database supported by Any-DB, which includes MySQL, Postgres, and SQLite3.

The engine we just created (named db) is a query factory. We can create queries using select, insert, update or delete as methods:

var departments = db.select('departments', ['name', 'manager_id'])

Since it’s common to use only a single database in your application, you can set the global default engine for the module like so:

gesundheit.defaultEngine = db
# This is now equivalent to db.select(...)
gesundheit.select('departments', ['name', 'manager_id'])

Aliasing tables and fields

Any function that accepts a table or field parameter will accept a string, an instance of the appropriate AST node type, or an alias object. Alias objects are objects with a single key-value pair where the key is an alias name and the value is the object to be aliased. So the alias object {p: 'people'} will generate the SQL string people AS p. Here is an example of aliasing table and field names:

# SELECT manager_id AS m_id FROM departments AS d;
select({d: 'departments'}, [{m_id: 'manager_id'}])

(This example also shows passing a list of fields to :func:`~queries/index::SELECT` as the second parameter).

Conversely, if you are using SUDQuery.column to return a Column node, you can use Column.as to return an aliased version of the node:

var q = select('departments')
q.fields(q.c('manager_id').as('m_id'))

This also works with Relation and SqlFunction instances (as returned by sqlFunction).

Footnotes

[1]Use BaseQuery.copy if you want to generate multiple independent refinements from a single query instance.
[2]Actually, queries can be rebound with BaseQuery.bind, but this should only be used if you know what you’re doing and why.

A quick note on async, errors and throw

Gesundheit throws exceptions at pretty much every opportunity. The only time an error is returned to a callback or emitted via event emitter is when a query is actually executed. Any error that gesundheit can detect at query building time will cause an exception to be thrown. This keeps the query building API’s straightforward and synchronous, and means gesundheit can prevent your code from continuing to run with an obviously broken query.