Subqueries inside SELECT are not nullable

[danielrearden]

Example from the playground:

interface DB {
  user: User
}

interface User {
  id: string
  first_name: string | null
  last_name: string
}

const rows = await kysely
  .selectFrom('user')
  .select(['first_name', 'last_name', kysely.selectFrom('user').select('id').limit(1).as('some_id')])
  .execute()

Here, the resulting type is

{
  first_name: string | null;
  last_name: string;
  some_id: string;
}

However, there's no guarantees that the provided subquery will actually return any rows, and in the case where there are no rows returned, some_id would evaluate to null.

Is there a way to get string | null as the type for some_id (without using $castTo)? If not, I think it would make sense to either make these values nullable by default, or provide a way to configure this behavior either globally or an a per-query basis.

If the value was nullable by default, that would obviously be a pretty significant breaking change. However, it is arguably the safest approach, since the nullability of the value is contingent on not just the WHERE condition inside the subquery, but also the state of the database itself. The value could safely be made non-nullable by wrapping the whole expression inside a coalesce function. If there's a particular scenario where the value could never be null, then arguably that would be an appropriate use case for manually overriding the type -- and not the other way around. Just my two cents.

[koskimas]

That's a good point! I'll see if there is a clean way to make the type nullable in these cases.

[jirutka]

I have the opposite problem. My subquery can never return null and I cannot figure out how to get rid of | null in the return type without using an unnecessary coalesce. $castTo doesn’t work here.

.selectFrom('timetableSlot as s')
...
.select(eb => eb
  .selectFrom('timetableSlotTeacher as t')
  .select(eb => sql<number[]>`json_group_array(${eb.ref('t.teacherId')})`.as('x'))
  .whereRef('t.timetableSlotId', '=', 's.id')
  .$castTo<number[]>() // <-- has no effect
  .as('teacherIds'),
)
...
.execute()