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(...)

db.$with(...).as(...)

and

db.with(...).insert(...).values(...)

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(...)

query(...)

and writing SQL?

Inserting into tables with foreign key relationships

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(...)

db.$with(...).as(...)

and

db.with(...).insert(...).values(...)

db.with(...).insert(...).values(...)

query(...)

query(...)

and writing SQL?

Inserting into tables with foreign key relationships

Similar Threads

Inserting into tables with foreign key relationships

Similar Threads

Similar Threads

Similar Threads