lorentz Posts - Answer Overflow

lorentz

•Created by lorentz on 2/4/2025 in #help

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

db
  .selectFrom('foo')
  .innerJoin('bar', '=', ''bar.foo_id', 'foo.id')
  .select(eb => [
    'foo.id',
    recordToJson(eb, 'bar')
  ])

db
  .selectFrom('foo')
  .innerJoin('bar', '=', ''bar.foo_id', 'foo.id')
  .select(eb => [
    'foo.id',
    recordToJson(eb, 'bar')
  ])

to generate sql like this

select foo.id, to_json(bar) -- (row_to_json would also work)
from foo
inner join bar on bar.foo_id = foo.id

select foo.id, to_json(bar) -- (row_to_json would also work)
from foo
inner join bar on bar.foo_id = foo.id

so this generates the correct SQL, but lacking any type safety

export const recordToJson = (
  tableRef: string,
) => sql`(select to_json(${sql.table(tableRef)}))`

export const recordToJson = (
  tableRef: string,
) => sql`(select to_json(${sql.table(tableRef)}))`

8 replies

KKysely

•Created by lorentz on 11/26/2024 in #help

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. If I remove the .$castTo to the discriminated union type in the end this type is inferred

const rows: {
    id: number;
    type: Type;
    details: unknown;
}[]

const rows: {
    id: number;
    type: Type;
    details: unknown;
}[]

I would be happy to hear any tips and tricks 🙇

2 replies

KKysely

•Created by lorentz on 9/12/2024 in #help

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

10 replies

KKysely

•Created by lorentz on 8/5/2024 in #help

how to decode a point to `{x: number, y: number}` when selected inside `jsonBuildObject`?

when i select a column of type point the basic way i get it decoded to {x: number, y: number}, but when i wrap it in jsonBuildObject i get it as the raw string. example:

const query = db
  .selectFrom('event')
  .select((eb) => [
    'event.location',
    jsonBuildObject({
      location: eb.ref('event.location'),
    }).as('wrapperObject'),
  ]);

const query = db
  .selectFrom('event')
  .select((eb) => [
    'event.location',
    jsonBuildObject({
      location: eb.ref('event.location'),
    }).as('wrapperObject'),
  ]);

[
  {
    "location": {
      "x": 4.9,
      "y": 52.378
    },
    "wrapperObject": {
      "location": "(4.9,52.378)"
    }
  }
]

[
  {
    "location": {
      "x": 4.9,
      "y": 52.378
    },
    "wrapperObject": {
      "location": "(4.9,52.378)"
    }
  }
]

12 replies

KKysely

•Created by lorentz on 5/22/2024 in #help

are conditional CTEs possible?

i don't find any hints here https://kysely.dev/docs/category/cte. i have tried the conditional where approach (https://kysely.dev/docs/examples/WHERE/conditional-where-calls), but i get type error Type 'QueryCreatorWithCommonTableExpression ... The types returned by 'with(...)' are incompatible between these types.

5 replies

Gaming

Programming