Drizzle Team•12mo ago

insert returning relations

Hello, is it possible to have the relations included in the returned row using insert().returning()? Or do I have to make a separate query for that?

8 Replies

Angelelz•12mo ago

You can't insert in more that one table at the same time. What I would suggest is doing these type of operations in one transaction

plumbe0OP•12mo ago

Uhm, I don't need to insert in multiple tables. I need to upsert a user to their table and then have it returned along with their posts. I'm doing upsert like this:

const user = await db
  .insert(users)
  .values({ id: userId, ...userData })
  .onConflictDoUpdate({ target: users.id, set: userData })
  .returning()

const user = await db
  .insert(users)
  .values({ id: userId, ...userData })
  .onConflictDoUpdate({ target: users.id, set: userData })
  .returning()

I'd like to have the user's posts returned as well. Is it possible? E.g.

.returning({with: { posts: true } })

.returning({with: { posts: true } })

Angelelz•12mo ago

Ah, I see. I don't think that's possible in raw sql, but I don't know that much of the subject. I you find it's possible in SQL, you should be able to do it in Drizzle

plumbe0OP•12mo ago

In Prisma it is possible, I shall ask the devs if they plan to implement it

Angelelz•12mo ago

Prisma is doing several queries under the hood for you, Drizzle will never do that without you telling it.

plumbe0OP•12mo ago

for sure. I was just wondering if my .returning() call was missing some params or if this feature is not implemented

Angelelz•12mo ago

Looks like this is actually possible in one query but only in postgres. Just not supported yet in Drizzle https://stackoverflow.com/questions/41202661/postgres-returning-clause-with-join

Stack Overflow

Postgres RETURNING clause with join

In the following SQL, how could I make the RETURNING clause join to something else and return the joined row(s)? Here it only returns the row from mytable that was updated, but I'd like it to retur...

Angelelz•12mo ago

In SQLite it's not possible:

7. The RETURNING clause may only reference the table being modified. In an UPDATE FROM statement, the auxiliary tables named in the FROM clause may not participate in the RETURNING clause.

https://www.sqlite.org/lang_returning.html

Gaming

Programming

insert returning relations