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
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
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")...Performance impact of "$if" ?
According to the documentation, $if "has an impact on typescript performance and it should only be used when necessary". I would like to use it for conditional selects. How bad is the performance impact? Thanks in advance!
Solution:
It's impossible to predict. But if you start seeing long pauses before you get intellisense, try to reduce the
$if calls to see if it helps. The impact is much smaller now than it was when I wrote that doc.How can I join 2 tables where 1 side is a JSON property in a JSON column?
I am trying this:
```
db.selectFrom('my_table')
.selectAll(['my_table'])
.innerJoin(...
Solution:
Hey 👋
```ts
const rows = await db
.selectFrom("my_table")...
Pagination
Hi everyone, I have a question. I'm working with a database in mssql and I'm creating a function to paginate the data in a table. For this I was doing something like the following: const person = await db
.selectFrom('person')
.selectAll()
.where('id', '=', '1')
.offset(0)...
Solution:
Kysely never "translates" anything.
[Bug] innerJoinLateral selectAll prepends schema
apologies for the hastily written bug report. i found a workaround so im not super concerned. writing this down in case it's helpful:
1. have a postgres
db variable that's instantiated withSchema pointed at my_schema
2. selectFrom my_table with an innerJoinLateral on my_computed_object
3. select jsonObjectFrom(eb.selectFrom('my_computed_object').selectAll('my_computed_object'))...Solution:
Use
withSchema('public') for the subqueryExecuting stored procedure in MS SQL
Hello, I've used Kysely for PG DB and love it, now I have a MSS SQL project and need to execute some Stored Procedures, how would one do this?
Solution:
Hey 👋
Kysely has nothing for Stored Procedure execution. Wrap each execution with a type-safe function. Use
sql under the hood.
If these things can be introspected, there is potential in generating these type-safe functions....Flatten type of single column select?
Hi! I have helpers that look roughly like this:
```
function ownerId(organizationId: Expression<number>) {
eb.selectFrom('organization')...
Solution:
Hey 👋
There's a
$asScalar() utility method that's probably landing in v0.28.
https://github.com/kysely-org/kysely/blob/v0.28/src/query-builder/select-query-builder.ts#L1938-L1986...Typing enums
First of all, I still really enjoy using Kysely, great library!
I have the following pattern for
enums. I create the role table as an "enum" table (which I got from another help post I was reading earlier) and add it as an foreign key to the teacher table.
```ts...Any update on all migrations in single-transaction?
Is there anything new with the single-transaction for all migrations?
Need something like this:
```typescript...
V0.28 Type Issues
Hey i've been trying to use v0.28 branch, but whenever i install this branch from pnpm all the kysely types break, is there another way for me to use this branch. I tried locally cloning and building but seem to have the same type issues. Typescript just can't find kysely package at all.
Solution:
npm i https://pkg.pr.new/kysely-org/kysely@1278