How is it possible to execute raw query and expose results through REST API with strongloop?
I've read something about using hooks and dataSource.connector.query() but I cannot find any working examples.
Add a comment
|
2 Answers
Here is a basic example. If you have a Product model (/common/models/product.json), extend the model by adding a /common/models/product.js file:
module.exports = function(Product) {
Product.byCategory = function (category, cb) {
var ds = Product.dataSource;
var sql = "SELECT * FROM products WHERE category=?";
ds.connector.query(sql, category, function (err, products) {
if (err) console.error(err);
cb(err, products);
});
};
Product.remoteMethod(
'byCategory',
{
http: { verb: 'get' },
description: 'Get list of products by category',
accepts: { arg: 'category', type: 'string' },
returns: { arg: 'data', type: ['Product'], root: true }
}
);
};
This will create the following endpoint example: GET /Products/byCategory?group=computers
http://docs.strongloop.com/display/public/LB/Executing+native+SQL
9 Comments
marka.thore
there are few typos:
Product.byGroup should be Product.byCategory and "SELECT * FROM products should be "SELECT * FROM productsmarka.thore
can you provide an example of how to store a Model in DB? I mean, how to create a new
Product and then do insertmarka.thore
and.. another question, what about SQL Injection?
richardaum
docs.strongloop.com/display/public/LB/Executing+native+SQL
This feature has not been fully tested and is not officially supported: the API may change in future releases. In general, it is always better to perform database actions through connected models. Directly executing SQL may lead to unexpected results, corrupted data, and other issues. Why documentation states it?
Deepu
I tried a example with exact same code but found lot of issues with above example, for my case postgresql does not accept "?", it will accept "$1,$2" instead of "?" and params must be array object ex. var params = []; params.push(st1); params.push(st2);
|
- expose a remote method in your
/common/models/model.js - execute the sql query in the remote method (via
dataSource.connector.query(sql, cb);
2 Comments
Christiaan Westerbeek
What if you want only a single remote method on a new model? In that case I guess using your suggestion you would have to disabled all the default remote methods like find, updateAll, etc using disableRemoteMethod right?
superkhau
Yes, I believe that is the only way ATM. I think it's something we need to make easier for LoopBack 3 because I've seen multiple requests in the past for a feature to disable all or allow only n remote methods, etc. See github.com/strongloop/loopback/issues/…