Execute raw query on MySQL Loopback Connector

Question

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.

Solution 1

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

Solution 2

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);

Comments

[marka.thore]

there are few typos: Product.byGroup should be Product.byCategory and "SELECT * FROM products should be "SELECT * FROM products

[marka.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 insert

[marka.thore]

and.. another question, what about SQL Injection?

[user441058]

For StrongLoop 5.0.1 using Postgres, it requires the parameters to be an array: ds.connector.query(sql, [category], function (err, products) {

[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?

[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/…

[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);

[kensai]

Thanks Deepu to point out specific change for PostgreSQL. Good catch!

[Alexander Mills]

it's very unclear what params are, all I know is it is supposed to be an array

[carloscarcamo]

Thanks @Deepu, It was trying to figure out why the "?" was not working on my code