How to narrow type in select
Is it possible to narrow type from enum
company, person to just person in select query?
```
const results = await db
.selectFrom('receiver')
.where('type', '=', 'person')...Solution:
See
$narrowTypeSubquery from a function
I want to reuse a sub-query that refers to one of the fields in the root query's result.
I tried doing this: ```ts
function queryIsFriendOf<DB extends DatabaseSchema, TB extends keyof DB>(
eb: ExpressionBuilder<DB, TB>,...
Solution:
Don't pass in the expression builder. Its type easily becomes incompatible in other contexts.
Don't use an explicit result type. The result type here is NOT
boolean. It's { is_friend: boolean }. Yes, you can use that as a scalar in SQL and Kysely does handle that correctly. But don't explicitly set the wrong type.
You always need to provide a name for selections using the as method. The name is dialect-specific if you leave it out. Since kysely types don't know which dialect you're using, providing a name for that column automatically is impossible....why is WhereNode.where any operation node?
Hi. I'm working on a different query adapter (non-SQL), so I just want to ask why a WhereNode.where is simply any OperationNode and not a union of some operators (say boolean)
Solution:
It can be so many things that listing them as a union makes no sense.
are nested joins supported?
Like this one: ...
knex .select('*') .from('users') .join('accounts', function () { this.on(function () { this.on('accounts.id', '=', 'users.account_id'); this.orOn('accounts.owner_id', '=', 'users.id'); }); });
knex .select('*') .from('users') .join('accounts', function () { this.on(function () { this.on('accounts.id', '=', 'users.account_id'); this.orOn('accounts.owner_id', '=', 'users.id'); }); });
New dialect for snowflake - quotes and case sensitivity
Snowflake has different behavior compared to Postgres and other dialects currently in Kysely, I want to be able to control the casing of all identifiers / tables / schemas
meaning that if I have
query.selectFrom("table").select(["id"])
I want it to compile to the exact case as it is specified, and also to be able to control if with a configuration to the query compiler (this I imagine is just a simple amendment to the constructor)
...
Help with implementation of new dialect for snowflake
Hello,
I am trying to create a new dialect for snowflake, I am focusing on the query compiling part.
As I have read I saw that the snowflake dialect is supports standard SQL, however I don't know what does that mean when it comes to the
DefaultQueryCompiler
There are several things that snowflake does not support, such as indices....is there any helper to put all columns in json_build_object?
to avoid manuaล typing all the columns?
Solution:
Nope.
Advice for debugging timeout/connection issues with Kysely
Hi everyone,
We've been using Kysely with pg in prod in our startup for over a year now and everything has been mostly stable, however, we started encountering these random errors with no seemingly obvious reason.
I am 99.9% sure that this is not a Kysely specific issue, but I'd be grateful if someone could advice us on how to debug these issues, as we don't have much experience in this. Googling revealed some stack overflow threads were timeout configs were discussed, but that didn't help us too much....
Is it possible to change type of returned value with jsonBuildObject?
I struggle typing reponse schema in fastify cuz query returns
'wartosc' as string but actually it returns number. Can't simply change zod reponse schema, cuz kysely types wartosc in query is as string, and there is type mismatch anyway. (sorry for polish, ubiquitous language with polish domain masters)
```
jsonBuildObject({
id: eb.ref('domyslnaStawkaVatSprzedazy.id'),
nazwa: eb.ref('domyslnaStawkaVatSprzedazy.nazwa'),...Is it possible to configure to which types database columns are introspected?
We use kysely-codegen and we would like to use those generated types, but they ofter are translated to diffrent types we want. Lets say
int id is introspected to Generated<number> (we would like number type) or decimals are introspected to Numeric (we would like just number)Solution:
kysely-codegen is not developed by us. It's an independent project.
How to infer an aggregated column with `filterWhere` clause as nullable?
Greetings.
I am joining from another table using
leftJoin, then grouping rows by id, and finally using jsonAgg(jsonBuildObject(...)) with a filterWhere method chained on top of this expression as seen in the demo.
The problem is that the inferred type by kysely is incorrect, because it thinks the json aggregate function will always be an array, however, if there are no matching rows from the other table it will be NULL because of the filter where clause....Solution:
Hey ๐
This is a bug.
I'm not sure there's an easy/performant way of solving this....
Accessing underlying methods on the client
Hi. I'm using libsql and I want to batch statements to be sent to the database. The underlying clients have these methods implemented, so I was wondering if it were possible to access these underlying methods from the
Kysely class itself, or if it is necessary for me to write my own extension of the class to achieve thisHow to do `LIKE ANY` query in Kysely?
Hello, I am trying to build this query in Kysely but have no idea how to properly insert the
ANY into the query.
```sql
SELECT *
FROM your_table
WHERE your_column LIKE ANY (ARRAY[...Creating snippets (best pratice)
How can I create query snippet once and add it to multiple queries?
```ts
const querySnippet = kysely; // create once and add to both count and rows query instead of repeating the same code twice?
...
Solution:
But if you want a reusable helper, you need to let go of some type-safety. It's very difficult and often impossible to create a generic and strict helper. You can do something like this
```ts
db.selectFrom('whatever').select('foobar').where(myLovelyFilter(searchPostalCode))
...
How to order by desc using expression builder with case
I am ordering by using case but cannot find a way how to add
desc to the eb.
```ts
const person = await db
.selectFrom("person")...Solution:
Hey ๐
Try this:
```ts...
Is it possible to do "for await (const iterator of db.selectFrom("table").selectAll()) {....}
Can I execute as an async generator like in a cursor ?
Solution:
Hey ๐
Have you tried https://kysely-org.github.io/kysely-apidoc/interfaces/SelectQueryBuilder.html#stream ?...
Question about ColumnTypes and dates.
I have a simple yet confusing problem. In postgres I have a table with a column of type
DATE (yyyy-mm-dd).
```sql
CREATE TABLE IF NOT EXISTS person(
id INT NOT NULL,...Solution:
And yes, the "select type" is currently used as the return type AND the query type. We could add a fourth "query" type to
ColumnType which would be used in where statements (and other similar statements).
But then subqueries in where statements would break if the select type and the query type don't match....How to type tables with dynamic names?
The MS SQL dialect only supports global temporary objects, that forces us to add random table suffix to prevent collisions between sessions.
Is it possible to type the table contents as a second query?
Conceptually my code runs like this:...
Solution:
Hey ๐
A workaround would be to use the same table name in
.withTables and in the queries, and implement a plugin that on-the-fly adds the random suffix to all references to that table....Any limits on insertValues(array)?
I am seeding data in migrations and received error when trying to insert over 35.000 rows at once.
```typescript
const rows = [ // there are 35.000 objects here]
await db...
Solution:
Just insert in chunks