中文

A JSON REST API with PDO

AI-assistedDrafted with AI assistance and reviewed before publishing; examples are version-pinned. Verify against official docs when in doubt.

PHP 8.4

Single-page front ends, mobile clients and third-party integrations all need an API. This chapter builds CRUD with PDO and a small routing table — no framework — and gets the parts that actually matter right: status codes, one error shape, and validation before anything touches the database.

The interface

Method Path Purpose Success
GET /api/posts paginated list 200
GET /api/posts/{id} one record 200 / 404
POST /api/posts create 201 + Location
PUT /api/posts/{id} full replace 200 / 404
PATCH /api/posts/{id} partial update 200 / 404
DELETE /api/posts/{id} delete 204 / 404

One error shape for everything:

{ "error": { "code": "validation_failed", "message": "title cannot be empty", "fields": { "title": "required" } } }

Clients then have exactly two things to read: the HTTP status and error.code.

Entry point and routing

<?php
// public/index.php
declare(strict_types=1);

require __DIR__ . '/../src/bootstrap.php';

header('Content-Type: application/json; charset=utf-8');
header('X-Content-Type-Options: nosniff');

$method = $_SERVER['REQUEST_METHOD'];
$path = parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH) ?? '/';
$path = rtrim($path, '/') ?: '/';

try {
    route($method, $path, $pdo);
} catch (ApiError $e) {
    respond($e->status, ['error' => $e->payload()]);
} catch (Throwable $e) {
    error_log((string) $e);
    respond(500, ['error' => ['code' => 'internal_error', 'message' => 'Internal server error']]);
}

route() matches parameterised paths with a regular expression:

function route(string $method, string $path, PDO $pdo): void
{
    if (preg_match('#^/api/posts/(\d+)$#', $path, $m)) {
        $id = (int) $m[1];
        match ($method) {
            'GET'    => showPost($pdo, $id),
            'PUT'    => updatePost($pdo, $id, true),
            'PATCH'  => updatePost($pdo, $id, false),
            'DELETE' => deletePost($pdo, $id),
            default  => throw new ApiError(405, 'method_not_allowed', 'Method not allowed'),
        };
    }

    if ($path === '/api/posts') {
        match ($method) {
            'GET'  => listPosts($pdo),
            'POST' => createPost($pdo),
            default => throw new ApiError(405, 'method_not_allowed', 'Method not allowed'),
        };
    }

    throw new ApiError(404, 'not_found', 'Endpoint not found');
}

A 405 response should carry an Allow header listing what the resource does accept — clients and API explorers use it. Set it per path group where you know the set.

Response helpers

<?php
// src/bootstrap.php
declare(strict_types=1);

