PostgreSQL - PHP

PostgreSQL & PHP: Modern Connectivity

Connecting PHP applications to a PostgreSQL database is a common requirement. The modern, secure, and recommended approach is to use PHP Data Objects (PDO), an abstraction layer that provides a consistent and powerful interface for database access.

Prerequisites: The PDO_PGSQL Extension

Before you can connect, ensure the PDO PostgreSQL driver is enabled in your PHP installation. You can check this by running php -m | grep pdo_pgsql in your terminal or by looking for the following line in your php.ini file (and making sure it’s not commented out):

extension=pdo_pgsql

In modern development environments using Docker, you would typically install and enable this extension within your Dockerfile.

Connecting to the Database

A connection is established by creating an instance of the PDO class. It’s crucial to wrap this logic in a try...catch block to handle potential connection errors gracefully.

<?php

$host = '127.0.0.1';
$port = '5432';
$dbname = 'testdb';
$user = 'postgres';
$password = 'your_password'; // Best practice: use environment variables!

// Data Source Name (DSN)
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname";

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // Fetch results as associative arrays
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $password, $options);
    echo "Connected to the $dbname database successfully!";
} catch (\PDOException $e) {
    // In a real app, log this error instead of echoing it
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// $pdo object is now ready for querying
?>

Key PDO Methods

Instead of procedural functions, you’ll use methods on your PDO object. Here are the most important ones:

Method	Description
prepare(string $query)	Prepares an SQL statement for execution. This is the first step in preventing SQL injection.
execute(array $params = null)	Executes a prepared statement, optionally passing an array of parameters to bind.
query(string $query)	Executes an SQL statement directly and returns a result set. Use only for queries without user input.
fetch()	Fetches the next row from a result set.
fetchAll()	Returns an array containing all of the result set rows.
rowCount()	Returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement.
lastInsertId(string $name = null)	Returns the ID of the last inserted row, specifying the sequence name if needed.

CRUD Operations with Prepared Statements

Using prepared statements is non-negotiable for security. It separates the SQL query from the data, making SQL injection impossible.

INSERT: Creating a Record

<?php
// Assumes $pdo connection object from above

$sql = 'INSERT INTO company (name, age, address, salary) VALUES (:name, :age, :address, :salary)';

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

$stmt->execute([
    'name' => 'Allen',
    'age' => 25,
    'address' => 'Texas',
    'salary' => 15000.00
]);

$lastId = $pdo->lastInsertId('company_id_seq'); // Note: sequence name may be needed
echo "New record created successfully. Last inserted ID is: " . $lastId;
?>

SELECT: Reading Records

<?php
// Assumes $pdo connection object

$sql = 'SELECT id, name, salary FROM company WHERE age > ?';

$stmt = $pdo->prepare($sql);
$stmt->execute([24]);

$companies = $stmt->fetchAll();

foreach ($companies as $company) {
    echo $company['id'] . ' | ' . $company['name'] . ' | $' . $company['salary'] . "\n";
}
?>

UPDATE: Modifying a Record

<?php
// Assumes $pdo connection object

$sql = 'UPDATE company SET salary = :salary WHERE id = :id';

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

$stmt->execute([
    'salary' => 25000.00,
    'id' => 1
]);

echo $stmt->rowCount() . " record(s) updated successfully.";
?>

DELETE: Removing a Record

<?php
// Assumes $pdo connection object

$sql = 'DELETE FROM company WHERE id = ?';

$stmt = $pdo->prepare($sql);
$stmt->execute([2]);

echo $stmt->rowCount() . " record(s) deleted successfully.";
?>

Best Practices for Modern PHP

• Store Credentials Securely: Never hard-code database credentials in your code. Use environment variables (e.g., via a .env file and a library like vlucas/phpdotenv) and load them at runtime.
• Error Handling: Always use PDO::ERRMODE_EXCEPTION and wrap your database logic in try...catch blocks. Log errors to a file in production instead of displaying them to users.
• Connection Management: For a web application, establish a single database connection when the application bootstraps and reuse the $pdo object throughout the request lifecycle. Avoid connecting in a loop or per-function.
• Use Composer: Manage your project dependencies, including any database-related libraries or ORMs, with Composer, the standard package manager for PHP.