DBMS / 04. QueryBuilder
Updated: 25. August 2020
Author: Peter Širka

04. QueryBuilder

Professional Support Chat with contributors

With QueryBuilder you can create conditions or you can catch the result from.

var builder = DMBS().find();
// builder === QueryBuilder

Quick Links:



Contains a raw object for insert/update/modify.


builder.autofill($, allowedfields, skippedfields, def_sort, [def_maxlimit], [localized])

Creates a filter automatically according to the Total.js Schema. Query parameters will be used for the filtering and parameters:

  • fields (separated by comma) will be returned from DB
  • page will be used as a page
  • limit will be used as a limit otherwise def_maxlimit
  • sort will be used for sorting otherwise def_sort (can contain multiple values separated by , comma)
// builder.autofill($, allowedfields, skipfields, def_sort, def_maxlimit);
// @$ {SchemaOptions} Instance of SchemaOptions
// @allowedfields {String} Optional, can contain fields not defined in the schema and must be separated by comma ","
// @skippedfields {String} Optional, can contain skip fields defined in schema and must be separated by comma ","
// @def_sort {String} Optional, default sort in the form "FIELDNAME_asc" or "FIELDNAME_desc"
// @def_maxlimit {Number} Optional, a maximum count of items per page
// @localized {String} Optional, defines localized name of fields
// returns QueryBuilder

// If "def_maxlimit" is not defined then the pagination isnt't applied

// Example:
db.find('users').autofill($, 'dtcreated:Date,dtupdated:Date', 'id', 'dtcreated_desc', 50);

// Example with localized fields:
db.find('users').autofill($, 'dtcreated:Date,dtupdated:Date', 'id', 'dtcreated_desc', 50, 'name,description').language('sk');

builder.between(name, a, b)

Creates a between condition.

// builder.between(name, a, b);
// @name {String} A field name
// @a {Number} A value
// @b {Number} B value
// returns QueryBuilder

db.find('users').between('age', 20, 30);


A callback for the response. It's executed if the operation will have a response.

// builder.callback(fn);
// @fn {Function}
// returns QueryBuilder

