[![NPM Version][npm-version-image]][npm-url] [![NPM Downloads][npm-downloads-image]][npm-url] [![Node.js Version][node-image]][node-url] [![Build Status][travis-image]][travis-url]

Universal QueryBuilder for Node.js

Node-QueryBuilder is an ambitious attempt to create a kind of "universal translator" which provides programmers a consistent API to connect to and query any database (traditional and NoSQL) supported by the module. The module is highly extensible and, in theory, can suppport any database provided that the driver has been written for it.

The API of this module very closely mimics Codeigniter's Active Record (now called "Query Builder") library and much of the code has been directly translated from the PHP libraries in Codeigniter to JavaScript. A lot of credit needs to go to the folks over at EllisLab (https://ellislab.com/codeigniter) and all the contributors to the Codeigniter project (of which I am one): https://github.com/EllisLab/CodeIgniter/

The primary benefits of this module (currently) are:

• Ability to write queries agnostically to the database you intend to query
• Supports all basic database commands (insert, update, delete, select, etc...)
• Extend capabilities from the most popular native database drivers in NPM.
• Supports method chaining
• Automatically escapes field values and identifiers by default
• Is fully unit tested
• Very thoroughly documented
• Allows for greater flexibility and more control over a full ORM
• Lighter-weight than an ORM
• Allows you to drop down to the native methods of your driver if you choose to
• Allows for different drivers for different versions (SQLite 2 vs SQLite 3)
• The order in which you call the methods is irrelevant except for the execution methods (get, insert, update, delete) which must be called last.
• Can used as a learning tool/Rosetta stone

Table of Contents

• Database Drivers

• How to Install

• License Info

• Quick Example

• Connecting to Your Database

  • Quick Reference
  • Standard Connection Settings
  • Choosing the Database Type
  • Choosing the Connection Type
  • Managing Connections

• API Methods

  • Chainable Methods
  • Execution Methods
    • What Are Execution Methods?
    • Handling Error Messages and Results
    • Response Format Examples
  • Other Library-Specific Methods

• Contribute

Database Drivers

Currently Written:

• MySQL / MariaDB
• Microsoft SQL Server

Coming Soon:

• Postgres
• Oracle
• SQLite
• MongoDB

How to install

npm install node-querybuilder

License Info

Licensed under the GPL license and MIT:

• http://www.opensource.org/licenses/GPL-license.php
• http://www.opensource.org/licenses/mit-license.php

Quick Example

This quick example shows how to connect to and asynchronously query a MySQL database using a pooled connection.

1const QueryBuilder = require('node-querybuilder');
2const settings = {
3    host: 'localhost',
4    database: 'mydatabase',
5    user: 'myuser',
6    password: 'MyP@ssw0rd'
7};
8const pool = new QueryBuilder(settings, 'mysql', 'pool');
9
10pool.get_connection(qb => {
11    qb.select('name', 'position')
12        .where({type: 'rocky', 'diameter <': 12000})
13        .get('planets', (err, response) => {
14            qb.disconnect();
15
16            if (err) return console.error("Uh oh! Couldn't get results: " + err.msg);
17
18            // SELECT `name`, `position` FROM `planets` WHERE `type` = 'rocky' AND `diameter` < 12000
19            console.log("Query Ran: " + qb.last_query());
20
21            // [{name: 'Mercury', position: 1}, {name: 'Mars', position: 4}]
22            console.log("Results:", response);
23        }
24    );
25});

Anywhere a callback is used in the examples below, you can substitute for a Promise (or async/await). Here's the same code above in Promise format:

1const QueryBuilder = require('node-querybuilder');
2const settings = {
3    host: 'localhost',
4    database: 'mydatabase',
5    user: 'myuser',
6    password: 'MyP@ssw0rd'
7};
8const pool = new QueryBuilder(settings, 'mysql', 'pool');
9
10async function getPlanets() {
11    try {
12        const qb = await pool.get_connection();
13        const response = await qb.select('name', 'position')
14            .where({type: 'rocky', 'diameter <': 12000})
15            .get('planets');
16
17        // SELECT `name`, `position` FROM `planets` WHERE `type` = 'rocky' AND `diameter` < 12000
18        console.log("Query Ran: " + qb.last_query());
19
20        // [{name: 'Mercury', position: 1}, {name: 'Mars', position: 4}]
21        console.log("Results:", response);
22    } catch (err) {
23        return console.error("Uh oh! Couldn't get results: " + err.msg);
24    } finally {
25        qb.disconnect();
26    }
27}
28
29getPlanets();

Connecting to Your Database

Quick Reference

Driver	Default	Ready	single	pool	cluster	Additional Connection Options
mysql	✓	Yes	Yes	Yes	Pending	node-mysql connection options
mssql		Yes	Yes	Yes	???	tedious connection options
sqlite3		No	Yes	???	???
oracle		No	Yes	???	???
postgres		No	Yes	Yes	???
mongodb		No	Yes	???	???

Standard Connection Settings

The options listed below are available for all database drivers. Additional properties may be passed if the driver of the database you are connecting to supports them. See the "Additional Connection Options" column above for a link to the a specific driver's connection options documentation.

Option	Default	Optional	Description
host	localhost	No	The server you're connecting to
user	NULL	No	The database user
password	NULL	Yes	The database user's password
database	NULL	Yes	The database to connect to
port	NULL	Yes	The database port to use when connecting
pool_size	10	Yes	Max connections for pool connection type
pool_min	10	Yes	Min connections for pool connection type (mssql only)
acquireTimeout	10000	Yes	The milliseconds before a timeout occurs during the connection acquisition.
debug	false	Yes	If true, debug info will be place in app log
version	default	Yes	Version of database driver to use

The best way to store these options is in a JSON file outsite of your web root where only root and the server user can access them.

Example JSON File

We'll call this db.json.

1{
2    "host": "db.myserver.com",
3    "user": "myusername",
4    "password": "P@s$w0rD",
5    "database": "myDB",
6    "pool_size": 50
7}

Example App

1const settings = require('db.json');
2// Second and third parameters of the QueryBuilder method default to 'mysql' and 'standard', respectively
3const qb = new require('node-querybuilder')(settings);

Of course you can also just have a normal javascript object directly within your code somwhere if you're honing your inner Chuck Norris:

Chuck Norris App

