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:password@127.0.0.1/databasevarAgent=require('sqlagent/pg').connect('connetion-string-to-postgresql');/*// It's executed when the datbase returns an unexpected errorAgent.error = function(err, type, query) {};*/// Agent() returns new instance of SQL Agentvarsql=Agent();

Additional configuration:

postgresql://user:password@127.0.0.1/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:password@127.0.0.1/databasevarAgent=require('sqlagent/mysql').connect('connetion-string-to-mysql');varsql=newAgent();

SQL Server (MSSQL)

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

MongoDB

// Example: mongodb://user:password@127.0.0.1/databasevarAgent=require('sqlagent/mongodb').connect('connetion-string-to-mongodb');varnosql=newAgent();

Initialization for Total.js

Create a definition file:

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

Usage:

// When you use RDMBS:// var sql = DATABASE([ErrorBuilder]);varsql=DATABASE();// sql === SqlAgent// +v9.9.6 enable debuggingsql.debug=true;// When you use MongoDB:// var nosql = DATABASE([ErrorBuilder]);varnosql=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.

varAgent=require('sqlagent/mysql').connect({host: "localhost",user: "root",password: "",database: "test",typeCast: functioncastField(field,useDefaultTypeCasting){if((field.type==="BIT")&&(field.length===1)){varbytes=field.buffer();return(bytes[0]===1);}return(useDefaultTypeCasting());}});varsql=newAgent();

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;varpipeline=[];pipeline.push({$match: $match});pipeline.push({$group: $group});collection.aggregate(pipeline,callback);});// ORsql.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',newDate());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',newDate());});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 assql.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

varcount=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

varmax=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

varexists=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

varcompare=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 changedconsole.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

varmax=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 + autoincrementvaruser=sql.insert('user','tbl_user');user.set('name','Peter');varaddress=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 + autoincrementvaruser=sql.insert('user','tbl_user');user.set('name','Peter');// Lock latest inserted identificatorsql.lock();// is same as// sql.put(sql.$$);varaddress=sql.insert('tbl_user_address');address.set('iduser',sql.$$);// adds latest primary id valueaddress.set('country','Slovakia');varemail=sql.insert('tbl_user_email');email.set('iduser',sql.$$);// adds locked valueemail.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

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 commandssql.exec(function(err,response){console.log(response);});

Preparing (dependencies)

you can use multiple sql.prepare()

varuser=sql.update('user','tbl_user');user.where('id',20);user.set('name','Peter');varselect=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 === ErrorBuildersql.builder('address').set('idaddress',response.address.id);resume();});varaddress=sql.update('address','tbl_user_address');address.where('iduser',20);sql.exec();

Validation

you can use multiple sql.validate()

sql.validate(fn)

varselect=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 === ErrorBuilderif(!response.address){error.push('Sorry, address not found');// cancel pending queriesreturnresume(false);}sql.builder('user').set('idaddress',response.id);// continueresume();});varuser=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 === ErrorBuilderif(!response.address){error.push('Sorry, address not found');returnfalse;}sql.builder('user').set('idaddress',response.id);returntrue;});

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.

varselect=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');varuser=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 orderssql.select('orders','tbl_orders');sql.bookmark(function(error,response){// error === ErrorBuilder// skip logssql.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 === ErrorBuilderconsole.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 === ErrorBuilderif(!response.users||respone.users.length===0)error.push(newError('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

varescaped1=Agent.escape(value);// or ...varsql=newAgent();varescaped2=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]);// usersconsole.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(){varsql=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 resultsvarresults=yieldsync(sql.$$exec())();console.log(results);// or get a specific result:varresult=yieldsync(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)throwerr;console.log(response);// response will contain only orders},'orders');

SqlBuilder

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

// Creates SqlBuildervarbuilder=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 writebuilder.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

varinsert=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(newObjectID(),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 infonosql.select('fs.files').make(function(builder){// available fields - _id,filename,contentType,length,chunkSize,uploadDate,aliases,metadata,md5builder.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:

varAgent=require('sqlagent/pg').connect('...');asyncfunctiondata(){varb=newAgent();b.select('users','tbl_users');varusers=awaitb.promise('users');console.log(users);}data();

Contributors

Contributor	Type	E-mail
Peter Širka	author + support	petersirka@gmail.com
Martin Smola	contributor + support	smola.martin@gmail.com
Jay Kelkar	contributor	jkelkar@gmail.com
Aidan Dunn	contributor	aidancheyd@gmail.com

Contact

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

Name		Name	Last commit message	Last commit date
.gitignore		.gitignore
.npmignore		.npmignore
index.js		index.js
license.txt		license.txt
mongodb.js		mongodb.js
mysql.js		mysql.js
package.json		package.json
pg-lo.js		pg-lo.js
pg.js		pg.js
readme.md		readme.md
sqlserver.js		sqlserver.js

Uh oh!

Latest commit

History

Folders and files

Repository files navigation

A very helpful ORM for node.js

Initialization

Basic initialization

PostgreSQL

MySQL

SQL Server (MSSQL)

MongoDB

Initialization for Total.js

IMPORTANT

Usage

Select

Push (only for MongoDB)

Listing

Save

Insert

Update

Delete

Query

Aggregation

Exists

Compare

Transactions

Special cases

How to set the primary key?

How to use latest primary id value for relations?

How to use latest primary id value for multiple relations?

If not or If exists

Default values

Modify results

Preparing (dependencies)

Validation

Global

Stored procedures

Skipper

Bookmarks

Error handling

Escaping values

Predefined queries

Waiting for specified values

Bonus

How to get latest inserted ID?

Expected values? No problem

Measuring time

Events

Generators in total.js

Priority

Debug mode

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

SqlBuilder

builder.callback(fn)

builder.set()

builder.raw()

builder.inc()

builder.rem()

builder.sort()

builder.random()

builder.skip()

builder.take()

builder.page()

builder.first()

builder.join()

builder.where()

builder.group()

builder.having()

builder.and()

builder.or()

builder.in()

builder.between()

builder.overlaps()

builder.like()

builder.sql()

builder.query()

builder.scope()

builder.define()

builder.schema()