monologue

Monologue - Streamlined query building

Breaking Changes for v0.3.0

Monologue now defaults to sanitizing strings inline as opposed to creating bound parameters. This option can be changed by passing { escape: false } into monologue().

The join function now defaults to INNER JOIN for parity with MYSQLs defaults

Install

npm install monologue

Usage

var monologue = require('monologue');

// Less than basic SELECT statement


// call the SQL wrappers in any order, see below: where, group, where, order
var mono = monologue().select( "*", "users")
    .where( { "id": [1,2,3,4,5,6] } ) // alternative to where("id").in([...])
    .where( 'date_time' ).between( '2012-09-12', '2013-01-20')
    .group( ['type', 'hamster' ] )
    .where( "name", "OR" ).like("ro%en") // out of order, also passing "OR" as separator
    .order( "id" )
    .limit( '300', 1000 )
    .query();

console.log( mono.sql );
// output: SELECT * FROM users WHERE id IN (1,2,3,4,5,6) AND date_time BETWEEN '2012-09-12' AND '2013-01-20' OR name LIKE 'ro%en' GROUP BY type, hamster ASC ORDER BY id ASC LIMIT 1000, 300


// JOIN (default is inner):
// SELECT * FROM users u INNER JOIN posts p ON p.user_id = u.id WHERE category = '67'

monologue().select( "*", "users u" )
    .join( "posts p", "p.user_id = u.id" )
    .where( { "category": "67" } )
    .query().sql;


// JOIN (LEFT, as argument):
// SELECT * FROM users u LEFT JOIN posts p ON p.user_id = u.id WHERE category = '67'

monologue().select( "*", "users u" )
    .join( "LEFT", "posts p", { "p.user_id": "u.id" } )
    .where( { "category": "67" } )
    .query().sql;


// SELECT into outfile: the third param (OPTIONALLY ENCLOSED BY) is, as stated, optional. Just pass in the line ending and leave the 4th param out, the rest will be taken care of
// output: SELECT * FROM users WHERE company = 'general motors' INTO OUTFILE '/tmp/datafile' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'


monologue().select( "*", "users" )
    .where( { "company": "general motors" } )
    .file( "/tmp/datafile", ",", '"', "\\n" )
    .query().sql;


// SELECT into outfile: without third param
// output: SELECT * FROM users WHERE company = 'general motors' INTO OUTFILE '/tmp/datafile' FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n'

monologue().select( "*", "users")
    .where( { "company": "general motors" } )
    .file( "/tmp/datafile", ",", "\\n" )
    .query().sql;


// INSERT, passing an array of objects
// output: INSERT INTO users (first_name, password, username) VALUES ('me','1234','test'),('pasta','abcd','example')

monologue().insert( 'users', [
    { username: 'test', password: '1234', first_name: 'me' },
    { username: 'example', password: 'abcd', first_name: "pasta" }
] ).query().sql;


// INSERT, passing a single object
// output: INSERT INTO users (first_name, password, username) VALUES ('cubert','abcd','me')

monologue().insert( 'users', { username: 'me', password: 'abcd', first_name: "cubert" } ).query().sql;


// UPDATE
// output: UPDATE users SET email = '[e-mail]', password = 'abcdefg', username = 'yoyo' WHERE id = 23

monologue().update( "users", {username: "yoyo", email: '[e-mail]', password: "abcdefg"} ).where( {id: 23} ).query().sql;


// DELETE
// output: DELETE FROM users WHERE first_name = 'me' AND password = '1234' AND username = 'test'

monologue().delete( 'users', { username: 'test', password: '1234', first_name: "me" } ).query().sql;

// UNION
// Wrappers can be out of order BEFORE the UNION statement,
// wrappers after will be applied to the secondary statment
// output: SELECT username, email FROM users WHERE company_id = '1234' UNION SELECT screename, email_address FROM app_users WHERE company = 'coName'
monologue().select('username, email', 'users').where({"company_id": "1234"}).union('screename, email_address', 'app_users').where({"company":"coName"}).query().sql