SneakOnYou Posts - Answer Overflow

SneakOnYou

•Created by SneakOnYou on 7/16/2024 in #help

New dialect for snowflake - quotes and case sensitivity

Snowflake has different behavior compared to Postgres and other dialects currently in Kysely, I want to be able to control the casing of all identifiers / tables / schemas meaning that if I have query.selectFrom("table").select(["id"]) I want it to compile to the exact case as it is specified, and also to be able to control if with a configuration to the query compiler (this I imagine is just a simple amendment to the constructor) which functions should be overridden in the query compiler ? Thank you in advance:typescript: :kysely:

9 replies

KKysely

•Created by SneakOnYou on 7/14/2024 in #help

Help with implementation of new dialect for snowflake

Hello, I am trying to create a new dialect for snowflake, I am focusing on the query compiling part. As I have read I saw that the snowflake dialect is supports standard SQL, however I don't know what does that mean when it comes to the DefaultQueryCompiler There are several things that snowflake does not support, such as indices. Do I need to throw when there is a visitDropIndex or visitDropIndex ? Are there any other things I should look out for ? What happens if I want to use function that are in snowflake, how do I acheive it ?

4 replies

KKysely

•Created by SneakOnYou on 1/24/2024 in #help

full join "using" list of columns

Hey, How can I create a full join which uses "using" I want to use "using" in the join but I only see "on", maybe I can acheive this using sqlusing , what is the correct way ? example:

with result1(src__id,period,amount_a) as (values ('id1','Q1',2) ),
     result2(src__id,period,amount_b) as (values ('id1','Q1',9),
                                                 ('id2','Q2',1) ),
     result3(src__id,period,amount_c) as (values ('id2','Q2',4))
select src__id,
       period,
       sum(amount_a) amount_a,
       sum(amount_b) amount_b,
       sum(amount_c) amount_c,
       sum(coalesce(amount_a,0)+coalesce(amount_b,0)-coalesce(amount_c,0)) as "A+B-C" 
from result1 
full outer join result2 using (src__id,period)
full outer join result3 using (src__id,period)
group by src__id,period;

with result1(src__id,period,amount_a) as (values ('id1','Q1',2) ),
     result2(src__id,period,amount_b) as (values ('id1','Q1',9),
                                                 ('id2','Q2',1) ),
     result3(src__id,period,amount_c) as (values ('id2','Q2',4))
select src__id,
       period,
       sum(amount_a) amount_a,
       sum(amount_b) amount_b,
       sum(amount_c) amount_c,
       sum(coalesce(amount_a,0)+coalesce(amount_b,0)-coalesce(amount_c,0)) as "A+B-C" 
from result1 
full outer join result2 using (src__id,period)
full outer join result3 using (src__id,period)
group by src__id,period;

19 replies

KKysely

•Created by SneakOnYou on 1/23/2024 in #help

Is kysely sanitizing sql injection when using raw sql ?

Suppose I have a piece of code that does sql${myString} where myString contains sql injection, will kysely sanitize it or is it something that I need to take care of ?

5 replies

KKysely

•Created by SneakOnYou on 11/6/2023 in #help

Using sql.lit

Hey I have a question about using sql.lit and using dates

const query = kyselyBase
        .selectFrom("person")
        .select([
          sql.lit("(N/A)").as("asd"),
          sql.lit(true).as("asd2"),
          sql.lit(new Date()).as("zxc"),
          sql.lit(null).as("qwe")
        ]);

const query = kyselyBase
        .selectFrom("person")
        .select([
          sql.lit("(N/A)").as("asd"),
          sql.lit(true).as("asd2"),
          sql.lit(new Date()).as("zxc"),
          sql.lit(null).as("qwe")
        ]);

I get the date as string, is there a way to get the date as date ? is there a better way of doing it ?

8 replies

KKysely

•Created by SneakOnYou on 10/4/2023 in #help

$if with sql.raw doesn't compile

Hey, I have updated my kysely version after I deleted node_modules and npm installed and I am getting an error for the following code: const filterWhereQuery = sql.raw(LOWER("${dimensionName}") LIKE '%${filter?.toLowerCase()}%');

const query = replaceParametersInCompiledMetric(
    kyselyBaseQuery
      .selectFrom(`${schema}.${table}`)
      .distinct()
      .select(dimensionName)
      .$if(!!filter, (q) => q.where(filterWhereQuery))
      .$if(!!pageLimit, (q) => q.limit(pageLimit || 0).offset(pageOffset * (pageLimit || 0)))
      .compile()
  );

const query = replaceParametersInCompiledMetric(
    kyselyBaseQuery
      .selectFrom(`${schema}.${table}`)
      .distinct()
      .select(dimensionName)
      .$if(!!filter, (q) => q.where(filterWhereQuery))
      .$if(!!pageLimit, (q) => q.limit(pageLimit || 0).offset(pageOffset * (pageLimit || 0)))
      .compile()
  );

The error in at the 1st if where there is suddenly a type problem: Argument of type 'RawBuilder<unknown>' is not assignable to parameter of type 'ExpressionOrFactory<any, ${string}.${string}, SqlBool>'. Property 'isSelectQueryBuilder' is missing in type 'RawBuilder<unknown>' but required in type 'SelectQueryBuilderExpression<Record<string, SqlBool>>' what can I do to fix this quickly that doesn't require a huge change ? postgres

4 replies

KKysely

•Created by SneakOnYou on 7/20/2023 in #help

issue with complied sql when doing + interval operation

Hey, I have a query that is generated using kysely where I select the following field sql that I get as a string from a file: fields = [close_date + interval '12 month'] what happens is that when I do .select(fields) the close_date + interval '12 month' gets wrapped in double quotes, what is the best way to correct this postgres

26 replies

Gaming

Programming