1const qb = new require('node-querybuilder')({
2    host: 'db.myserver.com',
3    user: 'myusername',
4    password: 'P@s$w0rD',
5    database: 'MyDB',
6    pool_size: 50
7});

Choosing the Database Type

This part is super simple. Just pass which one you'd like to use as the second parameter to the constructor (mysql is the default):

Example:

1const qb = new require('node-querybuilder')(settings, 'mssql');

Choosing the Connection Type

This library currently supports 3 connection methods:

• single (default)

  • This will use the driver's basic single connection capabilities. All connections to your app will use this single database connection. This is usually less than ideal for most web applications but might be quite suitable for command line scripts and the like.
  • All drivers must have this connection type.

• pool

  • This will utilize the driver's connection pooling capabilities if it is offered. Connection pooling allows your application to pull from a pool of connections that were created by the driver. Typically the connections will be handed out to requesting methods in a round-robin fashion. This is ideal for a web application.

• cluster

  • NOTE: This feature is currently incomplete.
  • When you have a cluster of servers and you want to create pools of connections to different servers to help load balance your stack, using the cluster connection type can come in handy. This is ideal for high-traffic web sites and applications that utilize a farm of database servers as opposed to just one.

Note: You will specify the type of connection as the third parameter to the contructor

Example:

1const qb = new require('node-querybuilder')(settings, 'mysql', 'pool');

Managing Connections

It's important to handle your connections properly. When not using a pool, for every connection you make, you'll need to disconnect it when you're done. If you're using a pool (or cluster), it's a similar concept... but you'll be releasing the connection back to the pool so it can be used again later.

Single Connection Example:

1const qb = new require('node-querybuilder')(settings, 'mysql');
2
3qb.get('planets', (err, response) => {
4    // Disconnect right away unless you're going to use it again for subsequent query
5    qb.disconnect();
6
7    if (err) return console.error(err);
8    return console.log("Results: ", response);
9});

Connection Pool Example:

1const pool = new require('node-querybuilder')(settings, 'mysql', 'pool');
2
3// Get a connection (aka a QueryBuilder instance) from the pool
4pool.get_connection(qb => {
5    qb.get('planets', (err, response) => {
6        // Release right away unless you're going to use it again for subsequent query
7        qb.release();
8
9        if (err) return console.error(err);
10        return console.log("Results: ", response);
11    });
12});

API Methods

NOTE: The compatibility portions of these tables are subject to change as features and drivers are written!

Chainable Methods

Chainable methods can be called as many times as you'd like in any order you like. The final query will not be built and executed until one of the execution methods, like get(), are callled. As the name implies, the methods can be chained together indefinitely but this is not required. You definitely call them individually with the same effect at execution time.

API Method	SQL Command	MySQL	MSSQL	Oracle	SQLite	Postgres	Mongo
select()	SELECT	✓	✓
distinct()	DISTINCT	✓	✓
select_min()	MIN	✓	✓
select_max()	MAX	✓	✓
select_avg()	AVG	✓	✓
select_sum()	SUM	✓	✓
from()	FROM	✓	✓
join()	JOIN	✓	✓
where()	WHERE	✓	✓
where_in()	IN	✓	✓
where_not_in()	WHERE	✓	✓
or_where()	WHERE	✓	✓
or_where_in()	WHERE	✓	✓
or_where_not_in()	WHERE	✓	✓
like()	LIKE	✓	✓
or_like()	LIKE	✓	✓
or_not_like()	LIKE	✓	✓
not_like()	LIKE	✓	✓
group_by()	GROUP BY	✓	✓
having()	HAVING	✓	✓
or_having()	HAVING	✓	✓
order_by()	ORDER BY	✓	✓
limit()	LIMIT	✓	✓
offset()	OFFSET	✓	✓
set()	SET	✓	✓
returning()	OUTPUT	✗	✓

---

SELECT

.select(fields[,escape])

This method is used to specify the fields to pull into the resultset when running SELECT-like queries.

Parameter	Type	Default	Description
fields	String/Array	Required	The fields in which to grab from the database
escape	Boolean	true	TRUE: auto-escape fields; FALSE: don't escape

The fields provided to this method will be automatically escaped by the database driver. The fields parameter can be passed in 1 of 2 ways (field names will be trimmed in either scenario):

NOTE: If the select method is never called before an execution method is ran, SELECT * will be assumed.

• String with fields seperated by a comma:

  • .select('foo, bar, baz')

• Array of field names

  • .select(['foo', 'bar', 'baz'])

Examples

1// SELECT * FROM galaxies
2qb.select('*').get('foo', (err, results) => {});
3
4// Easier and same result:
5qb.get('foo', (err, results) => {});
6
7// Async/Await version:
8const results = await qb.get('foo');
9

An array of field names:

1// SELECT `foo`, `bar`, `baz`
2qb.select(['foo', 'bar', 'baz']);

You can chain the method together using different patterns if you want:

1// SELECT `foo`, `bar`, `baz`, `this`, `that`, `the_other`
2qb.select(['foo', 'bar', 'baz']).select('this, that, the_other');

You can alias your field names and they will be escaped properly as well:

1// SELECT `foo` as `f`, `bar` as `b`, `baz` as `z`
2qb.select(['foo as f', 'bar as b', 'baz as z']);

You can optionally choose not to have the driver auto-escape the fieldnames (dangerous, but useful if you a utilize function in your select statement, for instance):

1// SELECT CONCAT(first_name,' ',last_name) AS `full_name`
2qb.select('CONCAT(first_name,' ',last_name) AS `full_name`', false);

In order to successfully use subqueries in your select statements, you must supply false to the second parameter. Please, for custom clauses containing subqueries, make sure you escape everything properly! ALSO NOTE: with this method, there may be conflicts between database drivers!

1// (SELECT `name` FROM `planets` WHERE `id`=8675309) AS `planet_name`
2qb.select('(SELECT `name` FROM `planets` WHERE `id`=8675309) AS `planet_name`', false);
3

NOTE: If you use this technique to add driver-specific functions, it may (and probably will) cause unexpected outcomes with other database drivers!

---

DISTINCT

.distinct()

This SQL command is used to prevent duplicate rows from being returned in the resultset at the database level. It should only be used when querying data (execution methods: .get() & .get_where()) (not inserting, updating or removing). If it's provided to another execution method, it will simply be ignored.

This method takes no parameters

Example

1// SELECT DISTINCT `id`, `name`, `description` FROM `users`
2qb.distinct().select('id, name, description').get('users', callback);

