Home

Awesome

A very helpful ORM for node.js

Professional Support Chat with contributors NPM version NPM downloads MIT License

IMPORTANT:

Initialization

Basic initialization

PostgreSQL

// Example: postgresql://user:password@127.0.0.1/database
var Agent = require('sqlagent/pg').connect('connetion-string-to-postgresql');

/*
// It's executed when the datbase returns an unexpected error
Agent.error = function(err, type, query) {

};
*/

// Agent() returns new instance of SQL Agent
var sql = Agent();

Additional configuration:

postgresql://user:password@127.0.0.1/database?native=true&ssl=true

MySQL

// Example: mysql://user:password@127.0.0.1/database
var Agent = require('sqlagent/mysql').connect('connetion-string-to-mysql');
var sql = new Agent();

SQL Server (MSSQL)

// Example: mssql://user:password@127.0.0.1/database
// Example with name of instance: mssql://user:password@localhost_SQLEXPRESS/database
var Agent = require('sqlagent/sqlserver').connect('connetion-string-to-mssql');
var sql = new Agent();

MongoDB

// Example: mongodb://user:password@127.0.0.1/database
var Agent = require('sqlagent/mongodb').connect('connetion-string-to-mongodb');
var nosql = new Agent();

Initialization for Total.js

Create a definition file:

// Below code rewrites total.js database prototype
require('sqlagent/pg').init('connetion-string-to-postgresql', [debug]); // debug is by default: false
require('sqlagent/mysql').init('connetion-string-to-mysql', [debug]); // debug is by default: false
require('sqlagent/sqlserver').init('connetion-string-to-sqlserver', [debug]); // debug is by default: false
require('sqlagent/mongodb').init('connetion-string-to-mongodb', [debug]); // debug is by default: false

Usage:

// When you use RDMBS:
// var sql = DATABASE([ErrorBuilder]);
var sql = DATABASE();
// sql === SqlAgent

// +v9.9.6 enable debugging
sql.debug = true;

// When you use MongoDB:
// var nosql = DATABASE([ErrorBuilder]);
var nosql = DATABASE();
// nosql === SqlAgent

IMPORTANT

In order for mysql to return Boolean values please set the data type in db to BIT(1) and use bellow code for initialization.

var Agent = require('sqlagent/mysql').connect({
    host: "localhost",
    user: "root",
    password: "",
    database: "test",
    typeCast: function castField( field, useDefaultTypeCasting ) {
        if ( ( field.type === "BIT" ) && ( field.length === 1 ) ) {
            var bytes = field.buffer();
            return( bytes[ 0 ] === 1 );
        }
        return( useDefaultTypeCasting() );
    }
});
var sql = new Agent();

Usage

Select

instance.select([name], table)

sql.select('users', 'tbl_user').make(function(builder) {
    builder.where('id', '>', 5);
    builder.page(10, 10);
});

sql.select('orders', 'tbl_order').make(function(builder) {
    builder.where('isremoved', false);
    builder.page(10, 10);
    builder.fields('amount', 'datecreated');
});

sql.select('products', 'tbl_products').make(function(builder) {
    builder.between('price', 30, 50);
    builder.and();
    builder.where('isremoved', false);
    builder.limit(20);
    builder.fields('id', 'name');
});

sql.exec(function(err, response) {
    console.log(response.users);
    console.log(response.products);
    console.log(response.admin);
});

Push (only for MongoDB)

