drizzle-team / drizzle-team/drizzle-orm

[FEATURE]: Coalesce helper

Open
#3,708 15 comments 52 reactions 0 assignees View on GitHub
enhancement qb/crud
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

### Feature hasn't been suggested before.

- [X] I have verified this feature I'm about to request hasn't been suggested before.

### Describe the enhancement you want to request

It would be handy to have a `coalesce` helper, same as we have for `count`, `distinct` or `min`.

This is what I'm using right now:

```ts
function coalesce(
column: C,
defaultValue: C['_']['data'] | AnyColumn<{ data: C['_']['data'] }>
): SQL {
return sql`coalesce(${column}, ${defaultValue})`.mapWith(column)
}
```

This covers most basic cases where we want to provide a default value for a column or use a fallback column. However, it **would need some extra tweaking to properly handle nullability of the second column** when provided.

The function could be overloaded like this:

```ts
function coalesce<
C1 extends Column,
C2 extends AnyColumn<{ data: C1['_']['data'] }>
>(
column: C1,
defaultValue: C2
): SQL<
C2 extends AnyColumn<{ notNull: true }>
? C1['_']['data']
: C1['_']['data'] | null
>
```

But this wouldn't account for columns coming out of left/right joins, which could be null even if not nullable.

Also, more complex scenarios like coalescing an arbitrary number of columns are challenging, since we cannot easily pass arbitrary values to `sql`. We would need something like this:

```ts
// NOTE types removed for simplicity
function coalesce(...expressions: any[]) {
const templateStringsArray = [
'coalesce(',
...new Array(expressions.length - 1).fill(', '),
')',
] as unknown as TemplateStringsArray

return sql(templateStringsArray, ...expressions)
}
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.