PHP-CRUD-API now supports PostgreSQL 9

For the pasts months I have been building PHP-CRUD-API (formerly MySQL-CRUD-API). It is a single PHP file that provides an instant powerful and consistent REST API for a MySQL, PostgreSQL or MS SQL Server database. The application uses reflection to “detect” the table structure and then provide an API in only a few hundred lines of PHP code. It is quite comparable to the REST functionality of the experimental HTTP plugin in MySQL 5.7.

Production performance < 10 ms

I recently finished the test suites and started using it in production. I’ve added some simple .htaccess based firewalling to ensure that only trusted applications can talk to it. Most REST API calls are handled well under 10 ms, so the performance impact on the consuming web application is acceptable. It manages to keep page loads under 100 ms even when doing several REST API calls.

PostgreSQL support and more

Recently PostgreSQL was added as a supported database. With the addition of this third database backend I also changed the name from MySQL-CRUD-API to PHP-CRUD-API. Other features that were recently added are support for “CORS pre-flight requests” (mainly for AngularJS) and “JSON batch insert”. Feature requests are very welcome on the php-crud-api Github page.

Contributions / Future

If you feel like contributing, then maybe these topics inspire you:

  1. Set up Travis automated tests
  2. Add an API documentation generator
  3. Create a plugin system for authentication, authorization and accounting
  4. Port to NodeJS, Java or C#

If you like the project, please give it a star on Github!

3 popular GOTO conference talks

GOTO conferences are for developers by developers. On gotocon.com you find the upcoming conferences:

  • GOTO London: Sep. 14 – 18, 2015
  • GOTO Copenhagen: Oct. 5 – 8, 2015
  • GOTO Berlin: Dec. 2 – 4, 2015
  • GOTO Chicago: May 2016
  • GOTO Amsterdam: June 13 – 15, 2016

There have already been many GOTO conferences. Many of the past talks are available on YouTube. Below you find 3 interesting talks from the YouTube GOTO conference channel.

1) Introduction to NoSQL

goto_happy_unhappy_sql

A GOTO classic from the 2012 Aarhus conference. This is actually the most viewed talk on the YouTube GOTO conference channel. Martin Fowler explains what NoSQL is and when it needs to be applied. Like no other he explains the advantages of SQL and the circumstances under which choosing NoSQL may make sense. Apart from the NoSQL topics, his explanation of off-line locks and document based databases is so good that is enough reason by itself to watch this video.

2) Challenges in implementing microservices

goto_how_fast_can_you_go

This video was recorded more recently (August 2015) in Amsterdam. Fred George explains how Web and SOA have led to new paradigms for structuring enterprise applications. Instead of a few, business­ related services, he developed systems made of many small short-lived services. This approach is called “micro­services” and Fred George talks about his experience building applications in this paradigm.

3) How Go is making us faster

goto_go_is_making_us_faster

In July 2015 Wilfried Schobeiri explained in Chicago on a GOTO conference why Go is a good match for microservices. I’m not sure about the factual correctness of the speed comparisons with C++ and Java, but Go is definitely very fast. The thing to take away from this talk is that Go might be a great match for microservices, since it has a nice standard library/toolset, easy parallelism/concurrency and simple deployment.

Data-driven API design

We live in a world of APIs. LeaseWeb is an infrastructure provider that has taken on an API-first approach. This means that we first build an API for our products. Only after the API is finished we build the UI for the product and we do this on top of our own API. This “eat your own dog food” principle ensures a high quality API in which bugs are found before the customer encounters them.

Our “bare metal” (formerly: dedicated server) team has an API for switch port management. Opening a switch port may (dependent on the switch brand) take up several seconds. This is because (for instance) a SSH connection has to be made to the switch, then an XML command and answer has to be sent back and forth. Also, some switches may wait a few seconds to respond with a status, as they are trying to identify the link status, after the port is opened.

But what if a customer requests a “faster” API? We can’t make opening or closing a switch port go faster. What to do? If any API call would only modify a single record in the database, then the execution time would be predictable (and always low). To achieve this there are two API designs possible: “asynchronous” and “data-driven”. In this post we will explore both and show you why a data-driven API is a better choice for consistent APIs.

Synchronous API design

This is the naive implementation. It allows you to Open/Enable the switch port attached to the server with a synchronous call:

POST /bareMetals/{bareMetalId}/switchPort/open

Parameters:

bareMetalId (integer) - The id of the bare metal server

Example request:

