Kysely•2mo ago

Casting part of a return type

Hi there, Some of my queries return lots of fields. I want to be able to cast the return value of some of these fields. For example, I have a SELECT statement on a numeric field, but pg will parse this to a string. I want to be able to tell this in my output. - I can't use $.castTo because this destroys the other values in my SELECT clause. - I can't use $.narrowType because I am casting from number to string, not narrowing Does anyone have any suggestions? Example:

const rows = await db
  .selectFrom("person")
  .innerJoin("pet", "owner_id", "person.id")
  .where("first_name", "=", sql.lit("Jennifer"))
  .where("species", "=", species)
  .select(["first_name", "pet.name as pet_name"])
  .$castTo<{first_name: number}>()
  .execute();

const rows = await db
  .selectFrom("person")
  .innerJoin("pet", "owner_id", "person.id")
  .where("first_name", "=", sql.lit("Jennifer"))
  .where("species", "=", species)
  .select(["first_name", "pet.name as pet_name"])
  .$castTo<{first_name: number}>()
  .execute();

Here, we lose information about pet_name. I want to keep information about pet_name, while also being able to say that first_name will be returned as a number.

Solution:

https://kyse.link/RLzXh

Jump to solution

17 Replies

koskimas•2mo ago

Why not just fix the column type in your table interface?

OverHashOP•2mo ago

Hey, I can't because I have some operations like eb("a", "-", "b") (where b is my column) which will then error I presume you are talking about something like ColumnType<string, number | string, string | number> I will try make a better minimal playground repro Pretty much just like this https://kyse.link/UzWtJ In my Postgres database this is specifically the numeric(9, 6) type.

koskimas•2mo ago

eb("a", "-", "b") will not fail even if a and b are strings.

OverHashOP•2mo ago

So pg will return values like

{
    quantity: "100.000000";
    computed_quantity: "95.000000";
}

{
    quantity: "100.000000";
    computed_quantity: "95.000000";
}

It might be because of my coalesce call

koskimas•2mo ago

You can also configure postgres to return numbers instead of strings https://kysely.dev/docs/recipes/data-types#configuring-runtime-javascript-types

Data types | Kysely

When talking about data types in Kysely we need to make a distinction between the two kinds of types:

koskimas•2mo ago

If you're sure javascript numbers are enough to represent your data

OverHashOP•2mo ago

It's really hard for me to get a playground repro from this (when I use string in my types), but the error is something along the lines of

eb.fn.coalesce(
  eb.ref("person.quantity"),
  eb.lit("0"),
)

eb.fn.coalesce(
  eb.ref("person.quantity"),
  eb.lit("0"),
)

which gives something like

Property 'isSelectQueryBuilder' is missing in type 'ExpressionWrapper<{ /* snipped */ }, /* snipped */>' but required in type 'SelectQueryBuilderExpression<Record<string, string | null>>'.

Property 'isSelectQueryBuilder' is missing in type 'ExpressionWrapper<{ /* snipped */ }, /* snipped */>' but required in type 'SelectQueryBuilderExpression<Record<string, string | null>>'.

for some reason it works in playground though..

Solution

koskimas•2mo ago

https://kyse.link/RLzXh

OverHashOP•2mo ago

https://kyse.link/sNH35 Here's one of my places where it fails using string (using BETWEEN clause)

 Property 'isSelectQueryBuilder' is missing in type 'ExpressionWrapper<Database, "person", string>' but required in type 'SelectQueryBuilderExpression<Record<string, number>>'.

 Property 'isSelectQueryBuilder' is missing in type 'ExpressionWrapper<Database, "person", string>' but required in type 'SelectQueryBuilderExpression<Record<string, number>>'.

koskimas•2mo ago

https://kyse.link/flAWw Just match the types

OverHashOP•2mo ago

the actual SQL is fine if you use number type, because internally it is numeric(9, 6) I can't use .lit there right, because that requires it to be a number? Have to use .val("0") instead?

koskimas•2mo ago

You can use sql.lit eb.lit doesn't allow strings as it's not safe

OverHashOP•2mo ago

yep

koskimas•2mo ago

But just make postgres return numbers What's the issue with that?

OverHashOP•2mo ago

ok sweet, so probably doing things like

eb
  .val(parseBodyResult.data.remainingQuantity) // remainingQuantity is a number here
  .$castTo<string>()

eb
  .val(parseBodyResult.data.remainingQuantity) // remainingQuantity is a number here
  .$castTo<string>()

is the way to go? I'm not so sure if I want to do that over my entire db would be good if I could tell pg to only do that for a few columns but i can't see that being possible

koskimas•2mo ago

That works

OverHashOP•2mo ago

aha I found my issue, I was using a CTE and

.with("total_quantity", (db) =>
  db
    .selectFrom("person")
    .select((eb) =>
      eb.fn
        .sum<number>("person.initial_quantity")
        .as("quantity_consumed"),
    )
)

.with("total_quantity", (db) =>
  db
    .selectFrom("person")
    .select((eb) =>
      eb.fn
        .sum<number>("person.initial_quantity")
        .as("quantity_consumed"),
    )
)

key being the <number> as the sum, rather than <string> ok, thanks for your help and tips. think its good to close this now!

Gaming

Programming

Casting part of a return type