Kysely•15mo ago

How can one make `jsonObjectFrom` respect nullability of underlying subquery?

Hello, I am getting a type error when using the jsonObjectFrom helper on a subquery where the base table type of a field is not null, but all fields returned by jsonObjectFrom are nullable. We are passing the eb with the jsonObjectFrom helper inline in our query and were wondering if there a way to specify that a field will not be null?

24 Replies

koskimas•15mo ago

It's not possible to automatically determine nullability. Even if the field you use is non-null, the value might be missing from the database --> null

bombillazoOP•15mo ago

What about if the field is marked as not null in the database? For example I have this eb inside by select on table user:

export interface Account {
  id: string;
  name: string;
  ...
}

export interface User {
  id: string;
  account_id: string;
  ...
}

// part of select query for User
...
jsonObjectFrom(
  eb
  .selectFrom('account')
  .selectAll()
  .whereRef(
    'account.id',
    '=',
    'user.account_id'
   ),
).as('state'),

export interface Account {
  id: string;
  name: string;
  ...
}

export interface User {
  id: string;
  account_id: string;
  ...
}

// part of select query for User
...
jsonObjectFrom(
  eb
  .selectFrom('account')
  .selectAll()
  .whereRef(
    'account.id',
    '=',
    'user.account_id'
   ),
).as('state'),

For field name in account, Kysely knows the type of name is string (the DB type definition is set because the column is not nullable at the DB level). If I query the account table directly, nullability is preserved/works.

koskimas•15mo ago

I guess in this trivial case it would technically be possible. But as soon as you add another where condition there, all bets are off. Also if user is a CTE or a joined table with a different structure, things fail If we implemented this, it'd work only in very very specific circumstances. But it would make the types more complex, slower and harder to maintain

bombillazoOP•15mo ago

ok gotcha so then by default jsonObjectFrom just assumes the data is not guaranteed to exist due to the possibilities you mentioned. Is there a "Kysely way" to narrow the types on a jsonObjectFrom expression?

koskimas•15mo ago

Currently we don't have anything built-in for that, but we probably should. You can do something like this for now

function notNull<T>(expr: AliasableExpression<T>) {
  return expr.$castTo<Exclude<T, null>>()
}

notNull(
  jsonObjectFrom(
    eb
    .selectFrom('account')
    .selectAll()
    .whereRef(
      'account.id',
      '=',
      'user.account_id'
     ),
  )
).as('state'),

function notNull<T>(expr: AliasableExpression<T>) {
  return expr.$castTo<Exclude<T, null>>()
}

notNull(
  jsonObjectFrom(
    eb
    .selectFrom('account')
    .selectAll()
    .whereRef(
      'account.id',
      '=',
      'user.account_id'
     ),
  )
).as('state'),

I didn't run that so there might be typos and other mistakes.

bombillazoOP•15mo ago

hmm ok, but that is to make the state field not null, or to make the name field in the state field not null?

koskimas•15mo ago

It makes the type it gets as an input not null. So if your jsonObjectFrom call returns a Account | null, passing that through notNull will spit out Account But I just realized AliasableExpression doesn't have the $castTo method. You can do this instead

function notNull<T>(expr: AliasableExpression<T>) {
  return expr as unknown as AliasableExpression<Exclude<T, null>>
}

function notNull<T>(expr: AliasableExpression<T>) {
  return expr as unknown as AliasableExpression<Exclude<T, null>>
}

bombillazoOP•15mo ago

Ok, what we need is for Account to not have name : string | null since jsonObjectFrom will make all fields in that result nullable. Ill figure something out, one potential idea that comes to mind is to make jsonObjectFrom accept a generic to kinda merge/override the natural result ...

koskimas•15mo ago

jsonObjectFrom doesn't make the fields of the object nullable

bombillazoOP•15mo ago

Oh.

koskimas•15mo ago

It makes the object itself nullable

bombillazoOP•15mo ago

ok and that extends to name being potentially null?

koskimas•15mo ago

no Just hover over the objects to see the type https://kyse.link/?p=s&i=woz93LYcl2yAiBZ56hIj Hover over the result

bombillazoOP•15mo ago

ok I see... something you're right. I saw it on my side as well

bombillazoOP•15mo ago

Something else is messing with me then, I have this expression and the results show name is string | null

bombillazoOP•15mo ago

and the interface definition

koskimas•15mo ago

What does the query look like where you use that helper?

bombillazoOP•15mo ago

koskimas•15mo ago

I have no idea what's causing that Ah You're not using table-specified name. Some other table also has the name column Use business.name

bombillazoOP•15mo ago

🤦‍♂️ You da man 😆

bombillazoOP•15mo ago

lesson learned hahaha ! excellent 😄

bombillazoOP•15mo ago

I truly appreciate the time helping and guiding me!!!

koskimas•15mo ago

I'm happy to help people like you!

bombillazoOP•15mo ago

PD: Im surprised (and impressed) the Kysely types are smart enough to actually detect ambiguous column names across tables. Good thing to be conscious of

Gaming

Programming

How can one make `jsonObjectFrom` respect nullability of underlying subquery?