Cacomania: RESTful API with Slim PHP and SQLite


RESTful API with Slim PHP and SQLite

Guido Krömer - 28. April 2013 - Tags: , , ,

I needed a small RESTful API, primarily for playing around with AngularJS. The Slim Framework perfectly fits my needs. It is a PHP micro framework for writing a prototype or really small applications, without the overhead and the steep learning curve of a complete framework likes Yii, Symfony or Zend. At the same time, you get stuck at the Zend2 module configuration, you could have finished a working prototype with Slim.

I used SQLite as database for the API, because SQLite is configuration free it is perfect for prototyping. For keeping the focus, of this tutorial, at the Slim Framework itself, I skipped using any kind of ORM framework. Due the use of PDO any other SQL Database like MySQL could be used, too.

To gain some practical benefit from this tutorial the REST API going to be discussed in this tutorial manages bookmarks in a CRUD style. I hope this help me with my bookmark chaos among Linux, Windows, tablet and smart phone.

Install SQLite & PDO_SQLITE

You may have to install SQLite, if you already have an MySQL Server running you can proceed with "Use MySQL instead of SQLite" and skip this part.

The following packages has to be installed under Ubuntu 12.10 for running SQLite with PHP, for example:

$ apt-get install libsqlite3-0 libsqlite3-dev php5-sqlite

Since SQLite uses a flat file, no configuration or user management has to be done, we are finished with this part and can start playing around with Slim.

Use MySQL instead of SQLite

If you want to use MySQL, you need a database with the table listed below.

