Pg-promise: Array with ORDER BY

[normanfeltz]

Hi,

I use pg-promise in TypeScript with Angular.
I develop the application with a REST API interface.

I want to implement sort parameters in my REST API interface.
We get a array with colums to sort in ASC or in DESC.
What would be the best way for it to be clean and secure with pg-promise ?

Cordialy

Norman F.

[boromisp]

In accordance with the Contribution Notes you might want to post your questions next time on Stack Overflow instead of the issue tracker.

I don't think you can avoid dynamically building the query.
To escape the column names you can use the built-in formatting functions.

Let's say, you have an array containing your sort order in this form:

const order = [
    {column: 'a', desc: true},
    {column: 'b'},
    {column: 'c', desc: true}
];

You can then safely transform this array into SQL this way:

let sql = 'SELECT * FROM table'

if (order.length > 0) {
    sql += ' ORDER BY ';
    sql += order.map(t => pgp.as.name(t.column) + (t.desc ? ' DESC' : '')).join(', ');
}

// sql == 'SELECT * FROM table ORDER BY "a" DESC, "b", "c" DESC'

Since the column name is escaped by pgp.as.name(...), and the direction is a constant string, it is safe from injection.

[vitaly-t]

Named Parameters support list of names automatically, as per documentation.

But if you need to use ASC / DESC, then the most elegant approach is with Custom Type Formatting:

const order = [
    {column: 'a', desc: true},
    {column: 'b'},
    {column: 'c', desc: true}
];

const orderBy = a => ({
        toPostgres: () => a.map(t => pgp.as.name(t.column) + (t.desc ? ' DESC' : '')).join(),
        rawText: true
});

Then in a query you can do:

query('SELECT * FROM "table" ORDER BY $1', [orderBy(order)]);

[normanfeltz]

Hi,

Thanks for your responses.

Sorry for no respect of Contribution Notes, i had not read it.

After asking the question, I have found pg.as.format and I did it with that.

But these examples are very interesting, and allowed me to improve and optimize my code.

Cordialy

Norman F.

[vitaly-t]

Good, sorted then!