Before exploring newsql's features, let's start with simplifying relational database programming by newsql.
newsql provides some handy functions to make database access extremely easy. For example, if you want to query data entries from a table, you can do:
newsql.find(tableName, query, cb);
where tableName is the name of the table to be queried. query is a JSON object containing query values. In its simplest form, a query object is the column-value pair which will make up the WHERE clause of a SQL statement. Considering a query object as below:
{zipcode: '94604'}
newsql will translate the query object to something like:
WHERE zipcode = '94604'
With a very simple format, a query object can actually express most (if not all) of the query conditions. We'll talk more about the query object in the next section.
Back to the newsql.find() function. cb is the callback function which will be passed with two arguments: an error object if anything goes wrong and an array of return objects. If your query yields no result, the return list will be empty (instead of null).
As explained in the previous section, a query object can be translated into the WHERE clause of a SQL statement. We've shown a very simple query object with only one property. What if the query object has multiple properties? The effect is to "AND" all the properties of a query object. For example, if we have a query object as below:
{
zipcode: '94604',
rooms: 3
}
the WHERE clause will become:
WHERE zipcode = '94604' AND rooms = 3
So far so good, but what if we need comparators other than the equal operator ('=') ? The format can be extended a bit so we can specify the operators we want.
{colName: {op: 'the_comparator', value: x}}
Assuming we want to query houses with more than 3 bedrooms in a specific area, below is what we can do:
{
zipcode: '94604',
rooms: {op: '>', value: 3}
}
So we're able to specify multiple query terms with various comparators and "AND" them together. If you need to "OR" properties instead of "AND", you just need to specify 'or' as the property name and a query object as its value:
{
or: a_query_object
}
For example, if you want to query houses with pricing lower than $400,000 or having only 2 bedrooms, you can specify the query condition this way:
{
or: {
price: {op: '<', value: 400000},
rooms: 2
}
}
Pretty simple, isn't it? Up to this point, you probably have already known how to make compounded queries. You just have to specify 'or' or 'and' as the property name with yet another query object as the property value. Check this example:
{
and: {
zipcode: '94604',
or: {
cost: {op: '<', value: 400000},
rooms: 2
}
}
}
That can produce a WHERE clause like:
WHERE zipcode = '94604' AND
(cost < 400000 OR rooms = 2)
We can even make that query object more concise:
{
zipcode: '94604',
or: {
cost: {op: '<', value: 400000},
rooms: 2
}
}
As logic "AND" is the default behavior when a query object has multiple properties.
With query objects at hands, we have lots of expressive power to specify the desired query conditions. As shown in the previous section, to find out houses with pricing lower than $400,000 or having just 2 bedrooms in a specific area, you can do it by:
var query = {
zipcode: '94604',
or: {
cost: {op: '<', value: 400000},
rooms: 2
}
};
newsql.find('Housing', query, function(err, list) {
// list containing houses meet our criterion
});
newsql.find() would look up data meeting our criteria. However, if we want to specify returned columns or join tables, how can we achieve that with newsql.find() ? There must be a way to specify the return columns or table joins in the find() function call. The answer is to use SQL expressions described in chapter 1.
Assuming you're still looking for houses, but this time you're looking for appartments near an university (say UC Berkeley). We can make a SQL expression doing just that.
var expr = newsql.sql('House AS hs')
.join({
table: 'Universities AS un',
onWhat: 'hs.zipcode=un.zipcode'
})
.column(['price', 'rooms', 'hs.addr as address'])
.filter({name: 'un.name', op: '='});
newsql.find(expr, {name: 'UC Berkeley'}, function(err, list) {
});
So there are two possible usages of newsql.find(): one with table name and query objects while the other using SQL expressions. How to use them properly? Here is a tip. If a specific query will be applied often, it's better to formulate that query into a SQL expression. You may even want to give it a nice name so you can quickly remember what it's about. Let's just do it to our previous example:
var houseNearUniversityExpr =
newsql.sql('House AS hs')
.join({
table: 'Universities AS un',
onWhat: 'hs.zipcode=un.zipcode'
})
.column(['price', 'rooms', 'hs.addr as address'])
.filter({name: 'un.name', op: '='});
By giving a nice name to a SQL expression, it helps us to manage and reuse the SQL expression. Next time when you invoke hourseNearUniversityExpr, you know exactly what it's about withut checking back its definition or source code.
In the previous section, we go some distance to descirbe the various usages of find(). Doing insert, update or delete are just as simple because they bear a similar format as the find() function.
To insert an entry to a table, you can use the insert() function with a table name and the data to be inserted. For example:
var data = {
price: 380000,
rooms: 3
};
newsql.insert('House', data, function(err, pk) {
});
The callback function will be passed an error object (if any) and an entity key object. The entity key object will contain primary keys and their values of the newly inserted entity.
You can replace the table name with a SQL expression in the insert() function. A SQL expression built for the insert() function is needed. However, you can also reuse existing SQL expressions. In our case, you can actually use the houseNearUniversityExpr expression defined eariler to do insert:
newsql.insert(houseNearUniversityExpr, data, cb);
You may be a bit surprised to see houseNearUniversityExpr being used in insert as that expression was built to do table joins. newsql is smart enough to take what it needs from a SQL expression.
Doing table update is similar. The update() function intakes four parameters: a table name, a JSON object containing data to be updated, a query object and a callback function. Below is an example:
var data = {rooms: 2},
query = {id: 338};
newsql.update('House', data, query, function(err) {
});
Again, you can replace the table name with a SQL expression. If the SQL expression has table columns defined, newsql will filter out 'data' properties not matched with the SQL expression table columns. Because newsql will by default save whatever data properties you throw at it, this can be a very useful feature. When you want to limit possible data properties to a certain set, you can specify the permitted properties as columns in a SQL expression and allow newsql to do the checking (filtering).
Finally, you can use the del() function to delete table entries. The del() function takes three parameters: a table name, a query object and a callback function. Below is a typical use case for delete:
newsql.del('House', {id: 337}, function(err) {
});
We've wrapped up the guide of using newsql in "regular" SQL operations. Using that understanding as a foundataion, in the next chapter we'll start to push the limit of relational databases and make them suitable for the dynamic features of today's web applications.