When And How To Use DB::Transaction() In Laravel

Hello Artisan in this tutorial i will discuss about Laravel database transactions. I will also discuss why should we use database transactions and what is the advantage of laravel database transactions.

If you read the Laravel docs of Database transactions then you will see that Database Transactions describes wrapping our database calls within a closure. First let's try to know what is database transactions. 

What is Database Transactions?

A database transaction gives us the powerful ability to safely perform a set of data-modifying SQL queries (such as insertions, deletions or updates). This is made safe because you can choose to rollback all queries made within the transaction at any time.

We should use database transactions when one table is directly dipendant on other table and we need to insert data both table simultaniouly. This time we can use database transactions to insert our data. 

See the example code.

// Create Account
$account = Account::create([
    'accountname' => $request->name
]);

// Create User

User::create([
    'username' => $request->username,
    'account_id' => $account->id,
]);

 

This situation can cause issues. Like

Account was not created

If the account was not created, Then there's no id to pass to the user for its account_id field. In this scenario, the errors will occur in our system.

User was not created.

If, however, the account was created, but the user was not, then we run into issues. You now have an account with no available users, and there is disparity in the database data.

Database transactions consist of three possible "tools":

  • Creating a transaction 
  • Rolling back a transaction 
  • Committing a transaction 

The previous sample code can be pseudo-coded with transactions as such:

// Start transaction
beginTransaction();

// Run Queries
$acct = createAccount();
$user = createUser();

// If there's an error
//    or queries don't do their job,
//    rollback!
if( !$acct || !$user )
{
    rollbackTransaction();
} else {
    // Else commit the queries
    commitTransaction();
}

 

Basic Transactions in Laravel

The first way to run a transaction within Laravel is to put your queries within a closure passed to the DB::transaction() method:

DB::transaction(function()
{
    $newAcct = Account::create([
        'accountname' => Input::get('accountname')
    ]);

    $newUser = User::create([
        'username' => Input::get('username'),
        'account_id' => $newAcct->id,
    ]);
});

In this case there is no chance to occur issuse in our application. 

 

Read also : Laravel and N + 1 Problem | How To Fix N + 1 Problem

 

Bit Advanced Transactions in Laravel

 public function invoice_report_store(Request $request, $id)
    {
        foreach ($request->selling_qty as $key => $value) {

          $invoice_details = InvoiceDetail::where('id',$key)->first();
          $product = Product::where('id',$invoice_details->product_id)->first();

          if( $product->qty < $request->selling_qty[$key])
          {
            session()->flash('message','Sorry! '.$product->name.' product stock is empty!');
            return redirect()->back();
          }

        }

        $invoice = Invoice::find($id);
        $invoice->approved_by = $request->created_by;
        $invoice->status = 1;

        \DB::transaction(function () use( $request, $invoice, $id ) {

            foreach ($request->selling_qty as $key => $value) 
            {
              $invoice_details = InvoiceDetail::where('id',$key)->first();
              $invoice_details->status = 1;
              $invoice_details->save();
              $product = Product::where('id',$invoice_details->product_id)->first();
              $product->qty = ((float)$product->qty) - ((float)$request->selling_qty[$key]);
              $product->save();
            }
            $invoice->save();
        });

      session()->flash('message','Success! Invoice is approved!');
      return redirect()->route('invoice.view');
    }

 

Look carefully we need to insert data multiple table at the same time. But all others table data is depend on Invoice table.So here i use transactions to save our data. 

Note: The DB facade's transaction methods control the transactions for both the query builder and Eloquent ORM. Keep in mind that if the is inserted in one table but can't find the other data, then Laravel must insert data first in database then rollback the data. That's why we can easily avoid error having used database transactions.

 

Hope this Laravel database transactions tutorial will help you to learn something new.

 

#laravel #laravel-7 #laravel-7x #database-transactions #query-optimization