docs

Documentation

MoSQL is largely based on helper registration. Most functionality is achieved through simple single-purpose functions. As such, this documentation primarily focuses on the different types of MoSQL helpers:

• Query Types
• Query Helpers
• Conditional Helpers
• Update Helpers
• Column Definitions
• Actions

Making a Query

There are two main components to building a query:

• Selecting a query type
• Filling in the details with query helpers

{
  type: 'insert' // <- Your query type
  /* ... */
}

Our query type is insert. Looking at the type definition we see the following available helpers denoted by brackets:

{with} insert into {table} {columns} {values} {expression} {returning}

Specifying a helper in your query will run the corresponding helper function and replace the {helper} with the result of the function.

// => insert into "users" ("name", "hobbies") values ($1, $2)
{
  type: 'insert'
, table: 'users'
, values: {
    name: 'Bob'
  , hobbies: ['Baking', 'Skiiing', 'LARPing']
  }
}

Some helpers will accept sub-queries. In this way, queries can easily be composed:

// Insert with values from a select
{
  type: 'insert'
, table: 'users'
, columns: [ 'name', 'email' ]
, expression: {
    type: 'select'
  , table: 'other_users'
  , columns: [ 'name', 'email' ]
  , where: { id: 7 }
  }
}

If you need to cast a column to some other type, that is also possible:

{
  type: 'select'
, table: 'users'
, where: { 'some_id::int': 7 }
}

Access JSON and HStore fields

See this document.

Root API

mosql.sql( query, [values] )

Convert a mosql query object to mosql query result interface object of the structure:

{
  query       // The resulting sql string
, values:     // The array of parameterized values
, toString(): // Function for returning the sql string
, toQuery():  // Function for returning the sql string
}

The two functions toString and toQuery are convenience methods for other librarlies like node-pg.

mosql.registerQueryType( name, definition )

See query type docs

mosql.registerQueryHelper( name, [options], callback )

See query helper docs

mosql.conditionalHelpers.add( name, [options], callback )

See conditional helper docs

mosql.columnDefinitions.add( name, callback )

See column definition docs

mosql.registerActionHelper( name, callback )

See action helpers docs

mosql.registerUpdateHelper( name, [options], callback )

See update helpers docs

mosql.quoteObject( field[, table[, schema[, database]]] )

Returns sql quoted column or object string string

Examples:

mosql.quoteObject('name')                             // => "users"
mosql.quoteObject('name', 'users')                    // => "users"."name"
mosql.quoteObject('name', 'users', 'person', 'my_db') // => "my_db"."person"."users"."name"
mosql.quoteObject('my_db.person.users.name')          // => "my_db"."person"."users"."name"
mosql.quoteObject('users.name')                       // => "users"."name"
mosql.quoteObject('*', 'users')                       // => "users".*
mosql.quoteObject('users.*')                          // => "users".*
mosql.quoteObject('users.data::json->id')             // => "users"."data"::json->'id'