12

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.

colsen
  • 573
  • 5
  • 13
marka.thore
  • 2,795
  • 2
  • 20
  • 35

2 Answers2

27

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

jlareau
  • 2,860
  • 1
  • 17
  • 11
  • there are few typos: `Product.byGroup` should be `Product.byCategory` and `"SELECT * FROM products` should be `"SELECT * FROM products` – marka.thore Nov 21 '14 at 11:25
  • 1
    can you provide an example of how to store a Model in DB? I mean, how to create a new `Product` and then do insert – marka.thore Nov 21 '14 at 11:47
  • and.. another question, what about SQL Injection? – marka.thore Nov 21 '14 at 11:49
  • For StrongLoop 5.0.1 using Postgres, it requires the parameters to be an array: ds.connector.query(sql, [category], function (err, products) { – user441058 Nov 23 '15 at 07:29
  • 1
    https://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? – richardaum Nov 25 '15 at 12:55
  • 5
    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); – Deepu Jun 02 '16 at 09:22
  • Thanks Deepu to point out specific change for PostgreSQL. Good catch! – kensai Oct 14 '16 at 20:39
  • it's very unclear what params are, all I know is it is supposed to be an array – Alexander Mills Oct 21 '16 at 23:09
  • Thanks @Deepu, It was trying to figure out why the "?" was not working on my code – carloscarcamo Mar 16 '17 at 07:33
1
  1. expose a remote method in your /common/models/model.js
  2. execute the sql query in the remote method (via dataSource.connector.query(sql, cb);
superkhau
  • 2,781
  • 18
  • 9
  • 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](https://docs.strongloop.com/display/public/LB/Exposing+models+over+REST#ExposingmodelsoverREST-HidingmethodsandRESTendpoints) right? – Christiaan Westerbeek Mar 12 '16 at 19:18
  • 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 https://github.com/strongloop/loopback/issues/843#issuecomment-112950822 – superkhau Mar 12 '16 at 23:51