decho Posts - Answer Overflow

decho

Explore posts from servers

DIAdiscord.js - Imagine a boo! 👻TtRPC KKysely

•Created by decho on 7/1/2024 in #help

How to infer an aggregated column with `filterWhere` clause as nullable?

Greetings. I am joining from another table using leftJoin, then grouping rows by id, and finally using jsonAgg(jsonBuildObject(...)) with a filterWhere method chained on top of this expression as seen in the demo. The problem is that the inferred type by kysely is incorrect, because it thinks the json aggregate function will always be an array, however, if there are no matching rows from the other table it will be NULL because of the filter where clause. I know I can use the .$castTo<{...}>() helper and manually set the type, but that can become problematic, especially when dealing with larger objects. Is there any workaround for this problem? I thought filterWhere would be smart enough to make the column nullable, or maybe I'm not smart enough myself and I'm missing something 🙂 https://kyse.link/COF71

5 replies

KKysely

•Created by decho on 6/11/2024 in #help

Question about ColumnTypes and dates.

I have a simple yet confusing problem. In postgres I have a table with a column of type DATE (yyyy-mm-dd).

CREATE TABLE IF NOT EXISTS person(
    id INT NOT NULL,
    birthday DATE NOT NULL
);

CREATE TABLE IF NOT EXISTS person(
    id INT NOT NULL,
    birthday DATE NOT NULL
);

Kysely's PostgresDialect is using the node-postgres/pg driver, which transforms date columns as javascript Date objects when it returns rows, however, you can use strings and/or Date objects as query parameters, both are valid ways to query the database. This works both in node and when you write raw SQL:

-- both are valid queries
SELECT * FROM person WHERE birthday < '2020-01-01';
SELECT * FROM person WHERE birthday < NOW();

-- both are valid queries
SELECT * FROM person WHERE birthday < '2020-01-01';
SELECT * FROM person WHERE birthday < NOW();

The problem is that I'm not sure how to define my database types to account for that. If I create the following type I will get type error if I use string in my .where clauses:

export interface PersonTable {
  id: Generated<number>;
  birthday: ColumnType<Date, Date, Date>;
}

// ❌ will give a type error because birthday is a string
await db.selectFrom('person').where('birthday', '=', '2020-01-01');

export interface PersonTable {
  id: Generated<number>;
  birthday: ColumnType<Date, Date, Date>;
}

// ❌ will give a type error because birthday is a string
await db.selectFrom('person').where('birthday', '=', '2020-01-01');

and, if I change the ColumnType to be a union of Date | string as acceptable values for ColumnType -> SelectType, then the inferred result for birthday in all queries will also be the same union, but we obviously know node-postgres only returns JS Date objects:

export interface PersonTable {
  id: Generated<number>;
  birthday: ColumnType<Date | string, Date | string, Date | string>;
}

// ❌ res.birthday will be inferred as Date | string instead of just Date
const res = await db.selectFrom('person').selectAll().executeTakeFirstOrThrow();

export interface PersonTable {
  id: Generated<number>;
  birthday: ColumnType<Date | string, Date | string, Date | string>;
}

// ❌ res.birthday will be inferred as Date | string instead of just Date
const res = await db.selectFrom('person').selectAll().executeTakeFirstOrThrow();

So my question is, is there any way to deal with this problem? Here is a kyse.link as well: https://kyse.link/2CJ6I In a certain way, the SelectType in ColumnType is more like select type (how you can select) and also a return type (how the result is inferred).

10 replies

KKysely

•Created by decho on 6/2/2024 in #help

Creating a jsonAgg helper funtcion that returns a context aware `AggregateFunctionBuilder`

Greetings. I am wondering if it's possible to create a helper that does exactly what eb.fn.jsonAgg does but for JSONB. - I have tried using the helpers/postgres/jsonArrayFrom, but that is functionally not the same because it creates a subquery. - I have tried creating a simple helper, but it's not type/context aware, and it doesn't support method chaining (.distinct() for example):

function jsonbAgg<O>(
    expr: Expression<O>,
): RawBuilder<Simplify<O>[]> {
    return sql`jsonb_agg(${expr})`;
}

function jsonbAgg<O>(
    expr: Expression<O>,
): RawBuilder<Simplify<O>[]> {
    return sql`jsonb_agg(${expr})`;
}

