Simple SQL mapper engine for Node.js.
Only support MySql for now.
#select user.query:
select * from user where 1=1
and id=@id
and userName=@userName
and password=@password
and email=@email
and id in (@ids)
#select[#insert, #update, #delete, #sql] SQLID:
#select[#insert, #update, #delete, #sql] SQLID { ... }
A SQLID is an identify for a SQL.
You should make sure your SQLID is unique for all SQL definitions.
- @param or @@param
This stands for a parameter named "param". One '@' means the param is optional, two '@'s means the param is mandatory.
The sql body is recognized as lines.
If there are parameters in one line, when paramters being filled:
if all parameters are optional, the line will be removed when any paramter is empty(undefined, null, empty string, false, 0)
if any parameter is mandatory, the line will not be removed
- @param@ or @@param@
Suffix "@" means the parameter value will be escaped as a column or table name when the parameter is filled.
For example, if the value of param is "user", then it is escaped as `user` when filled.
Very like JavaScript code except you need replace the parameter as @param.
#select test.sql1:
select * from user where 1=1
and id=@id
and userName=@userName
and password=@password
and email=@email
#if(@ids && @ids.length > 0){
#for(var i = 0; i < @ids.length; i++) {
or id= @ids[i]
}
}
#include(SQLID)
The SQL with SQLID will be replace in the position of the #include line.
#sql common.paging:
order by @orderBy@ limit @from, @to
#select user.query:
select * from user where 1=1
and id=@id
and userName=@userName
and password=@password
and email=@email
and id in (@ids)
#include(common.paging)
SQL map files' extension must be ".sql";
var SqlMap = require("sqlmap");
SqlMap.loadSqlMaps(sql_map_file_or_dir_path, function(err, mapContext){
if (err) throw err;
...
});
Only for MySql for now.
The connection is pooled and clustered at backend by mysqljs/mysql module.
var SqlMap = require('sqlmap');
var masterConfig = {
host: 'db_master_host',
user: 'db_user_name',
password: 'db_password',
database: 'db_name'
};
var slaveConfig = {
host: 'db_slave_host',
user: 'db_user_name',
password: 'db_password',
database: 'db_name'
};
var sqlMap = new SqlMap(masterConfig, [slaveConfig]);
sqlMap.destroy(function(err) {
if (err) throw err;
console.log("Connections destroyed!");
});
Reading and writing separation automatically. When execute a #select SQL, then the engine auto chooses the slave configure to apply a query. The engine auto chooses master configure for other SQL types.
//Assume the SQLID is "user.query"
sqlMap.dQuery("user.query", {
ids: ['1', 2, 3],
orderBy: 'userName',
from: 0,
to: 100
}, function (err, results) {
if (err) throw err;
// results is an array for the returned objects.
...
});
The generated SQL is:
select * from user where 1=1 and id in ('1', 2, 3) order by `userName` limit 0, 100
Native SQL query wrapper for mysqljs/mysql
sqlMap.query("select * from user where id in (?) order by ?? limit ? to ?", [
['1', 2, 3],
'userName',
0,
100
], function (err, results) {
if (err) throw err;
// results is an array for the returned objects.
...
});
Please refer the test/test-promise.js.