PostgreSQL PHP: Working with BLOB

Summary: in this tutorial, you will learn how to work with PostgreSQL BLOB using PHP such inserting BLOB, querying BLOB, and deleting BLOB.

BLOB stands for the binary large object that is used to store binary data such as the content of a file. PostgreSQL does not have the BLOB data type. However, you can use the  bytea data type for storing the binary string.

We will create a new table named company_files to store the binary string.

CREATE TABLE company_files (
     id        SERIAL PRIMARY KEY,
     stock_id  INTEGER NOT NULL,
     mime_type CHARACTER VARYING(255) NOT NULL,
     file_name CHARACTER VARYING(255) NOT NULL,
     file_data BYTEA NOT NULL,
     FOREIGN KEY(stock_id) REFERENCES stocks(id)
);
Code language: SQL (Structured Query Language) (sql)

We will store the content of a file in the file_data column. In addition, we will read the file from the assets/images folder and insert them into the company_files table.

To work with the BLOB data, we create a new class named BlobDB.

Inserting PostgreSQL BLOB data

The following insert() method reads data from a file specified by the $pathToFile parameter and inserts it into the company_files table.

   /**
     * Insert a file into the company_files table
     * @param int $stockId
     * @param string $fileName
     * @param string $mimeType
     * @param string $pathToFile
     * @return int
     * @throws \Exception
     */
    public function insert($stockId, $fileName, $mimeType, $pathToFile) {
        if (!file_exists($pathToFile)) {
            throw new \Exception("File %s not found.");
        }

        $sql = "INSERT INTO company_files(stock_id,mime_type,file_name,file_data) "
                . "VALUES(:stock_id,:mime_type,:file_name,:file_data)";

        try {
            $this->pdo->beginTransaction();
            
            // create large object
            $fileData = $this->pdo->pgsqlLOBCreate();
            $stream = $this->pdo->pgsqlLOBOpen($fileData, 'w');
            
            // read data from the file and copy the the stream
            $fh = fopen($pathToFile, 'rb');
            stream_copy_to_stream($fh, $stream);
            //
            $fh = null;
            $stream = null;

            $stmt = $this->pdo->prepare($sql);

            $stmt->execute([
                ':stock_id' => $stockId,
                ':mime_type' => $mimeType,
                ':file_name' => $fileName,
                ':file_data' => $fileData,
            ]);

            // commit the transaction
            $this->pdo->commit();
        } catch (\Exception $e) {
            $this->pdo->rollBack();
            throw $e;
        }

        return $this->pdo->lastInsertId('company_files_id_seq');
    }
Code language: PHP (php)

How it works.

1. First, call the pgsqlLOBCreate() method of the PDO object to create a new large object and get the OID of the large object.
2. Next, call the pgsqlLOBopen() method to open  a stream on the large object to write data to it.
3. Then, read data from a file and copy the data from the file stream to the large binary object.
4. After that, prepare the INSERT statement and execute it.
5. Finally, call the lastInsertId to get the generated ID.

Note that the pgsqlLOBCreate() method must be called within a transaction, therefore, we place all the logic within a transaction.

Place the following code in the index.php file to insert the content of the google.png file into the company_files table.

<?php

require 'vendor/autoload.php';

use PostgreSQLTutorial\Connection as Connection;
use PostgreSQLTutorial\BlobDB as BlobDB;

try {
    // connect to the PostgreSQL database
    $pdo = Connection::get()->connect();
    // 
    $blobDB = new BlobDB($pdo);
    $fileId = $blobDB->insert(2, 'logo', 'image/png', 'assets/images/google.png');

    echo 'A file has been inserted with id ' . $fileId;
} catch (\PDOException $e) {
    echo $e->getMessage();
}
Code language: SQL (Structured Query Language) (sql)

Launch the index.php file, we get the following message.

A file has been inserted with id 1
Code language: SQL (Structured Query Language) (sql)

To verify the insert operation, we use the following query:

SELECT * FROM company_files;
Code language: SQL (Structured Query Language) (sql)

 id | stock_id | mime_type | file_name |  file_data
----+----------+-----------+-----------+--------------
  1 |        2 | image/png | logo      | \x3137323730
(1 row)

Querying PostgreSQL BLOB data

The following read() method reads the BLOB data from the company_files table and output the file content to the web browser.

    /**
     * Read BLOB from the database and output to the web browser
     * @param int $id
     */
    public function read($id) {

        $this->pdo->beginTransaction();

        $stmt = $this->pdo->prepare("SELECT id, file_data, mime_type "
                . "FROM company_files "
                . "WHERE id= :id");

        // query blob from the database
        $stmt->execute([$id]);

        $stmt->bindColumn('file_data', $fileData, \PDO::PARAM_STR);
        $stmt->bindColumn('mime_type', $mimeType, \PDO::PARAM_STR);
        $stmt->fetch(\PDO::FETCH_BOUND);
        $stream = $this->pdo->pgsqlLOBOpen($fileData, 'r');

        // output the file
        header("Content-type: " . $mimeType);
        fpassthru($stream);
    }
Code language: PHP (php)

How it works.

1. First, prepare a SELECT statement.
2. Next, execute the SELECT statement by calling the execute() method.
3. Then, pass the OID to the pgsqlLOBOpen() method of a PDO object to get the stream.
4. After that, output the stream to based on the mime type of the file.
5. Finally, because the pgsqlLOBopen() must be called within a transaction, we called the beginTransaction() at the beginning of the method.

To test the read() method, we place the following code in the file.php:

<?php

require 'vendor/autoload.php';

use PostgreSQLTutorial\Connection as Connection;
use PostgreSQLTutorial\BlobDB as BlobDB;

$pdo = Connection::get()->connect();
$blobDB = new BlobDB($pdo);

// get document id from the query string
$id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT);

$file = $blobDB->read($id);
Code language: PHP (php)

The file.php file gets the id value from the query string and outputs the file stored in the company_files table to the web browser.

Deleting PostgreSQL BLOB data

The following delete() method deletes a row in the company_files table.

    /**
     * Delete the large object in the database
     * @param int $id
     * @throws \Exception
     */
    public function delete($id) {
        try {
            $this->pdo->beginTransaction();
            // select the file data from the database
            $stmt = $this->pdo->prepare('SELECT file_data '
                    . 'FROM company_files '
                    . 'WHERE id=:id');
            $stmt->execute([$id]);
            $stmt->bindColumn('file_data', $fileData, \PDO::PARAM_STR);
            $stmt->closeCursor();

            // delete the large object
            $this->pdo->pgsqlLOBUnlink($fileData);
            $stmt = $this->pdo->prepare("DELETE FROM company_files WHERE id = :id");
            $stmt->execute([$id]);

            $this->pdo->commit();
        } catch (\Exception $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
Code language: PHP (php)

How it works.

1. First, get the OID object from the file_data column.
2. Second, use the pgsqlLOBUnLink() method to remove the BLOB data and execute the DELETE statement to remove a row specified by an ID in the company_files table.

In this tutorial, we have shown you how to insert, query, and delete BLOB data in the PostgresQL database.