[FEATURE]: nulls first/last support for order by asc & desc in postgres

[VaibhavAcharya]

Describe what you want

currently the asc & desc do not support order of nulls (first/last)

by default in postgres the nulls come first for desc and last for asc orders, which is quite stupid to be honest but anyways,

I needed that so build my own wrappers for it below but I think this should be a inbuild thing

import { type AnyColumn, type SQLWrapper, sql } from "drizzle-orm"

export function asc(column: SQLWrapper | AnyColumn, nullsLast: boolean = true) {
  if (nullsLast) return sql`${column} asc nulls last`

  return sql`${column} asc`
}

export function desc(
  column: SQLWrapper | AnyColumn,
  nullsLast: boolean = true,
) {
  if (nullsLast) return sql`${column} desc nulls last`

  return sql`${column} desc`
}

[mauriciabad]

Thanks!

For mySQL use the following:

import { sql, type AnyColumn, type SQLWrapper } from 'drizzle-orm'

export function ascNullsEnd(column: SQLWrapper | AnyColumn) {
  return sql`ISNULL(${column}), ${column} asc`
}

export function descNullsEnd(column: SQLWrapper | AnyColumn) {
  return sql`ISNULL(${column}), ${column} desc`
}

export function ascNullsStart(column: SQLWrapper | AnyColumn) {
  return sql`NOT ISNULL(${column}), ${column} asc`
}

export function descNullsStart(column: SQLWrapper | AnyColumn) {
  return sql`NOT ISNULL(${column}), ${column} desc`
}

[marceloverdijk]

Same would be useful for SQLite

SQLite considers NULL values to be smaller than any other values for sorting purposes. Hence, NULLs naturally appear at the beginning of an ASC order-by and at the end of a DESC order-by. This can be changed using the "ASC NULLS LAST" or "DESC NULLS FIRST" syntax.

https://www.sqlite.org/lang_select.html#orderby