Postgres Transaction in PHP

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In PostgreSQL, a transaction refers to a sequence of operations that are executed as a single unit of work. If you’d like to perform Postgres transactions in PHP, it’s easy to do so using the PHP PDO. In this article, we’ll provide code examples that demonstrate how to execute a PostgreSQL transaction from a PHP application.

Prerequisites

If you’re planning to follow along with the code examples in this tutorial, you’ll need to have the following prerequisites in place:

  • PostgreSQL must be installed and configured on your machine.
  • You should have some basic knowledge of PostgreSQL.
  • PHP must be installed on your machine, whether it’s a standalone installation or done with a package installer like XAMPP.
  • Composer must be installed on your machine.

What is a PostgreSQL Transaction?

A PostgreSQL transaction is composed of series of database operations performed as a single unit. If a failure or error occurs that prevents the completion of the transaction, the entire transaction is rolled back and the database will not be affected at all. Transactions have four key properties that are named in the acronym “ACID”: atomicity, consistency, isolation and durability.

By default, the auto-commit mode is enabled in Postgres– this means that every statement issued by an application will be automatically committed to the database. We can disable this mode once we call the method beginTransaction() of the PDO object.

When we begin a transaction, the changes can only be committed once we invoke the commit() method of the PDO object. If an error is encountered during the process, we can reverse the changes via rollback() method of the PDO object.

The code shown below illustrates the basic method for performing transactions in the PHP PDO:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php

try {
$pdo->beginTransaction();

$pdo->query("SELECT <field1>,<field2>,... FROM table");

$stmt = $pdo->prepare("DELETE QUERY");
$stmt->execute();

$stmt = $pdo->prepare("ANOTHER DELETE QUERY");
$stmt->execute();

$db->commit();
} catch (\PDOException $e) {
$db->rollBack();
throw $e;
}

Let’s take a closer look at this code:

  • We call the method beginTransaction() to begin the transaction.
  • We then create a query immediately after that.
  • After every statement, we perform the execute() method.
  • We call the commit() method to accept the changes as defined in the prepare statements.
  • Finally, we issue a rollback() method that only gets invoked if we encounter an error. The rollback() method cancels all operations and requests for changes as defined in the prepare statements.

Creating New Postgres Table

In this section, we’ll create a sample dataset that we can use throughout this tutorial.

We’ll begin by logging in to the Postgres shell. Then we’ll issue commands to create a database and some tables.

  • To create a database, we use the following command:
1
use project
  • To create tables within the new database, we’ll use these SQL statements:
1
2
3
4
5
CREATE TABLE IF NOT EXISTS personnel (
id SERIAL PRIMARY KEY,
name CHARACTER VARYING(100),
lastname CHARACTER VARYING(100)
);
1
2
3
4
CREATE TABLE IF NOT EXISTS tasks (
id SERIAL PRIMARY KEY,
task CHARACTER VARYING(100)
);
1
2
3
4
5
6
7
8
CREATE TABLE personnel_tasks(
tasks_id INTEGER NOT NULL,
personnel_id INTEGER NOT NULL,
PRIMARY KEY (tasks_id,personnel_id),
FOREIGN KEY(personnel_id) REFERENCES personnel(id),
FOREIGN KEY(tasks_id) REFERENCES tasks(id)

);

The SQL statements shown above will create three tables: ‘personnel’ , ‘tasks’ and ‘personnel_tasks’. These tables reside in the database named ‘project’

At this point, we should have a list of tables that looks like the following:

1
2
3
4
5
6
Schema | Name | Type | Owner
--------+-----------------+-------+----------
public | personnel | table | postgres
public | personnel_tasks | table | postgres
public | tasks | table | postgres
(3 rows)

Next, we’ll insert sample data into the tasks table:

1
INSERT INTO tasks (task) VALUES('check inventory'),('check schedule'),('check communication');

We need to populate the tasks table in advance because we’ll be assigning tasks to new personnel as we create their records.

Project Structure

Shown below is the project structure of our basic application:

alt text

