PHP-CRUD-API Documentation

When working with database fields that store JSON strings, the API can treat them as structured objects. This allows for nested property access and updates (e.g., updating properties.model within a JSON field).

Note that JSON string fields cannot be partially updated; the entire JSON object must be provided. This middleware is disabled by default and must be enabled via the middlewares configuration setting.

// Example of a JSON field structure handled by the middleware
{
    "id": 1,
    "name": "Calculator",
    "price": "23.01",
    "properties": {
        "depth": false,
        "model": "TRX-120",
        "width": 100,
        "height": null
    }
}

Instead of application-level middleware, you can use database-level permissions (SQL GRANT). To support this, you must use the reconnect middleware to switch database users per request.

Note: The OpenAPI specification will not reflect these permissions because they are not read during the reflection step.

'reconnect.usernameHandler' => function () {
    return 'mevdschee';
},
'reconnect.passwordHandler' => function () {
    return 'secret123';
},

PHP-CRUD-API is a single-file application. You can install it by downloading the api.php file directly from the latest release or via the raw GitHub URL. Once downloaded, upload it to your webserver and configure the database connection at the bottom of the file.

For local development, you can use PHP's built-in web server:

php -S localhost:8080

After starting the server, you can test the installation by accessing a resource via URL, for example: http://localhost:8080/api.php/records/posts/1

The xml middleware allows you to translate input and output between JSON and XML. To request an XML response instead of the default JSON, use the format=xml query parameter.

This middleware is disabled by default and must be enabled via the middlewares configuration setting.

GET /records/posts/1?format=xml

You can filter a list based on values in related tables by prefixing the column name with the table path (the same paths used in join). This uses an SQL EXISTS sub-query, meaning the filter keeps the parent record if at least one related record matches, but it does not filter the nested related records themselves.

Example: To find posts that have a comment containing 'great': GET /records/posts?filter=comments.message,cs,great

GET /records/posts?filter=comments.message,cs,great
GET /records/posts?filter=categories.name,eq,announcement

You can extend the API by creating custom controller classes. A controller must implement a constructor that accepts five specific parameters: Router, Responder, GenericDB, ReflectionService, and Cache. You use the Router to register your custom endpoints.

use Psr//... (imports)

class MyHelloController {
    private $responder;

    public function __construct(Router $router, Responder $responder, GenericDB $db, ReflectionService $reflection, Cache $cache)
    {
        $router->register('GET', '/hello', array($this, 'getHello'));
        $this->responder = $responder;
    }

    public function getHello(ServerRequestInterface $request): ResponseInterface
    {
        return $this->responder->success(['message' => "Hello World!"]);
    }
}

You can perform operations on multiple records at once by specifying multiple primary keys in the URL.

• Batch Read: GET /records/{table}/{id1},{id2}. Returns an array of objects.
• Batch Update: PUT /records/{table}/{id1},{id2}. The body must be an array of objects corresponding to the IDs in the URL. Returns an array of results (e.g., [1, 1]).
• Batch Create: POST /records/{table}. The body must be an array of objects. Returns an array of new primary keys.
• Batch Delete: DELETE /records/{table}/{id1},{id2}. No body required. Returns the number of deleted rows.

Transactions: Batch operations are wrapped in a database transaction. If any operation fails, the entire batch is rolled back. On failure, the status code is 424 (Failed Dependency) and the response body contains an array of error documents.

To run the functional tests included in the repository, use the build.php and test.php scripts.

php build.php
php test.php

API key authentication works by sending a key in a request header. Include apiKeyAuth in your middlewares list.

Configuration Parameters:

• apiKeyAuth.mode: Set to optional to allow anonymous access (default: required).
• apiKeyAuth.header: The name of the API key header (default: X-API-Key).
• apiKeyAuth.keys: A comma-separated list of valid API keys.

Usage Example:

X-API-Key: 02c042aa-c3c2-4d11-9dae-1a6e230ea95e

Session Info:

• The authenticated API key is stored in $_SESSION['apiKey'].
• This method does not require or use session cookies.

To use PHP-CRUD-API, you need:

• PHP 7.2 or higher with PDO drivers enabled.
• Supported Databases:
  • MySQL 5.7 / MariaDB 10.0+ (Spatial features supported)
  • PostgreSQL 9.5+ with PostGIS 2.2+ (Spatial features supported)
  • SQL Server 2017+ (2019+ for Linux support)
  • SQLite 3.22+ (Spatial features NOT supported)

If you choose to use Redis, Memcache, or Memcached as your cacheType, you must install the corresponding PHP extensions and services on your system:

• For Redis: sudo apt install php-redis redis
• For Memcache: sudo apt install php-memcache memcached
• For Memcached: sudo apt install php-memcached memcached