OverHash Posts - Answer Overflow

OverHash

•Created by OverHash on 10/15/2024 in #help

ilike with reference

Hey there, I'm looking to perform the following SQL operation:

WHERE groups.group_name ILIKE '%' || users.first_name || '%'

WHERE groups.group_name ILIKE '%' || users.first_name || '%'

but unfortunately I'm having trouble doing this in Kysely. Here is my current attempt:

eb(
  "groups.group_name",
  "ilike",
  eb.fn<string>("concat", [
    eb.val("%"),
    eb.ref("users.first_name"),
    eb.val("%"),
  ]),
)

eb(
  "groups.group_name",
  "ilike",
  eb.fn<string>("concat", [
    eb.val("%"),
    eb.ref("users.first_name"),
    eb.val("%"),
  ]),
)

which will compile fine, but unfortunately pg throws a error: could not determine data type of parameter $1 error when running. I printed the sql query compiled by Kysely and got:

sql: 'where ("groups"."group_name" ilike concat($1, "users"."first_name", $2) ',
parameters: [ '%', '%' ]

sql: 'where ("groups"."group_name" ilike concat($1, "users"."first_name", $2) ',
parameters: [ '%', '%' ]

which Pg can't seem to handle. Does anyone have any suggestions? I recognize that this problem might not necessarily be with Kysely.

6 replies

KKysely

•Created by OverHash on 10/5/2024 in #help

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.

38 replies

KKysely

•Created by OverHash on 9/30/2024 in #help

Selecting result of subtraction with summation

Hey, I'm having trouble translating the effective following Postgresql code to Kysely code:

SELECT
  reagent_inventory.initial_quantity -
  COALESCE(SUM(reagent_inventory_usage.quantity_consumed), 0) AS remaining_quantity

SELECT
  reagent_inventory.initial_quantity -
  COALESCE(SUM(reagent_inventory_usage.quantity_consumed), 0) AS remaining_quantity

I tried to use this:

.select((eb) =>
  eb(
    eb.val("inventory.initial_quantity"),
    "-",
    eb.fn.coalesce(
      eb.fn.sum<number>("inventory_usage.quantity_consumed"),
      eb.val(0),
    ),
  ).as("remaining_quantity"),
)

.select((eb) =>
  eb(
    eb.val("inventory.initial_quantity"),
    "-",
    eb.fn.coalesce(
      eb.fn.sum<number>("inventory_usage.quantity_consumed"),
      eb.val(0),
    ),
  ).as("remaining_quantity"),
)

but this yields to a type error:

Argument of type 'ExpressionWrapper<Database, "inventory" | "inventory_usage", number>' is not assignable to parameter of type 'OperandValueExpressionOrList<...>, "inventory" | "inventory_usage", ExpressionWrapper<...>>'.
  Property 'isSelectQueryBuilder' is missing in type 'ExpressionWrapper<..., "inventory" | "inventory_usage", number>' but required in type 'SelectQueryBuilderExpression<Record<string, string | null>>'.

Argument of type 'ExpressionWrapper<Database, "inventory" | "inventory_usage", number>' is not assignable to parameter of type 'OperandValueExpressionOrList<...>, "inventory" | "inventory_usage", ExpressionWrapper<...>>'.
  Property 'isSelectQueryBuilder' is missing in type 'ExpressionWrapper<..., "inventory" | "inventory_usage", number>' but required in type 'SelectQueryBuilderExpression<Record<string, string | null>>'.

Does anyone have any ideas about how to safely get around this? Was hoping to avoid using sql. Here's a minimal repro of what I'm trying to do: https://kyse.link/EdviN

4 replies

KKysely

•Created by OverHash on 9/11/2024 in #help

bulk-insert with constant variable as one column

Hey, I'm basically trying to mirror https://stackoverflow.com/questions/6937442/in-postgres-is-it-possible-to-insert-a-constant-combined-with-the-result-of-sel. I can't figure out how I'd achieve this with Kysely. I'm currently here:

