Kysely

Join the Kysely server to ask questions!

Join

Kysely

Join the community to ask questions about Kysely and get answers from other members.

Join

help

query-showcase

announcements

kewp

7/26/2023

Unions in a loop

I'm trying to do a UnionAll in a loop. My issue is I don't know how to initialise the query: ``` let query = ...// ??? ...

Solution:

You need to initialize it with the first select query.

wod

7/22/2023

where array_field has enum value

Hello there 👋 Today I'm experimenting with kysely, coming from prisma. I got stuck on rewriting the first query facePalm

I have a ShippingMethod table with stores field, which is array of Store enum...

Solution:

That's currently ~the best way to do it unfortunately. Here's a more type-safe approach: ```ts const shippingMethods2 = await this.dbService.db .selectFrom('ShippingMethod')...

Joe

7/21/2023

NOW() function in kysely

For instance, this statement is valid on Postgres: INSERT INTO films VALUES ('UA502', 'Bananas', 105, NOW()); I was wondering if there was anyways to achieve the same thing in kysely. Thank you 😄...

Solution:

```ts import { sql } from "kysely"; db .insertInto("films")...

SneakOnYou

7/20/2023

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...

elitan

7/20/2023

camelCase plugin with a strangely named column

We have a column named cl_companyID (I'm as confused as you) and I have issues with the camelCase plugin because it thinks the name is cl_company_id. Is there a way around this, without renaming the column? I was thinking maybe we could add support with some kind of lookup table with weird names for the plugin to respect....

Solution:

Something like ```ts class MyCamelCasePlugin extends CamelCasePlugin { protected override snakeCase(str: string): string {...

bvkimball

7/19/2023

Feature/Question: Would like to execute query with Prisma.$queryRaw or $executeRaw

I use prisma-kysely to generate my kysely types and i find this to be a great workflow. I still like using prisma for most of my usecase because I have other implementations to enforce security and business logic. I find it unnecessary to include pg module if i already have a query-executor and a connection with prisma. It would be nice to do something like the following: instead of: ``` const db = new Kysely<DB>({...

Solution:

See this recipe https://kysely.dev/docs/recipes/splitting-build-compile-and-execute-code

Catalysm

7/18/2023

Kysely not calling 'init' for Driver (?)

Hi all, preface this by saying I am starting out with Kysely and I might be making a dumb mistake 😄 I am getting the following error (had to cut off the last 2 lines bc Discord msg limit) ```...

Solution:

Okay my bad, this was a really dumb mistake... I was destroying the Kysely instance after the migrations...

elitan

7/18/2023

Build a basic Kysely Plugin

Hi, I need some help with a basic plugin I want to build. It's an encrypt/decrypt plugin. This is high level on how I want it to work: - Before data is written to the database, encrypt it...

Blåhaj

7/13/2023

Deleting multiple rows at once

I have a question, if I have like ```ts await db .deleteFrom('person') .where('groupId', '=', id) .execute();...

Solution:

All

Message Not Public

manda putra

7/13/2023

Question : How do you write integration test, and avoid flakynees?

Everytime I wrote test for the backend, I always do integration test that include real database call. I dont really like creating a mock. But the downside is, having a lot of test that have dependence on many table would create a flaky test sometimes. So we apply some rule to write integration test : 1. Always clean the data after the test run 2. Don't manipulate database seeder 3. Create a data seeder for test...

Solution:

I always clear the database before each individual test. It's also important to make sure each test is 100% independent and after a test, no queries keep running. If those are taken care of, there should be no flakiness. Wrapping tests in a transaction and rolling it back after each test might be slightly faster, but the database clearing speed has never been an issue in my tests. I've had projects with over a thousand tests and the runtime hasn't been an issue (well, it has, but not due to clearing the DB). It's much easier to just nuke+populate the DB before each test....

decho

7/11/2023

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...

Solution:

ok so here is what i ended up doing. 1. create a normal view in my database so kysely-codegen can generate types. 2. create exactly the same materialized view. 3. extend the generated DB and pass that to kysely, also exclude the normal view from it since I don't need it....

Swap

7/11/2023

Postgres Full Text Search

Hello, I am trying to search queries using Postgres full-text search, and here is my current implementation:

``ts
await this.db.selectFrom("city")
            .where(sql

to_tsvector('english', name) @@ to_tsquery('english', 'nas:*')`)...

Solution:

You can probably get rid of it like this

return sql`to_tsvector(${sql.lit('english')}, ${expr})`

return sql`to_tsvector(${sql.lit('english')}, ${expr})`

...

Josué Ayala

7/10/2023

Hey, there a way to update an existing migration/table?

Hey all, I'm trying to add a new field to an existing table, but I don't know how can I do that. I can't add the new field in the existing migration 'cause when I run again I'm getting the error something like "Table exists...". So the think what I'm trying to do is something like "[timestamp]_update_users_table.ts" and add the new fields!...

Solution:

Forget it, stupid question, I have discovered

alterTable

alterTable

in the schema module 😊 you can do this

await db.schema.alterTable('users').addColumn('...')

await db.schema.alterTable('users').addColumn('...')

...

suchcodemuchwow

7/8/2023

Does kysely convert Table names lowercase when it's building a query ?

When I try to run this code it tries to insert into "profile" table not "Profile": Note: I have only extracted important parts ```typescript...

Dr. Sauce

7/7/2023

Type of query result is {}[]

Hi all! Loving using Kysely at the moment, it's super easy to use. I ran into one problem, which I believe is probably a lack of understanding from myself around certain elements of TypeScript or Kysely itself. Here is the DB Schema from kysely-codegen:...

Solution:

It looks like the reassignment of query to itself was causing the issue. If I do something like this, the end result has the correct typing: ``` let queryBuilder; ...

decho

7/6/2023

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

Hey folks, apologies in advance in case I am missing something obvious. I've noticed something very strange. the tsc compiler which I rarely use (but still need sometimes nevertheless) is unable to compile because of some OnConflictBuilder error, which is way too cryptic for me to understand. I made a bare-minimum reproducible demo here:...

Solution:

I just released 0.26.0 that has the fix in it

mike

7/4/2023

How to reference the parent query when creating a subquery itself?

What if I want to use kysely to create a subquery with a literal reference only and use it in sequelize ORM as literal? // Here I want to generate the subquery with a reference to non-existing parent const kyselySubquery = ``` select...

Solution:

https://kyse.link/?p=s&i=eonkCC4XRvE93k3Bq7QN

decho

7/2/2023

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

Hey everyone. I am trying to recreate the following SQL (simplified demo): ```sql SELECT "users"."id",...

Solution:

Well, nevermind, I replaced filterWhere with filterWhereRef and now it's working correctly.

bun

7/1/2023

is this uuid() correct in mysql?

export async function up(db: Kysely<any>): Promise<void> { await db.schema .createTable("Payment") .addColumn("id", "char", (col) => col.defaultTo(raw("UUID()")).primaryKey())...

mike

6/30/2023

aliases vs strict mode in tsconfig

I have a problem with tsconfig. When I have strict mode kysely does not find aliases. What is the "most safe strict" mode which still works with kysely?

Solution:

This is "not as intended" usage. Our internals treat keys defined in DB as a whole and when trying to figure out if an alias was used we compare against ${T} as {string}.

Previous Next

Gaming

Programming

Kysely

Join the Kysely server to ask questions!

Kysely

Join the community to ask questions about Kysely and get answers from other members.