Working with Databases in PHP: PostgreSQL and MongoDB

Working with Databases in PHP: PostgreSQL and MongoDB

Modern web development in PHP rarely goes without interacting with databases. The choice between a relational (PostgreSQL) and a document-oriented (MongoDB) system depends on the data structure, performance requirements, and scalability of the project. In this article, we will cover how to connect to both DBMSs, perform basic operations, and what to pay attention to when choosing.

1. Connecting to PostgreSQL via PDO

PostgreSQL is a powerful relational DBMS with support for ACID transactions, complex queries, and extensible data types. In PHP, it is recommended to use the PDO (PHP Data Objects) extension to work with it, which provides a unified interface for different databases.

First, make sure that the pdo_pgsql and pgsql drivers are installed in your system. You can check this via phpinfo().

Example of connection and data insertion

<?php$host = 'localhost';$dbname = 'my_database';$user = 'my_user';$password = 'my_password';

try {    // Create DSN (Data Source Name)    $dsn = "pgsql:host=$host;dbname=$dbname";        // Connect    $pdo = new PDO($dsn, $user, $password, [        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC    ]);        echo "Connection to PostgreSQL established successfully!<br>";        // Data insertion using prepared statements    $sql = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)";    $stmt = $pdo->prepare($sql);        $stmt->execute([        ':name' => 'Ivan Petrov',        ':email' => 'ivan@example.com',        ':age' => 28    ]);        echo "User added. ID: " . $pdo->lastInsertId();    } catch (PDOException $e) {    echo "Connection error: " . $e->getMessage();    die();}?>

Using prepared statements protects against SQL injection and improves performance for repeated similar queries.

Data retrieval with filtering

<?php$minAge = 25;$search = "SELECT id, name, email, age FROM users WHERE age > :minAge ORDER BY age DESC";$stmt = $pdo->prepare($search);$stmt->execute([':minAge' => $minAge]);

$users = $stmt->fetchAll();

foreach ($users as $user) {    echo "{$user['name']} ({$user['age']} years old) - {$user['email']}<br>";}?>

PostgreSQL supports complex JOINs, subqueries, window functions, and full-text search — this makes it an ideal choice for projects with strict data integrity requirements.

2. Connecting to MongoDB via the official driver

MongoDB is a NoSQL database that stores data in the form of documents (JSON-like BSON objects). It is well-suited for projects with flexible schemas, rapid prototyping, and horizontal scaling.

To work with MongoDB in PHP, the mongodb extension and the mongodb/mongodb library (installed via Composer) are used.

Installing the driver

# Via Composer in the project rootcomposer require mongodb/mongodb

Example of connection and document insertion

<?phprequire_once 'vendor/autoload.php';

use MongoDB\\Client;use MongoDB\\Driver\\Exception\\Exception;

try {    // Connect to the local server    $client = new Client("mongodb://localhost:27017");        // Select the database and collection    $collection = $client->my_database->users;        // Insert a single document    $insertResult = $collection->insertOne([        'name' => 'Anna Smirnova',        'email' => 'anna@example.com',        'age' => 32,        'interests' => ['programming', 'reading', 'traveling'],