Nested transactions

[feniljariwala82]

I was going through the Transaction documentation. And I did not find an example of handling nested transactions.
Use Case: I want to write a query that first creates a user in the user table and second with the id of the user I want to create a profile in the profile table with other data. And I only want to use one transaction property. If there is a profile error I want to roll back user creation. The question is How to achieve this using lucid models or query builder?

type User= { id: number email: string password: string }

type Profile= { id: number first_name: string last_name: string user_id: string // foreign key mapping to users.id }

I am new to nested transactions. Please guide me.

[RomainLanz]

Hey @feniljariwala82! 👋🏻

This is not a nested transaction. It is a basic transaction.
So what have you tried so far that doesn't work?

📚 https://docs.adonisjs.com/guides/database/transactions

[feniljariwala82]

Let me just share, that User and Profile are different tables. I have queries, let me ask.

1. Currently I am creating a trx property using const trx = await Database.transaction() and passing it to both User and Profile models using useTransaction(trx). The question is when to use trx.commit()? Should I use it after I use await user.save() or await profile.save().
2. While using single trx property for different queries, would it work?

Using trx.rollback() is easy to understand. I understand that. My only concern is when to commit and would this approach work?

[thetutlage]

It should be after the user.save() and profile.save() call.

While using single trx property for different queries, would it work

Yup. That is the idea. That you wrap your multiple queries inside a transaction. If something fails, all queries will be rolled back and your database stays in a consistent state.

Similarly, if there are no errors and you call commit, then everything will be persisted to the database.

[feniljariwala82]

Thank you so much for the guidance. I tried and it worked. Again thank you so much.

[feniljariwala82]

It should be after the user.save() and profile.save() call.

While using single trx property for different queries, would it work

Yup. That is the idea. That you wrap your multiple queries inside a transaction. If something fails, all queries will be rolled back and your database stays in a consistent state.

Similarly, if there are no errors and you call commit, then everything will be persisted to the database.

I have another question. What if I pass trx property defined in the user model as a parameter to the method defined in another model profile. Would it work?