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.).
...
Joining multiple separate CTE's
I am working on a complex query. I can get it to run efficiently using multiple CTE's(Atleast in RAW SQL) but I want to port all this over to Kysely due to the typesafety.
What I want is something like this:
const cteOne = kyselyDb...
Solution:
conditionally added CTEs are not supported - too much type complexity involved.
you could
union with a subquery conditionally tho, as the result shape is already established, and union can be added to existing builder instances, outside of chain (but also with $call)....Is this a correct situation to use $castTo (jsonArrayFrom + Left Join)
```ts
// snippet from main query:
jsonArrayFrom(
eb
.selectFrom('address')...
Inconsistent Typing of NUMERIC Fields with jsonBuildObject
https://kyse.link/wxJiK
I came across an issue on GitHub (link) where JSON fields in PostgreSQL aren't automatically typed correctly due to everything being returned as a string. This means that when using jsonObjectFrom, the returned type is string, rather than the expected data type like
Date.
What's puzzling is that this behavior isn't consistent across all data types. For instance, NUMERIC values at the root level are returned as strings (which is fine), but when those same values appear inside a nested JSON object, their type is inferred as number instead. I would prefer to have NUMERIC values inside JSON objects returned as string to keep it consistent with how they are handled at the root level....Solution:
So, you either need those explicit casts, or allow the DB to cast the value to JSON number and fix your types.
AggregateFunctionBuilder inside a SUM
Hi there, I'm trying to build
...
SUM(CASE WHEN IsNoShow = 0 AND IsDNA = 0 THEN 1 ELSE 0 END)
SUM(CASE WHEN IsNoShow = 0 AND IsDNA = 0 THEN 1 ELSE 0 END)
Solution:
Here you go https://kyse.link/X1Vxc
Our types are a bit too strict and give the type
100 instead of number for eb.lit(100)...sql literal string in binary expression builder
Hi, I want the following:
```sql
SELECT
sum("ChargeAmount" * "1.0") AS "TotalChargeAmount"
FROM...
Solution:
You definitely don't want
"1.0" since that means you're referencing a column named 1.0Query result type mismatch
For unknown to me reason kysely adds
categoryId to nested object built with jsonObjectFrom or maybe it does not take related table, but copies selectFrom table.
Why does it happen?
https://kyse.link/0sjoX...Solution:
Use
selectAll('product_category') instead of selectAll() in the subquery
Getting the underlying connection pool/client from a kysely wrapper
Hi guys, I have a migration script that takes a raw PgClient object. Was wondering if it would be possible to extract said object from the Kysely<any> that is passed into the migration functions (up down)
thanks!...
Dynamic callbackURL on login
Hi guys,
I wonder if it's possible to have a dynamic Dynamic callbackURL on login if the user is admin or not?
Have a nice day....
postgres helper wrapping to_json referencing CTE?
is there a way to create a type safe postgres helper (
recordToJson) that is similar to jsonObjectFrom, but that references an existing CTE. in essence i want to be able to do something like this
```typescript
db
.selectFrom('foo')...Solution:
It's as simple as this https://kyse.link/t2h2a
Is it okay to repeatedly wrap a PG connection pool with kysely?
Chaps, my understanding is that it is safe to repeatedly wrap a postgres connection pool with kysely a la "new Kysely<DB>({dialect: new PostgresDialect({ pool }) })", and I can just let those kysely wrappers get garbage collected. Is this correct?
Convert record from Function call (RPC) to JSON
Hello, I am using raw SQL to run a function that returns a record, but it is returning it to the TS domain as a string like this:
instead of a JSON object with those values in key/value pairs. ...
"(b38ed355-c2b0-4998-aff9-8c8dc429ffe5,My_Test_Key,valid,aead-det,22,\"\\\\x7067736f6469756d\",\"2025-01-19 02:09:04.392092+00\",,\"\")"
"(b38ed355-c2b0-4998-aff9-8c8dc429ffe5,My_Test_Key,valid,aead-det,22,\"\\\\x7067736f6469756d\",\"2025-01-19 02:09:04.392092+00\",,\"\")"
Generating dynamic raw query help
Hello, we are trying to create a function utility to call RPCs, we want to dynamically generate the following SQL query:
...
SELECT my_function(arg1 => 'some_value', arg2 => 123);
SELECT my_function(arg1 => 'some_value', arg2 => 123);
could not determine data type of parameter $1
I am trying to nest PG functions array_agg and json_build_object
I tried like this
```js
select(({ fn, eb, val }) => {
return fn...
Solution:
Ok, perplexity gave me the answer.
You have to cast the key names à string with
cast(val("foo_col"), "text")...