Add a REST API to an existing database

28 Apr 2022 - by 'Maurits van der Schee'

I've written PHP-CRUD-API (3k Github stars) that lets you publish an instant REST API for an existing database. The latest version supports a mapping to allow you to clean up the names of your tables and columns. Other important features such as authentication and authorization were already supported. This recent addition makes the software better suited to publish a modern API on legacy systems (that run on MySQL, PostgreSQL or SQL Server).

Example mapping configuration

The config allows you to rename tables and columns with a comma separated list of mappings that are split with an equal sign, like this:

'mapping' => 'wp_posts=posts,wp_posts.ID=posts.id',

This specific example will expose the "wp_posts" table at a "posts" end-point (instead of "wp_posts") and the column "ID" within that table as the "id" property (in lower case instead of upper case).

NB: Since these two mappings overlap the first (less specific) mapping may be omitted.

Supporting existing databases

In order to publish an API for your existing database you may require the following features:

mapping: expose tables and columns with better suited naming trough a mapping
authentication: require users to identify themselves when accessing data
authorization: allow certain users access to certain tables and operations

If your database holds geospatial data then it may benefit from the geospatial and GeoJSON support.

Advantages over a custom API

A generated REST API may have 3 important quality advantages over writing your own API software:

consistency: generation of the API ensures consistency of all API end-points
performance: this code has been battle tested to have fairly good performance
documentation: your API will have an OpenAPI specification and documentation

I think that these advantages are essential to making a good API and are otherwise hard to get right.

TQ
dev.com