What is the short way of getting back a complex record that was inserted or updated with ids included?

[ridhwaans]

Sorry if the title is not clear

Suppose a main item has a 1-to-n or n-to-n relationship with 5-6 other tables

const {
        genres,
        credits,
        similar,
        keywords,
        ...mainItem
      } = item
      ;
await db.transaction(async (tx: any) => {
        const insertedMainItem = await tx
          .insert(mainItemTable)
          .values(mainItem)
          .onConflictDoUpdate({

in the same transaction, i write to the other tables and joinTables with new methods.

I currently call returning* to get back the primary key

.returning({ id: mainItemTable.id });

* (from the docs for sqlite driver. theres another callback called output() but i think thats exclusive to MSSQL?)

however, i dont just want a subset of the saved data. i'd like the full object back with their primary ids in the original structure that it was provided. with a spread operator most likely

performing upsert-read cycles on every item or stitching together the sum of its parts seems like the only way?

please advise

Thank you

[everton-dgn]

Call .returning() without arguments and you'll get all columns back — no need to list them or do a follow-up SELECT:

const [insertedMainItem] = await tx
  .insert(mainItemTable)
  .values(mainItem)
  .returning(); // all columns, including auto-generated id

insertedMainItem will be typed as typeof mainItemTable.$inferSelect.

Same shape for upserts:

const [upsertedMainItem] = await tx
  .insert(mainItemTable)
  .values(mainItem)
  .onConflictDoUpdate({
    target: mainItemTable.id,
    set: { ...mainItem, updatedAt: new Date() },
  })
  .returning();

Then use the returned id in the same transaction for the joined tables:

await tx.insert(genresTable).values(
  genres.map((g) => ({ ...g, mainItemId: insertedMainItem.id }))
);

One gotcha worth flagging: this works on PostgreSQL and SQLite. MSSQL uses .output() (different keyword) which you already noticed. MySQL has no RETURNING at all — on MySQL you need either a follow-up SELECT by primary key or a client-side pre-generated UUID/ULID in values().

[apoorva-01]

.returning() won't get you there. It only hands back columns from the row you just wrote in that one table, it doesn't follow relations. So genres, credits, similar and friends won't be in there no matter how you call it. And there's no single Drizzle call that inserts across 6 tables and returns you one fully-nested object. INSERT ... RETURNING is per-statement, that's a SQL thing, not a Drizzle limitation.

The clean pattern is: do all your writes, then do one relational read at the end of the same transaction using the query API.

await db.transaction(async (tx) => {
  const [main] = await tx.insert(mainItemTable).values(mainItem)
    .onConflictDoUpdate({ /* ... */ })
    .returning();

  // ... your writes to genres / credits / joinTables here ...

  const full = await tx.query.mainItemTable.findFirst({
    where: eq(mainItemTable.id, main.id),
    with: { genres: true, credits: true, similar: true, keywords: true },
  });

  return full; // fully nested, ids included, and consistent because it's still in the tx
});

tx.query gives you the same relational API as db.query, so the read sees your uncommitted writes and you get one assembled object out. Two round-trips instead of one, but it's the only way to get the whole graph back with ids.

(And yeah, output() is MSSQL-only. Ignore it on sqlite/pg.)