q2s v0.0.2

q2s

Simple tool for parsing query strings into complete SQL statements

Us this to translate either raw or parsed URL query strings into SQL commands. With q2s you will be able to build complex filters with just one step.

Installation

$ npm install q2s

Quick example

With raw query strings:

const q2s = require('q2s');

const query2sql = new q2s();

const query = '?name=John&lastname=Doe&*select=name,lastname,birthDate,address&*order=name';

const sql = query2sql.parse(query).sql('customers');

console.log(sql);
//=> "SELECT `name`, `lastname`, `birthDate`, `address` FROM customers WHERE `name`='John' AND `lastname`='Doe' ORDER BY `name` ASC"

With parsed query strings (e.g. using req.query from express.js):

// -- idem --

const query = {
  "name": "John",
  "lastname": "Doe",
  "*select": "name,lastname,birthDate,address",
  "*order": "name"
};

const sql = query2sql.parse(query).sql('customers');

console.log(sql);
//=> "SELECT `name`, `lastname`, `birthDate`, `address` FROM customers WHERE `name`='John' AND `lastname`='Doe' ORDER BY `name` ASC"

Documentation

• API
• Advanced use
  • WHERE clause
  • ORDER BY clause
  • SELECT clause

API

.parse(query)

Adds/resets the query and parses it

Parameters

• query

  (string|object)(Optional)

  Default value: ''

Return

(q2s) this

.sql(table)

Returns a SQL query or token. If table is ommited, returns just the SQL WHERE clause.

*Warning: if table is ommited and no filter is included, it will throw an error

Parameters

• table

  (string)(Optional)

  Default value: ''

Return

(string) SQL query

Advanced use

q2s allows you to build complex SQL queries with almost all the rules included in the language

WHERE clause

Condition operators

A part from the usual search using the 'equal' (=) operator, SQL includes other operators. Here's a list of them and how to build your query string to use them:

• = (equal): '?column=value'
• <> or != (not equal): '?column=!_value'
• > (greater than): '?column=>>_value'
• < (less than): '?column=<<_value'
• >= (greater than or equal): '?column=>_value'
• <= (less than or equal): '?column=<_value'
• BETWEEN (between an inclusive range): '?column=minValue<x<maxValue'
• LIKE (search for a pattern): '?column=~_pattern'

Examples

'equal', 'not equal', 'greater than', 'less than', etc:

let sql;

// Equal
sql = query2sql.parse('?lastname=Doe').sql('customers');
console.log(sql);
//=> "SELECT * FROM customers WHERE `lastname`='Doe'"

// Not equal
sql = query2sql.parse('?lastname=!_Doe').sql('customers');
console.log(sql);
//=> "SELECT * FROM customers WHERE `lastname`!='Doe'"

// Greater than
sql = query2sql.parse('?age=>>_35').sql('customers');
console.log(sql);
//=> "SELECT * FROM customers WHERE `age`>35"

// Less than
sql = query2sql.parse('?age=<<_35').sql('customers');
console.log(sql);
//=> "SELECT * FROM customers WHERE `age`<35"

// Greater than or equal
sql = query2sql.parse('?age=>_35').sql('customers');
console.log(sql);
//=> "SELECT * FROM customers WHERE `age`>=35"

// Less than or equal
sql = query2sql.parse('?age=<_35').sql('customers');
console.log(sql);
//=> "SELECT * FROM customers WHERE `age`<=35"

Inclusive range:

sql = query2sql.parse('?age=25<x<35').sql('customers');
console.log(sql);
//=> "SELECT * FROM customers WHERE `age` BETWEEN 25 AND 35"

Pattern match:

// Accepts any valid SQL pattern
sql = query2sql.parse('?email=~_%gmail.com').sql('customers');
console.log(sql);
//=> "SELECT * FROM customers WHERE `age` LIKE 'gmail.com%'";

Combination operators

By default, q2s will combine all the conditions in the WHERE clause with the 'AND' operator. To change that behavior, use the modifier or_ or not_ in the query string.

Examples

sql = query2sql.parse('?name=John&lastname=or_Doe&age=not_>>_35').sql('customers');
console.log(sql);
//=> "SELECT * FROM customers WHERE `name`='John' OR `lastname`='Doe' NOT `age`>35";

Condition grouping

To group de conditions inside the WHERE clause, use the (_ or _) modifiers in the query string.

Examples

sql = query2sql.parse('?name=John&(_lastname=Doe&lastname=or_Smith_)').sql('customers');
console.log(sql);
//=> "SELECT * FROM customers WHERE `name`='John' AND ( `lastname`='Doe' OR `lastname`='Smith' )";

ORDER BY clause

To sort de result-set you should include a parameter 'order' in your query string.

• Tip: In case there's a column in your table with the name 'order', use a precceding asterisk ('*order').

Examples

Default behavior (ascending sorting):

sql = query2sql.parse('?name=John&lastname=Doe&*order=age,name').sql('customers');
console.log(sql);
//=> "SELECT * FROM customers WHERE `name`='John' AND `lastname`='Doe' ORDER BY `age` ASC, `name` ASC"

Specifying sorting direction:

sql = query2sql.parse('?name=John&lastname=Doe&*order=age desc,name asc').sql('customers');
console.log(sql);
//=> "SELECT * FROM customers WHERE `name`='John' AND `lastname`='Doe' ORDER BY `age` DESC, `name` ASC"

SELECT clause

By default, q2s will select all columns (*) in the table. However, you can change this behavior including a parameter 'select' in your query string.

• Tip: In case there's a column in your table with the name 'select', use a precceding asterisk ('*select').

Examples

Include just the filtered columns (use the keyword 'filters'):

sql = query2sql.parse('?name=John&age=>>25&*select=filters').sql('customers');
console.log(sql);
//=> "SELECT `name`, `age` FROM customers WHERE `name`='John' AND `age`>25"

Include given columns:

sql = query2sql.parse('?name=John&age=>>25&*select=name,lastname,age,email,address').sql('customers');
console.log(sql);
//=> "SELECT `name`, `lastname`, `age`, `email`, `address` FROM customers WHERE `name`='John' AND `age`>25"