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@1278Identically named columns in multiple tables - How to use innerJoin.selectAll()?
Hey, I am new to using kysely, and also relatively new to using PostgreSQL.
I have trouble with the following query:
```js...
Solution:
Hey 👋🏻
You have 3 options:
1. use table scoped
selectAll on 1 table (selectAll('tables')) and then select the other table/s columns while aliasing columns that exist multiple "tables"....Type safe discriminated union query
I am looking for a way to make this query more type safe.
The generated SQL gives me the result I want, which adheres to the specified union type, but typescript needs some convincing and the
$castTo assertions I have added are hurting type safety.
If I remove .$castTo<unknown>() in the task query I get this error in the event query Property 'description' is missing in type '{ event_name: string; }' but required in type '{ description: string; }', and similar in the note query....Improving TS compile speed with multiple conditional selects?
Hi, I have API endpoints that query Postgres via Kysely. The API endpoints accept an
include object that determines whether to include various optional fields. Some endpoints have grown to accept 5-10 optional fields.
Each of these optional fields results in an additional $if branch in the Kysely query. And this seems to harm TS compile speed -- I'm now waiting 1-3min for each compilation.
What are the best ways to mitigate the compile speed downsides of using many $if calls?...Type safety in checkExpression in addCheckConstraint
Hi, is it possible somehow utilise kysely to create type-safe checkExpression and avoid writing stuff like this:
```typescript
.addCheckConstraint(
'constraint-name',...
Trouble with dynamically allocating table name via string
Hello!
I am having some trouble trying to allocate a table name dynamically via a string. I used the sql.table command in my SelectFrom statement but no such luck. Passing in a direct string also does not work as hoped.
I have read a few posts which indicate this is not possible, if not I can work around it, if it is however how might I achieve this?...
Dynamic expression builder using raw SQL for WHERE column LIKE string% OR
Hello!
We switched over to using Kysely in our project and it's going great so far! But I found a bit of a hitch with writing an old dynamic WHERE LIKE query.
I need to get the results of file paths stored in our system that start with a directory path. Previously in pure SQL we would generate an expression like this:...
Solution:
@Igal that works great. Leaving the full solution here for others who come looking
...
const condition = dirPaths.map((dir) =>
sql<boolean>`file_path LIKE ${dir + '%'}`)
.reduce((acc, condition) => sql<boolean>`${acc} OR ${condition}`);
const condition = dirPaths.map((dir) =>
sql<boolean>`file_path LIKE ${dir + '%'}`)
.reduce((acc, condition) => sql<boolean>`${acc} OR ${condition}`);
How to define multiple configs in a NX monorepo?
Hi, we have multiple applications in a NX monorepo and I need separate configs for migrations and seeders. How would you recommend to do it? When using
kysely init it creates the kysely.config.ts in the root ./config. Should I use conditional app selection by env variables?Making Updateable<X> fields required
Excuse me if this has already been posted, but I recently turned on strict mode after completing my migration to TS and I'm struggling to make certain fields required when updating an entity.
When I hover over
UpdateableActivity it shows:
```ts...Solution:
You can't make them required by kysely. In SQL update statements, every column is optional.
You can have your own interface that makes some fields required and use that as an input for your own update functions that use kysely....