- Lastly, I have tried recreating the jsonAgg function from the source code and that almost works, however it's not aware of the query context, and you can for example aggregate on a completely unrelated table, since it uses the entire DB type as first generic parameter (<DB extends MyDB, TB extends keyof DB....> Example of the problem demonstrated: https://kyse.link/7zHaq --- I have since refactored my code, but this might not always be an easy option in the future, so I was curious to know if there is a way around this problem.

2 replies

KKysely

•Created by decho on 7/27/2023 in #help

Modifying the return type of a query

I have a question, hopefully it's not a silly one. So I have generated types for my DB (via kanel-kysely), one of which is for a materialized view, and as such all columns of that view are generated as nullable. (I think that's just some Postgres weirdness, not related to kysely or kanel). I also have a function that runs a query and selects all from that view, and I am wondering if there is any way to modify that in kysely land so that the returned type has all of these columns as non-nullable. Here is a super basic example: https://kyse.link/?p=s&i=EBBWymS9HA2FBdR6X73o Basically I'm asking if there is a way to change the query so that all nulls from the column types are excluded.

6 replies

KKysely

•Created by decho on 7/11/2023 in #help

Ways to work with materialized views

Hey guys, I just wanted to ask before I embark on a journey on solving this problem on my own, if someone has found a good way to work with and implement materialized views with Kysely? I am currently using kysely-codegen but unfortunately it does not generate types for materialized views. kysely.introspect also does not return any data for materialized views either. My ultimate goal is to put a complex query in a materialized view (my leaderboard https://discord.com/channels/890118421587578920/1125059913551458406) and be able to do 3 things: 1. query the materialized view itself with kysely 2. use kysely to query the view itself SELECT * FROM my_materialized_view WHERE column = something 3. have types for it all 3 issues are solved if you have a tool that generates types for the materialized view (I think). --- So far I am thinking of two approaches. First one to be to just write my own types generator by querying pg_catalog and stuff, similar to kysely-codgen. Gonna take a while. Second solution would be to write a simple query in kysely which produces the result of the materialized view, wrap that in a function (which never gets called), and then write another function that returns raw query with type assertions.

import { sql } from 'kysely';

async function materializedQuery() {
    // this func never gets called, just using it to create a type
    return await db.selectFrom('countries').selectAll().execute();
}

async function materializedView() {
    return await sql`select * from my_materialized_view`.execute(db) as unknown as ReturnType<typeof materializedQuery>;
}

/** 
    function materializedView(): Promise<{
        id: number;
        name: string;
    }[]>
 */

import { sql } from 'kysely';

async function materializedQuery() {
    // this func never gets called, just using it to create a type
    return await db.selectFrom('countries').selectAll().execute();
}

async function materializedView() {
    return await sql`select * from my_materialized_view`.execute(db) as unknown as ReturnType<typeof materializedQuery>;
}

/** 
    function materializedView(): Promise<{
        id: number;
        name: string;
    }[]>
 */

This is obviously very hacky, and won't even solve problem #2. So I was just wondering if anyone faced this problem before and has any tips. Cheers!

31 replies

KKysely

•Created by decho on 7/6/2023 in #help

Unable to compile project with the TypeScript compiler (tsc) v.5.1.6

92 replies

KKysely

•Created by decho on 7/2/2023 in #help

Can't get filterWhere to work with joins from another table.

Hey everyone. I am trying to recreate the following SQL (simplified demo):

SELECT
  "users"."id",
  COUNT(*) FILTER (
    WHERE
      "predictions"."userId" = "users.id"
  ) AS "apperances"
FROM
  "users"
  INNER JOIN "predictions" ON "predictions"."userId" = "users"."id"
  INNER JOIN "matches" ON "matchId" = "predictions"."matchId"

SELECT
  "users"."id",
  COUNT(*) FILTER (
    WHERE
      "predictions"."userId" = "users.id"
  ) AS "apperances"
FROM
  "users"
  INNER JOIN "predictions" ON "predictions"."userId" = "users"."id"
  INNER JOIN "matches" ON "matchId" = "predictions"."matchId"

await kysely
  .selectFrom('users')
  .innerJoin('predictions', 'predictions.userId', 'users.id')
  .innerJoin('matches', 'matchId', 'predictions.matchId')
  .select('users.id')
  .select((eb) => [
    eb.fn.countAll().filterWhere('predictions.userId', '=', 'users.id').as('apperances')
  ])
  .execute();

await kysely
  .selectFrom('users')
  .innerJoin('predictions', 'predictions.userId', 'users.id')
  .innerJoin('matches', 'matchId', 'predictions.matchId')
  .select('users.id')
  .select((eb) => [
    eb.fn.countAll().filterWhere('predictions.userId', '=', 'users.id').as('apperances')
  ])
  .execute();

the problem occurs in filterWhere's last parameter and I am getting a type error. Does anyone know how to fix this or if there is a workaround? I am not 100% sure why this error appears, as far as I'm concerned the SQL I am trying to generate is completely valid. Here is a full kyse.link demo: https://kyse.link/?p=s&i=GQxCID0h0CK3Jq2Ki7FB

5 replies

KKysely

•Created by decho on 6/30/2023 in #query-showcase

CTE with exists() and case()

Hey guys, just wrote this one and I thought I'd share.

WITH
  "ongoing" AS (
    SELECT
      EXISTS (
        SELECT
        FROM
          "matches"
        WHERE
          "kickOff" BETWEEN NOW() - INTERVAL '2 hours' AND NOW()
      ) AS "is_ongoing"
    FROM
      "matches"
    LIMIT
      $1
  )
SELECT
  "matches".*
FROM
  "matches",
  "ongoing"
WHERE
  "kickOff" > NOW()
  AND CASE
    WHEN "ongoing"."is_ongoing" = TRUE THEN FALSE
    ELSE TRUE
  END
ORDER BY
  "kickOff" ASC
LIMIT
  $2

WITH
  "ongoing" AS (
    SELECT
      EXISTS (
        SELECT
        FROM
          "matches"
        WHERE
          "kickOff" BETWEEN NOW() - INTERVAL '2 hours' AND NOW()
      ) AS "is_ongoing"
    FROM
      "matches"
    LIMIT
      $1
  )
SELECT
  "matches".*
FROM
  "matches",
  "ongoing"
WHERE
  "kickOff" > NOW()
  AND CASE
    WHEN "ongoing"."is_ongoing" = TRUE THEN FALSE
    ELSE TRUE
  END
ORDER BY
  "kickOff" ASC
LIMIT
  $2

https://kyse.link/?p=s&i=weqpZStlVZ7TONs1D3UR 1. Create a CTE that checks if there is an ongoing match and returns a boolean value. 2. Find the closest match in a future date. 3. Only return it if there is no ongoing match. EDIT: Updated version: https://kyse.link/?p=s&i=r8bpSEIqQVS92bvZxqsn

5 replies

KKysely

•Created by decho on 4/18/2023 in #help

Shorthand way to pass the entire update object inside of doUpdateSet when updating multiple values.

Greetings! Thanks for developing Kysely, some of the stuff that it does actually blows my mind. I only started using it a few days ago, and I love it so far. Anyway, I have a quick and simple question. I am trying to update (upsert ) multiple values into a table like this:

const countries = [
    { id: 1, name: 'UK', continent: 'Europe' },
    { id: 2, name: 'France', continent: 'Europe' }
];

await db.insertInto('countries')
    .values(countries)
    .onConflict(oc => oc
        .column('id')
        .doUpdateSet(eb => ({
            name: eb.ref('excluded.name'),
            continent: eb.ref('excluded.continent')
        }))
    )
    .execute();

const countries = [
    { id: 1, name: 'UK', continent: 'Europe' },
    { id: 2, name: 'France', continent: 'Europe' }
];

await db.insertInto('countries')
    .values(countries)
    .onConflict(oc => oc
        .column('id')
        .doUpdateSet(eb => ({
            name: eb.ref('excluded.name'),
            continent: eb.ref('excluded.continent')
        }))
    )
    .execute();

This all works as expected, however, imagine that my countries had tons of keys besides id, name and continent. It would become really tedious and error prone if I had to write down my_column: eb.ref('excluded.my_coulmn') inside of the doUpdateSet method. This is not an issue if you are upserting a single object at a time (like a single country instead of an entire array), because then you can just reference that object inside the doUpdateSet, but I am working with an array of objects (countries). So my question is if there is a workaround/solution for this problem. Cheers!

62 replies

Gaming

Programming