Building a RESTful API with PHP and SQLite

It's been nearly two decades since I last touched PHP. Back then, CodeIgniter and FuelPHP were the go-to frameworks, and Laravel wasn't even a twinkle in Taylor Otwell's eye. As a JavaScript developer, I thought of no better way to relearn PHP than to create a framework for it!

Inspired by Hono, a lightweight web framework for JavaScript, I whipped together a simple framework that could parse requests and return responses. I liked Hono's API design, so I tried to closely match what it has to offer and built a nice Developer Experience (DX) around that idea. I called this framework Dumbo.

Turso has an experimental PHP HTTP SDK that works over HTTP only, as well as a PHP SDK that works with embedded replicas using a custom PHP extension.

In this tutorial, we'll walk through the process of setting up and creating this API step by step using the Turso HTTP PHP SDK.

#Prerequisites

Before we begin, make sure you have the following installed:

PHP 8.3 or higher
Composer (for managing dependencies)

#1. Setting Up the Project

First, let's create a new directory for our project and initialize it with Composer:

mkdir my-turso-php-api
cd my-turso-php-api
composer init

Follow the prompts to set up your composer.json file.

#2. Installing Dependencies

We need to install two main dependencies:

composer require darkterminal/turso-client-http notrab/dumbo

#3. Create a new database

Now create a database with a table to store users:

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

Use the button below to create a new database with the above schema:

Next create a database token, and construct a string that includes your database URL and auth token that can be passed to the LibSQL constructor:

dbname=libsql://...&authToken=...

You'll likely to want to set these as environment variables in your production environment.

#4. Creating the API

Now, let's create our index.php file and start building our API:

<?php
require "vendor/autoload.php";

use Darkterminal\TursoHttp\LibSQL;
use Dumbo\Dumbo;

$dsn = "dbname=libsql://...&authToken=...";
$client = new LibSQL($dsn);

$app = new Dumbo();

#5. Implementing CRUD operations

Now, let's implement the CRUD (Create, Read, Update, Delete) operations for our users:

#Read All Users

Routes can be defined using ->get, ->post, ->put, and ->delete methods. The first argument is the route path, and the second is a callback function that receives the context object.

Let's create the route for retrieving all users at GET /users:

$app->get("/users", function ($context) use ($client) {
    $result = $client->query("SELECT * FROM users")->fetchArray(LibSQL::LIBSQL_ASSOC);

    return $context->json($result);
});

The part ->fetchArray(LibSQL::LIBSQL_ASSOC) fetches the result of the query as an associative array. The LibSQL::LIBSQL_ASSOC constant specifies that the result should be returned as an associative array (column names as keys).

#Read a Single User

Next up, let's create a route for retrieving a single user by ID at GET /users/:id:

$app->get("/users/:id", function ($context) use ($client) {
    $id = $context->req->param("id");

    $result = $client->query("SELECT * FROM users WHERE id = ?", [$id])->fetchArray(LibSQL::LIBSQL_ASSOC);

    if (empty($result)) {
        return $context->json(["error" => "User not found"], 404);
    }

    return $context->json($result[0]);
});

Since results are returned in an array, we'll return the first item in the array.

#Create a User

Now, let's create a route for creating a new user at POST /users.

We'll do some basic validation to ensure that the name and email fields are provided. You'll likely want to use a library that handles this more elegantly in a production application.

If the validation passes, we'll insert the new user into the database and return the ID of the newly created user:

$app->post("/users", function ($context) use ($client) {
    $body = $context->req->body();

    if (!isset($body["name"]) || !isset($body["email"])) {
        return $context->json(["error" => "Name and email are required"], 400);
    }

    $result = $client->prepare("INSERT INTO users (name, email) VALUES (?, ?) RETURNING id")
        ->query([$body["name"], $body["email"]])
        ->fetchArray(LibSQL::LIBSQL_ASSOC);

    return $context->json(["id" => $result[0]["id"]], 201);
});

#Update a User

Next, let's create a route for updating a user at PUT /users/:id.

The validation here checks that at least one field was passed, but depending on the field that is passed, it will update the set clause.

When updated, the route will return the updated user:

$app->put("/users/:id", function ($context) use ($client) {
    $id = $context->req->param("id");

    $body = $context->req->body();

    if (!isset($body["name"]) && !isset($body["email"])) {
        return $context->json(["error" => "Name or email is required"], 400);
    }

    $setClause = [];
    $params = [];

    if (isset($body["name"])) {
        $setClause[] = "name = ?";
        $params[] = $body["name"];
    }

    if (isset($body["email"])) {
        $setClause[] = "email = ?";
        $params[] = $body["email"];
    }

    $params[] = $id;

    $result = $client->prepare(
        "UPDATE users SET " .
        implode(", ", $setClause) .
        " WHERE id = ? RETURNING *"
    )->query($params)->fetchArray(LibSQL::LIBSQL_ASSOC);

    if (empty($result)) {
        return $context->json(["error" => "User not found"], 404);
    }

    return $context->json($result[0]);
});

#Delete a User

Finally, let's create a route for deleting a user at DELETE /users/:id, returning the deleted user ID on success:

$app->delete("/users/:id", function ($context) use ($client) {
    $id = $context->req->param("id");

    $result = $client->prepare("DELETE FROM users WHERE id = ?")->execute([$id]);

    if (empty($result)) {
        return $context->json(["error" => "User not found"], 404);
    }

    return $context->json(["message" => "User deleted successfully"]);
});

#6. Running the API

Finally, add this line at the end of your index.php file to run the application:

$app->run();

#7. Testing the API

You can now run your API using PHP's built-in server:

php -S localhost:8000

Test your API using tools like cURL, Postman, or any HTTP client.

Here are some example requests:

Create a new user:

curl -X POST http://localhost:8000/users \
     -H "Content-Type: application/json" \
     -d '{"name": "John Doe", "email": "john@example.com"}'

Get all users:
```
curl http://localhost:8000/users
```
Get a single user
```
curl http://localhost:8000/users/1
```

Update a user

curl -X PUT http://localhost:8000/users/1 \
     -H "Content-Type: application/json" \
     -d '{"name": "Jane Doe"}'

Delete a user

curl -X DELETE http://localhost:8000/users/1

#Final thoughts

That's it! We've created a simple CRUD API using PHP with Dumbo and Turso — get the code.

Remember, this is just a starting point. In a production environment, you'd want to add error handling, input validation using a package, authentication, configure CORS, and other security measures. You might also want to structure your application into separate files for better organization as it grows.

Happy coding, and welcome back to the world of PHP!

Huge thanks to DarkTerminal for creating and maintaining the Turso PHP SDKs.