Blog about software development


PHP-CRUD-API version 2 released

14 Oct 2018 - by 'Maurits van der Schee'

Yesterday I released version 2 of PHP-CRUD-API, the TreeQL reference implementation in a single PHP file. About 6 months ago I wrote about my progress on version 2. I was expecting it would take me about 2 months until I could release a final version 2. It took 6 months and I created 8 beta releases in the process. I'm very thankful to the people that were trying these beta versions out and for the issue reports that I have received.

Removed: streaming data & client-side transformations

In both v1 and v2 the design goal is to have a light-weight REST API implementation. In v1 I believed low memory footprint was important. I implemented the API as a streaming service and that required a JSON transformation on the client-side (in the browser). Streaming is great as it allows you to serve millions of records without using much memory. There were also downsides. Most clients were running JavaScript and JavaScript was not capable of handling these amounts of data. Also people were often disabling the streaming by using server-side transformations as this was more easy to use. In v2 there is no more streaming data and no client-side transformations.

Removed: support for JSON/JSONB

JSON support looked really promising when it was introduced. Unfortunately I never got it working on a level that I was happy about. The database support was limited as SQL Server and older MariaDB versions do not have a JSON data type. The filtering on JSON properties was not standardized and hard to implement. In version 2 JSON fields are supported as strings. This means you can still read and write them as strings containing JSON, but there are no structural or performance benefits.

Removed: support for SQLite

SQLite does not support geospatial (GIS) queries. It also has problems supporting the boolean (and the JSON) data type. It was not used a lot as it had performance problems in high performance use cases. Although I understand SQLite use case (quick mock-ups) I decided to remove support for SQLite for now. This allows me to focus on other (more important) features.

Added: Reflection cache

Version 1 was doing database reflection calls (requesting a table's column definitions) on every request. In version 2 these calls are cached to facilitate high performance usage.

Added: Middleware extension system

In order to extend the software you can now implement "middleware". Middleware allows you to define a handler that gets to modify the request that the browser is sending to the router. It also allows to modify the response before it gets send back to the browser.

Added: Authentication middleware

The most requested feature was an easier to use authentication system. For this the "basic authentication" middleware is now provided. In order to easily hook up social (such as Facebook) authentication systems there is support for an authentication provider (such as Auth0) with the provided JWT authentication middleware. You can also look at PHP-API-AUTH: a single file PHP script that acts as an authentication provider.

Other improvements

Other improvements that have been implemented are:

Work in progress: CRUD on columns

In version 1 the REST end-point the "users" table was "api.php/users" and in version 2 it is "api.php/records/users". This is because there is also support for adjusting the column definitions of the "users" table on the "api.php/columns/users" end-point. This functionality is still work in progress, but the read-only part is fully working. Since the data types are standardized (based on ODBC), they are not specific to the underlying database engine.

Get it here: https://github.com/mevdschee/php-crud-api