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 additiveHow 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:...Zero number omitted from parameters
I looks like the compile() works incorrectly with zero number when used as parameter - the parameter is omitted from the array of sql components.
Not included in sql params:
```
// used in case/when...
Is there any way to postgres notify and listen with kysley?
I want to use
pg.on but I would create a new instance connection just for that. It would be much better if I can use kysley for pg_notify listener? Could I build a plugin with the current plugin systems? is it possible? or I can already use raw sql query?bool_or
Hi, How to use bool_or ? I am using it in case-when-then construction.
https://www.postgresql.org/docs/8.4/functions-aggregate.html...
How to retrieve field name raising error in database?
I am using kysely, zod, expressjs. I need to return field error and display it to the end user in frontend form. However I am not sure how to do it with kysely. Code for backend is pretty simple:
```
const FormData = z.object({
name: z.string({ required_error: "name is required" }),
});...
Using sql.lit
Hey
I have a question about using sql.lit and using dates
````
const query = kyselyBase...
Any thoughts on how to move data migrations from knex to kysely?
I've been working on shifting my code over to kysely, and it's been fantastic so far. However, I'm not entirely sure how to restructure my migrations directory into Kysely while maintaining up/down state without some manual overhead. The documentation has great advice for setting up migrations from scratch, but not as much for moving an existing project to its structure. I plan to collect all existing table data into a new initial migration, so that makes this somewhat easier. But there are still challenges. The way I see it I have 2 options:
1. Put a hacky check in the initial migration to apply its change only if one of my tables is not present.
2. Manually create the kysely migration record in existing databases to note that the initial migration has been run....
Using a custom HTTP driver for Kysely
Hi. I want to use neondb as my database. The issue is that I can't create a PostgresDialect class instance from the default provided pool, as that uses websockets - which is not what I want. Is there a way to tell Kysely to use HTTP connections (such as with
kysely-planetscale), or do I need to make the driver myself?Solution:
Hey 👋
Have you tried the kysely-neon community dialect?...
How to order by with embeddings using pgvector?
I'm trying to get this to work:
```js
const raw = db
.selectFrom("embeddingChunks")...
Solution:
You can't have parameters inside strings. https://kyse.link/?p=s&i=mSBxXrsodd1aLwlEKulE