PostgREST for MySQL in PHP

21 Mar 2016 - by 'Maurits van der Schee'

Today I stumbled upon "PostgREST", a generated PostgreSQL REST API. I have built PHP-CRUD-API, a similar tool, for MySQL in PHP.

PostgREST is a standalone web server that turns your database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations.

This great software is written by Joe Nelson and has acquired over 6000 stars on Github and has it's own website on postgrest.com.

Compared to PHP-CRUD-API

Below are a few things that PostgREST does different than PHP-CRUD-API:

Does not support MySQL or SQL Server
Does not generate a Swagger API specification
Harder to modify/hack, supposed to run as-is
Written in Haskell, not in PHP

Compared to PHP-CRUD-API PostgREST is much more full-featured:

Supports composite primary keys
Supports column filters on relations
Can "singularize" related objects
Supports "upserts"
Supports JSON Web Tokens
Uses database permissions as security model
Supports ordering with "nulls last"
Uses "Content-Range" for pagination (RFC7233)
Supports full-text search and like wildcards
Supports searching in data in JSONB fields
Supports CSV output
Supports pagination without count (as an option)
Does not return numbers as strings
Supports casting field types (e.g. "::text")
Supports bulk/batch insert and update
Supports Listen / Notify for external commands
Has schema search path to support versions
Works around information_schema permission limits

I'm not sure all of the above are nessecary features, but it is clear that there is still a lot of work to do on PHP-CRUD-API. I tried to pick 3 things that seemed like a particular good idea and that I did not implement yet:

Support JSON Web Tokens
Support column filters on relations
Support "upserts"

I will add these 3 things on my to-do list and you can expect them to be added in the near future.

(Edit 2016-04-05: Implemented "column filters on relations")

Other reflective APIs

In order to find out what I am missing I investigated some other reflective APIs.

JSON Server is a NodeJS project. Check out the Github readme.
DreamFactory is a Laravel (PHP) project. Docs and Source.
LoopBack is a Express (NodeJS) project. Docs and Source.

Note that JSON server is more suited for prototyping, while DreamFactory is really full-featured and LoopBack is better for high performance APIs.

TQ
dev.com