Database

Connecting PHP to PostgreSQL with PDO for persistent data storage

Introduction

In Tutorial 05, we built a REST API that stored data in a JSON file (items.json). That approach works for learning, but it has real limitations: no concurrent access safety, no way to query or filter data efficiently, and no data integrity guarantees. In this module, we move to PostgreSQL — a production-grade relational database — and connect to it from PHP using PDO (PHP Data Objects).

Module 05 (flat file):              Module 06 (database):

Browser --> Apache --> PHP          Browser --> Apache --> PHP
                        |                                   |
                  Read/write                          PDO connection
                  items.json                                |
                        |                            PostgreSQL
                  JSON array                         +--------------+
                  in a file                          | stories table |
                                                     |  id, title,  |
                                                     |  status, ... |
                                                     +--------------+

The API surface stays the same — clients still send the same HTTP requests and receive the same JSON responses. The only thing that changes is how we store and retrieve data on the server side.

Prerequisites

Before starting, make sure you have:

  1. PHP installed with the pdo_pgsql extension. Check with: php -m | grep pdo_pgsql
  2. PostgreSQL installed and running. Check with: psql --version
  3. A PostgreSQL user that can create databases (typically the postgres superuser)

Creating the Database

First, create the PostgreSQL database and load the schema:

# Create the database createdb stories_demo # Load the schema and seed data psql stories_demo < schema.sql

The schema.sql file creates a stories table and inserts three sample rows:

CREATE TABLE stories ( id SERIAL PRIMARY KEY, title VARCHAR(200) NOT NULL, description TEXT, priority VARCHAR(10) DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high')), status VARCHAR(20) DEFAULT 'todo' CHECK (status IN ('todo', 'in-progress', 'done')), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); INSERT INTO stories (title, description, priority, status) VALUES ('Setup project', 'Initialize the repository and install dependencies', 'high', 'done'), ('Design database schema', 'Define tables for user stories', 'high', 'in-progress'), ('Build REST API', 'Create CRUD endpoints for stories', 'medium', 'todo');

Verify the data loaded correctly:

# Connect to the database psql stories_demo # List tables \dt # Query the data SELECT * FROM stories; # Exit \q

Key points about the schema:

  • SERIAL auto-generates incrementing integer IDs
  • NOT NULL on title enforces that every story must have a name
  • CHECK constraints limit priority and status to valid values — the database itself rejects bad data
  • DEFAULT CURRENT_TIMESTAMP auto-fills the creation time

PDO Connection

PHP Data Objects (PDO) is PHP's standard database abstraction layer. It provides a consistent interface for connecting to different databases (PostgreSQL, MySQL, SQLite, etc.). The connection requires a DSN (Data Source Name) string that specifies the driver, host, and database name:

PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ]); ?>

The three PDO options we set are important:

Option Value Why
ATTR_ERRMODE ERRMODE_EXCEPTION Throws exceptions on errors instead of silently failing. This lets us use try/catch for proper error handling.
ATTR_DEFAULT_FETCH_MODE FETCH_ASSOC Returns rows as associative arrays ($row['title']) instead of both numeric and associative indexes.
ATTR_EMULATE_PREPARES false Uses real database-level prepared statements instead of PHP emulation. This is more secure and lets PostgreSQL optimize the query plan.

Set these in your Apache config, .env file, or shell: export DB_PASSWORD=secret

CRUD Operations with Prepared Statements

PDO prepared statements separate SQL structure from data values. You write the query with named placeholders (:title, :id), then pass the actual values in a separate step. The database driver handles escaping and type safety.

INSERT — Create a Story

prepare( 'INSERT INTO stories (title, description, priority, status) VALUES (:title, :description, :priority, :status) RETURNING *' ); $stmt->execute([ ':title' => trim($data['title']), ':description' => $data['description'] ?? null, ':priority' => $data['priority'] ?? 'medium', ':status' => $data['status'] ?? 'todo', ]); $newStory = $stmt->fetch(); ?>

RETURNING * is a PostgreSQL feature that returns the inserted row, including the auto-generated id and created_at. This saves a second query.

Compare to Tutorial 05 (JSON file):

$maxId) $maxId = $i['id']; } $newItem = ['id' => $maxId + 1, 'name' => $data['name'], 'completed' => false]; $items[] = $newItem; file_put_contents($dataFile, json_encode($items, JSON_PRETTY_PRINT)); // Tutorial 06: Database handles ID, constraints, timestamps $stmt = $pdo->prepare('INSERT INTO stories (title) VALUES (:title) RETURNING *'); $stmt->execute([':title' => $data['title']]); $newStory = $stmt->fetch(); ?>

SELECT — Read Stories

query('SELECT * FROM stories ORDER BY created_at DESC'); $stories = $stmt->fetchAll(); // Get one story by ID $stmt = $pdo->prepare('SELECT * FROM stories WHERE id = :id'); $stmt->execute([':id' => $id]); $story = $stmt->fetch(); ?>

Notice: query() is used when there are no parameters. prepare() + execute() is used when there are user-supplied values (like :id). This distinction matters for security.

UPDATE — Modify a Story

