Drizzle Team•2mo ago

How to update/insert id of a reference when I have a different constraint

I am trying to update/insert a record that has an fk constraint on another table that has an id as the primary key, and a unique constraint on appId and code. At the time of update/insert, I only have the unique constraint. How can I update/insert the record without fetching the id? Something like this:

const table1 = pgTable(
  "table_1",
  {
    id: serial("id").notNull(),
    // appId refers to a 3rd table
    appId: integer("app_id").notNull(),
    code: varchar("code").notNull(),
  },
  (t) => ({
    unq: unique().on(t.appId, t.code),
  })
);

const table2 = pgTable(
  "table_2",
  {
    userId: integer("user_id").references(() => userTable.id).notNull(),
    appId: integer("app_id").references(() => table3.id).notNull(),
    achievementId: integer("achievement_id").references(() => table1.id).notNull(),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.userId, t.appId, t.achievementId] })
  }),
);

// When upserting my record, I don't have achievementId, but I have the appId and code:

await db
  .insert(table_2)
  .values({
    userId,
    appId,
    achievementId: sqlMagicToGetAchievementId(appId, code),
  })
  .onConflictDoUpdate({
    target: [
      table_2,userId,
      table_2.appId,
      table_3.achievementId,
    ],
    set: { ... },
  })

const table1 = pgTable(
  "table_1",
  {
    id: serial("id").notNull(),
    // appId refers to a 3rd table
    appId: integer("app_id").notNull(),
    code: varchar("code").notNull(),
  },
  (t) => ({
    unq: unique().on(t.appId, t.code),
  })
);

const table2 = pgTable(
  "table_2",
  {
    userId: integer("user_id").references(() => userTable.id).notNull(),
    appId: integer("app_id").references(() => table3.id).notNull(),
    achievementId: integer("achievement_id").references(() => table1.id).notNull(),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.userId, t.appId, t.achievementId] })
  }),
);

// When upserting my record, I don't have achievementId, but I have the appId and code:

await db
  .insert(table_2)
  .values({
    userId,
    appId,
    achievementId: sqlMagicToGetAchievementId(appId, code),
  })
  .onConflictDoUpdate({
    target: [
      table_2,userId,
      table_2.appId,
      table_3.achievementId,
    ],
    set: { ... },
  })

6 Replies

tcurdt•2mo ago

That schema feels a little odd. Why is the achievementId referencing table2? typo? Anyway, the way I understand your situation I see two options: 1. make it a transaction, make a select and then use that id 2. use a sub query

ReizelOP•2mo ago

Yes, good catch! It should reference table1. I have fixed it now! I tried using $with and the sql operator. But both still feel like magic and I couldn't find enough documentation online to be able to figure it out on my own. Would you be able to provide an example of how this should look like?

tcurdt•2mo ago

I would just use a transaction instead of magic. Just along the lines of

  return await db.transaction(async (tx) => {
    // const found = await tx.select(table1)...
    // return await tx.insert(table2)...
  })

  return await db.transaction(async (tx) => {
    // const found = await tx.select(table1)...
    // return await tx.insert(table2)...
  })

ReizelOP•2mo ago

I am doing this for an array of items, not just one, so I would have to query for a lot of items. In prisma you would simply do this as:

await db.table.upsert({
  where: {
    appId,
    // would find the achievement id via the unique constraint:
    achievement: { appId, code },
  },
})

await db.table.upsert({
  where: {
    appId,
    // would find the achievement id via the unique constraint:
    achievement: { appId, code },
  },
})

Not sure if this is just Prisma abstracting and doing things client-side, or if there is a magic way of doing it using SQL. But it feels like this is way harder than it should be with Drizzle?

tcurdt•2mo ago

Doing it for many items would have been a very important detail to mention. This sounds like you want a CTE. Not sure whether drizzle supports that yet. https://www.reddit.com/r/webdev/comments/18mhecm/how_can_i_write_sql_ctes_in_drizzleorm_for/ https://github.com/drizzle-team/drizzle-orm/issues/2078 Would be interesting what actually sql queries prisma generates from that "upsert".

From the webdev community on Reddit: How can I write SQL CTE's in D...

Explore this post and more from the webdev community

GitHub

[FEATURE]: allow INSERT in CTEs (WITH clauses) · Issue #2078 · driz...

Describe what you want Drizzle ORM supports SELECT queries in CTEs (WITH clauses). From the docs: const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42))); const result...

ReizelOP•2mo ago

As far as I can tell it does allow using the $with method: https://orm.drizzle.team/docs/update#with-update-clause But I just can't seem to get it to work, and also it's not typesafe at all unless I am doing something wrong?

Drizzle ORM - Update

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Gaming

Programming

How to update/insert id of a reference when I have a different constraint