K
Kysely
First came Kysely, the type-safe query builder for TypeScript, then came people asking questions, then came the Discord server.Join if you like Kysely or have questions, or both.
JoinK
Kysely
First came Kysely, the type-safe query builder for TypeScript, then came people asking questions, then came the Discord server.Join if you like Kysely or have questions, or both.
JoinSelect with alias helper
Hello everyone.
I am currently trying to create a function that converts all the columns of a given table to this format: "tablename_columnname"
I almost did it, but my current solution is not fully typescript-friendly: SEE SNIPPET 1...
Dynamic case when expression
I was struggling to figure out how to dynamically build a case expression. At the moment I've just got hardcoded for 6 indexes which doesn't seem like a good approach.
```ts
export async function healParty(userId: string) {
// Array of the user's party...
Solution:
```ts
await db
.updateTable("beast")
.set((eb) => {
let caseBuilder: any = eb.case();...
RANK() query for a leaderboard
Hi I am trying to get a user's ranking and I got the following query working: https://kyse.link/KRaFK
I wanted to ask if this is the right approach to take, or if there is a way to avoid using the raw sql
snippet from the link:...
Solution:
Any way to create a partitioned table (PG)
I wasn't able to find anything in the docs or in intellisense.
Presumably for partitioned tables I need to break into an sql.raw and just do it that way?
Thanks in advance...
Solution:
Can be used...
).modifyEnd(sql` PARTITION BY HASH (user_id);`)
).modifyEnd(sql` PARTITION BY HASH (user_id);`)
I need a way to run a parameterized query as part of a Kysely transaction
Hi all, I am doing a bunch of normal database CRUD in a Kysely transaction. As part of that transaction, I need to perform a database action using a 3rd party library that expects my "database" to expose a
query(query : string, params: unknown[]). AFAICT there is no way for me to run a parameterized query with the kysely DB instance?
Worst case scenario
, I can manually manage the pool/transactions myself and wrap the client in a Kysely object but that certainly would not be ideal......Generic Kysely Client
hey guys,
i'm currently creating an ORM-agnostic queue engine.
Currently I'm trying to integrate kysely as new adapter.
...
Custom joins (probably raw)
Hi, how can I type this in kysely? Thanks for any help
```sql
SELECT
a.code AS approval_code...
Select by many IDs query with order based on input
I'm currently trying to write kysely queries to replace direct better-sqlite3 usage in my app, and one of them is a select query which receives list of IDs and outputs objects in the same order as the IDs list
currently, i almost have a solution using a with clause, raw sql, and
(VALUES (?),(?),...), but i don't know how to type the raw sql part so the output doesn't have any
please see https://kyse.link/tPC0N
thanks in advance...Solution:
yeah i did saw your thread but i didn't see that you've put another playground link with the solution, thanks!
```ts
db
.with(
"chosenIds(MediaId)",...
Reference proper table names when using schema prefix
If you have your kysely tables defined a
schema.table, when you want to selectFrom('schema.table') the actual name that should be used to refer to the table later in the query, should be table and not schema.table. Using the complete path results in errors depending on the actual query shape.
From my understanding, when you refer to a table by its full path, adding the schema prefix, you're just referencing it, but the name that's actually saved in the query scope to refer back to it, doesn't have the schema prefix anymore.
An example would be:...MERGE INTO using arbitrary values
Hi 👋🏽
I'm trying to construct a MERGE query using arbitrary values based on this stackoverflow answer.
Here's the example from that post:
```
MERGE INTO mytable AS tbl...
Solution:
Thanks, that's almost perfect (you did make a mistake, the alias for values should have been 'vals').
For posterity's sake, here's the full query to get output SQL matching the example:
```
const rows = await db...
Transaction is necessary in the migration file?
Hi
May I know if we need to wrap the migration in a transaction like the following?
```js
export async function up(db: Kysely<any>): Promise<void> {
await db.transaction().execute(async (trx) => {...
Strange error while inserting
With 0.28.2, I'm having a strange issue which always occur with a specific value being inserted. I tried removing plugins, but that didn't help either.
the error:
```
TypeError: 'get' on proxy: property '0' is a read-only and non-configurable data property on the proxy target but the proxy did not return its actual value (expected '#<Object>' but got '#<Object>')...
How can I use `->>` and `->` operators with ExpressionBuilder?
I have tried doing
eb(ref("table.column"), "->", eb.lit("foo")), but this does not work because "->" is not recognized as a valid operator. How do I do this without using raw SQL templates?Solution:
Ah,
ref("table.column", "->>").key("foo").Deferrable on unique indexes and constraints
Hi everyone,
I am trying to figure out how to add deferrable to indexes and constraints in psql dialect. I've found some issues (such as issue 224) however not found any related to what I need:
```sql...
I need to extract the type of eb in the query chain
I have a complex query (more than 800 lines). Its mostly json formatting. Is there a way I can extract out the type of eb so for each nested json object I build, I can create a helper method. Or is this not possible.
Also, is it an anti pattern to do the json formatting in the query iteself ?...
selectFrom or getExecutor not a function with custom dialect.
Hello, I am using a custom dialect which was basically forked from here [1] but when I try to run a query with it, I get one of 2 errors depending on how I try to run the query (with
selectFrom or with sql.raw().execute(db)):
- selectFrom is not a function
- executorProvider.getExecutor is not a function
...Solution:
OK, nevermind. My function was marked as async but I had moved the async functionality elsewhere so I was trying to query a Promise and typescript didn't catch the issue.
Can't figure out how to add a unique index on (foo, LOWER(bar)) for a table
Sorry chaps if this is obvious but can't seem to see any mention of this in the docs. I need to create a unique index on a tuple of columns - with the caveat that the second column is "case insensitive" - i achieve this by lowercasing the second column. I believe this is quite normal to do in vanilla pg but am unsure what the idiomatic way to do this in kysely is...
Solution:
Answer:
When constructing an index, you can daisy chain .column and .expression. Thus, you would do:
```typescript...
Using AsyncLocalStorage to Propagate Transaction Context
I'm trying to implement a pattern where I can use AsyncLocalStorage to automatically propagate a transaction context without having to explicitly pass a transaction object throughout my codebase.
The goal is to be able to write code like this:
```ts
const updateUsername = async (username: string) => {...
Solution:
For option 2, something like:
with-transaction.ts:
```ts...How to make general purpose queries?
Hello everyone.
I am currently developing a medium-scale server using express and Kysely as a query builder (which I have adored so far).
However, with the growing codebase, I've noticed that many select, update and insert queries are similar between them.
That being said, it would be very useful to have a wrapper to perform those similar queries, which I've tried to build like so:...
Solution:
Hey 👋
Don't. You're building your own ORM, might as well use an ORM - at least for, as you say, plenty of your access patterns, and use Kysely only when you get value from it being specific or from its composition capabilities.
Some ORMs already work with Kysely (e.g. Prisma) - so you get a type-safe low-level escape hatch, some will in the future (e.g. Mikro, Zenstack, etc.).
...