Drizzle Team•13mo ago

Upsert create/update joined table similar to Prisma

Hey all, trying to get another one of my queries to migrate away from prisma. This one is a bit more complex. Trying to see if I am able to take a joined table and either update it or create it. Prisma has this as a nested option but I couldnt figure out how to solve this with drizzle. In the example below, I have a store that I want to update its joined table shelf or create it if its not there

await prisma.store
      .update({
        where: {
          id: store.id,
        },
        data: {
          shelf: {
            upsert: {
              create: {
                shelfName: data.shelfName,
              },
              update: {
                shelfName: data.shelfName,
              },
            },
          },
        },
      })

await prisma.store
      .update({
        where: {
          id: store.id,
        },
        data: {
          shelf: {
            upsert: {
              create: {
                shelfName: data.shelfName,
              },
              update: {
                shelfName: data.shelfName,
              },
            },
          },
        },
      })

3 Replies

Angelelz•13mo ago

Assuming the reference column in the shelf table is storeId:

db.insert(shlef)
  .values({
    storeId: store.id,
    shleftName: data.shelftName
  }).onConflictDoUpdate({
    target: shelft.storeId,
    set: {shelfName: data.shelftName},
   })

db.insert(shlef)
  .values({
    storeId: store.id,
    shleftName: data.shelftName
  }).onConflictDoUpdate({
    target: shelft.storeId,
    set: {shelfName: data.shelftName},
   })

Angelelz•13mo ago

https://orm.drizzle.team/docs/insert#onconflict-and-upsert-insert-or-update

SQL Insert - DrizzleORM

Drizzle ORM | %s

imoby•13mo ago

In my case I’m trying to create or update a shelf assuming the store already exists but storeId is not a foreign key in shelf but shelfId is a foreign key of store. So I can’t insert into shelf with a value of storeId right?

Gaming

Programming

Upsert create/update joined table similar to Prisma