Sql-soar NPM | npm.io

SQL-SOAR

What Is SOAR

SOAR (Simple Object Adapter for Relational database) is a light-weight node.js module to harness SQL. It saves developers from the painstaking SQL hand-code tasks. Unlike ORM solutions, soar gives back to developers the capability to fully control how SQL statements are generated. soar has the following interesting features:

Reusable SQL: you can easily formulate a SQL statement into an expression. You can later invoke and reuse the same SQL expression with various query conditions.
Simple and elegant: you don't have to hand code the sql WHERE clause. Just specify the query values and soar will do the magic for you.
Multiple database access: it's very easy to access multiple databases within an application using soar.
Schema manipulation: you can define a table using JSON as the schema notation, and thus manipulate the table definition easily.
Full control: unlike ORM solutions, you have full control of how SQL is generated and applied.
Extremely light weight and efficient.

What's New

Here is what's new of the new release (v1.3.4). You can track how this software evolves in the release notes. Below are some highlights:

v1.2.0 SQL expressions can be stored as files. That makes reuse even easier.
v1.1.5 When joining tables, both syntax as "table AS alias" or "table alias" would work.
v1.1.4 Support the IN clause in the query conditions. Check this section to see how it can be done easily.
v1.1.3 If necessary, you can directly execute SQL statements using the runSql() function.

5 Minutes Guide

First of all, you have to config soar so it knows how to talk with your 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 signatures of the querying calls can be summarized as below:

soar.query('table_name', query_values, cb);
soar.list('table_name', query_values, cb);

where "query_values" is the column-value pair that will be translated into the WHERE clause. The query object can contain multiple properties. In that case, all properties wil be ANDed together. For example:

soar.list('Person, {age:25, city:'Oakland'}, callback);

is the same as 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 features shown above are just handy functions. They all invoke the execute() function to do their jobs. If you need to specify very complicated WHERE clauses, do table joins or do things in transactions and so forth, you'll need to do it with the execute() function. The execute() function is very powerful and probably too sophisticated for this 5 minutes guide. If you're interested, please refer to the execute() API.

Installation

npm install sql-soar

DB Settings

There are two ways to setup the database configuration in SOAR: using a config file or doing it programmatically.

The config.json File

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
	},
    "storedExpr": "file_path_to_the_stored_expressions"
}

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.

There is another property "storedExpr" specified in the option. The storedExpr property denotes the file directory where stored SQL expressions are located. This property is optional. If you don't use any stored SQL expression at all, you can leave out this property.

Configure Programmatically

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
            },
            "storedExpr": "file_path_to_the_stored_expressions"
     };

soar.config( options );

The option settings are the same as the config.json file.

Multiple Databases Configuration

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.

SQL Expressions

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 SQL expression.

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 explain what is a SQL expression:

var  expr = soar.sql('Person')
                .column(['id', 'addr AS address', 'age'])
                .filter( {name: 'age', op: '>='} )
                .extra( 'ORDER BY id' );

You can use the above SQL expression to do a database query:

