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

Blåhaj

7/28/2023

Using `.orderBy` in JSON

Hey, I have a schema that roughly looks like this ```ts export interface Member { id: string;...

Solution:

try something like: ```ts const rows = await kysely .selectFrom("member")...

decho

7/27/2023

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

Solution:

Hey 👋 Not a silly one at all. 🙂 You could use .$narrowType to narrow the result type in a type-safe way....

Kenneth

7/27/2023

Access column type in custom plugin

Hey! We would like to access the column type in one of my custom Kysely plugins. The AST does not seem to know the column type in the transformQuery method. Is there a way to access the column types within our plugin? ...

Solution:

That info is not available. The only way to get it is to query the db and Kysely never runs queries you don't explicitly execute.

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

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.