Using Eloquent, Doctrine DBAL, and Medoo with Slim 3

Published Sep 15, 2017Last updated Dec 18, 2017
Using Eloquent, Doctrine DBAL, and Medoo with Slim 3

Slim is a PHP micro framework that helps you quickly write simple yet powerful web applications and APIs. It is lightweight with compared to other PHP frameworks, such as Laravel, Zend Framework and Symfony, which provide multiple libraries in one package. Slim is less opinionated and very easy to learn. However, it doesn’t come with a database connection utility of its own. You can use any database connection toolkit you like. In this post, I am going to show you how to use Eloquent, Doctrine DBAL, and Medoo to connect your Slim application to a database. I will be using Medoo as the main example here to query the database. You can find the examples of Eloquent and Doctrine DBA in the GitHub respositories that I will provide at the end of this post.

Dependency Container

Slim uses a dependency container to prepare, manage, and inject application dependencies. It supports containers that implement PSR-11 or the Container-Interop interface. You can use third-party containers like Acclimate or PHP-DI. I chose The League Container because it allows autowiring if you want to reduce development time by removing the necessity of specifying properties and constructor arguments. Though it requires a bit of setup:

// Contruct League container.
$container = new \League\Container\Container;
$container->delegate(new \Slim\Container($settings));

// Enable auto wiring.
$container->delegate(
    new \League\Container\ReflectionContainer
);

// Get an instance of Slim and pass $container into Slim.
$app = new \Slim\App($container);

You can read more infomation from their webiste here.

To install the container:

$ composer require league/container

Database Connection Options

After you have set up the container, what you have to do is to add this line below to use it for adding dependencies that your application needs:

$container = $app->getContainer();

To add a database connection, you can choose:

  1. Medoo

medoo-logo-256.png

$container->add('Medoo\Medoo', function() {
    $dbconfig = require './config/database.php';
    return new \Medoo\Medoo([
        'database_type' => 'mysql',
        'database_name' => $dbconfig['name'],
        'server' => $dbconfig['host'],
        'username' => $dbconfig['username'],
        'password' => $dbconfig['password']
    ]);
});
  1. Doctrine DBAL

download.jpg

use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\DriverManager;

$container->add('Doctrine\DBAL\Connection', function() {
    $dbconfig = require './config/database.php';
    $connection = DriverManager::getConnection(array(
        'dbname' => $dbconfig['name'],
        'user' => $dbconfig['username'],
        'password' => $dbconfig['password'],
        'host' => $dbconfig['host'],
        'driver' => 'pdo_mysql',
        'charset' => 'utf8',
        'driverOptions' => array(
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
        )
    ), $config = new Configuration);
    return $connection;
});

  1. Eloquent

1721772.png

$container->add('Illuminate\Database\Capsule\Manager', function() {
    $dbconfig = require './config/database.php';
    $capsule = new \Illuminate\Database\Capsule\Manager;
    $capsule->addConnection([
        'driver' => 'mysql',
        'host' => $dbconfig['host'],
        'database' => $dbconfig['name'],
        'username' => $dbconfig['username'],
        'password' => $dbconfig['password'],
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
    ]);

    $capsule->setAsGlobal();
    $capsule->bootEloquent();

    return $capsule;
});

In my config/database.php, I have:

$name = 'slimapp';
$username = 'root';
$password = 'xxxxxx';
$host = 'localhost';

return [
    'type' => 'mysql',
    'options' => [
        'PDO::MYSQL_ATTR_INIT_COMMAND' => 'SET NAMES \'UTF8\''
    ],
    'dsn' => 'mysql:dbname=' . $name . ';host=' . $host,
    'host' => $host,
    'name' => $name,
    'username' => $username,
    'password' => $password,
];

Using Medoo

The cool thing about container is we can tell the container how to construct our object however we want. If you ask the container for a Medoo instance in the object that depends on it, the container will create it using \Medoo\Medoo with default the options that we defined above. This is very handy for objects that are used often.

All you will need is this basic structure in your class, User controller for instance:

namespace App\Controller;

use \Medoo\Medoo;

class User
{
    protected $database;

    public function __construct(Medoo $database) {
        $this->database = $database;
    }
}

1. The GET method

The container will construct \Medoo\Medoo that you have already defined and inject it in 'magically' for you when you call the controller in your route via the get() method in the container:

