Creating a simple REST API in PHP

I’m the author of php-crud-api and I want to share the core of the application with you. It includes routing a JSON REST request, converting it into SQL, executing it and giving a meaningful response. I tried to write the application as short as possible and came up with these 65 lines of code:

<?php

// get the HTTP method, path and body of the request
$method = $_SERVER['REQUEST_METHOD'];
$request = explode('/', trim($_SERVER['PATH_INFO'],'/'));
$input = json_decode(file_get_contents('php://input'),true);

// connect to the mysql database
$link = mysqli_connect('localhost', 'user', 'pass', 'dbname');
mysqli_set_charset($link,'utf8');

// retrieve the table and key from the path
$table = preg_replace('/[^a-z0-9_]+/i','',array_shift($request));
$key = array_shift($request)+0;

// escape the columns and values from the input object
$columns = preg_replace('/[^a-z0-9_]+/i','',array_keys($input));
$values = array_map(function ($value) use ($link) {
  if ($value===null) return null;
  return mysqli_real_escape_string($link,(string)$value);
},array_values($input));

// build the SET part of the SQL command
$set = '';
for ($i=0;$i<count($columns);$i++) {
  $set.=($i>0?',':'').'`'.$columns[$i].'`=';
  $set.=($values[$i]===null?'NULL':'"'.$values[$i].'"');
}

// create SQL based on HTTP method
switch ($method) {
  case 'GET':
    $sql = "select * from `$table`".($key?" WHERE id=$key":''); break;
  case 'PUT':
    $sql = "update `$table` set $set where id=$key"; break;
  case 'POST':
    $sql = "insert into `$table` set $set"; break;
  case 'DELETE':
    $sql = "delete `$table` where id=$key"; break;
}

// excecute SQL statement
$result = mysqli_query($link,$sql);

// die if SQL statement failed
if (!$result) {
  http_response_code(404);
  die(mysqli_error());
}

// print results, insert id or affected row count
if ($method == 'GET') {
  if (!$key) echo '[';
  for ($i=0;$i<mysqli_num_rows($result);$i++) {
    echo ($i>0?',':'').json_encode(mysqli_fetch_object($result));
  }
  if (!$key) echo ']';
} elseif ($method == 'POST') {
  echo mysqli_insert_id($link);
} else {
  echo mysqli_affected_rows($link);
}

// close mysql connection
mysqli_close($link);

This code is written to show you how simple it is to make a fully operational REST API in PHP.

Running

Save this file as “api.php” in your (Apache) document root and call it using:

http://localhost/api.php/{$table}/{$id}

Or you can use the PHP built-in webserver from the command line using:

$ php -S localhost:8888 api.php

The URL when ran in from the command line is:

http://localhost:8888/api.php/{$table}/{$id}

NB: Don’t forget to adjust the ‘mysqli_connect’ parameters in the above script!

REST API in a single PHP file

Although the above code is not perfect it actually does do 3 important things:

  1. Support HTTP verbs GET, POST, UPDATE and DELETE
  2. Escape all data properly to avoid SQL injection
  3. Handle null values correctly

One could thus say that the REST API is fully functional. You may run into missing features of the code, such as:

  1. No related data (automatic joins) supported
  2. No condensed JSON output supported
  3. No support for PostgreSQL or SQL Server
  4. No POST parameter support
  5. No JSONP/CORS cross domain support
  6. No base64 binary column support
  7. No permission system
  8. No search/filter support
  9. No pagination or sorting supported
  10. No column selection supported

Don’t worry, all these features are available in php-crud-api, which you can get from Github. On the other hand, now that you have the essence of the application, you may also write your own!

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!

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.

PHP-CRUD-API now has transforms!

Last week I created a new GitHub project called “PHP-CRUD-API” and it allows you to quickly setup a simple REST API with CRUD functionality by just adding a single “api.php” file to your project and configuring it’s database connection. Today I show how the relational support of this project works.

