ilike with reference
Hey there, I'm looking to perform the following SQL operation:
but unfortunately I'm having trouble doing this in Kysely. Here is my current attempt:...
WHERE groups.group_name ILIKE '%' || users.first_name || '%'
WHERE groups.group_name ILIKE '%' || users.first_name || '%'
Solution:
Using
Seemed to do the trick, instead of
sql`'%'`,
sql`'%'`,
eb.val("%"). Any other ways to make this more ergonomic?...Issue with how timestamptz is returned depending on main or subquery
I'm running into the following issue where my date is returned in two different ways. Date is a
timestamptz type column
When I grab the lesson as followed:
```ts...Solution:
Pretty sure
pg just parses the JSON object without checking strings if they represent timestamps and it doesn't get any metadata related to the contents of the JSON. That'll be quite slow as a default, to check those strings.select expression helper
I got this selectExpression that i use in many file
.select([
'e.id',
eventStartDate('e'),...
Solution:
tuple() and refTuple() questions
Hello, i would like to generate this query
DELETE FROM table WHERE (col1,col2,col3) IN (...insert values);
i found out from my code editor intellisense that tuple() and refTuple() exist, but to my surprise, i can only insert 2 values in a single tuple.
is there a way for me to achieve that in kysely? or do i stick to raw sql for that?
...Solution:
There's no such limitation
Casting part of a return type
Hi there,
Some of my queries return lots of fields. I want to be able to cast the return value of some of these fields. For example, I have a
SELECT statement on a numeric field, but pg will parse this to a string. I want to be able to tell this in my output.
- I can't use $.castTo because this destroys the other values in my SELECT clause....Solution:
Execute RAW Sql Queries
hello, is it possible to execute any raw query supported by my underlying sql driver with
kysely? i could not find any such function in the docs or the guide book
for example, describe table_name errors because kysely itself cannot compile it. i am using mysql2 as my driver....Solution:...
await sql`RAW QUERY HERE;`.execute(db);
await sql`RAW QUERY HERE;`.execute(db);
Message Not Public
Sign In & Join Server To View
Selecting result of subtraction with summation
Hey, I'm having trouble translating the effective following Postgresql code to Kysely code:
```sql
SELECT
reagent_inventory.initial_quantity -
COALESCE(SUM(reagent_inventory_usage.quantity_consumed), 0) AS remaining_quantity...
Solution:
oh oops, I think I just meant to use
"inventory.initial_quantity", rather than wrapping it in eb.val 🤦♂️Migrator transaction (postgresql dialect)
Hello everyone,
will the code below ensure all migrations are in a single transaction? if kysely migrations don't support AsyncLocalStorage, does it make sense to use unmanaged transaction and put
BEGIN before and COMMIT at the end?
```...Solution:
When you run
migrateToLatest() all migrations are run in a single transaction. They either all succeed or none of them succeed.
Also when you run migrateTo the same happens. The only case where you get individual transactions is when you run the migrations in multiple calls.
You can't use migrateTo or migrateToLatest()?...Kysely client usage and pooling question
Hello,
We have a long running server that at the moment creates a single Kysely instance. It is setup to use pg pooling. We have a util function called getKysely which returns this instance and is used throught our backend , like on every request handler that calls the DB. So in theory multiple concurrent requests call this function to get the Kysely client and make queries.
We are noticing that even within the same API endpoint handler, executing queries with a fetched client seems to be running on different connections. We think this because we sometimes execute raw sql queries which change the role of the connection, but later on using the same client we execute a query to check the current role and it is not the one set up previously. ...
Solution:
Kysely takes a connection from the pool for every query. It might be a brand new one or an existing connection in the pool. The pool is in charge of that. Kysely just requests a connection from the pool.
There is no way the pool could know about your requests and return the same connection. How do you imagine that could ever work? A request is not a fundamental ecmascript concept. There's no "request local storage" that could be used to store that info.
If you're coming from the java world, there you have a thread per request (at least with ancient frameworks) and the thread-local storage can be used to get the same connection/transaction automatically....
Implementing D1 batching (getting typed queries as input and returning typed InferResult)
Hi. I've wanted to get D1 batching to work, so I've tried doing as below. Functionally the code works, however I would like to get the proper type safety for this. Essentially I extend Kysely to add the
executeBatch method and I want to infer the results for each of the individual queries. Is this possible?I’m well versed in SQL and priority #1 is performance. Can Kysely still make sense for me?
The TLDR is, I am impressed by Kysely and it seems like a great library… but I also don’t want to use it just because it looks wonderful 😉 I am brand new to the ORM / query builder world. We are migrating a project to SQLite and my decision is coming down to raw SQL (which I am very comfortable with and is my natural inclination) vs Kysely. Here are some questions I have:
1. Could / should I use Kysely and just use the sql template literal tags which is comfortable for me given my raw SQL experience?
2. Are there performance considerations? I know with full blown ORM the answer is obviously yes but wondering with Kysely if there are many performance trade offs in comparison to writing raw sql?...
Solution:
1. If you only use raw SQL, Kysely offers very little to no type-safety for you.
2. With other dialects, not really. With sqlite, Kysely wraps the better-sqlite synchronous API to an async API that can decrease performance. I haven't measured it, but there could be an impact.
3. Yes.
4. You can think of Kysely just as a type-safe way to write raw SQL. That's all it tries to do and that's pretty much the only benefit....
Creating helper functions
I've gotten to the point in my application where a lot of the objects I'm building in my queries are returning in other queries.
In the following example I'd like to extract the module (with color) into something I can use in other queries.
I've been trying to find the correct (and working) way to this...
Solution:
```ts
function moduleWithColor(moduleId: Expression<string>) {
const eb = expressionBuilder<Database, never>()
return jsonObjectFrom(...
how to union two queries with json_build_object?
i would like to union two queries to get a result of type
QueryA | QueryB. this query fails the type checker but the generated query seems to work as intended. is there a type hint i can provide somehow?
https://kyse.link/2TBLF...Solution:
The unioned expressions need to have the same type. You used
first_name in the first one an name in the second one. If both have the same property name it works. https://kyse.link/Bc300
When you use different keys, the correct type of the JSON object would be { name: string | undefined, first_name: string | undefined } but Kysely isn't able to infer it....bulk-insert with constant variable as one column
Hey, I'm basically trying to mirror https://stackoverflow.com/questions/6937442/in-postgres-is-it-possible-to-insert-a-constant-combined-with-the-result-of-sel. I can't figure out how I'd achieve this with Kysely.
I'm currently here:
```ts
await trx...
Solution:
<#1239624163682029690> Had me covered!
```ts
await trx
.insertInto("mapped_groups")
.columns(["group_id_fk", "mapped_id_fk"])...
Making CTE's reusable
I wanted to make certain cte's in my project reusable and came up with the following solution.
Is there a "kysely" way to do this?
```ts...
Solution:
Hey 👋
There isn't a "kysely way" to do this....
pg migration raw sql CREATE FUNCTION gets error: "TypeError: Cannot redefine property: then"
When I try:
``
await sql
CREATE FUNCTION universal_history_trigger_function()...Solution:
So took a bit of searching every possible previous issue/thread and finally stumbled across this answer from @koskimas in another thread last year:
https://discord.com/channels/890118421587578920/1108366659707736154/1108386004232060938
You have two (or more) incompatible versions of Kysely and you are mixing parts of them.In my case this somehow occurred from kysely v0.27.3 + kysely-ctl 0.8.7. Bumped to v.0.27.4 + 0.9.0 respectively fixed it....
Roll back transaction prematurely
Given a Transaction, how do I roll it back without throwing an
Error inside the execution callback method?
```ts
return this._db.transaction().execute(async trx => {
const entryType = await this._db...Solution:
I managed to solve it with a wrapper function. Here it is, for anyone who may need it in the future:
``ts
/**
* Wraps a Kysely transaction such that any Err` returned from the callback results in a rollback....Dynamic query parts based on user input
Based on certain user input (REST API), I need to filter and/or sort by provided fields.
This could mean that some joins need to be added as well.
The fields to filter and sort are not always a 1-1 mapping to a database column, the names can deviate.
...
Unable to use table name with 3 dots in selectFrom
Hey
I am using the bigquery dialect adapter and I am trying to query the information schema.
it has the following structure
<DATASET>.INFORMATION_SCHEMA.COLUMNS
but when I pass a string with this structure to the "selectFrom" it just takes the first two...