$app->get('/users', function (Request $request, Response $response, $args) {

    // Autowiring the controller.
    $controller = $this->get('App\Controller\User');

    // Obtain result.
    $users = $controller->fetchUsers($request);

    // Default status code.
    $status = 200;

    // Output data.
    $data = [
        "status" => $status,
        "data" => $users
    ];

    $response->getBody()->write(json_encode($data));
    return $response->withStatus($status);
});

This is the method to fetch the collection of users in User controller:

public function fetchUsers(Request $request)
{
    // Columns to select.
    $columns = [
        'uuid',
        'name',
        'created_on',
        'updated_on',
    ];

    $collection = $this->database->select('user', $columns);
    return $collection;
}

2. The POST method

When you visit the page at http://0.0.0.0:8080/users, you will get:

{"status":200,"data":[]}

This is because there is no user yet in the user table in the database. So you will need the insert method in your controller:

public function insertUser(Request $request)
{
    // Get params and validate them here.
    $name = $request->getParam('name');
    $email = $request->getParam('email');

    // Throw if empty.
    if (!$name) {
        throw new \Exception('$name is empty', 400);
    }

    // Throw if empty.
    if (!$email) {
        throw new \Exception('$email is empty', 400);
    }

    // Create a timestamp.
    $date = new \DateTime();
    $timestamp = $date->getTimestamp();

    // Generate a version 1 (time-based) UUID object.
    $uuid1 = Uuid::uuid1();
    $uuid = $uuid1->toString();

    // Assuming this is a model in a more complex app system.
    $model = new \stdClass;
    $model->uuid = $uuid;
    $model->name = $name;
    $model->email = $email;
    $model->created_on = $timestamp;

    // Insert user.
    $result = $this->database->insert('user', [
        'uuid' => $model->uuid,
        'name' => $model->name,
        'email' => $model->email,
        'created_on' => $model->created_on
    ]);

    // Throw if it fails.
    // Returns the number of rows affected by the last SQL statement.
    if ($result->rowCount() === 0) {
        throw new \Exception('Insert row failed', 400);
    }

    // Return the model if it is OK.
    return $model;
}

Then add this to the route:

$app->post('/users', function (Request $request, Response $response, $args) {

    // Autowiring the controller.
    $controller = $this->get('App\Controller\User');

    // Default status code.
    $status = 200;

    // Obtain result.
    $data = $controller->insertUser($request);
    $data = [
        "status" => $status,
        "data" => $data
    ];

    $response->getBody()->write(json_encode($data));
    return $response->withStatus($status);
});

Note that I use ramsey/uuid to generate the uuid for each user I will inject into the user table.

Now if you go to Google Postman, create the keys below and type in the value in the Body section:

Key     Value
--------------------
name    rob
email   foo@bar.com

Choose POST method and hit the Send button, you will get:

{
    "status": 200,
    "data": {
        "uuid": "ece31064-9a37-11e7-be25-06dcb88957ba",
        "name": "rob",
        "email": "foo@bar.com",
        "created_on": 1505495064
    }
}

When you visit http://0.0.0.0:8080/users again, you will get:

{"status":200,"data":[{"uuid":"ece31064-9a37-11e7-be25-06dcb88957ba","name":"rob","created_on":"1505495064","updated_on":"0"}]}

You can add more users in and when you just want to query a single user on your route, e.g. http://0.0.0.0:8181/users/rob, you will get:

{"status":200,"data":{"uuid":"ece31064-9a37-11e7-be25-06dcb88957ba","name":"rob","created_on":"1505495064","updated_on":"0"}}

To achieve that, in your route, add:

$app->get('/users/{name}', function (Request $request, Response $response, $args) {

    // Autowiring the controller.
    $controller = $this->get('App\Controller\User');

    // Default status code.
    $status = 200;

    // Obtain result.
    $user = $controller->fetchUser($request, $args);

    $data = [
        "status" => $status,
        "data" => $user
    ];

    $response->getBody()->write(json_encode($data));
    return $response->withStatus($status);
});

The method to fetch a single user:

public function fetchUser(Request $request, array $args)
{
    // Columns to select.
    $columns = [
        'uuid',
        'name',
        'created_on',
        'updated_on',
    ];
    $data = $this->database->get('user', $columns, [
        "name" => $args['name']
    ]);

    // Throw error if no result found.
    if ($data === false) {
        throw new \Exception('No user found', 400);
    }
    return $data;
}

3. The PUT method

When you want to update that user, you just need to add an uuid key:

