bombillazo Posts - Answer Overflow

Topics

bombillazo

Explore posts from servers

DDeno RRefine KKysely HHono DTDrizzle Team

•Created by bombillazo on 9/26/2024 in #help

Kysely client usage and pooling question

Hello, We have a long running server that at the moment creates a single Kysely instance. It is setup to use pg pooling. We have a util function called getKysely which returns this instance and is used throught our backend , like on every request handler that calls the DB. So in theory multiple concurrent requests call this function to get the Kysely client and make queries. We are noticing that even within the same API endpoint handler, executing queries with a fetched client seems to be running on different connections. We think this because we sometimes execute raw sql queries which change the role of the connection, but later on using the same client we execute a query to check the current role and it is not the one set up previously. I am curious about how Kysely internally uses pooling, does it fetch a connection from the pool on every query? I know we are using a singleton, but how can I make sure that Kysely uses a single connection throughout the scope of a single request?

7 replies

•Created by bombillazo on 6/20/2024 in #help

How to do `LIKE ANY` query in Kysely?

Hello, I am trying to build this query in Kysely but have no idea how to properly insert the ANY into the query.

SELECT *
FROM your_table
WHERE your_column LIKE ANY (ARRAY[
    '%suffix1',
    '%suffix2',
    '%suffix3'
]);

SELECT *
FROM your_table
WHERE your_column LIKE ANY (ARRAY[
    '%suffix1',
    '%suffix2',
    '%suffix3'
]);

Any constructs with kysely that I should use in particular?

3 replies

•Created by bombillazo on 5/17/2024 in #help

Dynamic conditional raw query question

Hello there, Im trying to create a dynamic query like this:

 await sql`SELECT
  column AS "data"
FROM my_table 
${type ? ` WHERE type = ${type}` : ''};`.execute(client);

 await sql`SELECT
  column AS "data"
FROM my_table 
${type ? ` WHERE type = ${type}` : ''};`.execute(client);

But kysely is trying to do substitution where I have my TS logic to either add the WHERE clause or not. How can I fix this ?

6 replies

•Created by bombillazo on 4/11/2024 in #help

jsonArrayFrom with `as` not being typed

I have the following query

await db
    .selectFrom('user')
    .selectAll()
    .select((eb) => [
      jsonArrayFrom(
        eb
          .selectFrom('book')
          .selectAll()
          .where('book.is_enabled', '=', true)
      ).as('books'),
    ])
    .execute();

await db
    .selectFrom('user')
    .selectAll()
    .select((eb) => [
      jsonArrayFrom(
        eb
          .selectFrom('book')
          .selectAll()
          .where('book.is_enabled', '=', true)
      ).as('books'),
    ])
    .execute();

but the resulting query is not seeing the jsonArrayFrom field with the name books , rather it types it like this:

{
  [x: string]: {
      created_at: TimestampTZ;
      description: string | null;
      id: string;
      label: string | null;
      name: string;
      is_enabled: boolean;
  }[];
  ... 5 more ...;
  name: string;
}[]

{
  [x: string]: {
      created_at: TimestampTZ;
      description: string | null;
      id: string;
      label: string | null;
      name: string;
      is_enabled: boolean;
  }[];
  ... 5 more ...;
  name: string;
}[]

How can i fix this?

25 replies

•Created by bombillazo on 4/2/2024 in #help

Kysely setup in monolith API

Hello, we have a basic http server with a basic router and a PostgreSQL database. We're wondering what is the proper setup to use the Kysely client in our routes to make calls to the database. Should there be a singleton client that is used across routes, or do we need to instantiate a kysely client on every request ?

10 replies

•Created by bombillazo on 2/9/2024 in #help

How do I use `WHERE NOT EXISTS`?

I am trying to write the followng where condition:

INSERT INTO Filing (location_id, filing_date, other_columns)
SELECT L.location_id, '2023-01-01'::date, default_values_for_other_columns
FROM Location L
WHERE NOT EXISTS (
  SELECT 1
  FROM Filing F
  WHERE F.location_id = L.location_id
    AND F.filing_date = '2023-01-01'::date
);

INSERT INTO Filing (location_id, filing_date, other_columns)
SELECT L.location_id, '2023-01-01'::date, default_values_for_other_columns
FROM Location L
WHERE NOT EXISTS (
  SELECT 1
  FROM Filing F
  WHERE F.location_id = L.location_id
    AND F.filing_date = '2023-01-01'::date
);

But Im not sure how to approach it.

5 replies

•Created by bombillazo on 1/25/2024 in #help

Examples inner join using OR?

Hello, we are having trouble trying to have a inner join using OR, as in ON a.id = b.id OR a.sub_id = b.sub_id

5 replies

•Created by bombillazo on 1/18/2024 in #help

Omit/filter out columns from query

Hey, is there an easy or convenient way with Kysely to filter out data from a query? Lets say I have columns 1,2,3,4,..., 10 and I want to omit column 9, do I have to really list all my columns like this:

