PHP: PDO (pgsql)

Connects, creates a todo table if it does not exist, inserts a row and counts open rows. The code is the same against the original database and against MachSQL; only the address changes.

The only change

DB_DSN="pgsql:host=old-postgres;port=5432;dbname=app"
DB_DSN="pgsql:host=machsql-host;port=5432;dbname=app"

Nothing else changes: not the code, not the driver, not its version, not the SQL.

Versions

• PHP 8.5 with the pdo_pgsql extension

Run

export DB_DSN="pgsql:host=machsql-host;port=5432;dbname=app" DB_PASSWORD=...
php main.php

Expected output: inserted todo <id>, <n> open.

Source

The program as it is kept and tested in the Mach Database for PostgreSQL repository; highlighted lines read the connection setting.

main.php

<?php
// PDO with the pgsql driver.
declare(strict_types=1);

$pdo = new PDO(getenv('DB_DSN'), getenv('DB_USER') ?: 'app', getenv('DB_PASSWORD'), [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$pdo->exec('CREATE TABLE IF NOT EXISTS todo (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL, done boolean NOT NULL DEFAULT false)');
$insert = $pdo->prepare('INSERT INTO todo (title) VALUES (?) RETURNING id');
$insert->execute(['write the examples']);
$id = $insert->fetchColumn();
$open = $pdo->query('SELECT count(*) FROM todo WHERE NOT done')->fetchColumn();
printf("inserted todo %d, %d open\n", $id, $open);