Coding the PHP Postgres Transaction

Now that we’ve created a sample dataset for demo purposes, we’ll look at the PHP scripts that we’ll be using to perform the Postgres transaction.

We’ll modify a PHP file called ‘phptrans.php’ and add the code shown below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
<?php

namespace phptransaction;

class phptrans {

private $pdo;

/**
* Initialize the object with a specified PDO object
* @param \PDO $pdo
*/

public function __construct($pdo) {
$this->pdo = $pdo;
}

/**
* Add a new account
* @param string $name
* @param string $lastName
* @param int $task_id
* @param int $personnel_id
*/

public function addTask($name, $lastName, $tasks_id) {
try {
// we start the transaction
$this->pdo->beginTransaction();

// insert an personnel and get the id back
$personnel_id = $this->insertPersonnel($name, $lastName);

// add tasks for a personnel
$this->insertTask($tasks_id, $personnel_id);

// commit the changes if no error encountered
$this->pdo->commit();
} catch (\PDOException $e) {
// we rollback the changes should we encounter an error
$this->pdo->rollBack();
throw $e;
}
}

/**
* insert details for personnel
* @param string $name
* @param string $lastName
* @return int
*/

private function insertPersonnel($name, $lastName) {
$stmt = $this->pdo->prepare(
'INSERT INTO personnel(name,lastname) '
. 'VALUES(:name,:lastname)');

$stmt->execute([
':name' => $name,
':lastname' => $lastName
]);

return $this->pdo->lastInsertId('personnel_id_seq');
}

/**
* insert a new tasks for a target personnel
* @param int $task_id
* @param int $personnel_id
* @return bool
*/

private function insertTask($tasks_id, $personnel_id) {
$stmt = $this->pdo->prepare(
'INSERT INTO personnel_tasks (tasks_id,personnel_id) '
. 'VALUES(:tasks_id,:personnel_id)');

return $stmt->execute([
':tasks_id' => $tasks_id,
':personnel_id' => $personnel_id
]);
}

}

We’ve added plenty of comments to the code shown above to make the PHP script easier to understand.

Next, we’ll modify the ‘index.php’ file, which will invoke the above functions:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
require 'vendor/autoload.php';

use phptransaction\Connection as Connection;
use phptransaction\phptrans as phptrans;

try {
// connect to the PostgreSQL database
$pdo = Connection::get()->connect();

$phptrans = new phptrans($pdo);

// add accounts
$phptrans->addTask('John', 'Smith', 1);
$phptrans->addTask('Hanz', 'Solo', 2);
$phptrans->addTask('Sarah', 'Bucks', 3);


echo 'The new personnel have been added.' . '
'
;
//
$phptrans->addTask('Jaime', 'Abella', 99);
} catch (\PDOException $e) {
echo $e->getMessage();
}

We’re ready to test our PHP application by visiting the following URL: http://localhost/phptransaction/index.php

You should see something like this:

1
2
The new personnel have been added.
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "personnel_tasks" violates foreign key constraint "personnel_tasks_tasks_id_fkey" DETAIL: Key (tasks_id)=(99) is not present in table "tasks".

We can also verify the result of our operations by entering the Postgres shell and viewing the contents of the personnel table:

1
2
3
4
5
6
7
project=# SELECT * FROM personnel;
id | name | lastname
----+-------+----------
9 | John | Smith
10 | Hanz | Solo
11 | Sarah | Bucks
(3 ROWS)

We can also check the ‘personnel_tasks’ table:

1
2
3
4
5
6
7
project=# SELECT * FROM personnel_tasks;
tasks_id | personnel_id
----------+--------------
1 | 9
2 | 10
3 | 11
(3 ROWS)

Conclusion

If you’re a PHP developer and you’d like to utilize Postgres transactions in your code, you’ll find that it’s easy to accomplish the task using the PHP PDO. In this article, we provided all the sample code you’ll need to perform a Postgres transaction in PHP. With our code examples and instructions, you’ll be ready to implement transactions in your own PHP applications.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.