Safe way to insert into 3 separate tables and verify integrity

Hello, There are 3 separate tables that I need to update upon a a user request:

orders, details and coupons

How can I insert rows into 3 of them making sure that everything was updated without an issue? so that if one fails I will cancel everything?

Is this a job for transaction?

So something like:

DB::transaction(function () {
    DB::table('orders')->create(...); 
    DB::table('details')->create(...);
    DB::table('coupons')->create(...);
});

LaryAI

3 years ago

Level 58

Yes, using a transaction is a good solution to ensure data integrity when inserting into multiple tables. Here's an example using Laravel's database query builder:

DB::beginTransaction();

try {
    // Insert into orders table
    $orderId = DB::table('orders')->insertGetId([
        'user_id' => $userId,
        'total' => $total,
        // other order fields
    ]);

    // Insert into details table
    DB::table('details')->insert([
        'order_id' => $orderId,
        'product_id' => $productId,
        'quantity' => $quantity,
        // other details fields
    ]);

    // Insert into coupons table
    DB::table('coupons')->insert([
        'order_id' => $orderId,
        'code' => $code,
        // other coupon fields
    ]);

    DB::commit();
} catch (\Exception $e) {
    DB::rollback();
    // Handle the exception
}

In this example, we start a transaction using DB::beginTransaction(). Then we insert into the orders table and retrieve the inserted id using insertGetId(). We use this id to insert into the details and coupons tables. If any of the inserts fail, an exception will be thrown and we will roll back the transaction using DB::rollback(). If all the inserts succeed, we commit the transaction using DB::commit().

Ligonsker

3 years ago

Level 8

@LaryAI Shouldn't the beginTransaction() be inside the try.. catch block?

Snapey

3 years ago

Best Answer

Level 122

@Ligonsker The beginTransaction is unlikely to fail so its fine where it is, but probably makes no difference

You can use the closure approach in which case there is no need for the try-catch, however its not obvious that the transaction failed.

1 like

Please or to participate in this conversation.