A very helpful ORM for node.js

• installation $ npm install sqlagent

• for PostgreSQL $ npm install pg
• for MySQL $ npm install mysql
• for MS SQL Server $ npm install mssql
• for MongoDB $ npm install mongodb

• Currently supports PostgreSQL, MySQL, SQL Server and MongoDB
• Simple and powerful
• Best use with Total.js - web framework for Node.js

IMPORTANT:

• the code is executed as is added
• rollback is executed automatically when is the transaction enabled
• SQL Server: pagination works only in SQL SERVER >=2012
• SqlBuilder is a global object
• undefined values are skipped

Initialization

Basic initialization

PostgreSQL

// Example: postgresql://user:[email protected]/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:[email protected]/database?native=true&ssl=true

• native {Boolean} enables PG C native binding (faster than JavaScript binding, default: false)
• ssl {Boolean} enables SSL (default: false)
• max {Number} max. pools (default: 20)
• min {Number} min. pools (default: 4)
• idleTimeoutMillis {Number} idle timeout (default: 1000)

MySQL

// Example: mysql://user:[email protected]/database
var Agent = require('sqlagent/mysql').connect('connetion-string-to-mysql');
var sql = new Agent();

SQL Server (MSSQL)

// Example: mssql://user:[email protected]/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:[email protected]/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)

• name (String) is an identificator for results, optional (default: internal indexer)
• table (String) table name, the library automatically creates SQL query
• returns SqlBuilder

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)

• name (String) is an identificator for results, optional (default: internal indexer)
• table (String) table name, the library automatically creates SQL query
• returns SqlBuilder

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)

• name (String) is an identificator for results, optional (default: internal indexer)
• table (String) table name, the library automatically creates SQL query
• returns if value is undefined then SqlBuilder otherwise SqlAgent

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)

• name (String) is an identificator for results, optional (default: internal indexer)
• table (String) table name, the library automatically creates SQL query
• returns if value is undefined then SqlBuilder otherwise SqlAgent

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)

• name (String) is an identificator for results, optional (default: internal indexer)
• table (String) table name, the library automatically creates SQL query
• returns SqlBuilder

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)

• name (String) is an identificator for results, optional (default: internal indexer)
• query (String) SQL query
• params (Array) SQL additional params (each DB has own SQL implementation e.g. PG WHERE id=$1, MySQL WHERE id=?, etc.)
• returns if params is undefined then SqlBuilder otherwise SqlAgent

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)

• returns SqlBuilder

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)

• returns SqlBuilder

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)

• returns SqlBuilder

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])

• the module compares values between DB and value
• the response can be false or { diff: ['name'], record: Object, value: Object }
• works with sql.ifexists() and sql.ifnot()
• returns SqlBuilder

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

• returns SqlBuilder

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

• doesn't work with MongoDB
• rollback is performed automatically

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

Special cases

How to set the primary key?

• doesn't work with MongoDB

// 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"

• default primary key name is id
• works only in PostgreSQL because INSERT ... RETURNING must have specific column name

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', '[email protected]');
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

• you can set default values
• values are bonded immediately (not in order)

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

// ...
// ...

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

Modify results

• values are bonded in an order

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)

• you can use multiple sql.prepare()

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

• you can use multiple sql.validate()

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]);

• result_name_for_validation (String) a result to compare.
• error_message (String) an error message
• reverse (Boolean) a reverse comparison (false: result must exist (default), true: result must be empty) __

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

• doesn't work with MongoDB

var escaped1 = Agent.escape(value);

// or ...

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

Predefined queries

• doesn't work with MongoDB

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

• +3.1.0

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?

• doesn't work with MongoDB

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

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

sql.exec();

Expected values? No problem

• MongoDB supports expected values only in conditions.

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

• automatically adds and if is not added between e.g. 2x where

// 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

• name (String) column name
• value (Object) value

builder.raw()

builder.raw(name, value)

adds a raw value for update or insert without SQL encoding

• name (String) column name
• value (Object) value

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

• name (String) column name
• type (String) increment type (+ (default), -, *, /)
• value (Number) value

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

• name (String) column name
• desc (Boolean), default: false

builder.random()

builder.random()

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

builder.skip()

builder.skip(value)

skips records

• value (Number or String), string is automatically converted into number

builder.take()

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

takes records

• value (Number or String), string is automatically converted into number

builder.page()

builder.page(page, maxItemsPerPage)

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

• page (Number or String), string is automatically converted into number
• maxItemsPerPage (Number or String), string is automatically converted into number

builder.first()

builder.first()

sets sql.take(1)

builder.join()

• doesn't work with MongoDB

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

adds a value for update or insert

• name (String) table name
• on (String) condition
• type (String) optional, inner type inner, left (default), right

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

• name (String) column name
• operator (String), optional >, <, <>, = (default)
• value (Object)

builder.group()

• doesn't work with MongoDB

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

creates a group by in SQL query

• name (String or String Array)

builder.having()

• doesn't work with MongoDB

builder.having(condition)

adds having in SQL query

• condition (String), e.g. MAX(Id)>0

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

• name (String), column name
• value (String, Number or String Array, Number Array)

builder.between()

builder.between(name, a, b)

adds between to SQL query

• name (String), column name
• a (Number)
• b (Number)

builder.overlaps()

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

• only for PostgreSQL

adds overlaps to SQL query

• valueA (String, Number, Date)
• valueB (String, Number, Date)
• columnA (String), column A name
• columnB (String), column B name

builder.like()

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

adds like command

• name (String) column name
• value (String) value to search
• where (String) optional, e.g. beg, end, * ==> %search (beg), search% (end), %search% (*)

builder.sql()

• doesn't work with MongoDB

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

adds a custom SQL to SQL query

• query (String)

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

builder.query()

• works with MongoDB

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);

• only for SQL SERVER
• change the param type

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()

• doesn't work with MongoDB

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()

• doesn't work with MongoDB

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 (SqlBuilder) Another instance of SqlBuilder.

builder.toString()

• doesn't work with MongoDB

builder.toString()

creates escaped SQL query (internal)

Blob

PostgreSQL

• all file operations are executed just-in-time (you don't need to call sql.exec())
• all file operations aren't executed in queue

// 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

• all file operations are executed immediately, there's no need to call sql.exec()

// 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.

• sql.promise() performs sql.exec()
• look to example below:

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

| Contributor | Type | E-mail | |-------------|------|--------| | Peter Širka | author + support | [email protected] | | Martin Smola | contributor + support | [email protected] | | Jay Kelkar | contributor | [email protected] | | Aidan Dunn | contributor | [email protected] |

Contact

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