Create and Connect query ( coming from prisma)

[laurent512]

Hi everyone,

Migrating from prisma with limited database knowledge.

There are two entities, Adult & Pet.
Each Adult can have one or more Pet, each Pet must have an owner Adult.

In my current implementation there is a foreignkey in Pet pointing towards Adult id (pk).
I also setup a "one to many" relationship as it can be seen in the doc.

What is the right way to create in a single query a Pet and its Adult ?
Currently I am creating Adult, getting its Id in return and then creating its Pet but I assume there is a better way :)

Thank you !

[Angelelz]

This is the canonical sql way of inserting two related records. I would just add, this should be done inside a transaction, just so that if there is an issue somewhere, nothing will get committed halfway.

[laurent512]

Thank you for the reply !
Would you be able to show me how to do it with a sample code ?
I don't find anything on the doc https://orm.drizzle.team/docs/transactions

[Angelelz]

This is the example with a user and a post:

await db.transaction(async (transaction) => {
  const newUser = await transaction.insert(users).values({ name: 'Jane Doe' }).returning();
  await transaction.insert(posts).values({ content: 'Hello World', authorId: newUser.id });
});