Kysely

K

Kysely

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

Join

help

query-showcase

announcements

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?...
Solution:
Just give that type for the column in the table interface.

Creating a table within a schema using a column from a table in another schema

I'm using two schemas and I want to create a table in one of them with a column referencing a column that comes from another schema, how would I do this in migrations i'm using Postgresql ```` await db.schema...

Guidance to create a generic wrapper over Kysely

I am trying to create a generic authentication wrapper over Kysely. The idea is - You can pass some config to this function, which includes a reference to Kysely instance, a database table name and an array of column names. - The function will return an authenticator instance you can use to perform user lookups during Login. The internals of this function are not important for this discussion. ...

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?
Solution:
Not in any way that'd highlight or otherwise indicate the deprecated columns in queries. There's no typescript or IDE feature that'd allow string literals to be marked as deprecated.

Help with CTE query

Can anyone help me transform this SQL to Kysely? https://kyse.link/?p=s&i=Q7JBZIP6xMpYoEsHJSHi...
Solution:

Error this query cannot be compiled to SQL

I am trying to do `` await trx.executeQuery( sql<any>ALTER SCHEMA ${current.subdomain} RENAME TO ${subdomain};`...
Solution:
I the end I went with ``ts await sql .raw(ALTER SCHEMA "${current.subdomain}" RENAME TO "${subdomain}"`) .execute(trx)...

Object literal may only specify known properties, and 'clientId' does not exist in type 'InsertObjec

I'm getting this issue with an insert statement using Kysely. But I'm not sure what I'm doing wrong. Here's the Report interface generated via Kysely Codegen: ```js...
Solution:
ah, seems like LID/RID was a string but should be a number. Hard to debug these error messages.
No description

Reuse subquery selects

Hello, I am trying to correctly type a select subquery to use in different other queries: ```ts export const myUnion = () => (eb: ExpressionBuilder<KyselyDB, keyof KyselyDB>) => { return eb...
Solution:
You need to give an alias for the table. myUnion().as('u')

Use JSON key as text

Hello, I am using the following syntax to fetch data in a JSONB column: ```ts .select((eb) => [ 'id', eb...
Solution:
aah, i had to change the first -> to ->>, doh!

Typing issue when working with onConflict

Hi, so i was trying to use some of examples https://kysely-org.github.io/kysely-apidoc/classes/InsertQueryBuilder.html#onConflict I want to update column of json type in case of conflict ```typescript .onConflict((co) => co...
Solution:
The issue is a mismatch between the insert and select types. That's essentially trying to assing Json to string. This is something Kysely should handle, but it's really difficult in that case. What I'd do is this ```ts export type Json = ColumnType< JsonValue,...

getting Error: relation "myschema.kysely_migration_lock" does not exist

Hey, I am trying to use the migrator.migrateToLatest() to migrate a DB for me and it is failing with Error: relation "myschema.kysely_migration_lock" does not exist. Do I have to create the kysely tables manually to be able to run the migrator? I would have expected that the migrator creates these tables when they do not exist....

What is easiest way to get count alongside data for pagination?

I am running second query with raw sql SELECT COUNT(id) from table_name. is this legit?
Solution:
Here's one reusable way to get that done https://kyse.link/?p=s&i=5f2hXebbbH4P0VlNuShk

Reusable CTEs that depend on previous CTEs

I have 2 CTEs that I'd like to separate out into separate functions for readability. These are the CTEs: ``` .with("eligibleChats", (db) =>...
Solution:
If you meant eligibleChats instead of chatMessages, then you can do it like this
QueryCreator<DB & { eligibleChats: { chatId: string } }>
QueryCreator<DB & { eligibleChats: { chatId: string } }>
...

`update set from`

Accroding to @koskimas, kysely supports the update set from sql syntax. So far, I've been unable to figure out how (in lieu of dropping down to raw sql). We are planning to craft many of our update statements as batch operations in our repository layer, so this pattern will be used heavily. Here's some example sql to make it clear (hopefully) what I'm trying to do...
Solution:
And here's that helper for you https://kyse.link/?p=s&i=C0yoagEodj9vv4AxE3TH

Are JSON Arrays not supported for paramterization in the postgres driver?

Hey! I've been loving kysely so far and doing a ton of awesome code cleanup and refactoring with it. But I just hit a big snag with a seeming inability to use my json array columns for updates. It seems that the parameter parsing doesn't work with JSON. The types seem fine: https://kyse.link/?p=s&i=pc0iBgUrhjXxfAlSEh9i...
Solution:
Yeah, the pg driver doesn't serialize arrays. I think the reasoning is that it doesn't know, when serializing, if the target column will be a postgres array or json. If you only make the update and insert types string, everything works correctly. The row type is inferred correctly for output data. https://kyse.link/?p=s&i=NBfnbfzwNBlZ0YJpc7dj...

Combining selectAll and arbitrary expressions

Hey! Is it possible to combine selectAll (for selecting all columns of a couple of tables) and then add a couple of expressions like jsonArrayFrom on top of that?
Solution:
You can call select and selectAll as many times as you want. The calls are additive

How to plugin column aliases with table prefix?

I am not familiar with kysely plugin creation. I am also not sure if my issue can be done with such a plugin. My aim is to have overload method like this:
.selectAll('t1', 'prefix_t1')
.selectAll('t1', 'prefix_t1')
...

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 ? ```ts // some input args = { param: ['foo', 'bar']...
Solution:
Creating an expression builder instance is in the order of microseconds. Running the query is in the order of milliseconds or hundreds of milliseconds. You don't need to worry about expression builder instances unless you create a thousand of them per query.

Why is eb inferred as any in this update query?

I'm trying to do an update from values inserted in a CTE. But the update's set operation doesn't seem to properly infer a type for the eb variable in its expression builder. If I change the where clause to an eb callback, it does properly infer the type, which makes me concerned I'm not doing this correctly. Am I doing something wrong? Is this a minor typing bug? Here's the code example:...

Can you mix kysely with pg transactions?

For example: ``` try { await client.query('BEGIN') ...