POST /v1/bareMetals/1234/switchPort/open HTTP/1.1
Host: api.leaseweb.com
Accept: application/json
X-Lsw-Auth: 4e7d4f2d-e683-4192-a113-61dad6ac9a15

Example response:

HTTP/1.1 200 OK
Content-Type: text/html

{
	"switchPort": {
		"status": "opened",
		"switchNode": 1,
		"serverId": 1234,
		"serverName": "My Server"
	}
}

Note that this call may take several seconds to complete and does not follow the RESTful resource naming scheme.

Asynchronous API design

If the call is asynchronous then the request is the same:

POST /bareMetals/{bareMetalId}/switchPort/open

Parameters:

bareMetalId (integer) - The id of the bare metal server

Example request:

POST /v1/bareMetals/1234/switchPort/open HTTP/1.1
Host: api.leaseweb.com
Accept: application/json
X-Lsw-Auth: 4e7d4f2d-e683-4192-a113-61dad6ac9a15

Example response:

HTTP/1.1 200 OK
Content-Type: text/html

{
	"jobId": "f5825d99-62ed-433b-930b-1b9561fe4a6e",
	"createdAt": "2015-01-14T15:09:10+01:00"
}

The response is a job identifier that then has to be polled for status, like this:

GET /bareMetals/{bareMetalId}/jobs/{jobId}

Parameters:

bareMetalId (integer) - The id of the bare metal server
jobId (string) - The id of a job

Example request:

GET /v1/bareMetals/1234/jobs/f5825d99-62ed-433b-930b-1b9561fe4a6e HTTP/1.1
Host: api.leaseweb.com
Accept: application/json
X-Lsw-Auth: 4e7d4f2d-e683-4192-a113-61dad6ac9a15

Example response:

HTTP/1.1 200 OK
Content-Type: text/html

{
	"id": "f5825d99-62ed-433b-930b-1b9561fe4a6e",
	"status": 3,
	"statusDescription": "Failed",
	"createdAt": "2015-01-14T15:09:10+01:00",
	"updatedAt": "2015-01-14T15:09:12+01:00"
}

All reponses can be sent really fast as they are simple database lookups. It is important to realize that you need some sort of job manager in the back that does actually execute the jobs and updates the job status accordingly. Note that the commands do not follow the RESTful resource naming scheme.

Data-driven API design

When following data-driven API design you are simply modeling the database tables to facilitate the functionality. It allows you to have fast replies and also follows the RESTful resource naming scheme. You may get the status of the switch port attached to the server with a call like this:

GET /bareMetals/{bareMetalId}/switchPorts

Parameters:

bareMetalId (integer) - The id of the bare metal server

Example request:

GET /v1/bareMetals/1234/switchPorts HTTP/1.1
Host: api.leaseweb.com
Accept: application/json
X-Lsw-Auth: 4e7d4f2d-e683-4192-a113-61dad6ac9a15

Example response:

HTTP/1.1 200 OK
Content-Type: text/html

{
	"switchPorts": [
		{
			"status": "opened",
			"switchNode": 1,
			"serverId": 1234,
			"serverName": "My Server"
		}
	]
}

When you are opening the switch port you are actually inserting a record in the switchPortOpenRequests table with the standard POST as prescribed by the RESTful resource naming scheme, like this:

POST /bareMetals/{bareMetalId}/switchPortOpenRequests

Parameters:

bareMetalId (integer) - The id of the bare metal server

Example request:

POST /v1/bareMetals/1234/switchPortOpenRequests HTTP/1.1
Host: api.leaseweb.com
Accept: application/json
X-Lsw-Auth: 4e7d4f2d-e683-4192-a113-61dad6ac9a15

Example response:

HTTP/1.1 200 OK
Content-Type: text/html

{
	"id": "f5825d99-62ed-433b-930b-1b9561fe4a6e",
	"status": 1,
	"statusDescription": "Pending",
	"createdAt": "2015-01-14T15:09:10+01:00",
	"updatedAt": "2015-01-14T15:09:10+01:00"
}

Just like with the asynchronous model you can poll for status. Note that there is no job identifier, just a normal identifier, as the request is a normal resource that follows the RESTful resource naming scheme. You do still need to have a background process that watches the switchPortOpenRequests table to execute these requests when they are in “pending” state. This background process is responsible for updating both the switchPortOpenRequests and the switchPorts table with the correct status information.

GET /bareMetals/{bareMetalId}/switchPortOpenRequests/{id}

Parameters:

bareMetalId (integer) - The id of the bare metal server
id (string) - The id of the request

