PHP: Execute Any DB Access Code in Transaction

Reusable Code for Execute Any DB Access Code in Transaction

In this post, I will show you highly reusable utility code for wrapping and executing original db access code in transaction.

Anyway let's show you the code.

function executeInTransaction(callable $func)
{
    $conn = new PDO(SERVER_NAME, USERNAME, PASSWORD, DBNAME);
    try {
        $conn->beginTransaction();
        $func($conn);
        $conn->commit();
    } catch (Exception $ex) {
        $conn->rollBack();
        throw $ex;
    }
}
        

Oh, how to use? You just simply passing your core DB access code as the callable $func argument.
Okay, let me show you example in next section.

Example Usage

First, assume you have following 2 methods for inserting data to DB.

CONST SERVER_NAME = 'localhost';
CONST USERNAME = "username";
CONST PASSWORD = "password";
CONST DBNAME = "my_database";

// Create connection
$conn = new PDO(SERVER_NAME, USERNAME, PASSWORD, DBNAME);

$values = array(
    'value1' => '1',
    'value2' => '2',
    'value3' => '3',
);
insert($values, $conn);

/**
 * Core code for insertion
 */
function insertToTable1(array $values, PDO $conn)
{
    $sql = <<<EOF
INSERT INTO table1
      (column1, column2, column3) 
VALUES
      (:value1, :value2, :value3)
EOF;
    
    $stmt = $conn->prepare($sql);
    $stmt->bindColumn($stmt, $values['value1']);
    $stmt->bindColumn($stmt, $values['value2']);
    $stmt->bindColumn($stmt, $values['value3']);
    
    $stmt->execute();
}

function insertToTable2(array $values, PDO $conn)
{
    $sql = <<<EOF
INSERT INTO table2
      (column1, column2) 
VALUES
      (:value1, :value2)
EOF;
    
    $stmt = $conn->prepare($sql);
    $stmt->bindColumn($stmt, $values['value1']);
    $stmt->bindColumn($stmt, $values['value2']);
    
    $stmt->execute();
}
        

Now, if you would like to execute the above 2 methods in one transaction, what should you do?
You will write code for wrapping the above 2 methods by try{...} catch{...} like below.

function insert($values)
{
    $conn = new PDO(SERVER_NAME, USERNAME, PASSWORD, DBNAME);
    try {
        $conn->beginTransaction();
        insertToTable1($values, $conn);
        insertToTable2($values, $conn);
        $conn->commit();
    } catch (Exception $ex) {
        $conn->rollBack();
        throw $ex;
    }
}
        

Of course it's straight forward and no problem.
But you might ask yourself, "Every time should I write the kind of try{...} catch{...} stuff when we need to execute code in transaction?"
I have this kind of question, then I wrote function executeInTransaction(callable $func) method.
If we use executeInTransaction method, the code will be like below.

executeInTransaction(function($conn) use($values){
    insertToTable1($values, $conn);
    insertToTable2($values, $conn);
});
        

I think, hope you agree, the code is much cleaner then before, and one more the executeInTransaction method is reusable for any kind of wrapping db access code in transaction.
Of course this is not a silver ballet or golden hammer solution. But hope this code help your code base cleaner...