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 objectundefined
values are skipped
Initialization
Basic initialization
PostgreSQL
// Example: postgresql://user:password@127.0.0.1/databasevar Agent = ; /*// It's executed when the datbase returns an unexpected errorAgent.error = function(err, type, query) { };*/ // Agent() returns new instance of SQL Agentvar sql = ;
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/databasevar Agent = ;var sql = ;
SQL Server (MSSQL)
// Example: mssql://user:password@127.0.0.1/database// Example with name of instance: mssql://user:password@localhost_SQLEXPRESS/databasevar Agent = ;var sql = ;
MongoDB
// Example: mongodb://user:password@127.0.0.1/databasevar Agent = ;var nosql = ;
Initialization for Total.js
Create a definition file:
// Below code rewrites total.js database prototype; // debug is by default: false; // debug is by default: false; // debug is by default: false; // debug is by default: false
Usage:
// When you use RDMBS:// var sql = DATABASE([ErrorBuilder]);var sql = ;// sql === SqlAgent // +v9.9.6 enable debuggingsqldebug = true; // When you use MongoDB:// var nosql = DATABASE([ErrorBuilder]);var nosql = ;// 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 = ;var sql = ;
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; sql; sql; sql;
Push (only for MongoDB)
instance.push([name], collection, fn(collection, callback(err, response))
sql; // OR sql;
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; sql;
Save
instance.save([name], table, isINSERT, prepare(builder, isINSERT));
sql;
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; sql; sql;
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; // is same assql; sql;
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; sql;
Query
instance.query([name], query)
name
(String) is an identificator for results, optional (default: internal indexer)query
(String) SQL queryparams
(Array) SQL additional params (each DB has own SQL implementation e.g. PGWHERE id=$1
, MySQLWHERE id=?
, etc.)- returns if params is undefined then SqlBuilder otherwise SqlAgent
sql; sql;
Aggregation
instance.count([name], table)
- returns SqlBuilder
var count = sql;count; sql;
instance.max([name], table, column)instance.min([name], table, column)instance.avg([name], table, column)
- returns SqlBuilder
var max = sql;max; sql;
Exists
instance.exists([name], table)
- returns SqlBuilder
var exists = sql;exists; sql;
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()
andsql.ifnot()
- returns SqlBuilder
var compare = sql;// 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; sql;
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; sql;
Transactions
- doesn't work with MongoDB
- rollback is performed automatically
sql;sql;sql;
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;instance; instance;instance; instance; // back to default "id"
- default
primary key name
isid
- 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 + autoincrementvar user = sql;user; var address = sql;address;address; sql;
How to use latest primary id value for multiple relations?
// primary key is id + autoincrementvar user = sql;user; // Lock latest inserted identificatorsql;// is same as// sql.put(sql.$$); var address = sql;address; // adds latest primary id valueaddress; var email = sql;email; // adds locked valueemail;sql; sql;
If not or If exists
instance; instance;
Default values
- you can set default values
- values are bonded immediately (not in order)
sql; // ...// ... sql;
Modify results
- values are bonded in an order
sql;sql; sql; // ...// ... // Calling:// 1. select// 2. insert// 3. modify// 4. other commandssql;
Preparing (dependencies)
- you can use multiple
sql.prepare()
var user = sql;user;user; var select = sql;select;select;select;select; // IMPORTANT:sql; var address = sql;address; sql;
Validation
- you can use multiple
sql.validate()
sql.validate(fn)
var select = sql;select;select;select;select; // IMPORTANT:sql; var user = sql;user;user; sql;
Validation alternative (+v4.0.0)
// IMPORTANT:sql;
sql.validate([result_name_for_validation], error_message, [reverse]);
result_name_for_validation
(String) a result to compare.error_message
(String) an error messagereverse
(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;select;select;select; // IMPORTANT:sql; var user = sql;user; sql;sql; sql;
Validation alternative (+v8.0.0)
sql;sql;
Global
Stored procedures
sql; // with params// sql.query('myresult', 'exec myprocedure $1', [3403]); sql;
Skipper
sql;sql; // skip orderssql; sql; sql; sql;
Bookmarks
Bookmark is same as sql.prepare()
function but without resume
argument.
sql; sql; sql; sql;
Error handling
sql; sql; sql; // sql.validate([error message], [result name for validation])sql;// is same as:// sql.validate('error-orders-empty', 'orders'); sql;
Escaping values
- doesn't work with MongoDB
var escaped1 = Agent; // or ... var sql = ;var escaped2 = sql;
Predefined queries
- doesn't work with MongoDB
Agent.query(name, query);
Agent;Agent; sql;sql; sql;
Waiting for specified values
+3.1.0
sql; sql; sql;sql;sql;
Bonus
How to get latest inserted ID?
- doesn't work with MongoDB
sql; sql; sql;
Expected values? No problem
- MongoDB supports expected values only in conditions.
sql.expected(name, index, property); // gets a specific value from the arraysql.expected(name, property);
sql;sql; sql;
Measuring time
sql;
Events
sql;sql;sql;
Generators in total.js
{ var sql = ; sql; sql; // get all results var results = ; console; // or get a specific result: var result = ; console;}
Priority
Set a command priority, so the command will be processed next round.
sqlsql;sql; // --> takes last item in queue and inserts it as first (sorts it immediately).
Debug mode
Debug mode writes each query to console.
sqldebug = true;
We need to return into the callback only one value from the response object
sql; // --> returns first value from response (if isn't error)sql; // --> returns response.users (if is isn't error) sql;
SqlBuilder
- automatically adds
and
if is not added between e.g. 2x where
// Creates SqlBuildervar builder = sql$; builder;builder; // e.g.:sql;sql
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 namevalue
(Object) value
builder.raw()
builder.raw(name, value)
adds a raw value for update or insert without SQL encoding
name
(String) column namevalue
(Object) value
builder.set(obj)
adds an object for update or insert value collection
builder;// 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 nametype
(String) increment type (+
(default),-
,*
,/
)value
(Number) value
builder;builder;builder; // Short writebuilder;builder;
builder.rem()
builder.rem(name)
removes an value for inserting or updating.
builder;builder;
builder.sort()
builder.sort(name, [desc])builder.order(name, [desc])
adds sorting
name
(String) column namedesc
(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 numbermaxItemsPerPage
(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 nameon
(String) conditiontype
(String) optional, inner typeinner
,left
(default),right
builder;
builder.where()
builder.where(name, [operator], value)builder.push(name, [operator], value)
add a condition after SQL WHERE
name
(String) column nameoperator
(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 namevalue
(String, Number or String Array, Number Array)
builder.between()
builder.between(name, a, b)
adds between to SQL query
name
(String), column namea
(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 namecolumnB
(String), column B name
builder.like()
builder.like(name, value, [where])
adds like command
name
(String) column namevalue
(String) value to searchwhere
(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;
builder.query()
- works with MongoDB
builder.query(fieldname, filter)
adds a custom QUERY to filter.
builder;
builder.scope()
builder.scope(fn);
adds a scope ()
builder;builder; // RDMBS:builderscope { builder; builder; builder;}; // MongoDB:builderscope { builder; builder; builder;}; // 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;insert;insert;insert;sql;
builder.schema()
- doesn't work with MongoDB
builder.schema()
sets current schema for where
, in
, between
, field
, fields
, like
builder;builder; // --> b."name", b."age"builder;builder; // --> a."name", a."age"builder // --> 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; // "name", "age"builder; // Raw field: COUNT(id)builder; // 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; // sql.writeBuffer(buffer, callback(err, loid))sql; // sql.readStream(loid, [buffersize](default: 16384), callback(err, stream, size))sql;
MongoDB
- all file operations are executed immediately, there's no need to call sql.exec()
// nosql.writeStream(id, stream, filename, [metadata], [options], callback)nosql; // nosql.readStream(id, [options], callback(err, stream, metadata, size, filename))nosql; // get file infonosql; nosql;
Global events
Global events:
;
Async/Await
+v12.0.0
supports sql.promise([name], [callback(response)])
for using of async/await.
sql.promise()
performssql.exec()
- look to example below:
var Agent = ; { var b = ; b; var users = await b; console;} ;
Contributors
Contributor | Type | |
---|---|---|
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/