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.

Answer 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

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