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

Kristian Notari

3/6/2024

Static/reusable custom window functions

Hello, I'm trying to buld a properly typed array_agg window function and would like to reuse the existing FunctionModule in a reusable way, so to have orderby, over, and so on. Is there a way to get the function module you get from fn.agg outside of an expression builder and without creating a puppet kysely instance, so to create an array_agg function and return what fn.agg return but properly typed? As an example:...

Adophilus

3/6/2024

Is the Kysely main site down?

I just want to confirm if Kysely's main site is down

NestarZ

2/29/2024

How to loop an array and based on this create a dynamic CTE but preserve type ?

``ts
      for (const [references, columns] of referencesArray) {
        const newQb = qb.with(

cte_${references}`, (wb) => { wb = wb.selectFrom(references).distinct().selectAll(references); for (const column of columns) {...

Solution:

It's impossible to do this while keeping the types. Not only because of Kysely but also because of typescript. - You can't change the type of a variable in typescript. - You can't turn a string into a string literal. - Even if your strings had string literal types, you couldn't loop over them...

Kristian Notari

2/28/2024

On Conflict do update set ALL to be inserted columns

Here I am once again with a question for the on conflict bit in postgres. Would there be a way to automatically compose an update set statement for the on conflict do update bit with the information about all the columns we're going to insert, and automatically do an update with the excluded for those? Example here...

Saif

2/26/2024

How to use kysely in edge runtime (like NextJS 14 middleware)?

Error: The edge runtime does not support Node.js 'crypto' module. Learn More: https://nextjs.org/docs/messages/node-module-in-edge-runtime Full log: https://pastebin.com/JSXBNRxv ...

mike

2/23/2024

how to write not (array1 && array2)

Hi again, how to write following code? I would love to use not(expression) Expected result:...

mike

2/23/2024

Partial compile

Hello everyone, is it possible (somehow) to generate only part of the compiled query? I have an edge-case when want to use only part ("where" statement in my case) of the compiled query from kysely. Is there any short-way to do it? I definitely don't want to parse the result. Thanks Mike

oof2win2

2/18/2024

Querying on jsonArrayFrom

Hi. So i need to fetch posts with comments from a database that are written by a set group of authors and have comments that are written by a set group of people. How would I accomplish this with sql? The best thing I can think of is fetch all posts and comments where the posts are written by the authors, and then do further clientside filtering on the comments - but is there a way to do this with the sql itself?

bun

2/15/2024

Why does `.stream()` return a single result when I pass in a `chunkSize` option ?

I'm guessing it returns chunkSize mount of rows for each iteration, but shouldn't it be an array instead?

djMax

2/15/2024

Is there a way to keep full date precision?

It appears that selecting dates from Postgresql loses some precision, which wreaks havoc with my paging logic. Is there a way to get dates to have full precision on the JS side?

djMax

2/14/2024

How can I get total row count without blowing up types?

I have a simple query function that is in charge of generating a list of ids so that it can be passed into a function which does some other fancy stuff with those ids as a CTE. Like so: ``` export async function getByExternalId( db: Kysely<DB>,...

Solution:

I guess maybe this works?

.select(['some_id', (eb) => eb.fn.count('some_id').over().as('total')])

.select(['some_id', (eb) => eb.fn.count('some_id').over().as('total')])

...

Saif

2/14/2024

Is there a way to handle migrations with .sql files?

I enjoy being able to control the specific queries in the migration files. To do this, I use postgrator (https://www.npmjs.com/package/postgrator) along with a custom script that also manages which environment should run each file. Does Kysely migrations also support this feature (migrations with only .sql files)? ```sql -- context: dev,stage,prod CREATE TABLE IF NOT EXISTS postgrator_migrations ( version BIGINT NOT NULL,...

Solution:

But nothing prevents you from using a separate tool for migrations. There's no benefit from using Kysely's migrations. And to be honest, the migration module has received very little love. It's quite bare-bones.

Saif

2/14/2024

Postgres: Transaction not honouring previous queries

Hi, I have a table with unique constraint on some columns. In a transaction, I am deleting all the entries from the table and then I am inserting new entries. However, I am getting

error: duplicate key value violates unique constraint "xxxxx_key"

I only want the deletion to happen if the insertion is successful, and that's why I tried using transaction. However, the transaction seems to work in a different way than I thought. ...

Solution:

If you insert before you delete, that's what's going to happen. You insert something and only after that delete the duplicates. The constraint is checked immediately when you insert.

cheekybuddha

2/13/2024

How do I specify a MySQL index hint?

Hi, I am trying to build the following query (as a CTE, as part of a larger query), but I can not work out how I can pass the index hint (USE INDEX ...): ```sql SELECT sc.synopsis_fk, 'characters',...

tzezar

2/13/2024

question about transaction isolation level

I am not sure if the way I do queries with transaction with kysely is the right way. I would be glad if someone could take a look:

``
try {
    await sql

BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;`...

sirhype

2/13/2024

case when column = another column

I have a semi-complex query, and I'm trying to use a case() that includes a when() where the two values are columns (aka the two columns have the same value). It doesn't seem to be supported, however in theory this should work. Any idea how I can make it work with Kysely? The .when("shahz_response", "=", "rank") is where I'm attempting this, but this isn't working...

Solution:

Welp, looks like I figured it out! .when("shahz_response", "=", e.ref("rank"))...

Saif

2/12/2024

Webstorm warning with multiple joins

```ts const organizationsWithAdminUser = await db .selectFrom("organizations") .innerJoin("groups", "groups.organization_id", "organizations.id") .innerJoin("users_groups", "users_groups.group_id", "groups.id")...

bombillazo

2/9/2024

How do I use `WHERE NOT EXISTS`?

I am trying to write the followng where condition: ```sql 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...

Solution:

You can call exists inside not to use not exists, something like this ```ts db.insertInto("Filing") .columns(["location_id", "filing_date"])...

Message Not Public

oof2win2

2/7/2024

transforming database values to JS values (dates in sqlite)

hi. is it possible to transform database values with any driver into any JS value? such as integers to timestamps on specified columns? i want to do this for sqlite and other databases but idk how

lorefnon

2/6/2024

How to enable pragmas with SqliteDialect

Hello, I am using SqliteDialect and want to enable foreign_keys pragma. From SQLite Docs:

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection....

Solution:

https://github.com/subframe7536/kysely-sqlite-tools/blob/master/packages/sqlite-utils/src/pragma.ts#L21-L23

Message Not Public

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.