lorentz
lorentz
PostsComments
KKysely
Created by lorentz on 4/16/2025 in #help
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: 
const updateUsername = async (username: string) => {
  await withTransaction(async () => {
    const user = await db.selectFrom('users')
      .where('id', '=', userId)
      .select(['id', 'isVerified'])
      .executeTakeFirstOrThrow();

    if (user.isVerified) {
      await db.updateTable('users')
        .where('id', '=', userId)
        .set({ username })
        .execute();

      await logUserActivity('username_changed');
    }
  });
}

const logUserActivity = async (activity: string) => {
  await db.insertInto('activityLog')
    .values({
      activityType: activity,
      timestamp: new Date(),
    })
    .execute();

  await db.updateTable('users')
    .set({ lastActivityAt: new Date() })
    .where('id', '=', currentUserId())
    .execute();
}
const updateUsername = async (username: string) => {
  await withTransaction(async () => {
    const user = await db.selectFrom('users')
      .where('id', '=', userId)
      .select(['id', 'isVerified'])
      .executeTakeFirstOrThrow();

    if (user.isVerified) {
      await db.updateTable('users')
        .where('id', '=', userId)
        .set({ username })
        .execute();

      await logUserActivity('username_changed');
    }
  });
}

const logUserActivity = async (activity: string) => {
  await db.insertInto('activityLog')
    .values({
      activityType: activity,
      timestamp: new Date(),
    })
    .execute();

  await db.updateTable('users')
    .set({ lastActivityAt: new Date() })
    .where('id', '=', currentUserId())
    .execute();
}
Where any query made using db inside the withTransaction callback would automatically use the transaction created by withTransaction, even across function boundaries and imported modules. I've attempted to implement this by creating a custom dialect wrapper along these lines, but I'm running into initialization issues and type problems. Does anyone know of an example implementation/recipe for this I can use, or have any pointers? (I know this was intentionally dropped from Kysely as a native feature, and I'm aware of the risks) Any guidance would be greatly appreciated!
9 replies
KKysely
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