Cloudflare D1 Support

[batonac]

Cloudflare D1 is Cloudflare's cloud-native SQLite-based Databse offering. There are Cloudflare D1 bindings for Laravel available, for reference of how to proxy the query and bindings to the D1's /query endpoint in the Cloudflare API.

This would be a powerful addition to the SQLite databse integration, opening up the way to run WordPress Playground on Cloudflare Workers.

[batonac]

Here's the source code for the referenced Cloudflare D1 bindings for Laravel:

================================================================================
File: ./src/CloudflareConnector.php
================================================================================

<?php

namespace XliteDev\LaravelD1;

use Saloon\Http\Connector;

abstract class CloudflareConnector extends Connector
{
    public function __construct(
        protected ?string $token = null,
        public ?string $accountId = null,
        public string $apiUrl = 'https://api.cloudflare.com/client/v4',
    ) {
        $this->withTokenAuth($token);
    }

    public function resolveBaseUrl(): string
    {
        return $this->apiUrl;
    }

    protected function defaultHeaders(): array
    {
        return [
            'Content-Type' => 'application/json',
            'Accept' => 'application/json',
        ];
    }
}


================================================================================
File: ./src/CloudflareD1Connector.php
================================================================================

<?php

namespace XliteDev\LaravelD1;

use Saloon\Http\Response;

class CloudflareD1Connector extends CloudflareConnector
{
    public function __construct(
        public ?string $database = null,
        protected ?string $token = null,
        public ?string $accountId = null,
        public string $apiUrl = 'https://api.cloudflare.com/client/v4',
    ) {
        parent::__construct($token, $accountId, $apiUrl);
    }

    public function databaseQuery(string $query, array $params): Response
    {
        return $this->send(
            new D1\Requests\D1QueryRequest($this, $this->database, $query, $params),
        );
    }
}


================================================================================
File: ./src/CloudflareRequest.php
================================================================================

<?php

namespace XliteDev\LaravelD1;

use Saloon\Contracts\Connector;
use Saloon\Http\Request;

abstract class CloudflareRequest extends Request
{
    protected CloudflareConnector $connector;

    public function __construct($connector)
    {
        $this->connector = $connector;
    }

    protected function resolveConnector(): Connector
    {
        return $this->connector;
    }
}


================================================================================
File: ./src/D1/D1Connection.php
================================================================================

<?php

namespace XliteDev\LaravelD1\D1;

use Illuminate\Database\SQLiteConnection;
use XliteDev\LaravelD1\CloudflareD1Connector;
use XliteDev\LaravelD1\D1\Pdo\D1Pdo;

class D1Connection extends SQLiteConnection
{
    public function __construct(
        protected CloudflareD1Connector $connector,
        protected $config = [],
    ) {
        parent::__construct(
            new D1Pdo('sqlite::memory:', $this->connector),
            $config['database'] ?? '',
            $config['prefix'] ?? '',
            $config,
        );
    }

    protected function getDefaultSchemaGrammar()
    {
        ($grammar = new D1SchemaGrammar)->setConnection($this);

        return $this->withTablePrefix($grammar);
    }

    public function d1(): CloudflareD1Connector
    {
        return $this->connector;
    }
}


================================================================================
File: ./src/D1/D1SchemaGrammar.php
================================================================================

<?php

namespace XliteDev\LaravelD1\D1;

use Illuminate\Database\Schema\Grammars\SQLiteGrammar;
use Illuminate\Support\Str;

class D1SchemaGrammar extends SQLiteGrammar
{
    public function compileTableExists()
    {
        return Str::of(parent::compileTableExists())
            ->replace('sqlite_master', 'sqlite_schema')
            ->__toString();
    }

    public function compileDropAllTables()
    {
        return Str::of(parent::compileDropAllTables())
            ->replace('sqlite_master', 'sqlite_schema')
            ->__toString();
    }

    public function compileDropAllViews()
    {
        return Str::of(parent::compileDropAllViews())
            ->replace('sqlite_master', 'sqlite_schema')
            ->__toString();
    }

    public function compileGetAllTables()
    {
        return Str::of(parent::compileGetAllTables())
            ->replace('sqlite_master', 'sqlite_schema')
            ->__toString();
    }

    public function compileGetAllViews()
    {
        return Str::of(parent::compileGetAllViews())
            ->replace('sqlite_master', 'sqlite_schema')
            ->__toString();
    }
}


================================================================================
File: ./src/D1/Pdo/D1Pdo.php
================================================================================

<?php

namespace XliteDev\LaravelD1\D1\Pdo;

use PDO;
use PDOStatement;
use XliteDev\LaravelD1\CloudflareD1Connector;

class D1Pdo extends PDO
{
    protected array $lastInsertIds = [];

    protected bool $inTransaction = false;

    public function __construct(
        protected string $dsn,
        protected CloudflareD1Connector $connector,
    ) {
        parent::__construct('sqlite::memory:');
    }

    public function prepare($query, $options = []): PDOStatement|bool
    {
        return new D1PdoStatement(
            $this,
            $query,
            $options,
        );
    }

