Create a REST API from MySQL database This tutorial is a step by step guide how to 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

Configuration

Models

Controller

Repository

Routing

Finished

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. Entry Author Collection Message <?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; use PSX\Http\Environment\HttpContextInterface; class Todo extends SchemaApiAbstract { /** * @Outgoing(code=200, schema="Acme\Model\Todo\Collection") */ protected function doGet(HttpContextInterface $context) { } /** * @Incoming(schema="Acme\Model\Todo\Entry") * @Outgoing(code=200, schema="Acme\Model\Todo\Message") */ protected function doPost($record, HttpContextInterface $context) { } } 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: todo_entry todo_author <?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; use PSX\Http\Environment\HttpContextInterface; class Todo extends SchemaApiAbstract { /** * @Inject * @var \PSX\Sql\TableManagerInterface */ protected $tableManager; /** * @Outgoing(code=200, schema="Acme\Model\Todo\Collection") */ protected function doGet(HttpContextInterface $context) { 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, HttpContextInterface $context) { $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.php file to make the controller accessible. [['ANY'], '/todo', 'Acme\Endpoint\Todo']