db.find('users').callback(function(err, response) {
    // @err {ErrorBuilder} can be "null"
    // @response {Array/Object}


Sets the connection string according to id.

// builder.conn(id);
// @name {String} A connection ID
// returns QueryBuilder

db.query('SELECT * FROM tbl_user').conn('server_perun');

// You need to have declared initialization in the form:
dbms.init('server_perun', 'postgresql://user:pass@localhost:5432/dbname1');


Checks if the field name contains some value.

// builder.contains(name);
// @name {String} A field name
// returns QueryBuilder



Makes a custom filter with help of responded data.

// builder.custom(fn);
// returns QueryBuilder

// fn(QueryBuilder, response, [last_response])

db.read('users').where('id', $.id);
db.read('orders').custom((builder, response) => builder.where('userid', response.id));
// db.read('orders').custom((builder, response, response_last) => builder.where('userid', response.id));


A data-callback. The method is executed if the operation will have a response without any error.

// builder.data(fn);
// @fn {Function}
// returns QueryBuilder

db.find('users').data(function(response) {
    // @response {Array/Object}

builder.done($, fn, [param])

This method returns a function with a wrapped callback. If the result is positive then the fn function is executed otherwise is executed $.invalid(err).

// db.done($, fn);
// $ {Total.js SchemaOptions/OperationOptions/Controller}
// @fn {Function(response)} A callback
// @param {Object}
// returns Function(err, response, param)

schema.setUpdate(function($) {
    var db = DBMS();
    db.mod('tbl_user', $.model).where('id', $.id).err('error-users-404').done($, function(response, param) {
        // @response {Object} with a positive result

builder.day(name, [operator], value)

Creates a day condition.

// builder.day(name, [operator], value);
// @name {String} A field name
// @operator {String} Optional, default "="
// @value {Number} A value
// returns QueryBuilder

db.find('users').day('created', 28);
db.find('users').day('created', '>', 12);


Checks if the field name is empty.

// builder.empty(name);
// @name {String} A field name
// returns QueryBuilder



This method applies values (according fields argument) as where condition in update() or modify().

// builder.equal(fields);
// @fields {String} Fields separated by comma
// returns QueryBuilder

db.modify('tbl_user', { id: model.id, name: model.name }, true).equal('id');

// The case above will be translated to:
// UPDATE tbl_user SET name=$2 WHERE id=$1
// and if no records won't be updated then DBMS perform insert in the form:
// INSERT tbl_user (id, name) VALUES($1, $2)


A fail-callback. The method is executed if the operation will contain an error.

// builder.fail(fn);
// @fn {Function}
// returns QueryBuilder

db.find('users').fail(function(err) {
    // @err {ErrorBuilder}

builder.fields(a, b, c, ...)

Selects only specific fields.

// builder.fields(a, b, c, ...);
// @a {String} A field name
// @b {String} Optional, a field name
// @c {String} Optional, a field name
// returns QueryBuilder

db.find('users').fields('id', 'name');


Returns only the one document.

// builder.first();
// returns QueryBuilder


builder.fulltext(name, value, [weight])

Creates a fulltext condition.

// builder.fulltext(name, value, [weight]);
// @name {String} A field name
// @value {String} A value
// @weight {Number} Optional, a weight in percentage (default "0")
// returns QueryBuilder

db.find('users').fulltext('name', 'Peter Sirka');


Reads a value from DB response. It can read a value from Array of responses or with a plain Object.

// db.find(name);
// @path {String} A path to response
// returns QueryBuilder

builder.in('id', builder.get('users.id'));

builder.gridfields(fields, [allowed])

This method cleans fields defined in e.g. URL address according to the allowed fields.

// db.gridfields(fields, [allowed]);
// @fields {String} A fields defined for example in URL address
// @allowed {String Array} Optional, Allowed fields
// returns QueryBuilder

db.gridfields($.query.fields, schema.fields);

builder.gridfilter(name, obj, type, [column_name])

This is a special method for creating a condition according to j-DataGrid filter.

// db.gridfilter(name, obj, type, [key]);
// @name {String} A property name in "obj"
// @obj {Object} With values for filtering
// @type {Object} A type of value in DB (String, Number, Date, Boolean)
// @column_name {String} Optional column name (default "name" argument)
// returns QueryBuilder

// For example usage in Total.js Schemas
var opt = $.query;

// Sample data:
// opt.type = 'something';
// opt.price = '200 - 300';
// opt.created = '2018';

opt.type && builder.gridfilter('type', opt, String);
opt.price && builder.gridfilter('price', opt, Number);
opt.created && builder.gridfilter('created', opt, Date);


This is a special method for creating a sort condition according to j-DataGrid filter.

// db.gridsort(name_type);
// @name_type {String} A column name
// returns QueryBuilder

// or
builder.gridsort('price asc');
builder.gridsort('price desc');
// or multi sort

builder.hour(name, [operator], value)

Creates a hour condition.

// builder.hour(name, [operator], value);
// @name {String} A field name
// @operator {String} Optional, default "="
// @value {Number} A value
// returns QueryBuilder

db.find('users').hour('created', 23);
db.find('users').hour('created', '>', 12);


Creates a where condition with where('id', value).

// builder.id(value);
// @value {Object/Array} A value
// returns QueryBuilder


builder.in(name, value, [field])

Creates a in condition.

// builder.in(name, value, [field]);
// @name {String} A field name
// @value {Object/Array} A value
// @field {String} Optional, a field name for obtaining of value from value (it takes a value according to the field name)
// returns QueryBuilder

db.find('users').in('name', ['Peter', 'Lucia']);
db.find('users').in('age', [20, 30, 40]);

// Example with "field" argument
db.find('users').in('id', [{ id: 1 }, { id: 2 }], 'id');

builder.inarray(name, value, [orempty])

Tries to find a value (or array) in array column. The condition is valid if some (just one) value is part of array.

// builder.inarray(name, value, [orempty]);
// @name {String} A field name
// @value {Array/Object} A value (can be array)
// @orempty {Boolean} it will be valid if the array will be empty (default: false)
// returns QueryBuilder

db.find('users').inarray('roles', ['admin', 'support']);

builder.insert(fn, [params])

An insert callback. This callback is executed if the inserting will be performed.

// builder.insert(fn);
// @fn {Function}
// @params {Object} Optional
// returns QueryBuilder

db.modify('users', { name: 'Peter' }, true).where('name', 'Peter').insert(function(doc) {
    // doc === { name: 'Peter' }
    doc.created = new Date();

// Or with params
db.modify('users', { name: 'Peter' }, true).where('name', 'Peter').insert(function(doc, params) {
    // doc === { name: 'Peter' }
    doc.created = new Date();

    // Output: { custom: 'params' }

}, { custom: 'params' });

builder.join(field, name)

This method can join another table/database. This method doesn't create a real join, it performs another SELECT with unique values. You can join data between different database, nested joins and you can do 1:1 or 1:N relations.

// builder.join(field, name);
// @field {String} Joined data wil be assigned into this field name
// @name {String} (Database+) Table name
// returns {QueryBuilder} returns a new QueryBuilder for "table"

Example for relation: 1:1

// Good to know:
var merchants = db.find('tbl_merchant');
var products = merchants.join('products', 'tbl_product').on('merchantid', 'id').first();
// builder.on('COLUM_NAME_IN_tbl_product', 'COLUM_NAME_IN_tbl_merchant')


      "name": "Merchant name",
      "products": { name: 'Shoes', ... }

Example for relation: 1:N

var merchants = db.find('tbl_merchant');
var products = merchants.join('products', 'tbl_product').on('merchantid', 'id');


      "name": "Merchant name",
      "products": [{ name: 'Shoes', ... }]

Example for relation nested joins

var merchants = db.find('tbl_merchant');
var products = merchants.join('products', 'tbl_product').on('merchantid', 'id').first();
var categories = products.join('category', 'cl_category').on('id', 'categoryid').first();


      "name": "Merchant name"
      "products": { name: 'Shoes', category: { name: 'Shoes', ... }, ... }

builder.language(language, [prefix], [skip])

Very helpful feature for multi-language records (localization). The function replaces § chars for entered language with the prefix. It works with builder.fields() and all filtering methods.

// builder.language(language, [prefix])
// @language {String}, can be nullable
// @prefix {String} optional, default "DBMS.languageprefix" (default: "_")
// @skip {String} optional


// Will be translated to:
// SELECT name_sk AS name, description_sk AS description

Skips specific language:

builder.language('en', '_', 'en');
// SELECT name, description

builder.language('sk', '_', 'en');
// SELECT name_sk AS name, description_sk AS description


Sets a limit.

// builder.limit(count);
// @count {Number} A count of records
// returns QueryBuilder


builder.log(msg, [user])

Writes a log into the file.

// builder.log(msg, [user]);
// @msg {String} A log message
// @user {String} Optional, a user name
// returns QueryBuilder

db.remove('users').log('Removed all users', 'Peter');


Creates a QueryBuilder scope.

// builder.make(fn);
// @fn {Function}
// returns DBMS

db.find('users').make(function(builder) {
}).find('products').make(function(buider) {

builder.minute(name, [operator], value)

Creates a minute condition.

// builder.minute(name, [operator], value);
// @name {String} A field name
// @operator {String} Optional, default "="
// @value {Number} A value
// returns QueryBuilder

db.find('users').minute('created', 23);
db.find('users').minute('created', '>', 12);

builder.month(name, [operator], value)

Creates a month condition.

// builder.month(name, [operator], value);
// @name {String} A field name
// @operator {String} Optional, default "="
// @value {Number} A value
// returns QueryBuilder

db.find('users').month('created', 11);
db.find('users').month('created', '>', 2);

builder.notin(name, value)

Creates a not in condition.

// builder.notin(name, value);
// @name {String} A field name
// @value {Object/Array} A value
// returns QueryBuilder

db.find('users').notin('name', ['Peter', 'Lucia']);
db.find('users').notin('age', [20, 30, 40]);


Creates or scope.

// builder.or(fn);
// @fn {Function}
// returns DBMS

db.find('users').or(function(builder) {
    builder.where('name', 'Peter');
    builder.where('name', 'Lucia');
    // Creates a where condition (name='Peter' OR name='Lucia')


Creates an object relation mapping.

// builde.orm([pk])
// @pk {String} a primary key (optional, default empty)

db.read('tbl_user').where('id', 123).orm().data(function(err, user) {

    // "user.dbms" will contain a special copy of QueryBuilder

    // Copies all "obj" values to "user" instance
    // dbms.copy(obj, [true/false: only existing keys])
    // Replaces the record with only modified values
    // Returns {Boolean}
    if (user.dbms.modified(obj)) {
        // some values are modified

    // Replaces "user" instance with a new object

    // Continues with DBMS processing of other queries and "user" instance will be processed in a new connection

    // Saves the current values into the db
    user.dbms.save(callback(err, inserted_count));

    // Removes the current object
    user.dbms.remove(callback(err, inserted_count));


builder.owner(name, value, memberof, [condition])

Creates an ownership condition.

// builder.owner(name, value, memberof, [condition])
// @name {String} a colum name
// @value {String/Number} a value for column "name"
// @member {Array String/Number} a member identifiers (or user is member of owner IDENTIFIERS)
// @condition {Object} optional, additianal condition COLUMN:VALUE

db.find('tbl_app').owner('userid', $.user.id, $.user.members, { shared: true });
// DMBS will generate something like this:
// (userid=$.user.id OR (userid IN ($.user.members) AND shared=TRUE))

Another example:

db.find('tbl_user').fields('dtupdated').take(5).orm('id').data(function(err, users) {
    for (var i = 0; i < users.length; i++) {
        var user = users[i];
        user.dtupdated = new Date();

builder.page(page, limit)

Sets take and skip according to the page and limit.

// builder.page(page, limit);
// @page {Number} A current page index
// @limit {Number} A maximum count of records per page
// returns QueryBuilder

db.find('users').page(1, 20);

builder.paginate(page, limit, maxlimit)

Sets take and skip according to the page and limit. This method converts page and limit to number automatically and safely if some value is string.

// builder.page(page, limit);
// @page {String} A current page index (default: 1)
// @limit {String} A maximum count of records per page
// @maxlimit {Number} A maximum/default count of records if "@limit" exceeds
// returns QueryBuilder

db.find('users').paginate(query.page, query.limit, 100);

builder.permit(name, type, value, [useridfield], [userid], [must])

Makes a condition for permissions. It's targeted for our designed permission system. It works with PostgreSQL version only:

permissions _varchar

Permissions are stored like this:

['C@admin', 'W@admin', 'R@admin', 'D@admin', 'R123456', 'R#employees']
  • first char e.g. C means a permission, in this case:
    • C create
    • R read
    • U update
    • D delete
  • second char @ means a role name, so e.g. @admin means admin role
  • second char # means a group name, so e.g. #employees means employees group
  • otherwise it means user identifier, e.g. R123456 is a user with id 123456
// builder.permit(name, type, value, [useridfield], [userid], [must]);
// @name {String} a field name
// @type {String} a permission type
// @value {String Array} list of user permissions
// @useridfield {String} optional, a name of userfield
// @userid {String} optional, current user id
// @must {Boolean} optional, reverses condition (default: false)

var mypermissions = ['@admin', '123456', '#employees'];
var myuserid = '654321';

// Tries to find all records with "R"userrolegroup, e.g. "R@admin", "@R123456" or "R#employees"
builder.permit('permissions', 'R', permissions);

// Tries to find all records with "R"userrolegroup, "W"userrolegroup, "D"userrolegroup
// A record must contain only the one permission type
builder.permit('permissions', 'RWD', permissions);

// Tries to find all records with "R"userrolegroup or user records
builder.permit('permissions', 'R', permissions, 'userid', myuserid);


Sets a name of the primary key. It's targeted for PostgreSQL only for inserting values into tables, schema of which contains primary key with serial data type.

// builder.primarykey(name);
// @name {String} Name of primary key
// returns QueryBuilder

db.insert('tbl_document', { name: 'New document' }).primarykey('id').callback(function(err, response) {
    console.log('Inserted ID: ' + response);

builder.regexp(name, value)

Creates a Regular Expression condition.

// builder.regexp(name, value);
// @name {String} A field name
// @value {RegExp} A value
// returns QueryBuilder

db.find('users').regexp('name', /\d+/);

builder.search(name, value, [operator])

Performs like operation.

// builder.search(name, value, [operator]);
// @name {String} A field name
// @value {String} A value
// @operator {String} Optional, possible values: "*" (default), "beg", "end"
// returns QueryBuilder

db.find('users').search('name', 'Peter');
db.find('users').search('name', 'Peter', 'beg');

builder.searchall(name, value)

Performs like operation for each word.

// builder.search(name, value, [operator]);
// @name {String} A field name
// @value {String / String Array} A value
// returns QueryBuilder

db.find('users').searchall('name', 'Peter Sirka');
// is same as:
db.find('users').searchall('name', ['Peter', 'Sirka']);


Performs like operation for the entire value in a row (in all columns). DBMS performs lowercase and replaces y for i.

// builder.searchfull(value);
// @value {String} A value
// returns QueryBuilder

db.find('users').searchfull('Peter Sirka');


Assigns a response to the defined property or it can assign a value to insert/update/modify operation (value must be defined).

// builder.set(prop, [value]);
// @prop {String} A property name
// @value {Object} A value, optional
// returns QueryBuilder

// 1. Wihtout value the method assigns the result to output
// Main output: { users: [ ..., ..., ... ] }

// 2. With filled value the method assigns the value into the model for insert/update/modify operation
db.update('users').set('name', 'Peter').set('age', 33).where('id', 1234);


Skips a count of records.

// builder.skip(count);
// @count {Number} A count of records
// returns QueryBuilder


builder.sort(name, [desc])

Creates a sort condition.

// builder.sort(name, [desc]);
// @name {String} A field name
// @desc {Boolean} Default: "false"
// returns QueryBuilder

db.find('users').sort('age', true);

builder.subquery(prop, query)

Creates a subquery for SELECT. Currently is supported for PostgreSQL only.

// builder.subquery(prop, query);
// @prop {String} A field name
// @query {String} A query
// returns QueryBuilder

db.find('tbl_user').subquery('countorders', 'SELECT COUNT(1) FROM tbl_order WHERE tbl_order.userid=tbl_user.id');


Takes a count of records.

// builder.take(count);
// @count {Number} A count of records
// returns QueryBuilder



Performs a transformation for value which will be inserted/modified. Works only with db.insert(), db.modify() or db.update() methods.

builder.transform(function(item, response, lastresponse) {
    // item {Object} Current object for inserting/updating (keep a reference)
    // response {Object} Contains all results from previous commands
    // lastresponse {Object} Contains latest response

    // Example:
    item.roomid = lastresponse.id;

builder.query(query, [argument])

Appends a raw query.

// builder.query(query, [arguments]);
// @query {String}
// returns QueryBuilder


builder.year(name, [operator], value)

Creates a year condition.

// builder.year(name, [operator], value);
// @name {String} A field name
// @operator {String} Optional, default "="
// @value {Number} A value
// returns QueryBuilder

db.find('users').year('created', 2018);
db.find('users').year('created', '>', 2012);


Creates a where condition with where('userid', value).

// builder.userid(value);
// @value {Object/Array} A value
// returns QueryBuilder



Creates a where condition with where('isremoved', false).

// builder.undeleted();
// returns QueryBuilder


builder.where(name, [operator], value)

Creates a where condition.

// builder.where(name, [operator], value);
// @name {String} A field name
// @operator {String} Optional, default "="
// @value {Object} A value
// returns QueryBuilder

db.find('users').where('name', 'Peter');
db.find('users').where('age', '<>', 33);