Working with Databases in PHP: PostgreSQL and MongoDB in Practice

Working with Databases in PHP: PostgreSQL and MongoDB in Practice

Modern web development in PHP rarely goes without interacting with databases. Two popular solutions — the relational PostgreSQL and the document-oriented MongoDB — provide developers with flexible tools for storing and processing data. In this article, we will look at how to connect to each of these DBMSs, perform basic CRUD operations, and avoid common mistakes. All code examples are written in PHP and ready to use.

Connecting to PostgreSQL via PDO

To work with PostgreSQL in PHP, it is recommended to use the PDO (PHP Data Objects) extension. It provides a unified interface for different DBMSs and protects against SQL injections through prepared statements.

Install the pdo_pgsql driver if it is not already activated. You can check this via phpinfo() or with the command:

php -m | grep pdo_pgsql

Example of connecting to a local database:

<?php$host = 'localhost';$dbname = 'my_database';$user = 'postgres';$pass = 'secret';

try {    $dsn = "pgsql:host=$host;dbname=$dbname";    $pdo = new PDO($dsn, $user, $pass, [        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC    ]);    echo "Connection to PostgreSQL successful!";} catch (PDOException $e) {    die("Connection error: " . $e->getMessage());}

After connecting, let's perform a simple data query. Let's create a users table and insert a few records:

<?php// Creating a table$pdo->exec("CREATE TABLE IF NOT EXISTS users (    id SERIAL PRIMARY KEY,    name VARCHAR(100) NOT NULL,    email VARCHAR(255) UNIQUE NOT NULL,    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");

// Inserting data via a prepared statement$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");$stmt->execute([':name' => 'Ivan Petrov', ':email' => 'ivan@example.com']);echo "User added, ID: " . $pdo->lastInsertId();

// Fetching all users$users = $pdo->query("SELECT * FROM users")->fetchAll();foreach ($users as $user) {    echo "{$user['name']} - {$user['email']}<br>";}

Working with MongoDB via the Official Driver

MongoDB is a NoSQL database that stores data in BSON (binary JSON) format. For PHP, there is an official mongodb driver, which is installed via Composer together with the mongodb/mongodb library.

Installation:

composer require mongodb/mongodb

Connecting to MongoDB and selecting a database:

<?phprequire 'vendor/autoload.php';

use MongoDB\\Client;

$uri = 'mongodb://localhost:27017';$client = new Client($uri);$collection = $client->my_database->users; // database my_database, collection users

echo "Connection to MongoDB established";

Now let's perform CRUD operations. In MongoDB, data is represented as associative arrays:

<?php// Inserting a document$insertResult = $collection->insertOne([    'name' => 'Anna Smirnova',    'email' => 'anna@example.com',    'age' => 28,    'tags' => ['php', 'mongodb']]);echo "New document ID: " . $insertResult->getInsertedId();

// Searching by field$user = $collection->findOne(['email' => 'anna@example.com']);echo "Name: " . $user['name'];

// Updating: adding the "status" field$collection->updateOne(    ['email' => 'anna@example.com'],    ['$set' => ['status' => 'active']]);

// Deleting$collection->deleteOne(['email' => 'anna@example.com']);

Comparison of Approaches: When to Choose PostgreSQL and When MongoDB