var  cmd = {list: 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;

soar will match the input query with the filter section of a SQL expression.

"Well, that looks nice but what's the befenit?" you may ask. The best part of SQL expression is that you can simply specify your query conditions as a JSON object and soar will match your query objerct with the filter section of a SQL expression. In other words, you're saved from the pains-taking task to re-compose a SQL statement simply becase you've changed your query condition (even very slightly) .

Besides, you can use the same SQL expressions in all CRUD operations. soar is smart enough to retrieve the related information from a SQL expression and compose the intended SQL statement. For example, you can use the same SQL expression for query to do update:

var  cmd = {update: expr};
     
soar.execute(cmd, {canDrive: true}, {age: 18}, callback);

Constructing a SQL expression is simple. It starts from the soar.sql(tableName) function. The soar.sql(tableName) function takes a table name as its input and returns a SQL Expression object. With that object, you can add selected table columns, set query conditions and specify addtional options (such as GROUP BY or ORDER BY). Every SQL expression function returns the expression object itself, so you can chain funcion calls to succintly compose a SQL expression.

What's better, if you keep using some SQL expressions in your applications, you may want to save them into files so you can reuse them. Stored in files, such SQL expressions are also very easy to maintain.

API

APIs related to SQL expressions

soar.sql(tableName)

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

expr.join(joinExpr)

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', 
                    on: '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'.
on: 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.

expr.column(column)

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

expr.filter(filter)

This function is used to set query conditions (filter) of a SQL expression. soar accepts various filter formats so you can easily specify the query conditions needed.

The easiest way is to simply specify a column name as the query condition:

var  expr = soar.sql('Person')
                .filter('age');

That is equavilent to the following SQL statement:

SELECT * FROM Person WHERE age = ?;

If the comparator is not equal (=) but other opertors, you can specify the filter as an object:

var  expr = soar.sql('Person')
                .filter({age: '>'});

That is equavilent to the following SQL statement:

SELECT * FROM Person WHERE age > ?;

The object filter just introduced is actually an short format. The "standard" object filter could have 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.

What about compound query conditions? You can do so with a single property filter object and the property value is an array (of object filters) such as the following:

var  expr = soar.sql('Person')
                .filter({
                    'or': [
                        {age: '>'},
                        'height'
                    ]
                });

That is equavilent to the following SQL statement:

SELECT * FROM Person WHERE age > ? OR height = ?;

If the compound operator is 'and', you can even omit it. For example:

var  expr = soar.sql('Person')
                .filter({
                    'and': [
                        {weight: '>'},
                        {height: '>'}
                    ]
                });

is the same as:

var  expr = soar.sql('Person')
                .filter([
                    {weight: '>'},
                    {height: '>'}
                ]);

expr.extra(extra)

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

APIs related to data manipulation

soar.execute(cmd, data, query, cb)

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. It should be noted the query parameter here should just be plain column-value pairs. The query object format is not applicable here.

The cmd parameter is a command to soar. It usually has an 'operator' property. The operator property can be one of the following: 'query', 'list', 'insert', 'update' and 'delete'. The value of the operator property is a SQL expression that is needed to generate the SQL statement. If you want to invoke a stored SQL expression, this property can be the path name of the stored SQL expression.

Besides, the cmd parameter could have the following properties:

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 = {update: 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 = {
        list: 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');
});

soar.query(tbName, query, cb)

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.

tbName: name of the table to be queried. If you're accessing multiple databases, you can use databaseName.tableName to specify which database you intend to query.
query: the query condition. Refer to Query Object for how to effectively use it.
cb: call back function which will be fed with two parameters: err and data. err is the error object (if any) and data is the query result as a plain Javascript object.

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

soar.list(tbName, query, cb)

Use this function to get multiple entries from a table.

tbName: name of the table to be queried. If you're accessing multiple databases, you can use databaseName.tableName to specify which database you intend to query.
query: the query condition. Refer to Query Object for how to effectively use it.
cb: call back function which will be fed with two parameters: err and list. err is the error object (if any) and list is the query result as a Javascript array. If nothing matches, list will be an empty array.

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

List with the IN clause

It's possible to use IN in the where clause, but it has to be done with the more sophisticaed execute() function. Below is how it can be done:

var  expr = soar.sql('Person')
                .filter({name: 'psnID', op: 'IN'});

var  cmd = {op: 'list', expr: expr},
     query = {psnID: [7, 29, 33]};                    
soar.execute(cmd, query, function(err, list)  {
    // list will contain people whose id is 7, 29 or 33
});

soar.insert(tbName, data, cb)

Inserting a new entry to a table.

tbName: name of the table to be inserted. If you're accessing multiple databases, you can use databaseName.tableName to specify which database you intend to do insert.
data: the data to be inserted. If data contains any properties which do not match the target table column, those properties will be ignored.
cb: call back function which will be fed with two parameters: err and pk. err is the error object (if any) and pk is the primary key of the newly inserted entry.

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

soar.update(tbName, data, query, cb)

Updating data entries in a table.

tbName: name of the table to be updated. If you're accessing multiple databases, you can use databaseName.tableName to specify which database you intend to update.
data: the data to be updated. If data contains any properties which do not match the target table column, those properties will be ignored.
query: the query condition. Refer to Query Object for how to effectively use it.
cb: call back function which will be fed with an err parameter (if any).

Example:

soar.update('Person', {name: 'John Mayer'}, {psnID: 1}, cb);

soar.del(tbName, query, cb)

Deleting entries of a table.

tbName: name of the table whose entries will be deleted. If you're accessing multiple databases, you can use databaseName.tableName to specify which database you intend to do deletion.
query: the query condition. Refer to Query Object for how to effectively use it.
cb: call back function which will be fed with an err parameter (if any).

Example:

soar.del('Person', {psnID: 1}, cb);

soar.runSql(conn, sql, arguments, cb)

This function can be used to run SQL statements directly if you still need to. Even though SOAR provides quite a few handy functions to access databases, sometimes you may still need to manually build a SQL statement. In such cases, you can use this function.

conn: the database connection. This parameter is optional. You'll pass in a connecton parameter mostly because the SQL statement should be executed within a transactions.
sql: the SQL statement to be executed.
arguments: data to be filled into the SQL statement (if the given SQL statement is a prepared statement).
cb: the call back function with two parameters: err and result.

Example:

soar.runSql('SELECT * FROM Person WHERE psnID=?',
            [101],
            function(err, result)  {});
            
/* execute a SQL statement without parameters */
soar.runSQL('SELECT * FROM Person', null, function(err, result) {
    // do something in the callback
});

How to do transactions

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 = {
            insert: 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();
                });		
        });
    };
});

Schema Management

Besides accessing data, you can also use soar to manage table schema.

createTable(schema, cb)

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

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

deleteTable(tableName, 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).

describeTable(tableName, 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).

Debug Messages

If you want to know what SQL statements are actually generated by soar, you can set the 'debug' property of a soar command to be true (this feature only works for the execute() function). For example:

var  cmd = {
        list: expr,
        debug: true    // set to 'true' will print out SQL
     };
     
soar.execute(cmd, query, cb);

Regarding Tests

The soar package comes with some test files. To run those tests, sample data have to be built first. Inside the "test/sampleData" 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.

Supported Database

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.

ORM mySQL sql

mysql

@everything-registry/sub-chunk-2811 apinode newsql

3 years ago

3 years ago

3 years ago

4 years ago