Smarter way to update while creating

In prisma, I can do something like this:

await prisma.blogs.createMany(
  data: blogs.map(b => ({
    name: 'asdf',
    text: 'asdfasdfs',
    users: { connect: b.userIdsToConnectTo }
  })
})

await prisma.blogs.createMany(
  data: blogs.map(b => ({
    name: 'asdf',
    text: 'asdfasdfs',
    users: { connect: b.userIdsToConnectTo }
  })
})

is there a smarter way to do this in drizzle? currently im doing this:

await tx.insert(blogs).values(blogs)

await Promise.all(updates.map(async (update) =>
  tx
    .update(users)
    .set({ blogId: update.newBlogId })
    .where(inArray(users.id, update.userIdsToConnectTo))
    .execute()
));

await tx.insert(blogs).values(blogs)

await Promise.all(updates.map(async (update) =>
  tx
    .update(users)
    .set({ blogId: update.newBlogId })
    .where(inArray(users.id, update.userIdsToConnectTo))
    .execute()
));

this is extremely slow to do the promise.all here. it would be much more efficient to just do this upon creation of the blogs if i could just do a create many and name one trip to the db. at the very least i'd like to be able to do 2 trips (one for the creation of all the new blogs and one for the user updates)

3 Replies

jakeleventhalOP•12mo ago

bump

francis•12mo ago

depends on the db engine you are using I believe prisma also does this the stupid way, it also makes one db query per updated, so I'm not sure there's a huge performance difference here but if you're using postgres you can use an upsert on users, as long as you are 100% sure that every user id you are referencing currently exists

const updateValues = updates.flatMap(update => update.userIdsToConnectTo.map(userId => ({id: userId, blogId: update.newBlogId})))
await tx.insert(users).values(updateValues).onConflictDoUpdate({target: [users.id], set: {blogId: sql`excluded.blogId`}]})

const updateValues = updates.flatMap(update => update.userIdsToConnectTo.map(userId => ({id: userId, blogId: update.newBlogId})))
await tx.insert(users).values(updateValues).onConflictDoUpdate({target: [users.id], set: {blogId: sql`excluded.blogId`}]})

note that your sql has to use the column casing you use at the database level, so if you have underscore db columns and camelcase drizzle prop names, you'll have to use excluded.blog_id instead you have to think about this from a sql engine standpoint rather than a drizzle standpoint. prisma is not doing anything smart here, one await call in prisma is translating into n database calls

jakeleventhalOP•12mo ago

@francis ended up doing something like this:

tx
  .update(users)
  .set({ blogId: sql`
    CASE
    ${sql`${blogUpdates.map(b => WHERE id in ${b.ids.map(id => `'${id}'`)} THEN b.id }`}}
    ELSE ${users.blogId}
    END
  ` });

tx
  .update(users)
  .set({ blogId: sql`
    CASE
    ${sql`${blogUpdates.map(b => WHERE id in ${b.ids.map(id => `'${id}'`)} THEN b.id }`}}
    ELSE ${users.blogId}
    END
  ` });

Gaming

Programming

Smarter way to update while creating