Feature request: third argument in `orderBy` for null sorting in relevant dialects

[jericirenej]

In PostgreSQL and SQLite the ORDER BY clause accepts a sorting configuration to override default behaviour. Currently, only two arguments can be passed - the value on which to order by and the direction. Alternatively, a raw query can also be provided. However, this might introduce some vulnerabilities when dealing with dynamic inputs.

A potential workaround currently is to use raw sql for the direction property: .orderBy(columnName, sql.raw(`${verifiedDirection} NULLS LAST`).

It would be useful to have an optional third argument by which to influence the default null sorting behaviour, perhaps something similar to:

orderBy(
UndirectedOrderByExpression<DB, TB, O>, 
direction?: OrderByDirectionExpression,
nulls?: "first"|"last"
)

[igalklebanov]

Hey 👋

I don't think the proposed API is the cleanest.

Since we're about to tackle this area anyway, because of mssql (orderby-offset-fetch coupling), we'll probably come up with an order by builder factory variant that'll have a nulls('first'|'last) method and maybe modifyEnd.

[jericirenej]

That sounds great and I'm looking forward to it! 👍 Does this mean that the current orderBy will no longer operate as it does now (with two parameters)? Or is this going to be an additional option or variant to add to the orderBy method?

[jacsamell]

+1 for this feature, Our workaround is as follows:

  export const orderNullsLast = (direction: OrderByDirection) => sql`${sql.raw(direction)} nulls last`;


db.selectFrom('table')
    .orderBy(order, orderNullsLast(direction))