mockingbird-sql

SQL statement builder

NAME

mockingbird-sql - A simple SQL statement builder

SYNOPSIS

const sql = require('mockingbird-sql');

let s = sql.select().columns([
  'ColA',
  'ColB',
  'ColC',
]).from('tableA')
  .leftOuterJoin('tableB', 'tableA.ColB = tableB.ColB', 'aliasB')
  .where(
    sql.and(
      'tableA.ColA like \'%value%\'',
      sql.lt('aliasb.ColX', 27)
    )
  ).orderBy('ColA');

let query = sql.mysql.MySql.toSql(s);

// query.sql    => SQL statement
// query.values => list of arguments

DESCRIPTION

This module exports several kinds of functions useful for building complex SQL queries programmatically.

The major class are constructors for SQL statement objects. Another is functions for building SQL expressions. The third is classes for converting the statement objects into SQL suitable for passing to database drivers in order to execute the query.

SQL Statement Objects

Currently, two statements are supported: SELECT and UNION. Others can be added as necessary.

SELECT

Constructor: module.select(): SELECT statement object

Methods:

All methods return the this object, to allow chaining.

• s.columns( columns )

• s.distinct( bool )

• s.from( table, alias )

• s.join( table, on, _alias )

• s.where( expression )

  • s.whereAnd( expression )

  • s.whereOr( expression )

• s.groupBy( column )

• s.orderBy( column, direction )

• s.limit( rows )

• s.offset( _rows )

• s.window( offset, limit )

UNION

Constructor: module.union(): UNION statement object

Methods:

• u.add( statements )

• u.orderBy( column, direction )

• u.limit( rows )

• u.offset( _rows )

• u.window( offset, limit )

Expressions

Logical operators

• module.and( expression, expression )

• module.or( expression, expression )

• module.not( expression, expression )

Unary operators

• module.isNull( expression )

• module.isNotNull( expression )

Binary operators

• module.op( column, value )

Operators are:

• eq
• neq
• gt
• lt
• gteq
• lteq
• like
• in

Trinary Operators

• module.between( column, left-value, right-value )

• module.notBetween( column, left-value, right-value )

Case operator

• module.case( expression )

The Case object has a number of methods:

• c.when( expression, result = null )

  If result is null, use the then method below to specifiy the result of the branch.

• c.then( result )

• c.else( result )

Conversion

This module is intended to support any (semi-) standardized SQL. Currently, it supports MySQL. The currently supported options are:

MySQL

Conversion of a generated statement to MySQL is handled by the mysql.MySQL object. Methods on this object are:

• mysql.MySQL.toSql( statement )

  This method returns an object containing two keys:

  • sql: A string representation of the SQL query, with placeholders for parameters.
  • values: A list of arguments for the query.

Oracle

Conversion to Oracle SQL is handled by oracle.Oracle object. The methods are the same as MySQL above.