DBMS / 03. Methods
Updated: 27. July 2020
Author: Peter Širka

03. Methods

Professional Support Chat with contributors

  • DBMS(name) is a global variable for entire project
var db = DBMS();

Quick links:

Properties

db.response

This property contains all obtained results.

var db = DBMS();

db.read('tbl_user').where('id', '12345');
db.read('tbl_auth').where('id', '12345').callback(function() {
    console.log(db.response);
    // tbl_user: { ... }
    // tbl_auth: { ... }
});

Methods

db.avg(name, prop)

Performs AVG to DB according to name argument. A response is a number.

// db.avg(name, prop);
// @name {String} A collection/table name
// @prop {String} A property name
// returns QueryBuilder
// response: Number

db.avg('users', 'age');

db.begin([db_name])

Begins a transaction. Works only with relational databases.

// db.begin([db_name]);
// @db_name {String} Optional, default: "default DB"
// returns DBMS

db.blob([name])

This method can store a file stream as a blob. Currently it works with PostgreSQL only.

// db.blob([name]);
// @name {String} Optional, A collection/table name (default: 'default' database)
// returns Object

// Writing:
db.blob().write(read_stream_OR_buffer, [name], callback(err, id));

// Reading:
db.blob().read(id, callback(err, read_stream));

// Removing:
db.blob().remove(id, callback(err));

db.callback(fn)

Executes fn when the module ends all DB operations.

// db.callback(fn);
// @fn {Function} A callback
// returns DBMS

db.callback(function(err, response) {
    // @err {ErrorBuilder} can be "null"
    // @response {Object} with results
    console.log(err, response);  
});

db.commit([db_name])

Performs COMMIT for open transaction. Works only with relational databases.

// db.commit([db_name]);
// @db_name {String} Optional, default: "default DB"

db.count(name)

Performs COUNT to DB according to name argument. A response is a number.

// db.count(name);
// @name {String} A collection/table name
// returns QueryBuilder
// response: Number

db.count('users');

db.data(fn)

Executes fn when the module ends without any error.

// db.data(fn);
// @fn {Function} A callback
// returns DBMS

db.data(function(response) {
    // @response {Object} with results
    console.log(response);
});

db.diff(name, form, [prop])

Performs a diff between database and form.

// db.diff(name, form, [prop]);
// @name {String} A collection/table name
// @form {Object Array} with received array from form
// @prop {String} a property name for pairing (default: "id")
// returns DBMS

var form = [{ id: '1234', name: 'Shoes' }];

db.diff('tbl_product_category', form, 'id').data(function(response) {

    response.add; // {Object Array} contains object from "form" variable
    response.upd; // {Object Array} contains { db: DB_ITEM, form: FORM_ITEM }
    response.rem; // {String Array} contains identifiers of all removed items

});

db.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);
    db.err('error-users-404');
    db.done($, function(response, param) {
        // @response {Object} with a positive result
        console.log(response);
    });
});

db.end([db_name])

Performs COMMIT or ROLLBACK according to the error stack.

// db.end([db_name]);
// @db_name {String} Optional, default: "default DB"

db.error(err, [reverse])

Performs a small validation for the last received data.

// db.error(err, [reverse]);
// @err {String/Function} A collection/table name
// @reverse {Boolean} Performs a reverse validation
// returns DMBS

db.error('usernotfound');
// or 
db.error(val => val != null ? true : 'usernotfound');

// A real example:
db.read('users').where('name', 'Peter');
db.error('usernotfound');
db.find('orders').in('userid', db.get('users.id'));
db.callback(console.log);

db.fail(fn)

Executes fn when the module ends with some error.

// db.fail(fn);
// @fn {Function} A callback
// returns DBMS

db.fail(function(err) {
    // @err {ErrorBuilder}
    console.log(err);
});

db.find(name)

Performs SELECT to DB according to name argument.

// db.find(name);
// @name {String} A collection/table name
// returns QueryBuilder
// response: Array

db.find('users');

db.get(path)

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 resopnse
// returns Function

db.find('invoices').in('id', db.get('users.id'));

db.check(name)

Checks if the database contains some records. The method returns Boolean in the callback.

// db.check(name);
// @name {String} A collection/table name
// returns QueryBuilder
// response: Boolean

db.check('users');

db.insert(name, doc, [unique])

Performs INSERT to DB according to name argument.

// db.insert(name, doc, [unique]);
// @name {String} A collection/table name
// @doc {Object} A document
// @unique {Boolean} Checks if the record doesn't exist
// returns QueryBuilder
// response: Number

db.insert('users', { name: 'Peter', age: 33 });

// Or with "unique" argument:
db.insert('users', { name: 'Peter', age: 33 }, true).where('name', 'Peter');

db.invalid(name, [err])

Adds error to ErrorBuilder.

// db.invalid(name, [err]);
// @name {String} A name or key
// @err {Error/String} Optional, an error or error description
// returns DBMS

db.invalid('error-users');
// or
db.invalid('error-users', 'User not found.');

db.kill([reason])

Kills all next queries into the DB. reason is used as an error for ErrorBuilder.

// db.kill([reason]);
// @reason {String} Optional, a reason
// returns DBMS

db.kill('error-users');

db.list(name)

Performs COUNT and SELECT to DB according to name argument. A response is in a specific format.

// db.list(name);
// @name {String} A collection/table name
// returns QueryBuilder
// response: Object

db.list('users');

// Output will be:
// { items: [...], count: 100, page: 1, pages: 10, limit: 10 }

