Connecting a database

Slim has no database layer of its own. The simplest option is PDO, the extension for SQL queries built into PHP. The connection is created once in the container and reaches any class that asks for it in its constructor.

The database and the table

Create the blog database:

mysql -u root -p -e "CREATE DATABASE blog CHARACTER SET utf8mb4"

It is convenient to keep the table structure in the project. Create the database folder and a file in it:

-- database/schema.sql
CREATE TABLE posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
INSERT INTO posts (title, body) VALUES
    ('First post', 'Text of the first post.'),
    ('Second post', 'Text of the second post.');

Run the file:

mysql -u root -p blog < database/schema.sql

The database now has a posts table with two rows.

Connection values

Add three lines to .env:

DB_DSN="mysql:host=localhost;dbname=blog;charset=utf8mb4"
DB_USER=root
DB_PASSWORD=

A DSN is a string that describes which database to connect to: the type, the server, the database name and the encoding. Then add a db section to the settings:

// config/settings.php
return [
    'debug' => filter_var($_ENV['APP_DEBUG'] ?? false, FILTER_VALIDATE_BOOL),
    'site_name' => $_ENV['SITE_NAME'] ?? 'Blog',
    'db' => [
        'dsn' => $_ENV['DB_DSN'] ?? '',
        'user' => $_ENV['DB_USER'] ?? '',
        'password' => $_ENV['DB_PASSWORD'] ?? '',
    ],
];

PDO in the container

// config/container.php
use Psr\Container\ContainerInterface;
 
PDO::class => function (ContainerInterface $c) {
    $db = $c->get('settings')['db'];
 
    return new PDO($db['dsn'], $db['user'], $db['password'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
},

The entry is added to the array next to the others. The first option makes PDO throw an exception on a query error, the second makes it return table rows as arrays keyed by column name. The connection is created on first access to PDO::class; pages that do not need the database never connect to it.

Checking

Add a temporary route. Inside a handler function the container is available as $this:

// config/routes.php
$app->get('/db-check', function (Request $request, Response $response) {
    $count = $this->get(PDO::class)->query('SELECT COUNT(*) FROM posts')->fetchColumn();
 
    $response->getBody()->write("Posts in the database: $count");
 
    return $response;
});

Open http://localhost:8080/db-check — the page shows "Posts in the database: 2". Remove the route after checking: database queries will live in a separate class, the subject of the next lesson.

Changing the structure

Slim does not manage the database structure. While the project is small, a schema.sql file is enough. When tables start changing often, a migration tool is added — Phinx, for example: each structure change is described in its own file and applied with one command.

Common mistake

A database password and address written straight into config/container.php end up in version control and in the hands of everyone with access to the code. Keep connection values only in .env, and the .env file itself out of the repository.