queryhelper v0.2.2

QueryHelper

A simple script that helps you create valid SQL-Queries. Chain the functions together to create the Querystrings that you need without loosing track!

Install

npm install queryhelper

Usage

Create a query by simply invoking a new sql object:

const sql = require('queryhelper');

let query = new sql("table").select();

You can request the final Query-String by calling query.getQueryString()

Instantiate

When creating a new sql-Object pass over the tablename you want to query for.

// First Instantiation
let query = new sql("table");

SELECT

By simply calling .select() all Columns will be selected in the Query:

let query = new sql("table").select();

SELECT * FROM table;

SELECT specific columns

To select specific columns pass an Array containing the columnnames in the .select([columns]) function:

const columns = ["col1", "col2", "col3"];
let query = new sql("table").select(columns);

SELECT col1, col2, col3 FROM table;

SELECT DISTINCT

To perform a SELECT DISTINCT simply pass a second paramter that has a positive value into the .select([columns], distinct) function:

const columns = ["col1", "col2", "col3"];
let query = new sql("table").select(columns, true);

SELECT DISTINCT col1, col2, col3 FROM table;

Alias in SELECT

To use aliases, transform the Strings inside the Array into Objects having a column attribute and an alias attribute; you can also mix the columns:

const columns = ['col1',
{column: 'col2', alias: 'Fancy Name'},
{column: 'col3', alias: 'Unknown'}];
let query = new sql("table").select(columns);

SELECT col1, col2 AS 'Fancy Name', col3 AS 'Unknown' FROM table;

WHERE

To produce a WHERE simply use the .where(condition) function:

let query = new sql("table")
    .select()
    .where("condition > 5");

SELECT * FROM table WHERE condition > 5;

Chaining WHERE-clauses

To achieve multiple WHERE just chain multiple .where(condition, operator) where operator denotes the chain-condition. Valid operator-values are: AND, OR, !AND, !OR. The exclamation point causes the AND/OR to transform into AND/OR NOT:

let query1 = new SQL("table")
    .select()
    .where("condition > 5", "!")
    .where("col2=='Jimbo'", "AND")
    .where("counter < 10", "!OR");

SELECT * FROM table WHERE NOT condition > 5 AND col2=='Jimbo' OR NOT counter < 10;

WHERE IN

To make a WHERE IN use the appropriate .whereIn(column, in, operator) function. The in parameter can be either an Array containing values or another sql instance. The operator parameter is again used for chaining and can again accept following values: AND, OR, !AND, !OR :

// Using an Array of values
const values = ["val1", "val2"];
let query = new sql("table")
    .select()
    .whereIn("column", values);

SELECT * FROM table WHERE column IN ('val1'. 'val2');

// Using a subquery
let sub = new sql("anothertable").select(["column"]);
let query = new sql("table")
    .select()
    .whereIn("column", sub);

SELECT * FROM table WHERE column IN (SELECT column FROM anothertable);

WHERE ANY / ALL

To query with WHERE condition > ANY ... or WHERE condition > ALL ... use the appropriate .whereAny(condition, sub, operator) or .whereAll(condition, sub, operator) function. In those functions sub can only be of type sql. Do not forget to embed a comparison operator into the condition (like <, >, =, etc):

let sub = new sql("table").select(["column"]);
let query = new sql("table")
    .select()
    .whereAny("condition >", sub);

SELECT * FROM table WHERE condition > ANY (SELECT column FROM table);

JOINS

There are 4 functions to create joins: .i_join(table, matcher) to create an inner join, .l_join(table, matcher) to create a left join, .r_join(table, matcher) to create a right join and .f_join(table, matcher) to create a full outer join. Please check if your database supports Full Outer Joins (Tested under MySQL 5.7 --> Full Outer Join does not work!):

let query = new sql("table")
    .select()
    .l_join("table2", "table.id=table2.id");

SELECT * FROM table LEFT JOIN tabl2 ON table.id=table2.id;

ORDER BY

To add an ORDER BY statement simply chain the .order(columns) function, the parameter columns has to be an Array of strings containing the columns to order. You can add ASC order DESC to the Strings in the Array to change the order direction:

const order = ["col1 ASC", "col2 DESC"];
let query = new sql("table")
    .select()
    .order(order);

SELECT * FROM table ORDER BY col1 ASC, col2 DESC;

GROUPS and HAVING

GROUP BY and HAVING are also straightforward to apply by using the functions .group(columns) and .having(condition) where the columns parameter can be an Array or a single String and the condition parameter a string:

let query = new sql("table")
    .select()
    .group(["col1", "col2"])
    .having("col1 > 5");

SELECT * FROM table GROUP BY col1, col2 HAVING col1 > 5;

Functions AVG, MIN, MAX, COUNT, SUM

QueryHelper gives the opportunity to use the SQL-Functions SUM(), AVG(), COUNT(), MIN(), MAX() by using the appropriate lowercase functions from the sql-class. Chain these functions after a .select(), .where(), .order(), .group(), or .having() call to change the appropriate part of the query. For this, pass as first parameter the column that should be enclosed by the SQL-Function and as a second optional parameter an alias. You can also chain multiple SQL-Functions with different rows to extend this even more:

let query = new sql("table")
    .select(["col1", "col2"])
    .min("col1", "Minimum")
    .max("col2", "Maximum");

SELECT MIN(col1), MAX(col2) FROM table;

const columns = ["test", "users", "password", "useraccount", "users"];
const grouping = ["users", "useraccount"];
let query = new sql("table")
    .select(columns)
    .count("users")
    .group(grouping)
    .having("users > 5")
    .count("users");

SELECT test, COUNT(users), password, useraccount, users FROM table GROUP BY users, useraccount HAVING COUNT(users) > 5;

UNIONS

Currently it is only possible to UNION two subqueries together and receive the final UNION-Querystring. Simply apply .union(query, order, all) at the very end of the sql-chain. query is the second query that should be unioned with the first sql-object. order applies an additional ORDER BY to the union and the all parameter applies an "ALL" to the UNION if the parameter has a positive value:

let subquery = new sql("countries").select().where("continent=europe");
// running union causes to return the final string instead of the sql-object!!!
let query = new sql("test").select().union(subquery, "countries ASC", true);

(SELECT * FROM test) UNION ALL (SELECT * FROM countries WHERE continent='europe') ORDER BY countries ASC;

TOP

To achieve an SELECT TOP use .top(number, percentage) where the percentage parameter causes to add "PERCENT" to the top-string;

LIMIT and OFFSET

Limiting the query can be made by using the .limit(limit, offset) function.

.