prepare('SELECT * FROM stories WHERE id = :id'); $stmt->execute([':id' => $id]); $existing = $stmt->fetch(); if (!$existing) { http_response_code(404); echo json_encode(['error' => 'Story not found']); exit; } // Merge: use submitted values, fall back to existing values $title = $data['title'] ?? $existing['title']; $description = $data['description'] ?? $existing['description']; $priority = $data['priority'] ?? $existing['priority']; $status = $data['status'] ?? $existing['status']; $stmt = $pdo->prepare( 'UPDATE stories SET title = :title, description = :description, priority = :priority, status = :status WHERE id = :id RETURNING *' ); $stmt->execute([ ':title' => trim($title), ':description' => $description, ':priority' => $priority, ':status' => $status, ':id' => $id, ]); $updated = $stmt->fetch(); ?>

The merge pattern (check existing, then apply partial updates) lets clients send only the fields they want to change — a partial PUT or PATCH-like behavior.

DELETE — Remove a Story

prepare('DELETE FROM stories WHERE id = :id RETURNING *'); $stmt->execute([':id' => $id]); $deleted = $stmt->fetch(); if (!$deleted) { http_response_code(404); echo json_encode(['error' => 'Story not found']); exit; } echo json_encode(['message' => 'Story deleted', 'story' => $deleted]); ?>

RETURNING * on DELETE gives us the deleted row, so we can confirm what was removed. If fetch() returns false, the ID didn't exist.

Building the API Endpoints

The full db-demo.php script ties the CRUD operations together with HTTP routing. The structure mirrors Tutorial 05 — a switch on the request method — but replaces file operations with PDO queries:

'Method not allowed']); } } catch (PDOException $e) { http_response_code(500); echo json_encode(['error' => 'Database error: ' . $e->getMessage()]); } ?>

The key structural difference from Tutorial 05: the entire switch block is wrapped in a try/catch for PDOException. If any query fails (bad SQL, constraint violation, connection lost), PHP catches the exception and returns a 500 error with details instead of crashing.

Running and Testing

Since db-demo.php is a regular PHP file, Apache serves it automatically if it's in your document root. For local development, you can also use PHP's built-in server:

# Start PHP's built-in development server php -S localhost:8000

Testing with curl

List all stories:

curl http://localhost:8000/db-demo.php/stories

Expected response:

[ { "id": 3, "title": "Build REST API", "description": "Create CRUD endpoints for stories", "priority": "medium", "status": "todo", "created_at": "2025-01-15 10:30:00" }, ... ]

Get a single story:

curl http://localhost:8000/db-demo.php/stories/1

Create a new story:

curl -X POST http://localhost:8000/db-demo.php/stories \ -H "Content-Type: application/json" \ -d '{"title": "Write tests", "description": "Add unit tests for API", "priority": "high"}'

Expected response (201 Created):

{ "id": 4, "title": "Write tests", "description": "Add unit tests for API", "priority": "high", "status": "todo", "created_at": "2025-01-15 11:00:00" }

Update an existing story:

curl -X PUT http://localhost:8000/db-demo.php/stories/1 \ -H "Content-Type: application/json" \ -d '{"status": "done"}'

Delete a story:

curl -X DELETE http://localhost:8000/db-demo.php/stories/1

Expected response:

{ "message": "Story deleted", "story": { "id": 1, "title": "Setup project", "description": "Initialize the repository and install dependencies", "priority": "high", "status": "done", "created_at": "2025-01-15 10:30:00" } }

SQL Injection Prevention

SQL injection is one of the most common and dangerous web vulnerabilities. It happens when user input is concatenated directly into SQL strings, allowing attackers to execute arbitrary SQL commands.

WRONG — String concatenation (vulnerable)

query("SELECT * FROM stories WHERE id = $id"); // An attacker sends: ?id=1; DROP TABLE stories; -- // The query becomes: // SELECT * FROM stories WHERE id = 1; DROP TABLE stories; -- ?>

RIGHT — Prepared statements (safe)

prepare('SELECT * FROM stories WHERE id = :id'); $stmt->execute([':id' => $_GET['id']]); $story = $stmt->fetch(); // Even if attacker sends: ?id=1; DROP TABLE stories; -- // PDO treats the entire value as a string parameter. // The database sees: WHERE id = '1; DROP TABLE stories; --' // Which simply returns no results (no integer match). ?>

With prepared statements, the SQL structure and the data are sent to the database separately. The database parses the SQL first (with placeholders), then binds the values. There is no way for user input to alter the SQL structure.

Comparison: JSON File vs Database

Here's how Tutorial 05's flat-file approach compares to Tutorial 06's PostgreSQL approach:

Aspect JSON File (Tutorial 05) PostgreSQL (Tutorial 06)
Data persistence Stored in a .json file on disk. Entire file read/written on every request. Stored in database tables with ACID guarantees. Only affected rows are read/written.
Concurrent access Unsafe. Two simultaneous writes can corrupt the file or lose data (last-write-wins). Safe. PostgreSQL handles concurrent reads/writes with row-level locking and MVCC.
Query capability Must load all data into memory, then loop through arrays in PHP to search/filter. Full SQL: WHERE, ORDER BY, JOIN, aggregations, indexes for fast lookups.
Data validation Must validate in PHP code. Nothing stops bad data from being written to the file. NOT NULL, CHECK constraints, foreign keys. Database rejects invalid data regardless of how it's inserted.
Setup complexity Zero setup. Just create a .json file. No external services needed. Requires PostgreSQL installed and running, database created, schema loaded, PHP extension enabled.
Scalability Breaks down quickly. Reading a 10MB JSON file on every request is slow. Handles millions of rows with indexes. Connection pooling supports many concurrent users.
Production-ready No. Suitable for prototypes and tutorials only. Yes. PostgreSQL is used in production by companies of all sizes.