Slim4 API Tutorial: 5 Transaction Database

In Slim4 API Tutorial: 5 Transaction Database, we will discuss how to use database transaction, commit, dan rollback in Slim4. After user was successfully authenticated by middleware, then user places an product order to shopping chart. Application can store order data in several tables at once. For example, user orders clothes, jackets and pants. In addition to store order shoping chart from the user, the system will also save the user's address which will be used as delivery address, delivery history, and so on.

Database transactions introduction

Database transaction is a process where the database will store or change data in the table (commit) when there is no error in the processing (transaction). However, if an error occurs in the process of saving or changing the data, the database will return the data to its original (rollback). An example of a database that will be used to perform a transaction database is as follows:

We can use sample data as follows:

# user_address
INSERT INTO `fashion_store_db`.`user_address` (`user_id`, `address`) VALUES ('1', 'User 1 Address');

# category
INSERT INTO `fashion_store_db`.`category` (`category`) VALUES ('Clothing');
INSERT INTO `fashion_store_db`.`category` (`category`) VALUES ('Shoes');
INSERT INTO `fashion_store_db`.`category` (`category`) VALUES ('Watches');

# product
INSERT INTO `fashion_store_db`.`product` (`category_id`, `name`, `stock`, `price`, `status`) VALUES ('1', 'Jacket', '4', '200000', '1');
INSERT INTO `fashion_store_db`.`product` (`category_id`, `name`, `stock`, `price`, `status`) VALUES ('1', 'T-shirt', '8', '100000', '1');
INSERT INTO `fashion_store_db`.`product` (`category_id`, `name`, `stock`, `price`, `status`) VALUES ('2', 'Boots', '1', '150000', '1');

Database script can be viewed at Github.

Order API implementation

Before implement the database transaction in Slim4, need to implement some models to accommodate objects or requests. It will use to store data in the database. We will use Order and OrderDetail.

Order Model is used to store data in orderorder table, data contains users, user addresses, and order status.

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Order extends Model
{
    protected $table = 'order';
    protected $primaryKey = 'id';
}

OrderDetail model uses order_detail table, it will be used to store user's order shoping chart.

<?php

namespace App\Models;

use Illuminate\Database\ELoquent\Model;

class OrderDetail extends Model
{
    protected $table = 'order_detail';
    protected $primaryKey = 'id';
}

Create route with endpoint name /order, this route will be used to accommodate incoming order requests, then models will process and store the data in order and order_detail tables. In Eloquent, database transaction will start the process by using syntax beginTransaction(). If all processes have no errors and finish the process, it will use commit() function. However, if an error occurs and will return to its original data, it will use commit(). Namun jika terjadi kesalahan dan akan mengembalikan data dalam keadaan semula menggunakan fungsi rollBack().

$app->post('/order', function (Request $request, Response $response) use ($capsule) {
    $body = $request->getBody();
    $json = json_decode($body, true);

    $connection = $capsule->getConnection();
    try {
        $connection->beginTransaction();

        $order = new Order();
        $order->user_id = $request->getAttribute('user_id');
        $order->user_address_id = $json['address_id'];
        $order->status = 0;
        $order->save();

        $items = [];
        foreach($json['item'] as $item) {
            $item['order_id'] = $order->id;
            $item['status'] = 0;
            $items[] = $item;
        }
        OrderDetail::insert($items);

        $connection->commit();
        $message['message'] = 'Order successfully';
        $statusCode = 201;
    } catch (\Exception $e) {
        $connection->rollBack();
        $message['message'] = $e->getMessage();
        $statusCode = 400;
    }

    $response->getBody()->write(json_encode($message));
    return $response->withHeader('Content-Type', 'application-json')
        ->withStatus($statusCode);
})->add($authMiddleware);

Order json data example :

{
    "address_id": 1,
    "item": [
        {
            "product_id": 1,
            "price": 200000,
            "amount": 1,
            "note": ""
        },
        {
            "product_id": 2,
            "price": 100000,
            "amount": 1,
            "note": "black"
        },
        {
            "product_id": 3,
            "price": 150000,
            "amount": 1,
            "note": ""
        }
    ]
}

The order request results using Postman:

Full code can be viewed at Github.