---

MIN

.select_min(field[,alias])

This SQL command is used to find the minimum value for a specific field within a resultset.

Parameter	Type	Default	Description
field	String	Required	The field to get the minimum value of
alias	String	NULL	Optional alias to rename field

Examples

1// SELECT MIN(`age`) FROM `users`
2qb.select_min('age').get('users', callback);

You can optionally include a second parameter to rename the resulting field

1// SELECT MIN(`age`) AS `min_age` FROM `users`
2qb.select_min('age', 'min_age').get('users', callback);

---

MAX

.select_max(field[,alias])

This SQL command is used to find the maximum value for a specific field within a resultset.

Parameter	Type	Default	Description
field	String	Required	The field to get the maximum value of
alias	String	NULL	Optional alias to rename field

Examples

1// SELECT MAX(`age`) FROM `users`
2qb.select_max('age').get('users', callback);

You can optionally include a second parameter to rename the resulting field

1// SELECT MAX(`age`) AS `max_age` FROM `users`
2qb.select_max('age', 'max_age').get('users', callback);

---

AVG

.select_avg(field[,alias])

This SQL command is used to find the average value for a specific field within a resultset.

Parameter	Type	Default	Description
field	String	Required	The field to get the average value of
alias	String	NULL	Optional alias to rename field

Examples

1// SELECT AVG(`age`) FROM `users`
2qb.select_avg('age').get('users', callback);

You can optionally include a second parameter to rename the resulting field

1// SELECT AVG(`age`) AS `avg_age` FROM `users`
2qb.select_avg('age', 'avg_age').get('users', callback);

---

SUM

.select_sum(field[,alias])

This SQL command is used to find the minimum value for a specific field within a result set.

Parameter	Type	Default	Description
field	String	Required	The field to get the minimum value of
alias	String	NULL	Optional alias to rename field

Examples

1// SELECT SUM(`age`) FROM `users`
2qb.select_sum('age').get('users', callback);

You can optionally include a second parameter to rename the resulting field

1// SELECT SUM(`age`) AS `sum_age` FROM `users`
2qb.select_sum('age', 'sum_age').get('users', callback);

---

FROM

.from(tables)

This SQL command is used to determine which sources, available to the active connection, to obtain data from.

Parameter	Type	Default	Description
tables	String/Array	Required	Table(s), view(s), etc... to grab data from

You can provide tables, views, or any other valid source of data in a comma-separated list (string) or an array. When more than one data-source is provided when connected to a traditional RDMS, the tables will joined using a basic join. You can also .from() multiple times to get the same effect (the order in which they are called does not matter).

Aliases can be provided and they will be escaped properly.

NOTE: You can also pass table/view names into the .get() and .get_where() methods and forego this method entirely.

Examples

Basic

1// SELECT `id`, `name`, `description` FROM `users`
2qb.select('id, name, description').from('users').get(callback);

Comma-Seperated

1// SELECT `u`.`id`, `u`.`name`, `u`.`description`, `g`.`name` AS `group_name`
2// FROM (`users` `u`, `groups` `g`)
3qb.select('u.id, u.name, u, description, g.name as group_name')
4    .from('users u, groups g')
5    .get(callback);

Array of Tables

1// SELECT `u`.`id`, `u`.`name`, `u`.`description`, `g`.`name` AS `group_name`
2// FROM (`users` `u`, `groups` `g`)
3qb.select('u.id, u.name, u, description, g.name as group_name')
4    .from(['users u', 'groups g'])
5    .get(callback);

Multiple From Calls

1// SELECT `u`.`id`, `u`.`name`, `u`.`description`, `g`.`name` AS `group_name`
2// FROM (`users` `u`, `groups` `g`)
3qb.from('groups g').select('u.id, u.name, u, description, g.name as group_name')
4    .from('users u')
5    .get(callback);

---

JOIN

.join(table, relation[,direction])

This SQL command is used query multiple tables related and connected by keys and get a single result set.

Parameter	Type	Default	Description
table	String	Required	The table or view to join to.
relation	String	Required	The "ON" statement that relates two tables together
direction	String	"left"	Direction of the join (see join types list below)
escape	Boolean	true	TRUE: Escape table name and conditions; FALSE: No escaping

Join Types/Directions

• left
• right
• outer
• inner
• left outer
• right outer

The table/view and the relationship of it to the main table/view (see: .from()) must be specified. The specific type of join defaults to "left" if none is specified (although it is recommended to always supply this value for readability). Multiple function calls can be made if you need several joins in one query. Aliases can (and should) be provided and they will be escaped properly.

Warning about complex relationship clauses This library currently does not support complex/nested ON clauses passed to the relation (second) parameter. You can supply multiple statements as long as they are not nested within parentheses. If you need to use a complex relationship clause, please make sure to escape those parts manually and pass false to the escape (fourth) parameter. See examples below for more details.

If anyone would like to add this capability, please submit a pull request!

Examples

If no direction is specified, "left" will be used:

1// SELECT `u`.`id`, `u`.`name`, `t`.`name` AS `type_name`
2// FROM `users` `u`
3// LEFT JOIN `types` `t` ON `t`.`id`=`u`.`type_id`
4qb.select('u.id, u.name, t.name as type_name').from('users u')
5    .join('types t', 't.id=u.type_id')
6    .get(callback);

You may specify a direction:

1// SELECT `u`.`id`, `u`.`name`, `t`.`name` AS `type_name`
2// FROM `users` `u`
3// RIGHT OUTER JOIN `types` `t` ON `t`.`id`=`u`.`type_id`
4qb.select('u.id, u.name, t.name as type_name').from('users u')
5    .join('types t', 't.id=u.type_id', 'right outer')
6    .get(callback);

Multiple function calls can be made if you need several joins in one query:

1// SELECT `u`.`id`, `u`.`name`, `t`.`name`  AS `type`, `l`.`name` AS `location`
2// FROM `users` `u`
3// LEFT JOIN `types` `t` ON `t`.`id`=`u`.`type_id`
4// LEFT JOIN `locations` `l` ON `l`.`id`=`u`.`location_id`
5const select = ['u.id', 'u.name', 't.name as type', 'l.name as location'];
6qb.select(select).from('users u')
7    .join('types t', 't.id=u.type_id', 'right outer')
8    .join('locations l', 'l.id=u.location_id', 'left')
9    .get(callback);