db.make(fn)

Creates a scope.

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

db.make(function(db) {
    db.find();
    db.read();
    db.must();
    // etc..
});

db.max(name, prop)

Performs MAX to DB according to name argument. A response is a number.

// db.max(name, prop);
// @name {String} A collection/table name
// @prop {String} A property name
// returns QueryBuilder
// response: Number

db.max('users', 'age');

db.min(name, prop)

Performs MIN to DB according to name argument. A response is a number.

// db.min(name, prop);
// @name {String} A collection/table name
// returns QueryBuilder
// response: Number

db.min('users', 'age');

db.modify(name, doc, [insert])

Performs UPDATE for the specific fields. A response is a number with count of modified documents.

// db.modify(name, doc, [insert]);
// @name {String} A collection/table name
// @doc {Object} A document
// @insert {Boolean} Performs "insert" if the document won't exist.
// returns QueryBuilder
// response: Number

db.modify('users', { '+age': 1, dateupdated: NOW }, true).where('name', 'Peter');

// Supports incremental values
// +prop performs val + 1
// -prop performs val - 1
// *prop performs val * 1
// /prop performs val / 1
// >prop performs Math.max(dbval, val);
// <prop performs Math.min(dbval, val);

// Supports toggles (supported in PostgreSQL)
// { '!prop': 1 }
// Performs "prop = !prop"

// Raw values (supported in PostgreSQL)
// { '=now': 'NOW()' }
// Performs "now=NOW()"

// +v0.7.7
// Read/Modify feature:
db.modify('users', function(doc, output, responses) {

    // @doc {Object}
    // @output {Object} a current output
    // @responses {Object} an object with all responses

    // doc can be "null" if you the "insert" param is "true"
    // DBMS will update only the fields declared below
    return { name: doc.name.toUpperCase() };

    // This skips updateting:
    // return null;

}).where('id', 123456).fields('name');

db.output(obj)

Sets a default output object.

// db.output(output);
// @obj {Object}
// returns DBMS

db.output({ type: 'detail' });
db.read('users').where('name', 'Peter').set('user');

// Output:
// { type: 'detail', user: { ... }}

db.read(name)

Performs SELECT with LIMIT 1 to DB according to name argument. A response is an object with the record data.

// db.read(name);
// @name {String} A collection/table name
// returns QueryBuilder
// response: Object

db.read('users');

db.remove(name)

Performs DELETE in DB according to name argument. A response is a number with count of removed documents.

// db.remove(name);
// @name {String} A collection/table name
// returns QueryBuilder
// response: Number

db.remove('users').where('name', 'Peter');

db.rollback([db_name])

Performs ROLLBACK for open transaction. Works only with relational databases.

// db.rollback([db_name]);
// @db_name {String} Optional, default: "default DB"
// returns DBMS

db.save(name, [isUpdate], model, prepare)

Can perform modify() or insert().

db.save(name, isUpdate, [model], prepare);
// @name {String} A collection/table name
// @isUpdate {Boolean} Optional, determines update (default: "model.id")
// @model {Object} Object for insert/modification
// @prepare {Function(builder, isUpdate, data)}

db.save('users', model, function(builder, isUpdate, data) {
    
    if (isUpdate) {
        data.updated = new Date();
        builder.where('id', data.id);
    } else {
        data.id = '123456';
        data.created = new Date();
    }
    
});

db.stream(name, limit, processor, [done])

This method can stream data from database.

// db.stream(name, limit, processor, done);
// @name {String} A collection/table name
// @limit {Number} How many records can take from DB?
// @processor {Function(response, next)}
// @processor {Function(err, count)} Optional
// returns QueryBuilder

db.stream('tbl_user', 10, function(response, next) {

    // Here you can process record
    console.log(response);

    // Next 10 users
    setTimeout(next, 1000);

}, function() {
    // DONE
});

db.sum(name, prop)

Performs SUM to DB according to name argument. A response is a number.

// db.sum(name, prop);
// @name {String} A collection/table name
// @prop {String} A property name
// returns QueryBuilder
// response: Number

db.sum('users', 'age');

db.task(fn)

Performs a sync task in a row.

// db.task(fn);
// @fn {Function}

db.task(function(responses, lastresponse) {
    lastresponse.user.name = lastresponse.user.name.toUpperCase();
});

db.transaction([db_name])

Opens a transaction. Works only with relational databases. It's alias to .begin() method.

// db.transaction([db_name]);
// @db_name {String} Optional, default: "default DB"
// returns DBMS

db.query([db_name], query, [value])

Executes a query. IMPORTANT: each DB engine can have own implementation of query arguments.

// db.query([db_name], query, [value]);
// @db_name {String} Connection ID (optional, default: "default")
// @query {String} Raw query
// @value {Object} Optional, query arguments
// returns QueryBuilder

// Example for PostgreSQL
db.query('SELECT * FROM tbl_users WHERE id=$1', [12345]).callback(console.log);
db.query('eshop_db', 'SELECT * FROM tbl_users').where('id', 12345).callback(console.log);

db.update(name, doc, [insert])

Performs UPDATE to DB according to name argument. A response is a number with count of updated documents.

// db.update(name, doc, [insert]);
// @name {String} A collection/table name
// @doc {Object} A document
// @insert {Boolean} Performs "insert" if the document won't exist.
// returns QueryBuilder
// response: Number

db.update('users', { name: 'Peter', age: 33 }, true).where('name', 'Peter');