SQL Expressions

Hand coding SQL statements is not a recommended programming practice. First of all, it's error-prone. Secondly, it produces un-readable programs. One of the design goal of newsql is to make applications easier to develop and maintain, so hand-coding SQL is out of considerations. We need something more expressive while easier to build and maintain.

A JSON notation of SQL statements is introduced for this purpose. Most SQL statements contain parts to specify table, columns, query conditions and additional options. We can use a JSON object to specify these parts:

{
  "table" : {
      "name": "name_of_the_table",
      "join" : [
          {
            "table": "table_to_be_joined",
            "onWhat": "tableA.col=tableB.col"
          },
          {
            // another join specification
          },
          ...
       ]
  },
  "columns": ["col1", "col2", ...],
  "filter": {
      "name": "name_of_the_column",
      "op": "comparator_used"
   },
  "extra": "any_additional_options"
}

If you need to specify compounded query conditions, the "filter" property of the JSON notation can be specified as:

{
  ...
  "filter": {
      "op": "and",
      "filter": [
          {"name": "colName", "op": "comparator"},
          ...
      ]
   },
  ...
}

With the JSON notation described above, it would be easier to generate the desired queries. Also, we have better chances to reuse them. Such JSON notations are called "SQL expressions".

SQL expressions are big improvements over hand-coded SQL statements. However, it's still a bit tedious to express them manually. newsql provides some handy functions to help developers make SQL expressions:

newsql.sql('Person As psn')
      .join({
         table: 'location as loc',
         onWhat: 'psn.addrID=loc.id'
       })
      .column(['name', 'address'])
      .filter({name: 'age', op: '>'})
      .extra('ORDER BY age');

The newsql.sql() function returns a SQL expression object which has the join(), column(), filter() and extra() functions. Each of these functions will return the SQL expression object itself so you can chain them together as shown in the above example.

If you want to see what a SQL expression look like, you can use the value() function to retrieve the JSON object and use JSON.stringify() to show it. Below is the sample code:

var  expr = newsql.sql('Person').value();
console.log( JSON.stringify(expr, null, 4) );

Knowing what SQL expressions are and how to build them, we're ready to apply them to our database queries. In the next chapter, we'll show you how to use a single SQL expression to do all the CRUD (create, read, update and delete) operations.