If you have a very complex condition you can choose to forego escaping (not recommended unless you know what you're doing). NOTE: Please make sure to escape values manually.

1// SELECT `u`.`id`, `u`.`name`, `t`.`name`  AS `type`
2// FROM `users` `u`
3// LEFT JOIN `user_meta` `um` ON
4//   CASE
5//     WHEN `u`.`id` = 4132 THEN `um`.`id` = `um`.`userId`
6//     WHEN `u`.`name` = 4132 THEN `um`.`name` = `u`.`id`
7const select = ['u.id', 'u.name', 'um.name as user_name'];
8const user_data = req.body;
9qb.select(select).from('users u')
10    .join('`user_meta` `um`', 'CASE WHEN `u`.`id` = ' + user_data.id  + ' THEN `um`.`id` = `um`.`userId` WHEN `u`.`name` = ' + user_data.id + ' THEN `um`.`name` = `u`.`id`', 'right outer', false)
11    .get(callback);

---

WHERE

This SQL command is used to limit the resultset based on filters.

Parameter	Type	Default	Description
field/filters	String/Object	Required	A field name, a WHERE clause, or an object of key/value pairs
value	Mixed	N/A	When the first parameter is a field name, this is the value
escape	Boolean	TRUE	TRUE: Escape field names and values; FALSE: No escaping

.where(field[,value[,escape]])

This method can be called in many different ways depending on your style and the format of the data that you have at the time of calling it. For standard SQL, all clauses will be joined with 'AND'—if you need to join clauses by 'OR', please us .or_where(). By default, all values and field names passed to this function will be escaped automatically to produce safer queries. You can turn this off by passing false into the third parameter.

If a valid field name is passed in the first parameter, you can pass an array the second parameter and the call will be treated as a .where_in().

Examples

If you just want to pass a single filter at a time:

1// SELECT `galaxy` FROM `universe` WHERE `planet_name` = 'Earth'
2qb.select('galaxy').where('planet_name', 'Earth').get('universe', callback);

If you need more complex filtering using different operators (<, >, <=, =>, !=, <>, etc...), you can simply provide that operator along with the key in the first parameter. The '=' is assumed if a custom operator is not passed:

1// SELECT `planet` FROM `planets` WHERE `order` <= 3
2qb.select('planet').where('order <=', 3).get('planets', callback);

You can conveniently pass an object of key:value pairs (which can also contain custom operators):

1// SELECT `planet` FROM `planets` WHERE `order` <= 3 AND `class` = 'M'
2qb.select('planet').where({'order <=':3, class:'M'}).get('planets', callback);

You can construct complex WHERE clauses manually and they will be escaped properly as long as there are no parenthesis within it. Please, for custom clauses containing subqueries, make sure you escape everything properly! ALSO NOTE: with this method, there may be conflicts between database drivers!

1// SELECT `planet` FROM `planets` WHERE `order` <= 3 AND `class` = 'M'
2qb.select('planet').where("order <= 3 AND class = 'M'").get('planets', callback);

You can pass a non-empty array as a value and that portion will be treated as a call to .where_in():

1// SELECT `star_system` FROM `star_systems`
2// WHERE `planet_count` >= 4, `star` IN('Sun', 'Betelgeuse')
3qb.select('star_system')
4    .where({'planet_count >=': 4, star: ['Sun', 'Betelgeuse']})
5    .get('star_systems', callback);

.or_where(field[,value[,escape]])

This method functions identically to .where() except that it joins clauses with 'OR' instead of 'AND'.

1// SELECT `star_system` FROM `star_systems`
2// WHERE `star` = 'Sun' OR `star` = 'Betelgeuse'
3qb.select('star_system').where('star', 'Sun')
4    .or_where('star', 'Betelgeuse')
5    .get('star_systems', callback);

.where_in(field, values[,escape])

This will create a "WHERE IN" statement in traditional SQL which is useful when you're trying to find rows with fields matching many different values... It will be joined with existing "WHERE" statements with 'AND'.

1// SELECT `star_system` FROM `star_systems`
2// WHERE `star` IN('Sun', 'Betelgeuse', 'Sirius', 'Vega', 'Alpha Centauri')
3const stars = ['Sun', 'Betelgeuse', 'Sirius', 'Vega', 'Alpha Centauri'];
4qb.select('star_system').where_in('star', stars).get('star_systems', callback);

.or_where_in(field, values[,escape])

Same as .where_in() except the clauses are joined by 'OR'.

1// SELECT `star_system` FROM `star_systems`
2// WHERE `planet_count` = 4 OR `star` IN('Sun', 'Betelgeuse')
3const stars = ['Sun', 'Betelgeuse'];
4qb.select('star_system').where('planet_count', 4)
5    .or_where_in('star', stars)
6    .get('star_systems', callback);

.where_not_in(field, values[,escape])

Same as .where_in() except this generates a "WHERE NOT IN" statement. All clauses are joined with 'AND'.

1// SELECT `star_system` FROM `star_systems`
2// WHERE `star` NOT IN('Sun', 'Betelgeuse', 'Sirius', 'Vega', 'Alpha Centauri')
3const stars = ['Sun', 'Betelgeuse', 'Sirius', 'Vega', 'Alpha Centauri'];
4qb.select('star_system').where_not_in('star', stars).get('star_systems', callback);

.or_where_not_in(field, values[,escape])

Same as .where_not_in() except that clauses are joined with 'OR'.

1// SELECT `star_system` FROM `star_systems`
2// WHERE `star` NOT IN('Sun', 'Betelgeuse')
3// OR `planet_count` NOT IN [2,4,6,8]
4const stars = ['Sun', 'Betelgeuse'];
5const planet_sizes = [2,4,6,8];
6qb.select('star_system')
7    .where_not_in('star', stars)
8    .or_where_not_in('planet_size', planet_sizes)
9    .get('star_systems', callback);

---

LIKE

This SQL command is used to find close matches where as the "WHERE" command is for precise matches. This is useful for doing searches.

Parameter	Type	Default	Description
field/filters	String/Object	Required	Field name or object of field/match pairs
value	String/Number	Required	The value you want the field to closely match
side	String	'both'	before: '%value'; after: 'value%', both: '%value%'

NOTE: You can, alternatively, use 'right' and 'left' in place of 'before' and 'after' if you prefer.

.like(field, match[,side])

All fields are escaped automatically, no exceptions. Multiple calls will be joined together with 'AND'. You can also pass an object of field/match pairs. Wildcard sides are interchangeable between before/left and after/right--choose the one that makes the most sense to you (there are examples of each below).

Examples

By default, the match string will be wrapped on both sides with the wildcard (%):

1// SELECT `first_name` FROM `users` WHERE `first_name` LIKE '%mber%'
2// Potential results: [{first_name: 'Kimberly'},{first_name: 'Amber'}]
3qb.select('first_name').like('first_name', 'mber').get('users', callback);

You can specify a side to place the wildcard (%) on if you'd like (before/left, after/right, both):

1// SELECT `first_name` FROM `users` WHERE `first_name` LIKE '%mber'
2// Potential results: [{first_name: 'Amber'}]
3qb.select('first_name').like('first_name', 'mber', 'before').get('users', callback);
4
5// SELECT `first_name` FROM `users` WHERE `first_name` LIKE 'Kim%'
6// Potential results: [{first_name: 'Kim'},{first_name: 'Kimberly'}]
7qb.select('first_name').like('first_name', 'Kim', 'right').get('users', callback);

You can also pass 'none' if you don't want to use the wildcard (%)

1// SELECT `first_name` FROM `users` WHERE `first_name` LIKE 'kim'
2// Potential results: [{first_name: 'Kim'}]
3qb.select('first_name').like('first_name', 'kim', 'none').get('users', callback);

If you'd like to have multiple like clauses, you can do that by calling like multiple times:

1// SELECT `first_name` FROM `users`
2// WHERE `first_name` LIKE 'Kim%'
3// AND `middle_name` LIKE '%lyt%'
4// AND `last_name` LIKE '%arris'
5qb.select('first_name')
6    .like('first_name', 'Kim', 'right')
7    .like('middle_name', 'lyt')
8    .like('last_name', 'arris', 'left')
9    .get('users', callback);

Or you can do it with an object of field/match pairs. If you want to pass a wildcard side, provide null as the second parameter and the side as the third. Note: All match values in an object will share the same wildcard side.

1// SELECT `first_name` FROM `users`
2// WHERE `first_name` LIKE '%ly'
3// AND `middle_name` LIKE '%the'
4// AND `last_name` LIKE '%is'
5qb.select('first_name')
6    .like({first_name: 'ly', middle_name: 'the', last_name: 'is'}, null, 'before')
7    .get('users', callback);

.or_like(field, match[,side])

This is exactly the same as the .like() method except that the clauses are joined by 'OR' not 'AND'.

Example

1// SELECT `first_name` FROM `users`
2// WHERE `first_name` LIKE 'Kim%'
3// OR `middle_name` LIKE '%lyt%'
4// OR `last_name` LIKE '%arris'
5qb.select('first_name')
6    .or_like('first_name', 'Kim', 'right')
7    .or_like('middle_name', 'lyt')
8    .or_like('last_name', 'arris', 'left')
9    .get('users', callback);

.not_like(field, match[,side])

This is exactly the same as the .like() method except that it creates "NOT LIKE" statements.

Example

1// SELECT `first_name` FROM `users`
2// WHERE `first_name` NOT LIKE 'A%'
3// AND `middle_name` NOT LIKE 'B%'
4// AND `last_name` NOT LIKE 'C%'
5qb.select('first_name')
6    .not_like({first_name: 'A', middle_name: 'B', last_name: 'C'}, null, 'after')
7    .get('users', callback);

.or_not_like(field, match[,side])

This is exactly the same as the .not_like() method except that the clauses are joined by 'OR' not 'AND'.

Example

1// SELECT `first_name` FROM `users`
2// WHERE `first_name` NOT LIKE 'A%'
3// OR `middle_name` NOT LIKE 'B%'
4// OR `last_name` NOT LIKE 'C%'
5qb.select('first_name')
6    .or_not_like({first_name: 'A', middle_name: 'B', last_name: 'C'}, null, 'after')
7    .get('users', callback);

---

GROUP BY

.group_by(fields)

This SQL command allows you to get the first (depending on ORDER) result of a group of results related by a shared value or values.

Parameter	Type	Default	Description
field(s)	String/Object	Required	Field name or array of field names

Examples

Group by a single field:

1// SELECT * FROM `users` GROUP BY `department_id`
2qb.group_by('department_id').get('users', callback);

Group by multiple fields:

1// SELECT * FROM `users` GROUP BY `department_id`, `position_id`
2qb.group_by(['department_id', 'position_id']).get('users', callback);

---

HAVING

.having(field, value)

This SQL command is similar to the 'WHERE' command but is used when aggregate functions are used in the "SELECT" portion of the query.

Parameter	Type	Default	Description
field/filters	String/Object	Required	Field name or object of field/value pairs to filter on
value	Mixed	NULL	Value to filter by
escape	Boolean	true	TRUE: Escape fields and values; FALSE: Don't escape.

This method works exactly the same way as the .where() method works with the exception of the fact that there is no 'HAVING' equivalent to 'WHERE IN'. See the .where() documentation if you need additional information.

Examples

If you just want to add a single having clause:

1// SELECT COUNT(*) AS `num_planets` FROM `star_systems`
2// GROUP BY `id`
3// HAVING `num_planets` = 5
4qb.group_by('id').having('num_planets', 5).count('star_systems', callback);

If you need more complex filtering using different operators (<, >, <=, =>, !=, <>, etc...), you can simply provide that operator along with the key in the first parameter. The '=' is assumed if a custom operator is not passed:

1// SELECT COUNT(*) AS `num_planets` FROM `star_systems`
2// GROUP BY `id`
3// HAVING `num_planets` > 5
4qb.group_by('id').having('num_planets >', 5).count('star_systems', callback);

You can conveniently pass an object of key:value pairs (which can also contain custom operators):

1// SELECT COUNT(*) AS `num_planets` FROM `star_systems`
2// GROUP BY `id`
3// HAVING `num_planets` > 5
4qb.group_by('id').having({'num_planets >': 5}).count('star_systems', callback);

You can construct complex WHERE clauses manually and they will be escaped properly. Please, for custom clauses containing subqueries, make sure you escape everything properly! ALSO NOTE: with this method, there may be conflicts between database drivers!

1// SELECT COUNT(*) AS `num_planets` FROM `star_systems`
2// GROUP BY `id`
3// HAVING `num_planets` > (5+2)
4qb.group_by('id').having("`num_planets` > (5+2)", null, false).count('star_systems', callback);

.or_having(field[,value[,escape]])

This method functions identically to .having() except that it joins clauses with 'OR' instead of 'AND'.

1// SELECT SUM(planets) AS `num_planets`, SUM(moons) AS `num_moons` FROM `star_systems`
2// GROUP BY `id`
3// HAVING `num_planets` >= 5 OR `num_moons` <= 10
4qb.group_by('id')
5    .having('num_planets >=', 5)
6    .or_having('num_moons <=', 10)
7    .count('star_systems', callback);

---

ORDER BY

.order_by(field[,direction])

This SQL command is used to order the resultset by a field or fields in descending, ascending, or random order(s).

Parameter	Type	Default	Description
fields	String/Array	Required	Field name or an array of field names, possibly with directions as well
direction	String	'asc'	'asc': Ascending; 'desc': Descending; 'rand'/'random'/'rand()': Random.

This is a very flexible method, offering a wide variety of ways you can call it. Variations include:

• Pass the field name and omit the direction
• Pass the field name and the direction as the first and second parameters, respectively (most common)
• Pass an array of fields to first parameter, direction to second parameter.
• Pass an array of fields + directions in first parameter and omit the second one.
• Pass an array of fields (+ directions for some to override second parameter) to first parameter, direction to second parameter.
• Pass a raw comma-separated string of field + directions in first parameter and omit the second one.

Examples

Pass the field name and omit the direction

1// SELECT * FROM `galaxies` ORDER BY `galaxy_name` ASC
2qb.order_by('galaxy_name').get('galaxies', callback);

Random sort

1// (MySQL) SELECT * FROM `galaxies` ORDER BY RAND() ASC
2// (MSSQL) SELECT * FROM `galaxies` ORDER BY NEWID() ASC
3qb.order_by('random').get('galaxies', callback);

Pass the field name and the direction as the first and second parameters, respectively

1// SELECT * FROM `galaxies` ORDER BY `galaxy_name` DESC
2qb.order_by('galaxy_name', 'desc').get('galaxies', callback);

Pass an array of fields to first parameter, direction to second parameter

1// SELECT * FROM `galaxies` ORDER BY `galaxy_name` DESC, `galaxy_size` DESC
2qb.order_by(['galaxy_name', 'galaxy_size'],'desc').get('galaxies', callback);

Pass an array of fields + directions in first parameter and ommit the second one.

1// SELECT * FROM `galaxies` ORDER BY `galaxy_name` DESC, `galaxy_size` ASC
2qb.order_by(['galaxy_name desc', 'galaxy_size asc']).get('galaxies', callback);

Pass an array of fields (+ directions for some to override second parameter) to first parameter, direction to second parameter

1// SELECT * FROM `galaxies` ORDER BY `galaxy_name` DESC, `galaxy_size` ASC
2qb.order_by(['galaxy_name desc', 'galaxy_size'],'asc').get('galaxies', callback);

Pass a raw comma-separated string of field + directions in first parameter and omit the second one.

1// SELECT * FROM `galaxies` ORDER BY `galaxy_name` ASC, `galaxy_size` DESC
2qb.order_by('galaxy_name asc, galaxy_size desc').get('galaxies', callback);

---

LIMIT

.limit(limit_to, offset)

This SQL command is used to limit a result set to a maximum number of results, regardless of the actual number of results that might be returned by a non-limited query.

Parameter	Type	Default	Description
limit_to	Integer	Required	The maximum number of results you want from the query
offset	Integer	NULL	Optional offset value (where to start before limiting)

Example

1// SELECT * FROM `users` LIMIT 5
2qb.limit(5).get('users', callback);

You can provide an option offset value instead of calling .offset() separately:

1// SELECT * FROM `users` LIMIT 5, 5
2qb.limit(5, 5).get('users', callback);

---

OFFSET

.offset(offset)

This SQL command is tell the "LIMIT" where to start grabbing data. If cannot be used without a limit having been set first.

Parameter	Type	Default	Description
offset	Integer	NULL	where to start before limiting

The practical uses of this method are probably miniscule since the .limit() method must be called in order to use it and the limit method provides a means by which to set the offset. In any case, the method is very simple: pass the result row index that you want to start from when limiting. This is most useful for pagination of search results and similar scenarios.

Example

1// SELECT * FROM `users` LIMIT 5, 25
2qb.limit(5).offset(25).get('users', callback);

---

SET

.set(key[, value[, escape]])

This SQL is used to set values to fields when utilizing the update, and insert methods. More than likely, you will choose use the shorthand notation provided by the aforementioned methods, but, this can be handy in some cases.

Parameter	Type	Default	Description
key	String/Object	Required	The key of field to be set or an object of key:value pairs
value	Mixed	NULL	Required if key is a string. Pass NULL if key is an object and you'd like to use the 3rd parameter
escape	String/Object	true	If false, keys and values will not be escaped.

Examples

Basic single setting of a value

1// UPDATE `users` SET `birthday` = '2015-02-04'
2qb.set('birthday','2015-02-04').update('users', callback);

Set multiple keys and values at once

1const birthday = new Date(1986, 7, 5, 8, 15, 23);
2// UPDATE `users` SET `birthday` = '2015-02-04', `anniversary` = '2010-05-15'
3qb.set({birthday: birthday, anniversary: '2010-05-15'}).update('users', callback);

---

RETURNING / OUTPUT

.returning(id)

This method is required for MSSQL when performing INSERT queries to get the IDs of the row(s) that were inserted. You should supply which column(s) should be returned by the INSERT query as the insert_id in the response object. If you need multiple values (compound primary key, for instance) you can supply an array of strings representing those columns. If you call this method while using the mysql driver, it will be ignored silently.

Parameter	Type	Default	Description
key	String/Array	Required	The ID or IDs used to identify the row that you're inserting

How This Works

Upon a successful INSERT query, you will be provided with a result object (see: Response Format Examples). I the returning() method is not called when using the MSSQL driver, the insert_id property of the result object will be NULL. This is not needed for the MySQL driver because its engine already supplies this info to the driver by default.

Examples

Basic single ID example

1// INSERT INTO [users] ([first_name], [last_name]) OUTPUT INSERTED.[id] VALUES ('John', 'Smith')
2qb.returning('id').insert('users', {first_name: 'John', last_name: 'Smith'});

Return multiple column that should act as the insert_id

1// INSERT INTO [users] ([position_request_id], [job_id], [name], [title]) OUTPUT INSERTED.[position_request_id], INSERTED.[job_id] VALUES (42, 1337, 'John Smith', 'Hacker')
2qb.returning(['position_request_id', 'job_id']).insert('applicants', {position_request_id: 42, job_id: 1337, name: 'John Smith', title: 'Hacker'});

---

Execution Methods

API Method	SQL Command	MySQL	MSSQL	Oracle	SQLite	Postgres	Mongo
query()	N/A	✓	✓
get()	N/A	✓	✓
get_where()	N/A	✓	✓
count()	COUNT	✓	✓
update()	UPDATE	✓	✓
update_batch()	N/A	✓	✓
insert()	INSERT	✓	✓
insert_batch()	N/A	✓	✓
insert_ignore()	INSERT IGNORE	✓	✗
delete()	DELETE	✓	✓
truncate()	TRUNCATE	✓	✓
empty_table()	DELETE	✓	✓

What are "Execution Methods"??

Execution methods are the end-of-chain methods in the QueryBuilder library. Once these methods are called, all the chainable methods you've called up until this point will be compiled into a query string and sent to the driver's query() method. At this point, the QueryBuilder will be reset and ready to build a new query. The database driver will respond with results depending on the type of query being executed or with an error message.

Handling Error Messages and Results

Callback Style

The final parameter of every execution method will be a callback function. If a callback is supplied to that final parameter, a Promise object WILL NOT be returned.

The parameters for the callback are in the node.js standard (err, response) format. If the driver throws an error, a JavaScript Standard Error object will be passed into the err parameter. The response parameter can be supplied with an array of result rows (.get() & .get_where()), an integer (.count()), or a response object containing rows effected, last insert id, etc... in any other scenario.

Promise Style

If a callback function is not supplied to the final parameter of execution methods, a Promise will be returned. If the driver throws an error a JavaScript Standard Error object will be sent to the the Promise's reject callback parameter. The response will be sent to the Promise's resolve callback parameter. The response can be an array of result rows (.get() & .get_where()), an integer (.count()), or a response object containing rows effected, last insert id, etc... in any other scenario.

Obviously, async/await is supported through this style.

Response Format Examples

API Method(s)	Response Format
get(), get_where()	[{field:value,field2:value2},{field:value, field2:value2}]
count()	Integer (ex. 578)
insert(), update(), delete()	Example: {insert_id: 579, affected_rows: 1, changed_rows: 0 [,and others per DB driver]}
insert_batch(), update_batch()	Example: {insert_id: 579, affected_rows: 1, changed_rows: 0 [,and others per DB driver]}

NOTE

When using the returning() method with compatible drivers (mssql), the insert_id property of the response object will be an array of objects containing key value pairs representing the requested "returned" columns along with their values.

Example:

1// results: {insert_id: [{id: 12345}], affected_rows: 1, changed_rows: 0}
2const results = await qb.returning('id').insert('users', {firstName: 'John', lastName: 'Smith'});

Callback Example

1pool.get_connection(qb => qb.get('foo', (err, response) => {
2    qb.release();
3    if (err) return console.error(err);
4    response.forEach((v) => /* Do Something */);
5}));

Promise Example

Note: Don't do it this way. It's silly, verbose, and out-dated.

1pool.get_connection().then(qb => {
2    const result = qb.get('foo');
3    qb.release();
4    return result;
5}).then(response => {
6    response.forEach((v) => /* Do Something */);
7    return response;
8}).catch(err =>{
9    return console.error(err);
10});

Async/Await Example

1async function getFoo() {
2    let qb;
3    try {
4        qb = await pool.get_connection();
5        const response = qb.get('foo');
6        response.forEach((v) => /* Do Something */);
7    } catch (err) {
8        console.error(err);
9    } finally {
10        if (qb) qb.release();
11    }
12}
13
14getFoo();

Using the Same Connection Pool Connection for Successive Calls

This is an ideal scenario for the async/await pattern.

1const pool = new require('node-querybuilder')(settings,'mysql','pool');
2const data = {first_name: 'John', last_name: 'Smith'};
3
4async function addUser() {
5    let qb;
6    try {
7        qb = await pool.get_connection();
8        const results = await qb.insert('users', data);
9
10        if (results.affected_rows === 1) {
11            const user = await qb.get_where('users', {id: res.insert_id});
12            console.log('New User: ', user);
13        } else {
14            throw new Error("New user was not added to database!");
15        }
16    } catch (err) {
17        console.error(err);
18    } finally {
19        if (qb) qb.release();
20    }
21}
22
23updateUser();

---

.query(query_string[, callback])

Parameter	Type	Default	Description
query_string	String	Required	Query to send directly to your database driver
callback	Function	undefined	(optional) What to do when the driver has responded

This method bypasses the entire QueryBuilder portion of this module is simply uses your database driver's native querying method. You should be cautious when using this as none of this module's security and escaping functionality will be utilized.

There are scenarios when using this method may be required; for instance, if you need to run a very specific type of command on your database that is not typical of a standard, CRUD-type query (ex. user permissions or creating a view).

Example

1const sql = qb.select(['f.foo', 'b.bar'])
2    .from('foo f')
3    .join('bar b', 'b.foo_id=f.id', 'left')
4    .get_compiled_select();
5
6qb.query("CREATE VIEW `foobar` AS " + sql, callback);

---

.get([table[,callback]])

Parameter	Type	Default	Description
table	String	undefined	(optional) Used to avoid having to call .from() seperately.
callback	Function	undefined	(optional) What to do when the driver has responded

This method is used when running queries that might respond with rows of data (namely, "SELECT" statements...). You can pass a table name as the first parameter to avoid having to call .from() separately. If the table name is omitted, and the first parameter is a callback function, there will be no need to pass a callback function into the second parameter.

Response Type

Array of rows/records

Examples

If you want to provide a table name into the first parameter:

1// SELECT * FROM `galaxies`
2qb.get('galaxies', callback);

If you already have the table added to the query:

1// SELECT * FROM `galaxies`
2qb.from('galaxies').get(callback);

Just a more-complicated example for the sake of it:

1/**
2 * SELECT
3 *    `g`.`name`,
4 *    `g`.`diameter`,
5 *    `g`.`type_id`,
6 *    `gt`.`name` AS `type`,
7 *    COUNT(`s`.`id`) as `num_stars`
8 * FROM `galaxies` `g`
9 * LEFT JOIN `galaxy_types` `gt` ON `gt`.`id`=`g`.`type_id`
10 * LEFT JOIN `stars` `s` ON `s`.`galaxy_id`=`g`.`id`
11 * GROUP BY `g`.`id`
12 * ORDER BY `g`.`name` ASC
13 * LIMIT 10
14 **/
15qb.limit(10)
16    .select(['g.name', 'g.diameter', 'gt.name as type'])
17    .select('COUNT(`s`.`id`) as `num_stars`',null,false)
18    .from('galaxies g')
19    .join('galaxy_types gt', 'gt.id=g.type_id', 'left')
20    .join('stars s', 's.galaxy_id=g.id', 'left')
21    .group_by('g.id')
22    .order_by('g.name', 'asc')
23    .get((err, response) => {
24        if (err) return console.error(err);
25
26        response.forEach(row => {
27            console.log(`The ${row.name} is a ${row.diameter} lightyear-wide ${row.type} galaxy with ${row.num_stars} stars.`);
28        });
29    });

---

.get_where(table, where[, callback])

Parameter	Type	Default	Description
table	String or Array	Required	Used to avoid having to call .from() separately.
where	Object	Required	Used to avoid having to call .where() separately
callback	Function	undefined	(optional) What to do when the driver has responded.

This method is basically the same as the .get() method except that if offers an additional shortcut parameter to provide a list of filters ({field_name:value}) to limit the results by (effectively a shortcut to avoid calling .where() separately). The other difference is that all parameters are required and they must be in the proper order.

Response Type

Array of objects representing the result rows.

Examples

Basic example:

1// SELECT * FROM `galaxies` WHERE `num_stars` > 100000000
2qb.get_where('galaxies', {'num_stars >': 100000000}, callback);

You can still provide other where statements if you want—they'll all work happily together:

1// SELECT * FROM `galaxies` WHERE `num_stars` > 100000000 AND `galaxy_type_id` = 3
2qb.where('num_stars >', 100000000).get_where('galaxies', {galaxy_type_id: 3}, callback);

---

.count([[table[, callback]])

Parameter	Type	Default	Description
table	String	undefined	(optional) Used to avoid having to call .from() separately.
callback	Function	undefined	(optional) What to do when the driver has responded.

This method is used to determine the total number of results that a query would return without actually returning the entire resultset back to this module. Obviously, you could simply execute the same query with .get() and then check the length property of the response array, but, that would take significantly more time and memory for very large resultsets.

The field in the resultset will always labeled be 'numrows'.

Response Type

Integer

Examples

1// SELECT COUNT(*) AS `numrows` FROM `galaxies` WHERE `type` = 3
2const type = 3;
3qb.where('type', type).count('galaxies', (err, count) => {
4    if (err) return console.error(err);
5    console.log("There are " + numrows + " Type " + type + " galaxies in the Universe.");
6});

---

.update(table, data[,where][, callback])

Parameter	Type	Default	Description
table	String	null	(suggested) The table/collection you'd like to update
data	Object	null	(suggested) The data to update (ex. {field: value})
where	Object	null	(optional) Used to avoid having to call .where() separately. Pass NULL if you don't want to use it.
callback	Function	undefined	(optional) What to do when the driver has responded.

This method is used to update a table (SQL) or collection (NoSQL) with new data. All identifiers and values are escaped automatically when applicable. The response parameter of the callback should receive a response object with information like the number of records updated, and the number of changed rows...

NOTE:

The first and second parameters are not required but I do suggest you use them as your code will be much easier to read. If you choose not to use them, you will need to pass a "falsey" value to each... you can't simply skip them. My recommendation is to use null. The way you would supply these values without using this method would be through the from() method for the first parameter and the set() method for the second parameter.

Response Type

Object containing information about the results of the query.

Examples

Here's a contrived example of how it might be used in an app made with the Express framework:

1const express = require('express');
2const app = express();
3const settings = require('db.json');
4const pool = new require('node-querybuilder')(settings, 'mysql', 'pool');
5
6app.post('/update_account', (req, res) => {
7    const user_id = req.session.user_id;
8    const sanitize_name = name => name.replace(/[^A-Za-z0-9\s'-]+$/,'').trim();
9    const sanitize_age = age => age.replace(/[^0-9]+$/,'').trim();
10
11    const data = {
12        first_name: sanitize_name(req.body.first_name),
13        last_name: sanitize_name(req.body.last_name),
14        age: sanitize_age(req.body.last_name),
15        bio: req.body.bio,
16    };
17
18    pool.get_connection(qb => {
19        qb.update('users', data, {id:user_id}, (err, res) => {
20            qb.release();
21            if (err) return console.error(err);
22
23            const page_data = {
24                prefill: data,
25            }
26            return res.render('/account_updated', page_data);
27        });
28    });
29});

Here's another (more-direct) example where one decided to supply the table, data, and filters through alternative methods:

1const qb = new require('node-querybuilder')(settings, 'mysql', 'single');
2qb.where('id', 42)
3    .from('users')
4    .set('email', 'email@domain.net')
5    .update(null, null, null, (err, res) => {
6        if (err) return console.error(err);
7        console.log("Updated: " + res.affected_rows + " rows");
8    });

---

.update_batch(table, dataset, index[,where][, callback])

Parameter	Type	Default	Description
table	String	Required	The table/collection you'd like to insert into
dataset	Array	Required	An array of data (rows) to update (ex. [{id: 3, field: value}, {id: 4, field: val}])
index	String	Required	Name of the key in each data object that represents a where clause.
where	Object	NULL	(optional) Used to avoid having to call .where() separately. Pass NULL if you don't want to use it.
callback	Function	undefined	(optional) What to do when the driver has responded.

This method is a somewhat-complex one and, when using transactional databases, a bit pointless. Nevertheless, this will allow you to update a batch of rows with one query which, in theory, should be faster than running multiple update queries.

The important thing to understand is that there are, essentially, two where clause portions with this method: a local one, and a global one. The index you specify in the 3rd parameter represents the name of the key in each data object of the dataset that will act as the local where clause for that particular row to be updated. That row, however, will only be updated if the global where clause(s) (4th param) have been satisfied as well.

NOTE: This method will create batches of up to 100 rows at a time. So, if you have 250 rows to update, this will make 3 queries to yo