Supported table relations

There are three types of table relations supported:

  • BelongsTo
  • HasMany
  • HasAndBelongsToMany

Blog example

When you use the “list” command of the API it allows you to specify multiple tables. If these tables have relations (foreign keys) the output will be filtered in such a way that only relevant records are returned. This is what the API outputs when you list posts and comments filtered on a specific post:

{
    "posts": {
        "columns": [
            "id",
            "user_id",
            "category_id",
            "content"
        ],
        "records": [
            [
                "1",
                "1",
                "1",
                "blog started"
            ]
        ]
    },
    "comments": {
        "relations": {
            "post_id": "posts.id"
        },
        "columns": [
            "id",
            "post_id",
            "message"
        ],
        "records": [
            [
                "1",
                "1",
                "great"
            ],
            [
                "2",
                "1",
                "fantastic"
            ]
        ]
    }
}

Not so useful right? You would probably like to see something like this:

{
    "posts": [
        {
            "id": "1",
            "comments": [
                {
                    "id": "1",
                    "post_id": "1",
                    "message": "great"
                },
                {
                    "id": "2",
                    "post_id": "1",
                    "message": "fantastic"
                }
            ],
            "user_id": "1",
            "category_id": "1",
            "content": "blog started"
        }
    ]
}

That is exactly what the function “php_crud_api_transform()” does. You run this function on the client after receiving the API response. This is beneficial as it uses the CPU and RAM of the API consumer instead of that of the API server. This transformation function is implemented in PHP and JavaScript, so that you can make spiders and users with browsers equally happy!

<?php
function php_crud_api_transform(&$tables) {
	$getobjs = function(&$tables,$table_name,$where_index=false,$match_value=false) use (&$getobjs) {
		$objects = array();
		foreach($tables[$table_name]['records'] as $record) {
			if ($where_index===false || $record[$where_index]==$match_value) {
				$object = array();
				foreach ($tables[$table_name]['columns'] as $index=>$column) {
					$object[$column] = $record[$index];
					foreach ($tables as $relation=>$reltable) {
						foreach ($reltable['relations'] as $key=>$target) {
							if ($target == "$table_name.$column") {
								$columnidx = array_flip($reltable['columns']);
								$object[$relation] = $getobjs($tables,$relation,$columnidx[$key],$record[$index]);
							}
						}
					}
				}
				$objects[] = $object;
			}
		}
		return $objects;
	};
	$tree = array();
	foreach ($tables as $name=>$table) {
		if (!isset($table['relations'])) {
			$tree[$name] = $getobjs($tables,$name);
		}
	}
	return $tree;
}

And the JavaScript version:

function php_crud_api_transform(tables) {
	var array_flip = function (trans) {
		var key, tmp_ar = {};
		for (key in trans) {
			tmp_ar[trans[key]] = key;
		}
		return tmp_ar;
	};
	var get_objects = function (tables,table_name,where_index,match_value) {
		var objects = [];
		for (var record in tables[table_name]['records']) {
			record = tables[table_name]['records'][record];
			if (!where_index || record[where_index]==match_value) {
				var object = {};
				for (var index in tables[table_name]['columns']) {
					var column = tables[table_name]['columns'][index];
					object[column] = record[index];
					for (var relation in tables) {
						var reltable = tables[relation];
						for (var key in reltable['relations']) {
							var target = reltable['relations'][key];
							if (target == table_name+'.'+column) {
								column_indices = array_flip(reltable['columns']);
								object[relation] = get_objects(tables,relation,column_indices[key],record[index]);
							}
						}
					}
				}
				objects.push(object);
			}
		}
		return objects;
	};
	tree = {};
	for (var name in tables) {
		var table = tables[name];
		if (!table['relations']) {
			tree[name] = get_objects(tables,name);
		}
	}
	return tree;
}

Check out all the source code on my GitHub account: https://github.com/mevdschee/php-crud-api

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.