Cannot close PDO connection

Question

I developed an API using SLIM that is working well, but unfortunately I have a problem on PDO connections. Essentially when the REST-API is called this logic is followed:

1. Route call controller class
2. Controller class call the model

Eg:

$this->post('/user/add', \User::class . ':add');

the route above will call the method add on the class User:

<?php namespace App\Controllers;

defined('BASEPATH') OR exit('No direct script access allowed');

use Slim\Http\Request;
use Slim\Http\Response;
use Core\Controller;
use App\Models\userModel;
use Exception;

class User extends Controller
{
    private $_user_model;

    public function __construct($settings)
    {
        parent::__construct($settings);
        $this->_user_model = new UserModel($settings);
    }

    public function add(Request $request, Response $response, array $args)
    {
        $data = $request->getParsedBody();
        $result = $this->_user_model->add($data['user']);

        return $response->withJSON([
            "status"    => SUCCESS_MSG,
            "data"      => json_encode($result, true),
            "message"   => "User stored."
        ]);
    }
}

the important part is in UserModel:

<?php namespace App\Models;

defined('BASEPATH') OR exit('No direct script access allowed');

use App\Controllers\License;
use Core\Model;
use Exception;
use PDO;

class UserModel extends Model
{
    public function __construct($settings)
    {
        parent::__construct($settingss);
    }

as you can see that class extends Model which instantiate a new connection, the problem is that when the API return the response the connection is not closed:

<?php namespace Core;

defined('BASEPATH') OR exit('No direct script access allowed');

use Core\Database;

class Model
{
    function __construct($settings, $tenant = false)
    {
        $this->db = new Database($settings, $tenant);
    }

    function __destruct()
    {
        $this->db = null;
    }
}

the Model class contains the db object which is part of Database, and also there is a destructor which should cancel the connection:

<?php namespace Core;

defined('BASEPATH') OR exit('No direct script access allowed');

use PDO;

class Database extends PDO
{
    public function __construct($settings, $tenant = false)
    {
        try
        {    
            $options = [
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",
                PDO::ATTR_PERSISTENT, false,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_ERRMODE => ($settings['displayErrorDetails']) ? PDO::ERRMODE_EXCEPTION : PDO::ERRMODE_SILENT
            ];

            $db = $settings['db'];
            parent::__construct($db['type'] . ':host=' . $db['host'] . ';dbname=' . $db['name'], $db['user'], $db['pass'], $options);
        }
        catch(PDOException $e)
        {
            throw $e->getMessage();
        }
    }
}

as you can see I setted ATTR_PERSISTENT to false. Each time the API is called a new connection is created but this is not disposed, infact if I execute:

show processlist

I have:

There is something wrong in my code? This issue is really annoying 'cause I will get TOO MANY CONNECTION when the API is called so many times.

Any ideas to fix that?

UPDATE

I saw that the problem happen only when there is an error in the API, I actually handle the error using a Container:

$container['errorHandler'] = function ($c) {
    return new \Core\ErrorHandler($c);  
};

and this is the full class:

<?php namespace Core;

defined('BASEPATH') OR exit('No direct script access allowed');

use Psr\Http\Message\ServerRequestInterface as Request;
use Psr\Http\Message\ResponseInterface as Response;

class ErrorHandler extends \Slim\Handlers\Error
{
    private $_settings;

    public function __construct($c)
    {
        $this->_settings = $c['settings'];
    }

    public function __invoke(Request $request, Response $response, \Exception $exception)
    {
        $status = $exception->getCode() : 500;

        $error = [
            "status" => ERROR_MSG,
            "data" => [
                "stack_trace" => ($this->_settings['displayErrorDetails']) ? $exception->getTraceAsString() : '',
            ],
            "message" => $exception->getMessage()
        ];

        $body = json_encode($error, JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT);

        return $response
            ->withStatus($status)
            ->withHeader("Content-type", "application/json")
            ->write($body);
    }
}

for some reason the application instance is still open and the connection too

Answer 1

In Slim you should use the container as "factory" for your application services, like the database connection. Please never extend a class from PDO, because it will cause a lot of strange bugs.

Create a container entry for a shared database connection object like this:

(Slim 3 example)

// container.php

use Slim\Container;
use PDO

// ...

$container[PDO::class] = function (Container $container) {
    $settings = $container->get('settings')['db'];

    $host = $settings['host'];
    $dbname = $settings['database'];
    $username = $settings['username'];
    $password = $settings['password'];
    $charset = $settings['charset'];
    $collate = $settings['collation'];

    $dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";

    $options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_PERSISTENT => false,
        PDO::ATTR_EMULATE_PREPARES => true,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES $charset COLLATE $collate"
    ];

    return new PDO($dsn, $username, $password, $options);
};

// ...

Then create a model (Data mapper) with a database connection (PDO) as dependency like this:

namespace App\Model

use PDO;

class UserModel
{
    private $connection;

    public function __construct(PDO $connection)
    {
        $this->connection = $connection;
    }

    public function findAllUsers(): array
    {
        $this->connection->query('SELECT * FROM users')->fetchAll();
    }
}

You don't need a destructor, because PHP will close the connection for you automatically when the request is done.