Key     Value
--------------------
name    rob
email   fooz@bar.com
uuid    ece31064-9a37-11e7-be25-06dcb88957ba

The update method that you need to add in the User controller:

public function updateUser(Request $request)
{
    // Get params and validate them here.
    $uuid = $request->getParam('uuid');
    $name = $request->getParam('name');
    $email = $request->getParam('email');

    // Throw if empty.
    if (!$uuid) {
        throw new \Exception('$uuid is empty', 400);
    }

    // Throw if empty.
    if (!$name) {
        throw new \Exception('$name is empty', 400);
    }

    // Throw if empty.
    if (!$email) {
        throw new \Exception('$email is empty', 400);
    }

    // Create a timestamp.
    $date = new \DateTime();
    $timestamp = $date->getTimestamp();

    // Assuming this is a model in a more complex app system.
    $model = new \stdClass;
    $model->uuid = $uuid;
    $model->name = $name;
    $model->email = $email;
    $model->updated_on = $timestamp;

    // Update the user.
    $result = $this->database->update("user", [
        "name" => $model->name,
        "email" => $model->email,
        'updated_on' => $model->updated_on,
    ], [
        "uuid" => $model->uuid
    ]);

    // Throw if it fails.
    // Returns the number of rows affected by the last SQL statement.
    if ($result->rowCount() === 0) {
        throw new \Exception('Update row failed', 400);
    }

    // Return the model if it is OK.
    return $model;
}

The route:

$app->put('/users', function (Request $request, Response $response, $args) {

    // Autowiring the controller.
    $controller = $this->get('App\Controller\User');

    // Default status code.
    $status = 200;

    // Obtain result.
    $data = $controller->updateUser($request);
    $data = [
        "status" => $status,
        "data" => $data
    ];

    $response->getBody()->write(json_encode($data));
    return $response->withStatus($status);
});

When you hit the Send button with the PUT method, you will get:

{
    "status": 200,
    "data": {
        "uuid": "ece31064-9a37-11e7-be25-06dcb88957ba",
        "name": "rob",
        "email": "fooz@bar.com",
        "updated_on": 1505496564
    }
}

4. The DELETE method

The last method we will need in this post is the delete method, so in the controller, add:

public function deleteUser(Request $request)
{
    // Get params and validate them here.
    $uuid = $request->getParam('uuid');

    // Throw if empty.
    if (!$uuid) {
        throw new \Exception('$uuid is empty', 400);
    }

    // Assuming this is a model in a more complex app system.
    $model = new \stdClass;
    $model->uuid = $uuid;

    // Delete user.
    $result = $this->database->delete("user", [
        "uuid" => $model->uuid
    ]);

    // Check the number of rows affected by the last SQL statement.
    // Throw if it fails.
    if ($result->rowCount() === 0) {
        throw new \Exception('Delete row failed', 400);
    }

    // Return the model if it is OK.
    return $model;
}

The route:

$app->delete('/users', function (Request $request, Response $response, $args) {

    // Autowiring the controller.
    $controller = $this->get('App\Controller\User');

    // Default status code.
    $status = 200;

    // Obtain result.
    $data = $controller->DeleteUser($request);
    $data = [
        "status" => $status,
        "data" => $data
    ];

    $response->getBody()->write(json_encode($data));
    return $response->withStatus($status);
});

In Postman, you just need to provide the uuid key:

Key     Value
--------------------
uuid    ece31064-9a37-11e7-be25-06dcb88957ba

When you hit the Send button with the DELETE method, it will result:

{
    "status": 200,
    "data": {
        "uuid": "ece31064-9a37-11e7-be25-06dcb88957ba"
    }
}

When you look for 'rob' on the browser with http://0.0.0.0:8181/users/rob, it is now gone:

{"status":400,"messsage":"No user found"}

That's it for using Medoo with Slim 3. You can get the source from its repository in github. Also, the source for Eloquent and Doctrine DBAL.

Conclusion

As you can see it is very easy to use a third party database package with Slim once you have set it up correctly. You can switch to any other database framework to suit your liking. The only thing you need to change is the database query methods in the controller. For Eloquent, Doctrine DBAL, and Medoo, you can check out their repositories above and compare their differences. Let me know what you think and what database package you use for your projects. If anything unclear or can be improved, please leave a comment below. Hope these basic examples are helpful.

Discover and read more posts from LAU TIAM KOK
get started