How to avoid data insertion if an error occurs? (method with several insertions) in Laravel

Question

I am building a web application and I am aware of a possible problem.

I have a controller in which I have a store method. This method creates several insertions in the database.

public function store(LocationPostRequest $request)
{
  $location = Location::create([...]);
  $order = new Order([...]);
  $location->order()->save($order);
  $contact = Contact::findOrFail(id);
  $order->contacts()->save($contacts);
  Transaction::create([])
}

As you can see, I have a lot of insertions.

If there is an error happening (or if the user loses the connection at some point), it will break the integrity of my database because my method will have added the first elements but not the others.

Am I right to think that? How to avoid this?

Go read up on database _transactions_. https://laravel.com/docs/7.x/database#database-transactions — CBroe, Jul 10 '20 at 09:37

score 4 · Answer 1 · answered Jul 10 '20 at 09:42

You can use laravel db transcation.

if any database query failed it will automatically rollback the previous query.

DB::transaction(function () {
    //set of queries
   //example 
   DB::table('users')->update(['votes' => 1]);
   DB::table('posts')->delete();
});

score 2 · Accepted Answer · answered Jul 10 '20 at 09:54

Yes, you are absolutely right. Your concern is a general problem with databases. The classical example is a bank transfer, which is about you sending money to me. The amount needs to be reduced from your money and added to mine. If it fails at the first operation, then it's bad luck, but at least nobody loses money. If it fails somewhere in the middle though, after the money is subtracted from you, but before it is added to me, then you will have a difficult time convincing me that you have actually sent the amount, while you have lost the given amount.

Your scenario is similar, maybe with less at stake, but still, it's important to make sure that your database status prior to your operations is restored if something fails. This is what gave birth to the idea that some operations should form an atomic bound with one-another and they should either succeed together, or fail together. This bounding together of operation is called transaction.

Transactions are supported in RDBMS instances that you probably use, see https://www.tutorialspoint.com/dbms/dbms_transaction.htm

Transactions are:

Atomic
Consistent
Durable
Isolated

These together form the ACID principle, which you intuitively discovered in your worry. Laravel has its own support for transactions, which you can invoke via

DB::transaction($somefunction)

Your job is to pass your function as a parameter to DB::transaction and worry not, because in case of failure Laravel claims that a rollback will occur. I would certainly test though, because, as Winston Churchill said:

However beautiful the strategy, you should occasionally look at the results.

score 1 · Answer 3 · answered Jul 10 '20 at 10:28

You can use try and catch along with DB:transaction().

try {
        //start the transaction
        DB::beginTransaction();

        //You code

        //commit the transaction
        DB::commit();
} catch (\Exception $e) {
        DB::rollBack();
        return response;
}

Hope this helps

How to avoid data insertion if an error occurs? (method with several insertions) in Laravel

3 Answers3

You can use laravel db transcation.