Create a REST API from MySQL database

This tutorial is a step by step guide howto build a simple REST API with a MySQL database as backend using the PSX framework. We will create a API around a table where we can receive and create todo entries.

Preparation

As basis we use the sample project. The getting started guide covers in detail howto setup the sample project. Basically you need to install the project through composer:

composer create-project psx/sample .

Also wee need to execute the following SQL statments on the database to create the needed tables.

CREATE TABLE IF NOT EXISTS `todo_author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `uri` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `todo_entry` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `authorId` int(10) NOT NULL,
  `title` varchar(255) NOT NULL,
  `insertDate` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
        
INSERT INTO `todo_author` (`id`, `name`, `uri`) VALUES
(1, 'admin', 'foo@bar.com');

INSERT INTO `todo_entry` (`id`, `authorId`, `title`, `insertDate`) VALUES
(1, 1, 'lorem ipsum', '2016-05-21 19:33:02'),
(2, 1, 'lorem ipsum', '2016-05-21 19:33:14');
        

Since we add new classes to the Acme namespace we also have to add a new autoload entry to the composer.json file so that our classes are automatically detected by composer.

"autoload": {
  "psr-0": {
    "Sample": "src/",
    "Acme": "src/"
  }
}

In order to update the autoloader you have to run the following command:

composer dump-autoload

Configuration

In order to work with a database we need to provide the database credentials to the configuration. By default the sample project works with an SQLite database because of that we have to change the connection. Therefor open the file configuration.php and enter the credentials in the psx_connection keys.

'psx_connection' => [
    'dbname'   => 'psx',
    'user'     => 'root',
    'password' => '',
    'host'     => 'localhost',
    'driver'   => 'pdo_mysql',
]

You can test the connection settings with the following command:

vendor/bin/psx dbal:run-sql "SELECT * FROM todo_entry;"

Models

At first we model simple POPOs which will contain the request and response data of our API. We add also annotations to the properties so that we can automatically generate a JsonSchema from these models.

<?php
// File: src/Acme/Model/Todo/Entry.php

namespace Acme\Model\Todo;

use DateTime;

class Entry
{
    /**
     * @Type("integer")
     */
    protected $id;

    /**
     * @Type("string")
     */
    protected $title;

    /**
     * @Ref("Acme\Model\Todo\Author")
     */
    protected $author;

    /**
     * @Type("string")
     * @Format("date-time")
     */
    protected $insertDate;

    /**
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @param integer $id
     */
    public function setId($id)
    {
        $this->id = $id;
    }

    /**
     * @return string
     */
    public function getTitle()
    {
        return $this->title;
    }

    /**
     * @param string $title
     */
    public function setTitle($title)
    {
        $this->title = $title;
    }

    /**
     * @return \Acme\Model\Todo\Author
     */
    public function getAuthor()
    {
        return $this->author;
    }

    /**
     * @param \Acme\Model\Todo\Author $author
     */
    public function setAuthor(Author $author)
    {
        $this->author = author;
    }
    
    /**
     * @return \DateTime
     */
    public function getInsertDate()
    {
        return $this->insertDate;
    }

    /**
     * @param \DateTime $insertDate
     */
    public function setInsertDate(DateTime $insertDate)
    {
        $this->insertDate = $insertDate;
    }
}
<?php
// File: src/Acme/Model/Todo/Author.php

namespace Acme\Model\Todo;

class Author
{
    /**
     * @Type("integer")
     */
    protected $id;

    /**
     * @Type("string")
     */
    protected $name;

    /**
     * @Type("string")
     */
    protected $uri;

    /**
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @param integer $id
     */
    public function setId($id)
    {
        $this->id = $id;
    }

    /**
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * @param string $name
     */
    public function setName($name)
    {
        $this->name = $name;
    }

    /**
     * @return string
     */
    public function getUri()
    {
        return $this->uri;
    }

    /**
     * @param string $uri
     */
    public function setUri($uri)
    {
        $this->uri = $uri;
    }
}
<?php
// File: src/Acme/Model/Todo/Collection.php

namespace Acme\Model\Todo;

class Collection
{
    /**
     * @Type("integer")
     */
    protected $totalResults;

    /**
     * @Type("array")
     * @Items(@Ref("Acme\Model\Todo\Entry"))
     */
    protected $entry;

    /**
     * @return integer
     */
    public function getTotalResults()
    {
        return $this->totalResults;
    }

    /**
     * @param integer $totalResults
     */
    public function setTotalResults($totalResults)
    {
        $this->totalResults = $totalResults;
    }

    /**
     * @return array
     */
    public function getEntry()
    {
        return $this->entry;
    }

    /**
     * @param array $entry
     */
    public function setEntry(array $entry)
    {
        $this->entry = $entry;
    }
}
<?php
// File: src/Acme/Model/Todo/Message.php

namespace Acme\Model\Todo;

class Message
{
    /**
     * @Type("boolean")
     */
    protected $success;

    /**
     * @Type("string")
     */
    protected $message;

    /**
     * @return boolean
     */
    public function getSuccess()
    {
        return $this->success;
    }

    /**
     * @param boolean $success
     */
    public function setSuccess($success)
    {
        $this->success = $success;
    }

    /**
     * @return string
     */
    public function getMessage()
    {
        return $this->message;
    }

    /**
     * @param string $message
     */
    public function setMessage($message)
    {
        $this->message = $message;
    }
}

Controller

Each controller represents an endpoint which can handle HTTP verbs. In our case we want that the endpoint should handle GET and POST requests. Therefor we add a doGet and doPost method. Through the @Incoming and @Outgoing annotation we declare which request and response format is allowed. Since our model classes already contain JsonSchema annotations we can simply refer to the class but it is also possible to provide a path to a JsonSchema file.

<?php
// File: src/Acme/Endpoint/Todo.php

namespace Acme\Endpoint;

use Acme\Table\Todo\Entry;
use PSX\Framework\Controller\SchemaApiAbstract;

class Todo extends SchemaApiAbstract
{
    /**
     * @Outgoing(code=200, schema="Acme\Model\Todo\Collection")
     */
    protected function doGet()
    {
    }

    /**
     * @Incoming(schema="Acme\Model\Todo\Entry")
     * @Outgoing(code=200, schema="Acme\Model\Todo\Message")
     */
    protected function doPost($record)
    {
    }
}

We have now provided all informations how the endpoint is structured. Based on such controllers we can automatically generate a API documentation or other API schema formats. Now we have to implement the logic where we actually CRUD data.

Repository

PSX provides a database layer which is especially designed to create nested API responses from complex SQL queries. Per table we have one repository class which provides basic CRUD operations but it is also easy to implement custom queries. At first we can generate the table classes through the folowing commands:

vendor/bin/psx sql:generate todo_author > src/Acme/Table/Todo/Author.php
vendor/bin/psx sql:generate todo_entry > src/Acme/Table/Todo/Entry.php

We have to adjust the namespace and add also a new method getEntries which creates the response for our controller:

<?php
// File: src/Acme/Table/Todo/Entry.php

namespace Acme\Table\Todo;

use PSX\Sql\TableAbstract;

class Entry extends TableAbstract
{
    public function getName()
    {
        return 'todo_entry';
    }

    public function getColumns()
    {
        return array(
            'id' => self::TYPE_INT | self::PRIMARY_KEY | self::AUTO_INCREMENT,
            'authorId' => self::TYPE_INT,
            'title' => self::TYPE_VARCHAR | 255,
            'insertDate' => self::TYPE_DATETIME
        );
    }

    /**
     * Custom method to produce a complex result
     */
    public function getEntries()
    {
        $sql = '    SELECT entry.id,
                           entry.title,
                           entry.insertDate,
                           author.name AS authorName,
                           author.uri AS authorUri
                      FROM todo_entry entry
                INNER JOIN todo_author author
                        ON entry.authorId = author.id 
                  ORDER BY entry.insertDate DESC 
                     LIMIT 0, 16';

        $definition = [
            'totalResults' => $this->doValue('SELECT COUNT(*) FROM todo_entry', [], []),
            'entry' => $this->doCollection($sql, [], [
                'id' => 'id',
                'title' => 'title',
                'insertDate' => 'insertDate',
                'author' => [
                    'name' => 'authorName',
                    'uri' => 'authorUri',
                ]
            ]),
        ];

        return $this->build($definition);
    }
}
<?php
// File: src/Acme/Table/Todo/Author.php

namespace Acme\Table\Todo;

use PSX\Sql\TableAbstract;

class Author extends TableAbstract
{
    public function getName()
    {
        return 'todo_author';
    }
    
    public function getColumns()
    {
        return array(
            'id' => self::TYPE_INT | self::PRIMARY_KEY | self::AUTO_INCREMENT,
            'name' => self::TYPE_VARCHAR | 32,
            'uri' => self::TYPE_VARCHAR | 128
        );
    }
}

Note: To simplify things we use the table classes directly in the controller. Normally it is best practice to use only services in the controller and the service has then access to the database layer.

We can then use the generated table classes inside the controller:

<?php
// File: src/Acme/Endpoint/Todo.php

namespace Acme\Endpoint;

use Acme\Table\Todo\Entry;
use PSX\Framework\Controller\SchemaApiAbstract;

class Todo extends SchemaApiAbstract
{
    /**
     * @Inject
     * @var \PSX\Sql\TableManagerInterface
     */
    protected $tableManager;

    /**
     * @Outgoing(code=200, schema="Acme\Model\Todo\Collection")
     */
    protected function doGet()
    {
        return $this->tableManager->getTable('Acme\Model\Todo\Entry')->getEntries();
    }

    /**
     * @Incoming(schema="Acme\Model\Todo\Entry")
     * @Outgoing(code=200, schema="Acme\Model\Todo\Message")
     */
    protected function doPost($record)
    {
        $this->tableManager->getTable('Acme\Model\Todo\Entry')->insert([
            'title' => $record->title,
            'authorId' => 1,
            'insertDate' => new \DateTime(),
        ]);

        return [
            'success' => true,
            'message' => 'Entry successful inserted!',
        ];
    }
}

Routing

To make the controller accessible we have to define a route. PSX uses a simple routing file which contains all available routes. Add the following entry to the routing file to make the controller accessible.

GET|POST|PUT|DELETE /todo Acme\Endpoint\Todo

Finished

It is now possible to test the endpoint at the uri /todo. The API should return the following JSON response:

{
    "totalResults": 2,
    "entry": [
        {
            "id": 2,
            "title": "lorem ipsum",
            "author": {
                "name": "admin",
                "uri": "foo@bar.com"
            },
            "insertDate": "2016-05-21T19:33:14Z"
        },
        {
            "id": 1,
            "title": "lorem ipsum",
            "author": {
                "name": "admin",
                "uri": "foo@bar.com"
            },
            "insertDate": "2016-05-21T19:33:02Z"
        }
    ]
}

If you have questions, problems or want to know more about PSX please take a look at the documentation page.