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

id

as the primary key, and a unique constraint on

appId

appId

and

code

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: { ... },
  })

tcurdt•10/12/24, 11:21 PM

That schema feels a little odd. Why is the referencing ? typo?
Anyway, the way I understand your situation I see two options:

make it a transaction, make a select and then use that id
use a sub query

ReizelOP•10/12/24, 11:56 PM

Yes, good catch! It should reference . I have fixed it now!

I tried using and the 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•10/13/24, 12:31 AM

I would just use a transaction instead of magic.

tcurdt•10/13/24, 12:35 AM

Just along the lines of

ReizelOP•10/13/24, 12:50 AM

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:

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•10/13/24, 9:00 AM

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•10/13/24, 11:14 AM

As far as I can tell it does allow using the 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.