Add `cast` function to `ExpressionBuilder`

[koskimas]

Add cast function that produces a cast(expr as datatype) expression. cast is not strictly a function so I think it should live under ExpressionBuilder.

The signature would be something like:

// If no type is provided, infer the type from the data type
cast<DT extends DataTypeExpression>(
  expr: ReferenceExpression<DB, TB>,
  dataType: DT
): ExpressionWrapper<DB, TB, DataTypeToType<DT>>

// Provide both type and dataType
cast<T>(
  expr: ReferenceExpression<DB, TB>,
  dataType: DataTypeExpression
): ExpressionWrapper<DB, TB, T>

For this we need the DataTypeToType mapping, which unfortunately differs from dialect to dialect.

We could implement the "global type configs" before this as discussed with @igalklebanov and the user would be able to override the DataTypeToType mapping.

[igalklebanov]

Some way (could be the globals we've discussed, could be something else) of declaring JS to SQL and SQL to JS mapping types will be epic, not just for cast which on its own, is a really useful feature to have, but for anything that Kysely currently infers a wide type for (e.g. aggregate functions inferring to string | number | bigint or booleans being SqlBool, or self-correcting a user that picked Date for a column type and the dialect always returns strings).

There's still investigating to do with globals, if they're possible, if they're a good solution, dealing with multi-dialect codebases, dealing with multi-kysely codebases, etc.

[koskimas]

What do you think about adding a cast function with only a basic automatic mapping for now? I'm thinking this should be pretty safe:

// TODO: This is just a basic mapping that should work on most dialects.
// We need to make this dialect-specific or at least overridable by the
// user.
type DataTypeToType<DT extends DataTypeExpression> = DT extends 'integer'
  ? number
  : DT extends 'int4'
  ? number
  : DT extends 'int2'
  ? number
  : DT extends 'tinyint'
  ? number
  : DT extends `tinyint(${number})`
  ? number
  : DT extends 'double precision'
  ? number
  : DT extends 'real'
  ? number
  : DT extends 'text'
  ? string
  : DT extends 'varchar'
  ? string
  : DT extends 'char'
  ? string
  : DT extends `varchar(${number})`
  ? string
  : DT extends `char(${number})`
  ? string
  : unknown

[koskimas]

Or this

// TODO: This is just a basic mapping that should work on most dialects.
// We need to make this dialect-specific or at least overridable by the
// user.
type DataTypeToType<DT extends DataTypeExpression> = DT extends
  | 'integer'
  | 'int4'
  | 'int2'
  | 'tinyint'
  | `tinyint(${number})`
  | 'double precision'
  | 'real'
  ? number
  : DT extends
      | 'text'
      | 'varchar'
      | `varchar(${number})`
      | 'char'
      | `char(${number})`
  ? string
  : DT extends Expression<infer T>
  ? T
  : KyselyTypeError<`There's no unambiguous mapping from ${DT extends string
      ? DT
      : 'raw sql'} to a typescript type. Please provide an explicit type argument for the cast method: cast<T>(expr)`>

[igalklebanov]

Good idea! I was about to comment with the "Or this" option but had a context switch - should go with that.

[dwickern]

I use this function and add the overloads I need:

import type { AliasableExpression, ColumnDataType, Expression } from 'kysely';
import { sql } from 'kysely';

type Int8 = number | string | bigint;

// if the input type can be null, then the output type can also be null; cast(null as type) always results in null
type CastExpression<From, To> = AliasableExpression<From extends null ? To | null : To>;

export function cast<T extends string | null>(expr: Expression<T>, type: 'bytea'): CastExpression<T, Buffer>;
export function cast<T extends Int8 | null>(expr: Expression<T>, type: 'int4'): CastExpression<T, number>;
// ... add any other casts you need

export function cast(expr: Expression<unknown>, type: ColumnDataType): AliasableExpression<unknown> {
  return sql`cast(${expr} as ${sql.raw(type)})`;
}

[igalklebanov]

Released in https://github.com/kysely-org/kysely/releases/tag/0.27.3

[musjj]

Is there a way to combine this with eb.fn?

eb
  .selectFrom("foo")
  .select([
    "foo.bar",
    (eb) => eb.cast((eb) => eb.fn.countAll(), "integer"),
  ])

It seems that this causes a type error.

EDIT: Whoops, it looks like I got it wrong:

eb
  .selectFrom("foo")
  .select((eb) => [
    "foo.bar",
    eb.cast<number>(eb.fn.countAll(), "integer").as("cnt"),
  ])