How to Connect to MySQL database in Slim framework 4

Last updated on November 17, 2022

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 the output in the browser at http://localhost:8080

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 the 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 assumes 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');
    });