The missing `ColumnType`?

[ethanresnick]

Right now, the existing ColumnType subtly conflates two different things:

1. What sort of operations are allowed on a table or column. So GeneratedAlways blocks providing a value for a column on INSERT; Generated makes it not required to do so.

2. The input/output formats of the column's data type, as accepted/returned by the driver.

This creates a problem whenever the type's input and output types are not identical.

Here's one domain-agnostic example: JSON columns that (might) hold arrays. In Postgres, the input needs to be something like:

sql`CAST(${JSON.stringify(value)} AS json)`

If the user instead tries to simply pass a raw JS array to the driver (matching the column's output type), the driver can't know whether the value should be serialized as a JSON array or a Postgres array; pg defaults to a Postgres array, which makes the insert/update blow up.

So, in our codebase, we have a utility function that handles generating the fragment shown above:

export function json<T extends JSONValue>(value: T) {
  return sql`CAST(${JSON.stringify(value)} AS json)`;
}

But, without some nudge (e.g. from the type system), developers almost always forget to call this utility (or don't even know that they should). This really becomes a problem when the column only occasionally holds an array value (e.g., if it takes arbitrary JSON blobs from the user), as then this mistake is even easy to miss in testing!

If we make our json utility return RawBuilder<T>, then kysely doesn't force the developer to call it: .update({ column: someArray }) is allowed just like .update({ column: json(someArray) }).

So, instead, we try to use the type system to force the user to call the json utility. To do that, we make it return a branded/tagged type — Tagged<T, "JsonColumnValue"> — and define the column as:

column: ColumnType<
  number[] | NewFancyFormat, 
  Tagged<number[] | NewFancyFormat, "JsonColumnValue">, 
  never
>

Now, the developer has to call the json function to get back a value to use in the update!

Except that there's one problem...

kysely.selectFrom("....").where("column", '=', someArray); 

works fine as far as the type system is concerned, but blows up at runtime. The issue is that the value in the where clause is typed according to the column's SelectType type, even though it really needs to be given a value in the column's input format type.

Granted, cases like these are quite rare, but I also think it'd be pretty easy to cover them in a pretty simple, backwards-compatible way by giving ColumnType a fourth type parameter — SelectInputType — which would default to SelectType and would be used as the type for the RHS in WHERE clauses (and perhaps some other niche places I'm forgetting about).