Kysely

K

Kysely

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

Join

help

query-showcase

announcements

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

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

Migration to seed huge amount of data

Hi, are there any guides or suggestions how to effectively seed huge amount of data with migrations? We have the data in separate json (or csv) files which are copied to "assets" folder in the dist folder. How do you do this?

Creating a jsonAgg helper funtcion that returns a context aware `AggregateFunctionBuilder`

Greetings. I am wondering if it's possible to create a helper that does exactly what eb.fn.jsonAgg does but for JSONB. - I have tried using the helpers/postgres/jsonArrayFrom, but that is functionally not the same because it creates a subquery. - I have tried creating a simple helper, but it's not type/context aware, and it doesn't support method chaining (.distinct() for example): ...

How to add array column in migrations?

I am trying to add a column to a table via migrations but cannot find how to do something like this: .addColumn('countyNames', 'varchar', col => col.IsArray() )...
Solution:
.addColumn('countyNames', sql`varchar[]`)
.addColumn('countyNames', sql`varchar[]`)
...

are conditional CTEs possible?

i don't find any hints here https://kysely.dev/docs/category/cte. i have tried the conditional where approach (https://kysely.dev/docs/examples/WHERE/conditional-where-calls), but i get type error Type 'QueryCreatorWithCommonTableExpression ... The types returned by 'with(...)' are incompatible between these types....
Solution:
No, conditional CTEs are not possible. But you could construct your CTE so that it doesn't do anything based on some input.

Dynamic conditional raw query question

Hello there, Im trying to create a dynamic query like this: ``ts await sqlSELECT column AS "data" FROM my_table ...

Update on conflict clause

Hi. I need to use the on conflict clause in an update query within sqlite. I've searched the docs in mysql and it isn't present there. I created an example below for replication of the desired behavior. The only thing that I can think of is using the template sql`` operator from kysely, but is there a better way? ```sql CREATE TABLE ReportCategory ( reportId TEXT, categoryId TEXT,...

SelectAll Overwrites column with same field name in a join

Hello, very odd issue here. I have a table Course with field 'id' and User with field 'id'. When I join these two tables and SelectAll(['Course', 'User']), there's only a single id field, with the value from User. Is this a bug, or am I doing something wrong? (Detailed snippets below). ```ts const result = await db...
Solution:
Kysely is not an ORM. It doesn't magically rename things. You need to provide non-colliding names.

concat in postgres

Hey! I want to concat few columns to get address, however query is built in wrong way cuz postgres uses || to concat. What am I doing wrong? ``` let warehouses = await db .selectFrom('warehouse as w')...

Type-only imports in migration files

In the Kysely documentation, an example is given of a migration file which imports Kysely from the kysely package. This import is only used to type the up and down functions, but not as a value. Depending on the TypeScript verbatimModuleSyntax compiler option, this may or may not result in a runtime import in the compiled code, which in the worst case could mean different behaviors of the migration script. My question is: Is it safe to import Kysely with a type-only import in migration files? If not, what side effects of the import are needed in such files? If the idea (as I suspect) is indeed only to import the Kysely type for typing the function arguments, I suggest also that the example should be changed to use a type-only import or accompanied by a note about the possibility of such an import....
Solution:
Hey 👋 It is safe to type-only....
Message Not Public
Sign In & Join Server To View

Typing reusable functions for filtering rows

Hi all, we're using Kysely at our company and loving it! We recently encountered some issues when trying to type reusable functions. One example of our current attempt: ``` function applyFilters<T>({...

generating raw sql?

I think I saw somewhere an option that you can build a query using kysely and convert it to raw sql, but I can't find that anywhere. Does such an option exist or did I get something mixed up?
Solution:
Hey 👋 Use the .compile() method instead of executing. For more info, check out the link @53ny posted....

Conditionally updating fields

How would i go about doing something like UPDATE table SET field = IF(condition, trueValue, falseValue) WHERE someCondition;, i can't exactly seem to find that in the docs Driver used: MySQL...
Solution:
Hey 👋 Something like: ```ts...

How to cast to date. eg : `DATE(created_at)`

How to write this query in kysely ```sql SELECT DATE(created_at) AS date, SUM(points) AS score FROM table WHERE id = '...' ...
Solution:
Hey 👋 Does this work for you? ```ts...
Next