$pdo = new PDO('sqlite:' . __DIR__ . '/../storage/app.db', null, null, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

final class ApiError extends RuntimeException
{
    public function __construct(
        public readonly int $status,
        public readonly string $code,
        string $message,
        public readonly array $fields = [],
    ) {
        parent::__construct($message);
    }

    /** @return array<string, mixed> */
    public function payload(): array
    {
        $data = ['code' => $this->code, 'message' => $this->getMessage()];
        if ($this->fields !== []) {
            $data['fields'] = $this->fields;
        }
        return $data;
    }
}

function respond(int $status, mixed $data = null, array $headers = []): never
{
    http_response_code($status);
    foreach ($headers as $name => $value) {
        header("{$name}: {$value}");
    }

    if ($status !== 204 && $data !== null) {
        echo json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    }
    exit;
}

function json_body(): array
{
    $raw = file_get_contents('php://input') ?: '';
    if ($raw === '') {
        return [];
    }

    try {
        $data = json_decode($raw, true, 512, JSON_THROW_ON_ERROR);
    } catch (JsonException) {
        throw new ApiError(400, 'invalid_json', 'Request body is not valid JSON');
    }

    if (!is_array($data)) {
        throw new ApiError(400, 'invalid_json', 'Request body must be a JSON object');
    }
    return $data;
}

respond() ends the request with exit, so no handler can accidentally append output after the JSON.

Schema

CREATE TABLE IF NOT EXISTS posts (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    title      TEXT NOT NULL,
    status     TEXT NOT NULL DEFAULT 'draft',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

SQLite is enough to start. Moving to MySQL means AUTO_INCREMENT instead of AUTOINCREMENT and DATETIME columns — the PHP code stays the same.

Validation

function validatePost(array $input, bool $partial = false): array
{
    $fields = [];

    $title = $input['title'] ?? null;
    if ($title === null) {
        if (!$partial) $fields['title'] = 'required';
    } elseif (!is_string($title) || trim($title) === '') {
        $fields['title'] = 'must not be empty';
    } elseif (mb_strlen($title) > 120) {
        $fields['title'] = 'at most 120 characters';
    }

    $status = $input['status'] ?? null;
    if ($status !== null && !in_array($status, ['draft', 'published'], true)) {
        $fields['status'] = 'must be draft or published';
    }

    if ($fields !== []) {
        throw new ApiError(422, 'validation_failed', 'Validation failed', $fields);
    }

    return array_filter([
        'title'  => is_string($title) ? trim($title) : null,
        'status' => $status,
    ], static fn ($v) => $v !== null);
}

Use 422 for semantically invalid input and 400 for malformed JSON. Clients branch on the difference: 400 means “fix your client”, 422 means “show the user what to change”.

Listing and pagination

function listPosts(PDO $pdo): never
{
    $page = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT) ?: 1;
    $perPage = filter_input(INPUT_GET, 'per_page', FILTER_VALIDATE_INT) ?: 20;
    $page = max(1, $page);
    $perPage = min(100, max(1, $perPage));
    $offset = ($page - 1) * $perPage;

    $sortable = ['id', 'created_at', 'title'];
    $sort = in_array($_GET['sort'] ?? '', $sortable, true) ? $_GET['sort'] : 'id';
    $dir = ($_GET['dir'] ?? 'desc') === 'asc' ? 'ASC' : 'DESC';

    $total = (int) $pdo->query('SELECT COUNT(*) FROM posts')->fetchColumn();

    $stmt = $pdo->prepare("SELECT id, title, status, created_at FROM posts ORDER BY {$sort} {$dir} LIMIT :limit OFFSET :offset");
    $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();

    respond(200, [
        'data' => $stmt->fetchAll(),
        'meta' => [
            'page'     => $page,
            'per_page' => $perPage,
            'total'    => $total,
            'pages'    => (int) ceil($total / $perPage),
        ],
    ]);
}

Sort columns come from a whitelist and never from the request directly; LIMIT and OFFSET are bound as integers. A cap on per_page is not optional — per_page=100000 is a denial of service with a friendly name.

Reading one record

function findPostOrFail(PDO $pdo, int $id): array
{
    $stmt = $pdo->prepare('SELECT id, title, status, created_at, updated_at FROM posts WHERE id = :id');
    $stmt->execute([':id' => $id]);
    $post = $stmt->fetch();

    if ($post === false) {
        throw new ApiError(404, 'not_found', 'Post not found');
    }
    return $post;
}

function showPost(PDO $pdo, int $id): never
{
    respond(200, ['data' => findPostOrFail($pdo, $id)]);
}

Creating

function createPost(PDO $pdo): never
{
    $data = validatePost(json_body());

    $stmt = $pdo->prepare(
        'INSERT INTO posts (title, status, created_at, updated_at) VALUES (:title, :status, :now, :now)'
    );
    $stmt->execute([
        ':title'  => $data['title'],
        ':status' => $data['status'] ?? 'draft',
        ':now'    => date('c'),
    ]);

    $id = (int) $pdo->lastInsertId();
    respond(201, ['data' => findPostOrFail($pdo, $id)], [
        'Location' => "/api/posts/{$id}",
    ]);
}

A 201 must include Location, otherwise the client has no idea where the new resource lives.

Updating and deleting

function updatePost(PDO $pdo, int $id, bool $full): never
{
    findPostOrFail($pdo, $id);                 // 404 before touching the body
    $data = validatePost(json_body(), !$full);

    if ($data === []) {
        throw new ApiError(422, 'validation_failed', 'Nothing to update', ['body' => 'provide at least one field']);
    }

    $sets = [];
    $params = [':id' => $id];
    foreach ($data as $column => $value) {
        $sets[] = "{$column} = :{$column}";
        $params[":{$column}"] = $value;
    }
    $params[':updated'] = date('c');

    $sql = 'UPDATE posts SET ' . implode(', ', $sets) . ', updated_at = :updated WHERE id = :id';
    $pdo->prepare($sql)->execute($params);

    respond(200, ['data' => findPostOrFail($pdo, $id)]);
}

function deletePost(PDO $pdo, int $id): never
{
    $stmt = $pdo->prepare('DELETE FROM posts WHERE id = :id');
    $stmt->execute([':id' => $id]);

    if ($stmt->rowCount() === 0) {
        throw new ApiError(404, 'not_found', 'Post not found');
    }
    respond(204);
}

The column names in $sets come from the fixed key set returned by validatePost(), not from user input — that is why concatenation is safe there. Values are still bound.

Verifying with curl

curl -s "http://localhost:8000/api/posts?page=1&per_page=5"
curl -s -X POST http://localhost:8000/api/posts \
  -H "Content-Type: application/json" \
  -d '{"title":"First post","status":"draft"}'
curl -s -i -X PATCH http://localhost:8000/api/posts/1 \
  -H "Content-Type: application/json" -d '{"status":"published"}'
curl -s -i -X DELETE http://localhost:8000/api/posts/1
curl -s -X POST http://localhost:8000/api/posts -H "Content-Type: application/json" -d '{"title":""}'

Check each response against the table: 201 with Location, 422 with fields, 204 with an empty body, 404 as JSON rather than an HTML error page.

Common mistakes

1. Returning 200 for failures

The client then has to parse the body to guess whether the call worked. Let the status code carry the outcome.

2. Emitting HTML error pages

display_errors=1 or a framework error page breaks the JSON contract. Pin Content-Type: application/json at the entry point and route every exception through respond().

3. Confusing [] and {}

json_decode($raw, true) maps both to an array. If the distinction matters, decode to objects (json_decode($raw)) and check.

4. Concatenating ORDER BY or LIMIT

Whitelist sort columns and cast pagination parameters. Prepared statements cannot help with identifiers.

5. Treating PATCH and PUT as the same

PUT replaces the resource (missing fields should be cleared or rejected); PATCH updates part of it. The code above lets PUT behave like PATCH for brevity — document that if you ship it.

6. No limit on page size

per_page=100000 will drag the database down. Cap it at 100 and clamp or reject beyond that.

7. No request body limit

A huge JSON payload eats memory. Limit client_max_body_size at the web server and check CONTENT_LENGTH in PHP.

8. Ignoring retries and idempotency

A network retry can create duplicates. If you need idempotent writes, accept an Idempotency-Key header and store the processed keys.

Summary

  • Path regex plus method dispatch is enough for a small REST API.
  • Status codes carry meaning: 201 with Location, 204 without a body, 422 with field errors, 405 with Allow.
  • Validation is centralised in validatePost(), which returns a controlled set of columns to build SQL from.
  • Pagination parameters are cast and capped; sort columns come from a whitelist.
  • Every exception collapses into one JSON shape — a stack trace never reaches the client.

Next: back to the todo app, or revisit PDO and prepared statements.

Tags