Example request:

GET /v1/bareMetals/1234/switchPortOpenRequests/f5825d99-62ed-433b-930b-1b9561fe4a6e HTTP/1.1
Host: api.leaseweb.com
Accept: application/json
X-Lsw-Auth: 4e7d4f2d-e683-4192-a113-61dad6ac9a15

Example response:

HTTP/1.1 200 OK
Content-Type: text/html

{
	"id": "f5825d99-62ed-433b-930b-1b9561fe4a6e",
	"status": 3,
	"statusDescription": "Failed",
	"createdAt": "2015-01-14T15:09:10+01:00",
	"updatedAt": "2015-01-14T15:09:12+01:00"
}

As you can see this last way of modeling does not only reduce every API call to be a database lookup, it also allows you to follow the (predictable) RESTful resource naming scheme.

Generating a data-driven API

The advantage of following an API naming scheme is that the API becomes predictable and can be generated. The PHP-CRUD-API project facilitates creating a full-featured API by uploading a single PHP file. This API will follow the resource naming scheme for your MySQL, PostgreSQL or MS SQL Server tables. With a data-driven API design this should not limit you to create any API functionality. Try it!

PHP-CRUD-API now supports SQL Server 2012

Although the project was initially aimed at only providing support for MySQL, now MS SQL Server 2012 is also supported (and even PostgreSQL). PHP-CRUD-API is a single PHP file that will provide a full REST API for your data structure. With the now added MsSQL_CRUD_API class you can also connect to a SQL Server database. This only works if the SQLSRV driver is installed in PHP and this is only available for Windows.

sql_server

The SQL Server code relies on the “OFFSET” command, which was added to SQL Server in version 2012. It also allows for UTF-8 character encoding, IMHO a character set any modern database should use. The offset command was added by popular demand (to SQL Server), because the pagination in SQL Server was quite cumbersome, especially when compared to MySQL. Now they are on par again. Also the choice of the SQLSRV driver over the more compatible, but inferior, FreeTDS driver was intentional.

SELECT * FROM [posts] ORDER BY [published] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

The response on this project has been mostly positive. There are people arguing that it is bad to expose the data structure of your database. My counter argument is that if you do proper database administration (data management) this is not true. And in that case you do not need all that boilerplate code that APIs generally consist of as it can all be automated. That is this project’s philosophy.

https://github.com/mevdschee/php-crud-api

Check out the code on Github and tell me what you think. Use the comments for feedback.

Simple PHP REST API script for MySQL

With single page applications (or SPAs) becoming very popular very quickly we see a rising need to add APIs for everything. Most companies take a first step by creating a simple data-driven API. I wrote a PHP script that generates a simple and fast REST API from your MySQL tables with full CRUD support. No MySQL? No problem: PostgreSQL and MS SQL Server are also supported. Even pagination and filtering is supported! It is only 450 lines of code, not exactly rocket science, but it may be useful when you need to whip up a Minimum Viable Product (MVP).

Limitations

  • Authentication or authorization is not included
  • Complex queries or transactions are not supported

Features

  • Single PHP file, easy to deploy.
  • Very little code, easy to adapt and maintain
  • Streaming data, low memory footprint
  • Condensed JSON: first row contains field names
  • Blacklist support for tables (and columns, todo)
  • JSONP/CORS support for cross-domain requests
  • Combined requests with support for multiple table names
  • Pagination, sorting and search support
  • Relation detection and filtering on foreign keys
  • Relation “transforms” for PHP and JavaScript

Configuration

This is a single-file application. In the bottom of the file you find the configuration:

$api = new MySQL_CRUD_API(
	"localhost",                        // hostname
	"user",                             // username
	"pass",                             // password
	"db",                               // database
	false,                              // whitelist
	array("users"=>"crudl")             // blacklist
);
$api->executeCommand();

Example output

When you request the URL http://localhost/api.php/cate* you will be matching a single table (“categories”) in the configured database. The (formatted) output would be something like this:

{
    "categories": {
        "columns": [
            "id",
            "name"
        ],
        "records": [
            [
                "1",
                "Internet"
            ],
            [
                "3",
                "Web development"
            ]
        ]
    }
}

As you can see the column names are only at the start of the object and the table name is used as a key on the object allowing for multiple table matches when using a wildcard (star) in the URL.

Get it on Github!

If you want to get it check out my Github page for this little project:

https://github.com/mevdschee/php-crud-api

Contributions, forks and additions are more than welcome.