drizzle-team / drizzle-team/drizzle-orm

[FEATURE]: Add encoding to sql operator

Open
#3,529 0 comments 1 reaction 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

The sql operator's [`mapWith` method](https://orm.drizzle.team/docs/sql#sqlmapwith) lets us specify mappers for decoding from the database, but there are use cases where we need to encode. See the example below.

1. Add a `decimalType` custom type

```ts
import { Decimal } from 'decimal.js'
import { type CustomTypeParams, customType } from 'drizzle-orm/pg-core'

const decimalTypeParams: CustomTypeParams<{
data: Decimal
driverData: string | number
}> = {
dataType() {
return 'numeric(64, 32)'
},

toDriver(data) {
return data.toString()
},

fromDriver(data): Decimal {
return new Decimal(data)
},
}

export const decimalType = customType(decimalTypeParams)
```

2. Query a column that uses it

```ts
// Example A: works great
eq(myTable.myDecimalCol, new Decimal("0"))

// Example B: driver error: invalid input syntax for type numeric: ""0""
eq(sql`abs(${myTable.myDecimalCol})`, new Decimal("0"))

// Example C: works, but not ideal imo
eq(sql`abs(${myTable.myDecimalCol})`, new Decimal("0").toString())
```

The issue in example B appears to be that it's not using the custom type's `toDriver` function, which is reasonable. However, there's no way to _make_ it use it.

This would be useful in functions that apply a SQL operator to a column value, but don't change its resultant type, e.g. it would be very useful to be able to write a function like this where the column's `mapFromDriverValue` _and_ `mapToDriverValue` are applied:

```ts
import {
type AnyColumn,
type GetColumnData,
type SQL,
type SQLWrapper,
Column,
is,
sql,
} from 'drizzle-orm'

export const abs: {
(column: SQL.Aliased): SQL
(
column: TColumn,
): SQL>
(column: Exclude): SQL
} = (column: SQL.Aliased): SQL => {
return sql`abs(${column})`.mapWith(
is(column, Column) ? column : (v: unknown) => v,
)
}
```

LMK if I'm missing something! Thanks!

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.