Full Stack LAMP - MEAN Developer, Python developer. Certified Azure Developer. Freelance programmer/consultant/trainer.

How to Connect to MySQL database in Slim framework 4

Defining MySQL database connection in the Slim 4 framework is pretty simple. In this slim framework 4 tutorial, we gonna create a project with Slim-Skeleton.

If you are looking for Slim Framework 3, follow the link How to configure/load database in Slim Framework 3

Install Slim Framework 4

We recommend you install the Slim Framework with the Composer dependency manager.

The easiest way to start working with Slim is to create a project using Slim-Skeleton as a base by running this bash command:

$ php composer.phar create-project slim/slim-skeleton:dev-master [app-name]

Replace [app-name] with the desired directory name for your new application.

You can then run it with PHP’s built-in webserver:

$ cd [app-name]; php -S localhost:8080 -t public public/index.php

upon successful slim 4 project setup, you should able to see output in the browser at http://localhost:8080

How to Connect to MySQL database in Slim framework 4
Output in Browser
Folder Structure of the project.

Add Database Settings

Go to the app directory, open the settings.php file, and pass the database setting to the Settings class along with other settings.

<?php
declare(strict_types=1);

use App\Application\Settings\Settings;
use App\Application\Settings\SettingsInterface;
use DI\ContainerBuilder;
use Monolog\Logger;

return function (ContainerBuilder $containerBuilder) {

    // Global Settings Object
    $containerBuilder->addDefinitions([
        SettingsInterface::class => function () {
            return new Settings([
                'displayErrorDetails' => true, // Should be set to false in production
                'logger' => [
                    'name' => 'slim-app',
                    'path' => isset($_ENV['docker']) ? 'php://stdout' : __DIR__ . '/../logs/app.log',
                    'level' => Logger::DEBUG,
                ],
                "db" => [
                    'driver' => 'mysql',
                    'host' => 'localhost',
                    'username' => 'root',
                    'database' => 'slim_test',
                    'password' => '',
                    'charset' => 'utf8mb4',
                    'collation' => 'utf8mb4_unicode_ci',
                    'flags' => [
                        // Turn off persistent connections
                        PDO::ATTR_PERSISTENT => false,
                        // Enable exceptions
                        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                        // Emulate prepared statements
                        PDO::ATTR_EMULATE_PREPARES => true,
                        // Set default fetch mode to array
                        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
                    ],
                ],
            ]);
        }
    ]);
};

Once settings are in place, open app/dependencies.php file and configure database

<?php
declare(strict_types=1);

use App\Application\Settings\SettingsInterface;
use DI\ContainerBuilder;
use Monolog\Handler\StreamHandler;
use Monolog\Logger;
use Monolog\Processor\UidProcessor;
use Psr\Container\ContainerInterface;
use Psr\Log\LoggerInterface;


return function (ContainerBuilder $containerBuilder) {
    $containerBuilder->addDefinitions([
        LoggerInterface::class => function (ContainerInterface $c) {
          .....
          .......

          .........
        },

        PDO::class => function (ContainerInterface $c) {

            $settings = $c->get(SettingsInterface::class);

            $dbSettings = $settings->get('db');

            $host = $dbSettings['host'];
            $dbname = $dbSettings['database'];
            $username = $dbSettings['username'];
            $password = $dbSettings['password'];
            $charset = $dbSettings['charset'];
            $flags = $dbSettings['flags'];
            $dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";
            return new PDO($dsn, $username, $password);
        },

    ]);
};
That's it. Slim framework 4 database configuration are done. let's test it.

Slim framework 4 Database Connection Test

Now open the app/routes.php file and define a route for testing the database connection.

The below code assume that your database has a task table if not update SQL queries in the below sample code.

    $app->get('/db-test', function (Request $request, Response $response) {
        $db = $this->get(PDO::class);
        $sth = $db->prepare("SELECT * FROM tasks ORDER BY task");
        $sth->execute();
        $data = $sth->fetchAll(PDO::FETCH_ASSOC);
        $payload = json_encode($data);
        $response->getBody()->write($payload);
        return $response->withHeader('Content-Type', 'application/json');
    });
  1. Good article, just one thing: the “flags” have been defined but you have not passed them, so PDO is not actually instructed to use them. Also the “driver” key has been defined but not used

  2. Oh so sorry was my fault.
    CREATE TABLE tasks (
     id int(11) NOT NULL,
     task varchar(255) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    INSERT INTO tasks (id, task) VALUES
    (1, ‘Hello World’),
    (2, ‘Hello Wand’);

    I had the second column named to name so the SQL error triggered the 405. Works flawless now. THX

  3. That is a great example! Can you show how you would be able to get the PDO information from within Actions?

    $db = $this->get(PDO::class); does not work when the route called an Action, and I try to set up a new database connection in the action instead of the route.

    I DO see the values doing an object dump for $this, but cannot figure out how to call $this correctly to get the information I need from the object inside my action script.

Leave a Reply