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. :-(

Upon further testing, this might be a bug with the StrongLoop Explorer not parsing the integer properly. This error only happens when I'm working with integers and it goes away if I change it to string. But then I get the error "error: bind message supplies 1 parameters, but prepared statement \"\" requires 0". I'll need to do testing without using the API Explorer tomorrow. — user441058, Nov 23 '15 at 08:25
I found out that this works fine for MySql. It works with numbers and strings. The only way to get this to work with Postgresql is to build a sql string from scratch (concatenate the values into the string without letting StrongLoop do it for me as parameters). Note that you have to write your own code to prevent sql-injection. One more thing, I looked at the source code for the StrongLoop library and they don't prevent sql injection either. They are just looping through the parameters and inserting the values into the sql string. Very bad practice. — user441058, Nov 26 '15 at 21:59

xangy · Accepted Answer · 2015-11-27T03:05:49.783

6

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.

edited Nov 27 '15 at 03:05

answered Nov 23 '15 at 09:29

xangy

1,185
1
8
19

Thanks for the link to the Loopback Connector docs. I didn't see that and I kept referencing the main StrongLoop docs. I switched it to Number (as well as [byId]) and now it gives me the error 'syntax error at end of input'. – user441058 Nov 23 '15 at 16:27
See my comment above that your answer works for MySql but won't work for Postgre. I assume the postgre library is buggy. – user441058 Nov 26 '15 at 22:00
hi refer this question at http://stackoverflow.com/questions/13119786/syntax-error-at-end-of-input-in-postgresql. I found that the placeholder needs to be changed from `?` to `$1` for postgresql. let me specify this in the answer. – xangy Nov 27 '15 at 03:03

StrongLoop query/stored procedure with Postgres?

1 Answers1