SOAR (Simple Object Adapter for Relational database) is a light-weight tool to harness SQL. It saves developers from the painstaking SQL hand-coding tasks. Not like most ORM solutions, soar gives back to developers the full control of how SQL statements are generated. soar has the following interesting features:
-
Reusable: you can formulate a SQL statement into an expression. You can later invoke that SQL expression with various query conditions.
-
Simple and elegant: you don't have to hand code the sql WHERE clause any more. Just specify the query values and soar will do the tedious works for you.
-
Multiple database access: it's very easy to access multiple databases within an application with soar.
-
Schema manipulation: you can define a table schema using JSON notations, and you can easily manipulate table schemas.
-
Full control: unlike most ORM solutions, you have full control of how SQL is generated and applied.
Except for "data view" (table schema representd in XML format) supprot, sql-soar is mostly compatible with the "soarjs" module. Very likely you can replace "soarjs" with "sql-soar" and your application will run without glitches. However, "sql-soar" has a even cleaner API and more developer friendly features, so "soarjs" developers are encourged to switch.
First of all, you have to config soar so it knows how to talk with the database:
var soar = require('sql-soar'),
var options = {
dbConfig: {
"host" : "127.0.0.1",
"database" : "soar",
"user" : "your_db_acc_name",
"password" : "your_db_passwd",
"connectionLimit" : 32
}
};
soar.config( options );
where "connectionLimit" specifies how many connections will be created and buffered in the connection pool.
That's all you need to do for setup. Now assuming you have a table called 'Person' and you want to find out all persons with age equal to 25 in that table. Below is how you can do with soar:
soar.list('Person', {age: 25}, function(err, list) {
// 'list' will contain persons whose age is 25.
});
That's similar to issuing a SQL statement like:
SELECT * FROM Person WHERE age = 25;
If you're sure your query result should have at most one entity, you may consider using "query" instead of "list":
soar.query('Person', {name: "David Lynch"}, function(err, data) {
if (err)
console.log( err.stack );
else
console.log('About David: %s', JSON.stringify(data));
});
So the signature of the querying calls is as below:
soar.query('table_name', query, callback);
soar.list('table_name', query, callback);
"query" is the column-value pair that will be translated into the WHERE clause. If the query object contains more than one properties, they wil be ANDed together. For example:
soar.list('Person, {age:25, city:'Oakland'}, callback);
is like querying database with the following SQL statement:
SELECT * FROM Person WHERE age = 25 AND city = 'Oakland';
If the comparator is not "=", you can specify the comparator in the query object:
var query = {
age: {op: '>=', value: 25}
};
soar.list('Person', query, callback);
Doing update is just as simple, see the example below:
soar.update('Person', {weight: 160}, {id: 28}, callback);
That's similar to issuing a SQL statement like:
UPDATE Person SET weight=160 WHERE id=28;
Inserting a new entry to a table can be done in a similar fashion:
soar.insert('Person', {name: 'Sean', age: 18}, callback);
so as "delete":
soar.del('Person', {age: 18}, callback);
As you can see the CRUD (create, read, update and delete) operations can be done in a very simple and intuitive way. However, the APIs explained above are just handy functions. They all invoke the execute() function to do their jobs. If you want to issue a query with very complicated WHERE clauses, do table joins or do things in transactions and so forth, you'll need to do it with execute(). The execute() function is very powerful and too sophisticated for this 5 minutes guide. If you're interested, please refer to the API section about execute().
npm install sql-soar
There are two ways to setup the database configuration in SOAR: using a config file or doing it programmatically.
Right beneath the SOAR installation directory, there is a config.json file which would look like:
{
"dbConfig": {
"host" : "127.0.0.1",
"database" : "soar",
"user" : "myDB_acc_name",
"password" : "xxxx",
"supportBigNumbers" : true,
"connectionLimit" : 32
}
}
where host is the database host and database is the database name. user and password are the database user name and password respectively. SOAR ueses the mysql node module as its mySQL driver and the connection pool feature is turned on by default.
You can configure the database connection settings right inside your node.js application. Here is how:
var soar = require('sql-soar');
var options = {
dbConfig: {
"host" : "127.0.0.1",
"database" : "soar",
"user" : "myDB_acc_name",
"password" : "xxxx",
"supportBigNumbers" : true,
"connectionLimit" : 32
}
};
soar.config( options );
Using SOAR to access multiple databases is extremely easy. In this section, we'll show you how to configure SOAR to connect to multiple databases.
In your config.json file, use an array of options instead of a single configuration option with each option specifying the settings of each database. Below is an example:
[
{
"dbConfig": {
"host" : "127.0.0.1",
"database" : "db_1",
"user" : "db1_acc_name",
"password" : "xxxx",
"supportBigNumbers" : true,
"connectionLimit" : 32
}
},
{
"dbConfig": {
"host" : "127.0.0.1",
"database" : "db_2",
"user" : "db2_acc_name",
"password" : "xxxx",
"supportBigNumbers" : true,
"connectionLimit" : 32
}
}
]
If you need to connect to 10 databases in your application, then the configuration array should have 10 elements. Configuring multiple databases programmatically is done similarily.
How to access each database in a multi-databases scenario will be explained in each database access method (query, list, create, update and delete) below.
As explained in the "5 Minutes Guide" above, if you want to do sophisticated SQL queries, you'll need a more powerful tool. That tool is the SQL expression.
You can use SQL expressions to instruct soar how to talk with databases. With SQL expressions, you can compose and reuse SQL queries in a clean and managable way. In essence, SQL expressions are nothing more than SQL statements encoded as a JSON object. An example should help to understand what is a SQL expression:
var expr = soar.sql('Person')
.column(['id', 'addr AS address', 'age'])
.filter( {name: 'age', op: '>='} )
.extra( 'ORDER BY id' );
The above sample code just constructed a SQL expression. You can use it to do a database query:
var cmd = {
op: 'list',
expr: expr
},
query = {age: 18};
soar.execute(cmd, query, function(err, list) {
// 'list' is the query result
});
That's equivalent to:
SELECT id, addr AS address, age
FROM Person
WHERE age >= 18;
"Well, that's nice but what's the befenit?" you may ask. The magic is you can use the same SQL expression in update:
var cmd = {
op: 'update',
expr: expr
};
soar.execute(cmd, {canDrive: true}, {age: 18}, callback);
Actually, the same SQL expressions can be used in all CRUD operations. soar is smart enough to retrieve the needed information from a SQL expression and compose the SQL statement you want.
Assuming you're satisfied, below is how to construct a SQL expression: soar.sql(tableName) takes a table name as its input and returns a SQL Expression object. With that object, you can add columns, set query conditions and specify addtional options. Most SQL expression functions will return the expression object itself, so you can chain funcion calls such that SQL expressions can be composed succintly.
This function returns a SQL expression. tableName is the name of a table. If you'll access multiple databases in an application, tableName has to be in the form of dbName.tableName so that soar knows which database to talk to.
Example:
var expr = soar.sql('myTable');
With the SQL expression obtained from the soar.sql() funciton call, you can use its join() function to specify table joins.
Example:
var expr = soar.sql('myTable AS myT')
.join({
table: 'Location AS loc',
onWhat: 'myT.locID=loc.locID'
});
If you want to make multiple joins, just call join() as many times as you need. The parameter to the join() function call is a plain JSON object with the following properties:
- table: name of the joined table.
- type: if you want to make a left join, you can set this property to 'LEFT'.
- onWhat: the join clause. If the use property described below is specified, this property will be ignored.
- use: the common column name to join two tables.
This function can be used to add table columns to a SQL expression. To add a single column, the parameter is the name of the column. If you want to add multiple columns, the parameter should be an array of column names.
Example:
var expr = soar.sql('Person')
.column(['name', 'age', 'weight']);
This function is used to set query conditions (filter) of a SQL expression. The parameter to the function call is a plain JSON object with the following properties:
- name: name of the filter. It's also used as the key to retrieve the query value from a query object. This property is required.
- field: the real column name in a table. If this property is missing, the name property will be used instead.
- op: what comparator to be used. It can be '>', '=' or 'IS NULL', etc.
- noArg: when a query operation does not require argument (e.g. IS NULL), this property should be set to true.
Note that this function should be called just once for a SQL expression. When called multiple times, the new setting will replace the old one.
Example:
var expr = soar.sql('Person')
.filter({name: 'age', op: '>='});
If you want to make a compound filter (ANDed or ORed filters), this is the function you need. op should be 'AND' or 'OR', and filters is an array of filters.
Example:
var orFilters = soar.chainFilters('OR', [
{name: 'region', op: '='},
{name: 'age', op: '>'}
]);
var expr = soar.sql('myTable')
.filter( orFilters );
The resulting filter (orFilters) is a compound filter ORing two filters (region and age).
This function can add extra options to a SQL statement. extra is a string with possible values like 'GROUP BY col_name' or 'ORDER BY col_name'.
Example:
var expr = soar.sql('myTable')
.extra('ORDER BY region');
This function can be used to execute SQL queries (query, list, insert, update and delete). The data parameter is a JSON object which contains data to be inserted or updated to a table entry. The query parameter is a JSON object which specifies the actual query values. The cmd parameter is a command to soar and it has the following properties:
-
op: should be one of the following: 'query', 'list', 'insert', 'update' and 'delete'.
-
expr: the SQL expression needed to generate the required SQL statement.
-
range: specifies the window of a result set. The range object can be created using the soar.range() function.
-
conn: a database connection object. You usually don't have to specify this property unless you want to do transactions.
-
debug: if set to true, soar will print out the SQL (prepare) statement along with the parameters applied.
If the data parameter is not needed, the function call can be simplified to execute(cmd, query, cb).
cb is the callback function which receives an error and sometimes a result object (when it's a query, list or insert operation).
Example:
var expr = soar.sql('Person'),
cmd {
op: 'update',
expr: expr
},
data = {
name: 'John',
age: 32
};
soar.execute(cmd, data, {id: 24}, function(err) {
// set person #24 to name='John' and age=32
});
Example of doing pagination:
var expr = soar.sql('Person'),
cmd = {
op: 'list',
expr: expr,
range: soar.range(1, 10) // return the first page with page size of 10
};
soar.execute(cmd, function(err, rows, count) {
console.log('total count is: ' + count);
console.log('but we will only return the first page of 10 items');
});
If you expect a table query should return only one entity (even though there maybe multiple matches to your query), you can use this function.
Example:
soar.query('Person', {psnID: 1}, function(err, data) {
// 'data' is the query result
});
var query = {
age: {op: '>=', value: 25},
weight: {op: '>=', value: 160}
};
soar.query('Person', query, function(err, data) {
// if data is not null,
// it's the person who is weighted more than 160 and older than 25
});
Use this function to query multiple entries from a table.
Example:
soar.list('Person', {age: 25}, function(err, list) {
// 'list' contains person who is at the age of 25
});
var query = {
age: {op: '>=', value: 25},
weight: {op: '>=', value: 160}
};
soar.list('Person', query, function(err, list) {
// 'list' will contain people
// who is weighted more than 160 and older than 25
});
Inserting a new entry to a table. 'data' is the data to be inserted.
Example:
soar.insert('Person', {name: 'Scott Cooper'}, function(err, pk) {
// 'pk' contains the primary key value of the inserted entry
// for example, it could be something like:
// {psnID: _the_psnID_of_the_newly_inserted_entity}
// where 'psnID' is the primary key of the Person table
});
Updating data entries in a table. 'data' is the new data. 'query' specifies which entries will be updated.
Example:
soar.update('Person', {name: 'John Mayer'}, {psnID: 1}, cb);
Deleting entries from a table. 'query' specifies which entries will be deleted.
Example:
soar.del('Person', {psnID: 1}, cb);
Doing transaction is faily simple. All you need to do is to obtain a database connection and set it to the soar command. However, only the soar.execute() funciton supprots transactions. You can not apply transactions to soar.query(), soar.list(), soar.update(), soar.insert() and soar.del().
Example:
var expr = soar.sql('Person');
soar.getConnection( function(err, conn) {
// remember to specify database connection in 'cmd'
var cmd = {
op: 'insert',
expr: expr,
conn: conn
},
data = {name: 'Scott Cooper'};
conn.beginTransaction(function(err) {
soar.execute(option, data, null, function(err, data) {
if (err)
conn.rollback(function() {
// remember to release the connection
conn.release();
});
else
conn.commit(function(err) {
if (err)
conn.rollback(function() {
// remember to release the connection
conn.release();
});
else
conn.release();
});
});
};
});
Besides accessing data, you can also use soar to manage table schema.
This function will create a database table. schema is a schema notation object which defines a table schema. Please refer to schema notation to know about what it is and how to create a schema notation. cb is a callback function when table creation is successful or erred.
If you want to call createTable() with a specific database conection object, you can do createTable(conn, schema, cb).
This function can be used to alter table schema. schema is a schema notation object which defines a table schema. Please refer to schema notation to know about what it is and how to create a schema notation. cb is a callback function when altering table is successfully done or erred.
If you want to call alterTable() with a specific database conection object, you can do alterTable(conn, schema, cb).
This function can be used to delete (drop) a table. tableName is the name of the table to be dropped. cb is a callback function when deleting table is successfully done or erred.
If you want to call deleteTable() with a specific database conection object, you can do deleteTable(conn, schema, cb).
This function can be used to derive schema from an existing table. tableName is the name of the table to be explored. cb(err, schema) is the callback function to return the table schema. The returned schema object is constructed as suggested by schema notation.
If you want to call describeTable() with a specific database conection object, you can do describeTable(conn, schema, cb).
If you want to know what SQLs are actually generated by soar, you can turn on debug messages as shown below:
soar.setDebug( true );
That will display generated SQL along with other debug information in console.
Sometimes dumping SQLs of every database query could be overwhelming. You can choose to print oup a specific query by setting the 'debug' property of a soar command to true (this feature only works for the execute() function). For example:
var cmd = {
op: 'list',
expr: expr,
debug: true // set to 'true' will print out SQL
};
soar.execute(cmd, query, cb);
The soar package comes with some test files. To run those tests, sample data have to be built first. Inside the "doc" directory there are two files: schema.sql and sampleData.sql. Those two files can be used to build the sample data. In addition, remember to change the user name and password in your config.json file and the related database settings in the test programs.
In the current release, soar only supports mySQL. If you want to use soar for other databases such as Postgre, MS SQL server or Oracle DB, etc, you'll have to write your own SQL generator. Right now SQL generation is implemented by ./lib/sqlGenMySql.js.