Drizzle Team•7mo ago

Inserting into tables with foreign key relationships

I have two tables: "part_of_speech" and "type". part_of_speech.type_id is a foreign key column referencing type.id. I want to insert a row into "part_of_speech" and set the "typeId" based on the "id" corresponding to a value from another column on the "type" table based on some filter. So far I have managed to do this in typescript land:

const verbType = (
    await dbClient
      .select({ typeId: types.id })
      .from(types)
      .where(sql`label->>'en-US' = 'Verb'`)
  )[0];

  const insertedPos = await dbClient
    .insert(partsOfSpeech)
    .values([
      {
        typeId: verbType!.typeId,
        value: '食べる',
        translation: jsonbSql({ 'en-US': 'Eat' }),
      },
      {
        typeId: verbType!.typeId,
        value: '読む',
        translation: jsonbSql({ 'en-US': 'Read' }),
      },
    ])
    .onConflictDoNothing()
    .returning();

const verbType = (
    await dbClient
      .select({ typeId: types.id })
      .from(types)
      .where(sql`label->>'en-US' = 'Verb'`)
  )[0];

  const insertedPos = await dbClient
    .insert(partsOfSpeech)
    .values([
      {
        typeId: verbType!.typeId,
        value: '食べる',
        translation: jsonbSql({ 'en-US': 'Eat' }),
      },
      {
        typeId: verbType!.typeId,
        value: '読む',
        translation: jsonbSql({ 'en-US': 'Read' }),
      },
    ])
    .onConflictDoNothing()
    .returning();

However, is this not possible somehow using db.$with(...).as(...) and db.with(...).insert(...).values(...) structure where I could reference the result column from the "$with" query in the values for each row I am inserting? I was trying to do this and seems logical, but was getting type errors (and while I know that there should be a single result returned in the with, drizzle doesn't know so an array will always be returned). is there a proper way with drizzle to do this? Or do you have to fall back to doing query(...) and writing SQL?

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Inserting into tables with foreign key relationships