PHP and PostgreSQL: A Powerful Duo for Web Development

Why PHP and PostgreSQL is Love at First Query?

The world of web development is full of stereotypes. One of them says: "PHP only friends with MySQL." There is some truth to this, but only until you try the PHP and PostgreSQL stack. It's like switching from a bicycle to a sports motorcycle: the same wheels, but the speed, maneuverability, and reliability are completely different.

PostgreSQL is not just a database. It's an object-relational monster that can work with JSON, geodata, full-text search, and complex transactions. And PHP is the perfect conductor for this orchestra. In this guide, we'll cover everything: from installing the driver to writing production-ready code. Let's go!

1. Installation and Setup: Preparing the Tools

Before writing code, let's make sure PHP knows about PostgreSQL. In most modern PHP distributions (e.g., XAMPP, OpenServer, Docker), the pdo_pgsql extension is already present, but it needs to be activated.

1.1. Enabling the Extension in php.ini

Open your php.ini and uncomment (remove the semicolon at the beginning) the line:

extension=pdo_pgsql

If you are using Linux, the installation looks like this:

sudo apt-get install php-pgsql\nsudo service apache2 restart  # or nginx + php-fpm

1.2. Creating a Database and User

Log into the PostgreSQL console:

sudo -u postgres psql

Now let's create a database and user for our project:

CREATE DATABASE pythonlib_blog;\nCREATE USER myapp WITH PASSWORD 'SuperSecret123';\nGRANT ALL PRIVILEGES ON DATABASE pythonlib_blog TO myapp;\n\\q

Done. Now we have a database pythonlib_blog and a user myapp.

2. First Connection: PDO as the Standard

Never use the old pg_connect() functions. In modern PHP, there is PDO (PHP Data Objects) — a unified interface for working with different databases. It is safer, more convenient, and supports prepared statements.

2.1. Wrapper Class for Connection

Let's create a simple Database class that will manage the connection:

<?php\n\nclass Database {\n    private static ?PDO $instance = null;\n    private const DB_HOST = 'localhost';\n    private const DB_PORT = 5432;\n    private const DB_NAME = 'pythonlib_blog';\n    private const DB_USER = 'myapp';\n    private const DB_PASS = 'SuperSecret123';\n\n    public static function getConnection(): PDO {\n        if (self::$instance === null) {\n            $dsn = "pgsql:host=" . self::DB_HOST . ";port=" . self::DB_PORT . ";dbname=" . self::DB_NAME;\n            try {\n                self::$instance = new PDO($dsn, self::DB_USER, self::DB_PASS, [\n                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,\n                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,\n                    PDO::ATTR_EMULATE_PREPARES => false,\n                ]);\n            } catch (PDOException $e) {\n                die("Connection failed: " . $e->getMessage());\n            }\n        }\n        return self::$instance;\n    }\n}

Why is this cool? First, singleton — we don't create a new connection on every request. Second, ATTR_EMULATE_PREPARES => false forces PostgreSQL to use real prepared statements, not emulation on the PHP side. This improves performance and security.

3. CRUD Operations: Create, Read, Update, Delete

Now for the meat. Let's say we have an articles table for articles on pythonlib.ru.

3.1. Creating the Table

Execute this SQL in your database (via psql or admin panel):

CREATE TABLE articles (\n    id SERIAL PRIMARY KEY,\n    title VARCHAR(255)