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.