CREATE TABLE `bookmark` (  
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
    `title` varchar(255) NOT NULL,  
    `url` varchar(255) NOT NULL,  
    PRIMARY KEY (`id`),  
    UNIQUE KEY `url` (`url`)

The PDO constructor which can be found in the api/index.php file has to be adjusted, too.

$db = new PDO('mysql:host=localhost;dbname=bookmark', 'bookmark_user', 'bookmark_password');

Slim in a nutshell

Since we have a working database we can start using the Slim framework. Let's start with a simple "Hello World" API which does not need a database. Calling the default route the API will output "Hello World" and by calling the route '/hello/YourName' the API will output "Hello YourName" via GET.

The basic bootstrapping of Slim just needs four lines of code, loading the Slim.php file, registering the auto loader, creating a new Slim instance and calling the run() method after all routes has been defined. The method get() has two params, the first one defines the route and the second one is a closure or the name of the function as string which should be called.

require '../Slim/Slim.php';
$app = new SlimSlim();

$app->get('/', function () { //as closure
    echo 'Hello World';

$app->get('/hello/:name', 'helloName'); //classic php style with function name

function helloName($name) {
    echo "Hello $name";



But before you can use your Slim API you need a .htaccess file like this one below, rewriting the routes to the index.php file. In my case the index.php is not located at the servers document root, it is located at the folder api. Therefore I had to add a RewriteBase statement.

RewriteEngine On
RewriteBase /api/
RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule ^ index.php [QSA,L]

Testing with cURL

For testing the API cURL can be used, like in the example below. Since the API only listens to GET requests no additional parameters has to be passed to cURL.

$ curl
Hello World
$ curl
Hello Caco

A RESTful bookmark API with Slim

Do you remember I was writing about a bookmark API at the beginning of this article, here is it. Since all responses of the API are JSON encoded objects the Internet media type has been globally set to application/json at line six. Please note the code makes use of the new PHP 5.4 array syntax, if you want to run the code with PHP 5.3 you have to replace the square bracket arrays with the classical array('foo' => 'bar') syntax. The two functions above the main code are helpers, getTitleFromUrl($url) returns the HTML title from the given URL and returnResult($action, $success = true, $id = 0) is used for returning the result of a POST, PUT or DELETE HTTP request method in a consistent format.

As mentioned before I skipped the usage of any ORM mapper to keep the focus on the Slim framework. Therefore each request method needs the reference of the PDO connection created at line seven, to pass this reference to each closure the use statement has to be used. Slim has a method for each HTTP method which defines the route and the closure which should be called.

Let's take a look at the put() method at line 46, which is used for editing a bookmark. The route tells that a request on bookmark/1 would edit the bookmark with the id 1 performing a DELETE request on the same route would delete the given bookmark. But back to editing the bookmark, at the beginning the update query has to be prepared, after that the execute method will fire the query with the needed query pramater. The query params are passed to the execute() method as array, since I'm not using named parameters the array is a simple array, if I would have used named parameter the array had to be an associate one. After the execution the returnResult() function gets called. For determining if the query was executed successfully the number of affected rows should be equal to one, this done by calling the rowCount() method.

For fetching all bookmarks the route '/bookmark' without an id as HTTP GET performs a simple SELECT * FROM bookmark;. The default PDO fetch behavior is PDO::FETCH_BOTH which would return an array that is column name and null indexed, to keep the amount of data small I used PDO::FETCH_CLASS which returns a PHP std class but PDO::FETCH_ASSOC would work, too. By defining an id the route '/bookmark/:id' as HTTP GET would call the closure which returns just the needed bookmark, the explicit casting of the id by using intval() is just a personal style and is not used for preventing SQL injection since parameterized SQL queries already doing the job.

require '../Slim/Slim.php';

$app = new \Slim\Slim();
$db = new PDO('sqlite:db.sqlite3');

function getTitleFromUrl($url)
    preg_match('/<title>(.+)<\/title>/', file_get_contents($url), $matches);
    return mb_convert_encoding($matches[1], 'UTF-8', 'UTF-8');

function returnResult($action, $success = true, $id = 0)
    echo json_encode([
        'action' => $action,
        'success' => $success,
        'id' => intval($id),

$app->get('/bookmark', function () use ($db, $app) {
    $sth = $db->query('SELECT * FROM bookmark;');
    echo json_encode($sth->fetchAll(PDO::FETCH_CLASS));

$app->get('/bookmark/:id', function ($id) use ($db, $app) {
    $sth = $db->prepare('SELECT * FROM bookmark WHERE id = ? LIMIT 1;');
    echo json_encode($sth->fetchAll(PDO::FETCH_CLASS)[0]);

$app->post('/bookmark', function () use ($db, $app) {
    $title = $app->request()->post('title');
    $sth = $db->prepare('INSERT INTO bookmark (url, title) VALUES (?, ?);');
        $url = $app->request()->post('url'),
        empty($title) ? getTitleFromUrl($url) : $title,

    returnResult('add', $sth->rowCount() == 1, $db->lastInsertId());

$app->put('/bookmark/:id', function ($id) use ($db, $app) {
    $sth = $db->prepare('UPDATE bookmark SET title = ?, url = ? WHERE id = ?;');

    returnResult('edit', $sth->rowCount() == 1, $id);

$app->delete('/bookmark/:id', function ($id) use ($db) {
    $sth = $db->prepare('DELETE FROM bookmark WHERE id = ?;');

    returnResult('delete', $sth->rowCount() == 1, $id);

$app->get('/install', function () use ($db) {
    $db->exec('  CREATE TABLE IF NOT EXISTS bookmark (
                    id INTEGER PRIMARY KEY, 
                    title TEXT, 
                    url TEXT UNIQUE);');



Testing with cURL

With the usage of cURL we can test the whole API, the request method is defined by the X parameter, additional data can be appended to the request using the d parameter and the last parameter is the URL which should get called.

If you use the SQLite database, a table has to be created first by calling "install" via GET.

$ curl

Once the table has been created, the first bookmark gets added by sending a POST request with the params url and the optionally param title.

$ curl -X POST -d "url=" -d "title=Cacomania"

Let's save a second bookmark without specifying the title.

$ curl -X POST -d "url="

The PUT request with a given id changes the particular bookmark.

$ curl -X PUT -d "url=" -d ""

To fetch a single bookmark needs an id to be specified.

$ curl -X GET

Without a given id all bookmarks gets returned.

$ curl -X GET 
[{"id":"1","title":"","url":"http:\/\/"},{"id":"2","title":"Hack a Day \u2014 Fresh hacks every day","url":"http:\/\/"}]

What the DELETE request does is really obviously.

$ curl -X DELETE


The Slim framework ist perfect for creating a small API or a prototype without the overhead of a large PHP framework. Using Slim saves a lot of time and the code is very readable up to a certain size. The routing is easy to use and quite power full. I just love it.

The code is available at GitHub and contains a AngularJS front end which will be discussed in an extra article later. But here is screenshot of the AngularJS front end:
AngularJS Bookmark App