query-types.md

Query Types

Query Types are the base structure of a query. They are composed of various strings and query helpers. MoSQL queries themselves are objects that have a query-type. This allows for natural query combination in cases of sub-queries and expressions.

{
  type: 'create-table'
, table: 'users'
, ifNotExists: true
, definition: { /* ... */ }
}

// Sub-queries:
{
  type: 'select'
, with: {
    other_users: { type: 'select' /* ... */ }
  }
}

Listed here are the available query types out of the box with MoSQL. Use the type directive to select what type of query you want to use.

Type: 'select'

Performs a select query.

Definition:

{with} select {expression} {distinct} {columns} {over} {table} {alias}
{joins} {join} {innerJoin} {leftJoin} {leftOuterJoin} {fullOuterJoin} {crossOuterJoin}
{where} {groupBy} {having} {order} {limit} {offset} {for}

Helpers Used: with, expression, distinct, columns, over, table, alias, joins, join, innerJoin, leftJoin, leftOuterJoin, fullOuterJoin, crossOuterJoin, where, groupBy, having, order, limit, offset, for

Note: The where helper was sufficiently complex to warrant its own helper system.

Note: The use of join, innerJoin, leftJoin, etc is deprecated. Instead, use the joins helper instead.

Type: 'insert'

Performs an insert query.

Definition:

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

Helpers Used: with, table, columns, values, expression, returning, conflict

Type: 'update'

Performs an update query

Definition:

{with} update {table} {values} {updates} {from} {where} {returning}

Note: The updates helper was sufficiently complex to warrant its own helper system.

Helpers Used: with, table, values, updates, from, where, returning

Playground

Type: 'delete'

Performs a delete query.

Definition:

{with} delete from {table} {alias} {where} {returning}

Helpers Used: with, table, alias, where, returning

Type: 'remove'

Performs a delete query.

Definition:

{with} delete from {table} {alias} {where} {returning}

Helpers Used: with, table, alias, where, returning

Type: 'create-table'

Create table statement.

Definition:

{with} create table {ifNotExists} {table} ({definition}) {inherits}

Helpers Used: with, ifNotExists, table, definition, inherit

Note: The definition helper was sufficiently complex to warrant its own helper system.

Type: 'drop-table'

Drop table statement.

Definition:

{with} drop table {ifExists} {table} {cascade}

Helpers Used: with, ifExists, table, cascade

Type: 'alter-table'

Alter a table.

Definition:

alter table {ifExists} {only} {table} {action}

Helpers Used: ifExists, only, table, action

Note: The action helper was sufficiently complex to warrant its own helper system.

Playground

Type: 'create-view'

Create a view.

Definition:

create {orReplace} {temporary} view {view} {columns} as {expression}

Helpers Used: orReplace, temporary, view, columns, expression

Type: 'function'

Function expression.

Definition:

{function}( {expression} )

Helpers Used: function, expression

Note: If your query type is not defined, MoSQL assumes you meant to call a function whose name corresponds to the passed in query type.

Example:

// sum( users.id )
{
  type: 'sum'
, expression: 'users.id'
}

Playground

Type: 'expression'

Simply returns the result of the expression helper.

Definition:

{expression}

Helpers Used: expression

Example:

// select 1
{
  expression: 'select 1'
}

// ( hello world )
{
  expression: {
    parenthesis: true
  , expression: 'hello world'
  }
}

Type: 'union'

Combines multiple queries via the union operation. See queries helper for more information. Works the same as the intersect and except types.

Definition:

{queries}

Helpers Used: with, queries

Example:

{
  type: 'union'
, all: true
, queries: [
    { type: 'select', table: 'users' }
  , { type: 'select', table: 'other_users' }
  ]
};

Result:

select "users".* from "users" union all select "other_users".* from "other_users"

Type: 'intersect'

Combines multiple queries via the intersect operation. See queries helper for more information. Works the same as the union and except types.

Definition:

{queries}

Helpers Used: with, queries

Example:

{
  type: 'intersect'
, all: true
, queries: [
    { type: 'select', table: 'users' }
  , { type: 'select', table: 'other_users' }
  ]
};

Result:

select "users".* from "users" intersect all select "other_users".* from "other_users"

Type: 'except'

Combines multiple queries via the except operation. See queries helper for more information. Works the same as the intersect and union types.

Definition:

{queries}

Helpers Used: with, queries

Example:

{
  type: 'except'
, all: true
, queries: [
    { type: 'select', table: 'users' }
  , { type: 'select', table: 'other_users' }
  ]
};

Result:

select "users".* from "users" except all select "other_users".* from "other_users"

Adding Your Own Query Types

MoSQL uses the same interface as its API consumers to build functionality.

mosql.registerQueryType( name, definition )

Alias for mosql.queryTypes.add

var mosql = require('mongo-sql');

mosql.registerQueryType(
  'select-one-user'
, 'select {columns} from users {joins} {where} limit 1'
);

mosql.queryTypes.add( name, definition )

Registers a new query type.

var mosql = require('mongo-sql');

mosql.queryTypes.add(
  'select-one-user'
, 'select {columns} from users {joins} {where} limit 1'
);

mosql.sql({
  type: 'select-one-user'
, where: { id: 7 }
});

mosql.queryTypes.has( name )

Returns a boolean denoting whether or not a query type exists.

mosql.queryTypes.get( name )

Returns the query type definition string.

mosql.queryTypes.list

Returns queryType names as a string array.