select(['1','2','3','4','5','6','7','8','10']

select(['1','2','3','4','5','6','7','8','10']

?

11 replies

•Created by bombillazo on 1/17/2024 in #help

How to create a typed array of columns for select?

Hello, I am trying to create some helpers that contain the list of columns used by multiple queries, and I want to have them typed to the table they belong to to have it suggest errors if a table changes or someone uses a non-valid column. How is this achievable with the kysely helper types? The array may also include expression builder or the jsonObjectFrom helpers since they are valid Select options

3 replies

•Created by bombillazo on 1/12/2024 in #help

Return `null` by default if no record found

Hey, I am looking to have my queries return null instead of undefined if no records match my query when using executeTakeFirst. Is there a way to set this in the Kysely client?

7 replies

•Created by bombillazo on 12/4/2023 in #help

Ad hoc custom column type

Hello, we have a table that on the database level, the column is a string type, but on the typescript level, the type is a Type literal union. Is there any way we can ad hoc type a column to a type we know during select queries and insert statements?

9 replies

•Created by bombillazo on 11/29/2023 in #help

Any way to docutype a Column as `@deprecated`?

Hello, We'd like to have columns we specify in out types with the @deprecated type identifier in the Table type definition comments to be used in queries, is there a way to make it work with Kysely?

4 replies

•Created by bombillazo on 11/22/2023 in #help

Reuse subquery selects

Hello, I am trying to correctly type a select subquery to use in different other queries:

export const myUnion =
  () => (eb: ExpressionBuilder<KyselyDB, keyof KyselyDB>) => {
    return eb
      .selectFrom('profile')
      .innerJoin('account', 'account.id', 'profile.account_id')
      .select((eb) => [
        eb.fn.coalesce('account.name', sql.lit('')).as('name'),
        eb
          .ref('profile.ids', '->>')
          .key('value')
          .as('code'),
        'user_id',
      ]);
     .union((eb) =>
       eb.parens(
         eb.selectFrom('invitees').select(['name', 'code', 'user_id']),
       ),
     );
  };

  const x = await kysely
    .selectFrom(myUnion())
    .select(['name', 'code'])
    .execute()

export const myUnion =
  () => (eb: ExpressionBuilder<KyselyDB, keyof KyselyDB>) => {
    return eb
      .selectFrom('profile')
      .innerJoin('account', 'account.id', 'profile.account_id')
      .select((eb) => [
        eb.fn.coalesce('account.name', sql.lit('')).as('name'),
        eb
          .ref('profile.ids', '->>')
          .key('value')
          .as('code'),
        'user_id',
      ]);
     .union((eb) =>
       eb.parens(
         eb.selectFrom('invitees').select(['name', 'code', 'user_id']),
       ),
     );
  };

  const x = await kysely
    .selectFrom(myUnion())
    .select(['name', 'code'])
    .execute()

However when I use the expression in another query I get a typing error

Type 'SelectQueryBuilder<KyselyDB, keyof KyselyDB, { government_id: string | null; name: string; code: string | null; }>' is missing the following properties from type 'AliasedExpression<any, any>': expression, alias

Type 'SelectQueryBuilder<KyselyDB, keyof KyselyDB, { government_id: string | null; name: string; code: string | null; }>' is missing the following properties from type 'AliasedExpression<any, any>': expression, alias

3 replies

•Created by bombillazo on 11/22/2023 in #help

Use JSON key as text

Hello, I am using the following syntax to fetch data in a JSONB column:

.select((eb) => [
  'id',
  eb
    .ref('jsonb_col', '->')
    .key('lvl_1')
    .key('value')
    .as('my_val'),
...

.select((eb) => [
  'id',
  eb
    .ref('jsonb_col', '->')
    .key('lvl_1')
    .key('value')
    .as('my_val'),
...

This returns my_val as a json value which is equivalent to using ->, how can I retrieve the data using the Postgres ->> operation that returns the data as TEXT?

4 replies

•Created by bombillazo on 11/13/2023 in #help

Query building optimization question

Hello, Id like some input from Kysely advanced users about my approach to using the eb. If I have a conditional select query, is this efficiently using the eb object for my query? Is it ok to use multiple levels of ebs ?

// some input
args = {
  param: ['foo', 'bar']
};

await kysely
    .selectFrom('my_table')
    .innerJoin(
      'other',
      'other.id',
      'my_table.other_id',
    )
    .selectAll()
    .$if(!!args.param && args.param.length > 0, (eb) => {
          return eb.where((eb) => {
            if (!args.param) throw Error('param is required');
            return eb(
              eb.ref('data', '->').key('param'),
              'in',
              args.param,
            );
          });
        })

// some input
args = {
  param: ['foo', 'bar']
};

await kysely
    .selectFrom('my_table')
    .innerJoin(
      'other',
      'other.id',
      'my_table.other_id',
    )
    .selectAll()
    .$if(!!args.param && args.param.length > 0, (eb) => {
          return eb.where((eb) => {
            if (!args.param) throw Error('param is required');
            return eb(
              eb.ref('data', '->').key('param'),
              'in',
              args.param,
            );
          });
        })

6 replies

•Created by bombillazo on 11/1/2023 in #help

Relational table join question

Hey, just out of curiosity, does Kysely have any helpers for joining tables across a relational table? For example, if I have tables user and account joined via the user_account relational tables, if I use jsonArrayFrom from a query on the user table to get accounts, do I always need to do a join with the intermediary table or does Kysely provide any type of shortcut for this?

6 replies

•Created by bombillazo on 11/1/2023 in #help

Raw SQL in select type to number

Hellow, I have the following sql expression in a select statement array:

 sql<number>`(${sql.raw(precisionScore)} * 100)::NUMERIC(10,2)`

 sql<number>`(${sql.raw(precisionScore)} * 100)::NUMERIC(10,2)`

but the return type of the column in the row is a string. How can I get the type to be a number?

18 replies

•Created by bombillazo on 9/7/2023 in #help

Deno usage

Hey, has anyone by any chance trie using Kysely with deno using the postgresjs library? https://github.com/porsager/postgres

4 replies

•Created by bombillazo on 9/6/2023 in #help

Returning numeric fields as float

Hello, we're migrating from Supabase to Kysely and we have some queries failing in our client because it seems Kysely is returning columns marked as numeric in string format. How can one setup Kysely to parse those numeric columns to float ?

7 replies

•Created by bombillazo on 8/31/2023 in #help

CASE example in docs?

Hello, where can one find an example of how to properly use CASE for query building? We want to use it inside a Where clause at the moment.

22 replies