StrongLoop query/stored procedure with Postgres?

Question

Per the docs, StrongLoop doesn't support running custom sql statements. https://docs.strongloop.com/display/public/LB/Executing+native+SQL

How anyone thinks you can build an enterprise app with just simple joins is beyond me, but I did find this post which says you can do it: Execute raw query on MySQL Loopback Connector

But this is for MySql. When I try it with Postgres I get the error: "Invalid value for argument 'byId' of type 'object': 0. Received type was converted to number." And it returns no data. Here is my code:

module.exports = function(account) {

account.byId = function(byId, cb){
    var ds=account.dataSource;
    var sql = "SELECT * FROM account where id > ?";
    ds.connector.execute(sql, [Number(byId)], function(err, accounts)    {
        if (err) console.error(err);
        console.info(accounts);
        cb(err, accounts);
    });
};
account.remoteMethod(
    'byId',
    {
        http: {verb: 'get'},
        description: "Get accounts greater than id",
        accepts: {arg: 'byId', type: 'integer'},
        returns: {arg: 'data', type: ['account'], root: true}
    }
);
};

For the part [Number(byId)], I've also tried [byId] and just byId. Nothing works.

Any ideas? So far I really like StrongLoop, but it looks like the Postgresql connector is not ready for production. I'll be doing a prototype with Sails next if this doesn't work. :-(

Answer 1

Here's the thing arg is of type 'integer' which is not a valid Loopback Type. Use `Number instead. Check the corrected code below:

module.exports = function(account) {
    account.byId = function(byId, cb){
        var ds = account.dataSource;
        var sql = "SELECT * FROM account WHERE id > $1";
        ds.connector.execute(sql, byId, function(err, accounts) {
            if (err) console.error(err);
            console.info(accounts);
            cb(err, accounts);
        });
    };
    account.remoteMethod(
        'byId',
        {
            http: {verb: 'get'},
            description: "Get accounts greater than id",
            accepts: {arg: 'byId', type: 'Number'},
            returns: {arg: 'data', type: ['account'], root: true}    //here 'account' will be treated as 'Object'.
        }
    );
};

Note: MySQL's prepared statements natively use ? as the parameter placeholder, but PostgreSQL uses $1, $2 etc.

Hope this works for you. Else try with [byId] instead of byId as per the docs.