The sql soar Programmer's Guide

The main objective of creating sql-soar is to help developers managing SQL. To do so it's suggested to avoid hand crafting SQL statements while still having precise control of how SQL statements are generated. To achieve the first goal, syntax wise we need something more manageable than SQL. Luckily, we have JSON which can perfectly fill the post. To have precise control of SQL generation, we probably have to keep away from ORM. So here comes sql-soar.

You can use sql-soar in three different styles. One is to access tables directly without specifying how that can be or should be done. The other way is to use some JSON expressions to tell sql-soar how the SQL statements should be constructed. The third approach is to save the JSON expression for SQL to a file and reuse it in your programs. In the following, we'll show you how to use sql-soar in these three different styles.

The Object Style

In this style, you don't worry about any SQL at all. You treat a table as an object model. This object style is very easy to use and works quite well. The only catch is that you're not going to access multiple tables in one shot. In other words, you cannot do table joins with the object style.

With the object style, you can query a table by providing the querying conditions like below:

soar.list('Person', {age: 25}, function(err, list) {
  // 'list' will contain persons whose age is 25.
});

or expecting just a single instance of return:

soar.query('Person', {name: "David Lynch"}, function(err, data) {
    if (err)
        console.log( err.stack );
    else
        console.log('About David: %s', JSON.stringify(data));
});

As you can tell from the examples above, the signatures of object query can be summarized as below:

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

You can do quite the same to update tables:

soar.update('Person', {weight: 160}, {id: 28}, (err) => {
    // check if the weight of the person (id=28) has been updated
});

or to insert a record to a table:

soar.insert('Person', {name: 'Sean', age: 18}, (err, pk) => {
    // 'pk' will contain the primary key of the inserted entry
    // if the primary key is an auto-increment primary key
);

It's similar to delete entries from a table:

soar.del('Person', {age: 18}, (err) => {
    // this will delete all persons of 18 years old
});

In the above examples, we use a JSON object (the query object) to specify query conditions. Even though what we've shown are quite simple query conditions, you can actually specify fairly sophisticated ones.

The Query Object

A query object is a JSON object which will be translated into the WHERE clause of a SQL statement by sql-soar. The basic form of a query object is as the following:

{colName: {op: 'the_comparator', value: x}}

The key is the column name of a table and its value specifies what operator will be applied on that column and what value will be used for comparison. So if you write something like:

{age: {op: '>', value: 18}}

you are to query a table by asking the 'age' column to be greater than 18.

to be continued...