instance.push([name], collection, fn(collection, callback(err, response))

sql.push('users', 'users', function(collection, callback) {

    var $group = {};
    $group._id = {};
    $group._id = '$category';
    $group.count = { $sum: 1 };

    var $match = {};
    $match.isremoved = false;

    var pipeline = [];
    pipeline.push({ $match: $match });
    pipeline.push({ $group: $group });

    collection.aggregate(pipeline, callback);
});

// OR

sql.push('users', 'users', function(collection, callback) {
    collection.findOne({ name: 'Peter' }, { name: 1, age: 1 }).toArray(callback);
});

Listing

instance.listing([name], table)

sql.listing('users', 'tbl_user').make(function(builder) {
    builder.where('id', '>', 5);
    builder.page(10, 10);
});

sql.exec(function(err, response) {

    // users will contain:
    // .count --> count of all users according to the filter
    // .items --> selected items
    // .page  --> a page number (+v11.0.0)
    // .pages --> page count (+v11.0.0)
    // .limit --> items limit per page (+v11.0.0)

    console.log(response.users.count);
    console.log(response.users.items);
});

Save

instance.save([name], table, isINSERT, prepare(builder, isINSERT));

sql.save('user', 'tbl_user', somemodel.id === 0, function(builder, isINSERT) {

    builder.set('name', somemodel.name);

    if (isINSERT) {
        builder.set('datecreated', new Date());
        return;
    }

    builder.inc('countupdate', 1);
    builder.where('id', somemodel.id);
});

Insert

instance.insert([name], table)

sql.insert('user', 'tbl_user').make(function(builder) {
    builder.set({ name: 'Peter', age: 30 });
});

sql.insert('log', 'tbl_logs').make(function(builder) {
    builder.set('message', 'Some log message.');
    builder.set('created', new Date());
});

sql.exec(function(err, response) {
    console.log(response.user); // response.user.identity (INSERTED IDENTITY)
    console.log(response.log); // response.log.identity (INSERTED IDENTITY)
});

IMPORTANT: identity works only with auto-increment in MS SQL SERVER.

Update

instance.update([name], table)

sql.update('user1', 'tbl_user').make(function(builder) {
    builder.set({ name: 'Peter', age: 30 });
    builder.where('id', 1);
});

// is same as
sql.update('user2', 'tbl_user').make(function(builder) {
    builder.where('id', 1);
    builder.set('name', 'Peter');
    builder.set('age', 30);
});

sql.exec(function(err, response) {
    console.log(response.user1); // returns {Number} (count of changed rows)
    console.log(response.user2); // returns {Number} (count of changed rows)
});

Delete

instance.delete([name], table)
instance.remove([name], table)

sql.remove('user', 'tbl_user').make(function(builder) {
    builder.where('id', 1);
});

sql.exec(function(err, response) {
    console.log(response.user); // returns {Number} (count of deleted rows)
});

Query

instance.query([name], query)

sql.query('user', 'SELECT * FROM tbl_user').make(function(builder) {
    builder.where('id', 1);
});

sql.exec(function(err, response) {
    console.log(response.user);
});

Aggregation

instance.count([name], table)

var count = sql.count('users', 'tbl_user');
count.between('age', 20, 40);

sql.exec(function(err, response) {
    console.log(response.users); // response.users === number
});

instance.max([name], table, column)
instance.min([name], table, column)
instance.avg([name], table, column)

var max = sql.max('users', 'tbl_user', 'age');
max.where('isremoved', false);

sql.exec(function(err, response) {
    console.log(response.users); // response.users === number
});

Exists

instance.exists([name], table)

var exists = sql.exists('user', 'tbl_user');
exists.where('id', 35);

sql.exec(function(err, response) {
    console.log(response.user); // response.user === Boolean (in correct case otherwise undefined)
});

Compare

instance.compare([name], table, value, [keys])

var compare = sql.compare('user', 'tbl_user', { name: 'Peter', age: 33 });
// OR: var compare = sql.compare('user', 'tbl_user', { name: 'Peter', age: 33 }, ['name']); --> compares only name field
// OR: compare.fields('name', 'age'); --> compares these fields (if aren't defined "keys")

compare.where('id', 35);

sql.exec(function(err, response) {

    if (response.user) {
        // shows the property names which were changed
        console.log(response.user.diff);
    }

});

instance.max([name], table, column)
instance.min([name], table, column)
instance.avg([name], table, column) // doesn't work with Mongo

var max = sql.max('users', 'tbl_user', 'age');
max.where('isremoved', false);

sql.exec(function(err, response) {
    console.log(response.users); // response.users === number
});

Transactions

sql.begin();
sql.insert('tbl_user', { name: 'Peter' });
sql.commit();

Special cases

How to set the primary key?

// instance.primary('column name') is same as instance.primaryKey('column name')

instance.primary('userid');
instance.insert('tbl_user', ...);

instance.primary('productid');
instance.insert('tbl_product', ...);

instance.primary(); // back to default "id"

How to use latest primary id value for relations?

// primary key is id + autoincrement
var user = sql.insert('user', 'tbl_user');
user.set('name', 'Peter');

var address = sql.insert('tbl_user_address');
address.set('id', sql.$$);
address.set('country', 'Slovakia');

sql.exec();

How to use latest primary id value for multiple relations?

// primary key is id + autoincrement
var user = sql.insert('user', 'tbl_user');
user.set('name', 'Peter');

// Lock latest inserted identificator
sql.lock();
// is same as
// sql.put(sql.$$);

var address = sql.insert('tbl_user_address');
address.set('iduser', sql.$$); // adds latest primary id value
address.set('country', 'Slovakia');

var email = sql.insert('tbl_user_email');
email.set('iduser', sql.$$); // adds locked value
email.set('email', 'petersirka@gmail.com');
sql.unlock();

sql.exec();

If not or If exists

instance.ifnot('user', function(error, response, value) {
    // error === ErrorBuilder
    // It will be executed when the results `user` contains a negative value or array.length === 0
    // Is executed in order
});

instance.ifexists('user', function(error, response, value) {
    // error === ErrorBuilder
    // It will be executed when the results `user` contains a positive value or array.length > 0
    // Is executed in order
});

Default values

sql.default(function(response) {
    response.count = 0;
    response.user = {};
    response.user.id = 1;
});

// ...
// ...

sql.exec(function(err, response) {
    console.log(response);
});

Modify results

sql.select(...);
sql.insert(...);

sql.modify(function(response) {
    response.user = {};
    response.user.identity = 10;
});

// ...
// ...

// Calling:
// 1. select
// 2. insert
// 3. modify
// 4. other commands
sql.exec(function(err, response) {
    console.log(response);
});

Preparing (dependencies)

var user = sql.update('user', 'tbl_user');
user.where('id', 20);
user.set('name', 'Peter');

var select = sql.select('address', 'tbl_address');
select.where('isremoved', false);
select.and();
select.where('city', 'Bratislava');
select.limit(1);

// IMPORTANT:
sql.prepare(function(error, response, resume) {
    // error === ErrorBuilder
    sql.builder('address').set('idaddress', response.address.id);
    resume();
});

var address = sql.update('address', 'tbl_user_address');
address.where('iduser', 20);

sql.exec();

Validation

sql.validate(fn)

var select = sql.select('address', 'tbl_address');
select.where('isremoved', false);
select.and();
select.where('city', 'Bratislava');
select.limit(1);

// IMPORTANT:
sql.validate(function(error, response, resume) {

    // error === ErrorBuilder

    if (!response.address) {
        error.push('Sorry, address not found');
        // cancel pending queries
        return resume(false);
    }

    sql.builder('user').set('idaddress', response.id);

    // continue
    resume();
});

var user = sql.update('user', 'tbl_user');
user.where('id', 20);
user.set('name', 'Peter');

sql.exec();

Validation alternative (+v4.0.0)

// IMPORTANT:
sql.validate(function(error, response) {

    // error === ErrorBuilder

    if (!response.address) {
        error.push('Sorry, address not found');
        return false;
    }

    sql.builder('user').set('idaddress', response.id);
    return true;
});

sql.validate([result_name_for_validation], error_message, [reverse]);

If the function throw error then SqlAgent cancel all pending queris (perform Rollback if the agent is in transaction mode) and executes callback with error.

var select = sql.select('address', 'tbl_address');
select.where('isremoved', false);
select.and();
select.where('city', 'Bratislava');
select.limit(1);

// IMPORTANT:
sql.validate('Sorry, address not found');

var user = sql.select('user', 'tbl_user');
user.where('id', 20);

sql.validate('Sorry, user not found');
sql.validate('Sorry, address not found for the current user', 'address');

sql.exec();

Validation alternative (+v8.0.0)

sql.validate('products', n => n.length > 0, 'error-products');
sql.validate('detail', n => !n, 'error-detail');

Global

Stored procedures

sql.query('myresult', 'exec myprocedure');

// with params
// sql.query('myresult', 'exec myprocedure $1', [3403]);

sql.exec(function(err, response) {
    console.log(response.myresult);
});

Skipper

sql.select('users', 'tbl_users');
sql.skip(); // skip orders
sql.select('orders', 'tbl_orders');

sql.bookmark(function(error, response) {
    // error === ErrorBuilder
    // skip logs
    sql.skip('logs');
});

sql.select('logs', 'tbl_logs');

sql.exec(function(err, response) {
    console.log(response); // --- response will be contain only { users: [] }
});

Bookmarks

Bookmark is same as sql.prepare() function but without resume argument.

sql.select('users', 'tbl_users');

sql.bookmark(function(error, response) {
    // error === ErrorBuilder
    console.log(response);
    response['custom'] = 'Peter';
});

sql.select('orders', 'tbl_orders');

sql.exec(function(err, response) {
    response.users;
    response.orders;
    response.custom; // === Peter
});

Error handling

sql.select('users', 'tbl_users');

sql.validate(function(error, response, resume) {

    // error === ErrorBuilder

    if (!response.users || respone.users.length === 0)
        error.push(new Error('This is error'));

    // total.js:
    // error.push('error-users-empty');

    resume();
});

sql.select('orders', 'tbl_orders');

// sql.validate([error message], [result name for validation])
sql.validate('error-orders-empty');
// is same as:
// sql.validate('error-orders-empty', 'orders');

sql.validate('error-users-empty', 'users');

Escaping values

var escaped1 = Agent.escape(value);

// or ...

var sql = new Agent();
var escaped2 = sql.escape(value);

Predefined queries

Agent.query(name, query);

Agent.query('users', 'SELECT * FROM tbl_users');
Agent.query('allorders', 'SELECT * FROM view_orders');

sql.query('users').where('id', '>', 20);
sql.query('orders', 'allorders').limit(5);

sql.exec(function(err, response) {
    console.log(response[0]); // users
    console.log(response.orders); // orders
});

Waiting for specified values

sql.when('users', function(error, response, value) {
    console.log(value);
});

sql.when('orders', function(error, response, value) {
    console.log(value);
});

sql.select('users', 'tbl_users');
sql.select('orders', 'tbl_orders');
sql.exec();

Bonus

How to get latest inserted ID?

sql.insert('user', 'tbl_user').set('name', 'Peter');

sql.bookmark(function() {
    console.log(sql.id);
});

sql.exec();

Expected values? No problem

sql.expected(name, index, property); // gets a specific value from the array
sql.expected(name, property);

sql.select('user', 'tbl_user').where('id', 1).first();
sql.select('products', 'tbl_product').where('iduser', sql.expected('user', 'id'));

sql.exec();

Measuring time

sql.exec(function(err, response) {
    console.log(sql.time + ' ms');
    // or
    // console.log(this.time)
});

Events

sql.on('query', function(name, query, params){});
sql.on('data', function(name, response){});
sql.on('end', function(err, response, time){});

Generators in total.js

function *some_action() {
    var sql = DB();

    sql.select('users', 'tbl_user').make(function(select) {
        select.where('id', '>', 100);
        select.and();
        select.where('id', '<', 1000);
        select.limit(10);
    });

    sql.select('products', 'tbl_product').make(function(select) {
        select.where('price', '<>', 10);
        select.limit(10);
    });

    // get all results
    var results = yield sync(sql.$$exec())();
    console.log(results);

    // or get a specific result:
    var result = yield sync(sql.$$exec('users'))();
    console.log(result);
}

Priority

Set a command priority, so the command will be processed next round.

sql.select('... processed as second')
sql.select('... processed as first');
sql.priority(); // --> takes last item in queue and inserts it as first (sorts it immediately).

Debug mode

Debug mode writes each query to console.

sql.debug = true;

We need to return into the callback only one value from the response object

sql.exec(callback, 0); // --> returns first value from response (if isn't error)
sql.exec(callback, 'users'); // --> returns response.users (if is isn't error)

sql.exec(function(err, response) {
    if (err)
        throw err;
    console.log(response); // response will contain only orders
}, 'orders');

SqlBuilder

// Creates SqlBuilder
var builder = sql.$;

builder.where('id', '<>', 20);
builder.set('isconfirmed', true);

// e.g.:
sql.update('users', 'tbl_users', builder);
sql.exec(function(err, response) {
    console.log(response.users);
})

builder.callback(fn)

builder.callback(function(err, response) {

});

+v11.0.0 returns a value from DB

builder.set()

builder.set(name, value)

adds a value for update or insert

builder.raw()

builder.raw(name, value)

adds a raw value for update or insert without SQL encoding

builder.set(obj)

adds an object for update or insert value collection

builder.set({ name: 'Peter', age: 30 });
// is same as
// builder.set('name', 'Peter');
// builder.set('age', 30);

builder.inc()

builder.set(name, [type], value)

adds a value for update or insert

builder.inc('countupdate', 1);
builder.inc('countview', '+', 1);
builder.inc('credits', '-', 1);

// Short write
builder.inc('countupdate', '+1');
builder.inc('credits', '-1');

builder.rem()

builder.rem(name)

removes an value for inserting or updating.

builder.set('name', 'Peter');
builder.rem('name');

builder.sort()

builder.sort(name, [desc])
builder.order(name, [desc])

adds sorting

builder.random()

builder.random()

Reads random rows. IMPORTANT: MongoDB doesn't support this feature.

builder.skip()

builder.skip(value)

skips records

builder.take()

builder.take(value)
builder.limit(value)

takes records

builder.page()

builder.page(page, maxItemsPerPage)

sets automatically sql.skip() and sql.take()

builder.first()

builder.first()

sets sql.take(1)

builder.join()

builder.join(name, on, [type])

adds a value for update or insert

builder.join('address', 'address.id=user.idaddress');

builder.where()

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

add a condition after SQL WHERE

builder.group()

builder.group(name)
builder.group(name1, name2, name3); // +v2.9.1

creates a group by in SQL query

builder.having()

builder.having(condition)

adds having in SQL query

builder.and()

builder.and()

adds AND to SQL query. IMPORTANT: In MongoDB has to be this operator used before all queries.

builder.or()

builder.or()

adds OR to SQL query. IMPORTANT: In MongoDB has to be this operator used before all queries.

builder.in()

builder.in(name, value)

adds IN to SQL query

builder.between()

builder.between(name, a, b)

adds between to SQL query

builder.overlaps()

builder.overlaps(valueA, valueB, columnA, columnB)

adds overlaps to SQL query

builder.like()

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

adds like command

builder.sql()

builder.sql(query, [param1], [param2], [param..n])

adds a custom SQL to SQL query

builder.sql('age=? AND name=?', 20, 'Peter');

builder.query()

builder.query(fieldname, filter)

adds a custom QUERY to filter.

builder.query('tags', { $size: 0 });

builder.scope()

builder.scope(fn);

adds a scope ()

builder.where('user', 'person');
builder.and();

// RDMBS:
builder.scope(function() {
    builder.where('type', 20);
    builder.or();
    builder.where('age', '<', 20);
});

// MongoDB:
builder.scope(function() {
    builder.or();
    builder.where('type', 20);
    builder.where('age', '<', 20);
});

// creates: user='person' AND (type=20 OR age<20)

builder.define()

builder.define(name, SQL_TYPE_LOWERCASE);

var insert = sql.insert('user', 'tbl_user');

insert.set('name', 'Peter Širka');
insert.define('name', 'varchar');
insert.set('credit', 340.34);
insert.define('credit', 'money');
sql.exec();

builder.schema()

builder.schema()

sets current schema for where, in, between, field, fields, like

builder.schema('b');
builder.fields('name', 'age'); // --> b."name", b."age"
builder.schema('a');
builder.fields('name', 'age'); // --> a."name", a."age"
builder.fields('!COUNT(id) as count') // --> a.COUNT()

builder.escape()

builder.escape(string)

escapes value as prevention for SQL injection

builder.fields()

builder.fields()

sets fields for data selecting.

builder.fields('name', 'age'); // "name", "age"
builder.fields('!COUNT(id)'); // Raw field: COUNT(id)
builder.fields('!COUNT(id) --> number'); // Raw field with casting: COUNT(id)::int (in PG), CAST(COUNT(id) as INT) (in SQL SERVER), etc.

builder.replace()

builder.replace(builder, [reference])

replaces current instance of SqlBuilder with new. The argument reference (default: false) when is true creates a reference to builder (it doesn't clone it). Better performance with lower memory.

builder.toString()

builder.toString()

creates escaped SQL query (internal)

Blob

PostgreSQL

// sql.writeStream(filestream, [buffersize](default: 16384), callback(err, loid))
sql.writeStream(Fs.createReadStream('/file.png'), function(err, loid) {
    // Now is the file inserted
    // Where is the file stored?

    // loid === NUMBER
    // SELECT * FROM pg_largeobject WHERE loid=loid
});

// sql.writeBuffer(buffer, callback(err, loid))
sql.writeBuffer(Buffer.from('Peter Širka', 'utf8'), function(err, loid) {
    // Now is the buffer inserted
    // Where is the buffer stored?

    // loid === NUMBER
    // SELECT * FROM pg_largeobject WHERE loid=loid
});

// sql.readStream(loid, [buffersize](default: 16384), callback(err, stream, size))
sql.readStream(loid, function(err, stream, size) {
    // stream is created
});

MongoDB

// nosql.writeStream(id, stream, filename, [metadata], [options], callback)
nosql.writeStream(new ObjectID(), Fs.createReadStream('logo.png'), 'logo.png', function(err) {
    // Now is the stream inserted
});

// nosql.readStream(id, [options], callback(err, stream, metadata, size, filename))
nosql.readStream(id, function(err, stream, metadata, size, filename) {
    stream.pipe(Fs.createWriteStream('myfile.png'));
});

// get file info
nosql.select('fs.files').make(function(builder){
    // available fields - _id,filename,contentType,length,chunkSize,uploadDate,aliases,metadata,md5
    builder.fields('filename', 'metadata');
});

nosql.exec(function(err, results){
    console.log(results);
});

Global events

Global events:

ON('database', function() {
    // Database is ready
});

Async/Await

+v12.0.0 supports sql.promise([name], [callback(response)]) for using of async/await.

var Agent = require('sqlagent/pg').connect('...');

async function data() {
    var b = new Agent();
    b.select('users', 'tbl_users');
    var users = await b.promise('users');
    console.log(users);
}

data();

Contributors

ContributorTypeE-mail
Peter Širkaauthor + supportpetersirka@gmail.com
Martin Smolacontributor + supportsmola.martin@gmail.com
Jay Kelkarcontributorjkelkar@gmail.com
Aidan Dunncontributoraidancheyd@gmail.com

Contact

Do you have any questions? Contact us https://www.totaljs.com/contact/

Professional Support Chat with contributors