    public function d1(): CloudflareD1Connector
    {
        return $this->connector;
    }

    public function setLastInsertId($name = null, $value = null): void
    {
        if ($name === null) {
            $name = 'id';
        }

        $this->lastInsertIds[$name] = $value;
    }

    public function lastInsertId($name = null): bool|string
    {
        if ($name === null) {
            $name = 'id';
        }

        return $this->lastInsertIds[$name] ?? false;
    }

    public function beginTransaction(): bool
    {
        return $this->inTransaction = true;
    }

    public function commit(): bool
    {
        return ! ($this->inTransaction = false);
    }

    public function inTransaction(): bool
    {
        return $this->inTransaction;
    }
}


================================================================================
File: ./src/D1/Pdo/D1PdoStatement.php
================================================================================

<?php

namespace XliteDev\LaravelD1\D1\Pdo;

use Illuminate\Support\Arr;
use PDO;
use PDOException;
use PDOStatement;

class D1PdoStatement extends PDOStatement
{
    protected int $fetchMode = PDO::FETCH_ASSOC;
    protected array $bindings = [];
    protected array $responses = [];

    public function __construct(
        protected D1Pdo &$pdo,
        protected string $query,
        protected array $options = [],
    ) {
        //
    }

    public function setFetchMode(int $mode, mixed ...$args): bool
    {
        $this->fetchMode = $mode;

        return true;
    }

    public function bindValue($param, $value, $type = PDO::PARAM_STR): bool
    {
        $this->bindings[$param] = match ($type) {
            PDO::PARAM_STR => (string) $value,
            PDO::PARAM_BOOL => (bool) $value,
            PDO::PARAM_INT => (int) $value,
            PDO::PARAM_NULL => null,
            default => $value,
        };

        return true;
    }

    public function execute($params = []): bool
    {
        $this->bindings = array_values($this->bindings ?: $params);

        $response = $this->pdo->d1()->databaseQuery(
            $this->query,
            $this->bindings,
        );

        if ($response->failed() || ! $response->json('success')) {
            throw new PDOException(
                (string) $response->json('errors.0.message'),
                (int) $response->json('errors.0.code'),
            );
        }

        $this->responses = $response->json('result');

        $lastId = Arr::get(Arr::last($this->responses), 'meta.last_row_id', null);

        if (! in_array($lastId, [0, null])) {
            $this->pdo->setLastInsertId(value: $lastId);
        }

        return true;
    }

    public function fetchAll(int $mode = PDO::FETCH_DEFAULT, ...$args): array
    {
        $response = match ($this->fetchMode) {
            PDO::FETCH_ASSOC => $this->rowsFromResponses(),
            PDO::FETCH_OBJ => collect($this->rowsFromResponses())->map(function ($row) {
                return (object) $row;
            })->toArray(),
            default => throw new PDOException('Unsupported fetch mode.'),
        };

        return $response;
    }

    public function rowCount(): int
    {
        return count($this->rowsFromResponses());
    }

    protected function rowsFromResponses(): array
    {
        return collect($this->responses)
            ->map(fn ($response) => $response['results'])
            ->collapse()
            ->toArray();
    }
}


================================================================================
File: ./src/D1/Requests/D1QueryRequest.php
================================================================================

<?php

namespace XliteDev\LaravelD1\D1\Requests;

use XliteDev\LaravelD1\CloudflareRequest;
use Saloon\Contracts\Body\HasBody;
use Saloon\Enums\Method;
use Saloon\Traits\Body\HasJsonBody;

class D1QueryRequest extends CloudflareRequest implements HasBody
{
    use HasJsonBody;

    protected Method $method = Method::POST;

    public function __construct(
        $connector,
        protected string $database,
        protected string $sql,
        protected array $sqlParams,
    ) {
        parent::__construct($connector);
    }

    public function resolveEndpoint(): string
    {
        return sprintf(
            '/accounts/%s/d1/database/%s/query',
            $this->connector->accountId,
            $this->database,
        );
    }

    protected function defaultBody(): array
    {
        return [
            'sql' => $this->sql,
            'params' => $this->sqlParams,
        ];
    }
}


================================================================================
File: ./src/D1ServiceProvider.php
================================================================================

<?php

namespace XliteDev\LaravelD1;

use Illuminate\Support\ServiceProvider;
use XliteDev\LaravelD1\D1\D1Connection;

class D1ServiceProvider extends ServiceProvider
{
    /**
     * Boot the service provider.
     *
     * @return void
     */
    public function boot()
    {
        //
    }

    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        $this->registerD1();
    }

    /**
     * Register the D1 service.
     *
     * @return void
     */
    protected function registerD1()
    {
        $this->app->resolving('db', function ($db) {
            $db->extend('d1', function ($config, $name) {
                $config['name'] = $name;

                $connection = new D1Connection(
                    new CloudflareD1Connector(
                        $config['database'],
                        $config['auth']['token'],
                        $config['auth']['account_id'],
                        $config['api'] ?? 'https://api.cloudflare.com/client/v4',
                    ),
                    $config,
                );

                return $connection;
            });
        });
    }
}