06. Database Setup

Introduction

In Module 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).

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)

Demo Files

createdb stories_demo
psql stories_demo < schema.sql
php -S localhost:8000
curl http://localhost:8000/db-demo.php/stories

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:

<?php
// DSN format: driver:host=...;dbname=...
$dsn = 'pgsql:host=localhost;dbname=stories_demo';
$user = 'postgres';
$pass = '';

// Create the connection with options
$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);
?>

The three PDO options we set are important:

<?php
$host   = getenv('DB_HOST')     ?: 'localhost';
$dbname = getenv('DB_NAME')     ?: 'stories_demo';
$user   = getenv('DB_USER')     ?: 'postgres';
$pass   = getenv('DB_PASSWORD') ?: '';
?>

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

<?php
$stmt = $pdo->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 Module 05 (JSON file):

<?php
// Module 05: Manually generate ID, manually write file
$maxId = 0;
foreach ($items as $i) {
    if ($i['id'] > $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));

// Module 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

<?php
// Get all stories, newest first
$stmt = $pdo->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

<?php
// First, check the story exists
$stmt = $pdo->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

<?php
$stmt = $pdo->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 Module 05 — a switch on the request method — but replaces file operations with PDO queries:

<?php
// Headers and CORS setup (same as Module 05)
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type');

if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit;
}

// Database connection (new in Module 06)
$dsn = "pgsql:host=$host;dbname=$dbname";
$pdo = new PDO($dsn, $user, $pass, [ /* options */ ]);

// Parse request (same as Module 05)
$method   = $_SERVER['REQUEST_METHOD'];
$path     = $_SERVER['PATH_INFO'] ?? '/';
$parts    = explode('/', trim($path, '/'));
$resource = $parts[0] ?? '';
$id       = isset($parts[1]) && $parts[1] !== '' ? (int)$parts[1] : null;

// Route by method (same structure, different storage)
try {
    switch ($method) {
        case 'GET':    /* SELECT queries  */ break;
        case 'POST':   /* INSERT query    */ break;
        case 'PUT':    /* UPDATE query    */ break;
        case 'DELETE': /* DELETE query    */ break;
        default:
            http_response_code(405);
            echo json_encode(['error' => 'Method not allowed']);
    }
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Database error: ' . $e->getMessage()]);
}
?>

The key structural difference from Module 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)

<?php
// DANGEROUS: user input directly in SQL string
$id = $_GET['id'];
$result = $pdo->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)

<?php
// SAFE: parameter is bound separately from SQL structure
$stmt = $pdo->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.

For a deeper look at database security, query optimization, and design patterns, see the Database Overview.

Comparison: JSON File vs Database

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