await trx
  .insertInto("mapped_groups")
  .columns(["group_id_fk", "mapped_id_fk"])
  .expression((eb) =>
    eb
      .selectFrom("groups")
      .select(["group_id_fk", sql`${insertedId}`])
      .where("group_name", "in", groups),
  );

await trx
  .insertInto("mapped_groups")
  .columns(["group_id_fk", "mapped_id_fk"])
  .expression((eb) =>
    eb
      .selectFrom("groups")
      .select(["group_id_fk", sql`${insertedId}`])
      .where("group_name", "in", groups),
  );

but this doesn't quite work on the select statement, since Kysely/TS doesn't like the second argument. Does anyone know how I could do a INSERT INTO ... SELECT ... query, where one of the values I am SELECTing is a constant (in my case, insertedId)?

4 replies

KKysely

•Created by OverHash on 8/29/2024 in #help

JSON Object Traversal

Hi everyone, new user of Kysely. I have the following json object inside a sess column:

{
  "cookie": {
    "originalMaxAge": 2592000000,
    "expires": "2024-09-28T04:18:12.565Z",
    "secure": false,
    "httpOnly": true,
    "path": "/",
    "sameSite": "lax"
  },
  "passport": { "user": { "user_id": "45c718f8-a3c7-4fb4-b50a-44c8fec2fea1" } }
}

{
  "cookie": {
    "originalMaxAge": 2592000000,
    "expires": "2024-09-28T04:18:12.565Z",
    "secure": false,
    "httpOnly": true,
    "path": "/",
    "sameSite": "lax"
  },
  "passport": { "user": { "user_id": "45c718f8-a3c7-4fb4-b50a-44c8fec2fea1" } }
}

I'm trying to write a type-safe Kysely query to traverse this. Essentially, I want a Postgresql query that looks like this:

select * from "sessions" where "sess"->'passport'->'user'->>'user_id' = '45c718f8-a3c7-4fb4-b50a-44c8fec2fea1';

select * from "sessions" where "sess"->'passport'->'user'->>'user_id' = '45c718f8-a3c7-4fb4-b50a-44c8fec2fea1';

I'm almost there, however, I'm running into some issue getting thjat last ->> part. Here is what I have so far:

await db
  .selectFrom("sessions")
  .where(
    ({ ref }) =>
      ref("sess", "->").key("passport").key("user").key("user_id"),
    "=",
    userId,
  )
  .executeTakeFirstOrThrow();

await db
  .selectFrom("sessions")
  .where(
    ({ ref }) =>
      ref("sess", "->").key("passport").key("user").key("user_id"),
    "=",
    userId,
  )
  .executeTakeFirstOrThrow();

this, unfortunately, generates a query like this:

select * from "sessions" where "sess"->'passport'->'user'->'user_id' = '45c718f8-a3c7-4fb4-b50a-44c8fec2fea1';

select * from "sessions" where "sess"->'passport'->'user'->'user_id' = '45c718f8-a3c7-4fb4-b50a-44c8fec2fea1';

(notice that the last user -> user_id is not a ->>, as it should be) which causes postgresql to fail. How am I supposed to tell Kysely to use ->> on the last step there?

8 replies

KKysely

•Created by OverHash on 8/28/2024 in #help

express-session store

Hi, I'm a new user of Kysely. I'm wondering if there is a way to connect kysely with express-session. For example, to use in combination with passport. For example, I was wondering if Kysely somehow exposes a pg compatible pg.Pool, which would allow me to use something like connect-pg-simple to have an adapter from the Kysely world to the pg world. I couldn't find anything around online or in this Discord, so I'm wondering if the Kysely ecosystem is mature enough to have this stuff around yet? Or any ideas as to how I could go about this without too much pain? 🙂

5 replies

Gaming

Programming