Kysely•3w ago

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)}))`

GitHub

kysely/src/helpers/postgres.ts at 660dfb43d5c48dd74d35f5fedae28f395...

A type-safe typescript SQL query builder. Contribute to kysely-org/kysely development by creating an account on GitHub.

Solution:

It's as simple as this https://kyse.link/t2h2a

Jump to solution

3 Replies

Solution

koskimas•3w ago

It's as simple as this https://kyse.link/t2h2a

koskimas•3w ago

You don't need that nested select there I forgot we have a built-in function for to_json https://kyse.link/WlgyZ

lorentzOP•3w ago

amazing! thank you so much. so much simpler ☺️ (tried to mark ✅ but getting The application did not respond)

Gaming

Programming

postgres helper wrapping to_json referencing CTE?

Did you find this page helpful?