Slim 4 - PostgreSQL database connection

I am using Slim 4 to build an API that will connect to a PostgreSQL 12 database and produce JSON objects.

To do this I am using the following skeleton with PHP 7.4.

I am looking for a simple, nice and correct way to connect to the database. I have been through a lot of tutorials but none of them explains the whole thing. I used to code PHP back in the dinosaurs era and gave up coding for 15 years. I have to admit that I am a bit lost with the dependencies and containers.

I am starting from scratch with the skeleton and I am very much open to any way establish the connection to the DB and use it in my routes.

My settings.php file looks like:

declare(strict_types=1);

use DI\ContainerBuilder;
use Monolog\Logger;

return function (ContainerBuilder $containerBuilder) {
    // Global Settings Object
    $containerBuilder->addDefinitions([
        '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" => [
                "host" => "localhost",
                "dbname" => "mydatabase",
                "user" => "myuser",
                "pass" => "mypassword"
            ],
        ],
    ]);
};

My dependencies.php file looks like:

declare(strict_types=1);

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) {
            $settings = $c->get('settings');

            $loggerSettings = $settings['logger'];
            $logger = new Logger($loggerSettings['name']);

            $processor = new UidProcessor();
            $logger->pushProcessor($processor);

            $handler = new StreamHandler($loggerSettings['path'], $loggerSettings['level']);
            $logger->pushHandler($handler);

            return $logger;
        },

    ]);
    $containerBuilder->addDefinitions([
        PDO::class => function (ContainerInterface $c) {
            $settings = $c->get('settings');

            $dbSettings = $settings['db'];

            $pdo = new PDO("pgsql:host=" . $dbSettings['host'] . ";dbname=" . $dbSettings['dbname'], $dbSettings['user'], $dbSettings['pass']);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            return $pdo;
        },
    ]);

};

Here is the index.php file:

declare(strict_types=1);
use App\Application\Handlers\HttpErrorHandler;
use App\Application\Handlers\ShutdownHandler;
use App\Application\ResponseEmitter\ResponseEmitter;
use DI\ContainerBuilder;
use Slim\Factory\AppFactory;
use Slim\Factory\ServerRequestCreatorFactory;

require __DIR__ . '/../vendor/autoload.php';

// Instantiate PHP-DI ContainerBuilder
$containerBuilder = new ContainerBuilder();
if (false) { // Should be set to true in production
$containerBuilder->enableCompilation(__DIR__ . '/../var/cache');
}

// Set up settings
$settings = require __DIR__ . '/../app/settings.php';
$settings($containerBuilder);

// Set up dependencies
$dependencies = require __DIR__ . '/../app/dependencies.php';
$dependencies($containerBuilder);

// Set up repositories
$repositories = require __DIR__ . '/../app/repositories.php';
$repositories($containerBuilder);

// Build PHP-DI Container instance
$container = $containerBuilder->build();

// Instantiate the app
AppFactory::setContainer($container);
$app = AppFactory::create();
$callableResolver = $app->getCallableResolver();

// Register middleware
$middleware = require __DIR__ . '/../app/middleware.php';
$middleware($app);

// Register routes
$routes = require __DIR__ . '/../app/routes.php';
$routes($app);

/** @var bool $displayErrorDetails */
$displayErrorDetails = $container->get('settings')['displayErrorDetails'];

// Create Request object from globals
$serverRequestCreator = ServerRequestCreatorFactory::create();
$request = $serverRequestCreator->createServerRequestFromGlobals();

// Create Error Handler
$responseFactory = $app->getResponseFactory();
$errorHandler = new HttpErrorHandler($callableResolver, $responseFactory);

// Create Shutdown Handler
$shutdownHandler = new ShutdownHandler($request, $errorHandler, $displayErrorDetails);
register_shutdown_function($shutdownHandler);

// Add Routing Middleware
$app->addRoutingMiddleware();

// Add Error Middleware
$errorMiddleware = $app->addErrorMiddleware($displayErrorDetails, false, false);
$errorMiddleware->setDefaultErrorHandler($errorHandler);

// Run App & Emit Response
$response = $app->handle($request);
$responseEmitter = new ResponseEmitter();
$responseEmitter->emit($response);

And finally the routes.php file:

declare(strict_types=1);

use App\Application\Actions\User\ListUsersAction;
use App\Application\Actions\User\ViewUserAction;
use Psr\Http\Message\ResponseInterface as Response;
use Psr\Http\Message\ServerRequestInterface as Request;
use Slim\App;
use Slim\Interfaces\RouteCollectorProxyInterface as Group;

return function (App $app) {
    $app->get('/', function (Request $request, Response $response) {
        $response->getBody()->write('Hello world!');
        return $response;
    });

    $app->group('/users', function (Group $group) {
        $group->get('', ListUsersAction::class);
        $group->get('/{id}', ViewUserAction::class);
    });
};

I must have made mistakes in the code :slight_smile:

What I am missing is the way to connect to the database in the routes to collect the data.

Please note that the very reason why I am using a framework and a skeleton is because I am not very good at coding :slight_smile:

Thanks in advance for your answers

The best way would be to use dependency injection. Your action class should declare a service or repository as dependency and the repository should declare the database connection e.g. PDO as dependency.

Hi,
Thanks for your answer.
I might be too bad in PHP to even start to understand what you explained but I trust it is correct.
Everybody is talking about containers, injections, dependencies and repositories… Back in the day I used to declare my variables and my connection and everything was working.
I am totally lost when it comes to declare three features in 4 files and then to include the whole thing in and then execute it… I realize that I had to copy and paste the content of 4 files to explain a feature as simple as connecting to a database with PHP.
I am seriously considering giving up with the framework as it seems way to complex to implement what I am looking for. The plan was to use it because I’m a n00b and I was hoping that my life would be simpler with a framework and a skeleton. It is not. Poor me.
Thanks again for taking the time to answer me.

Hi @pierre I’m sorry to hear that. But I think the time when we put everything into one method is over and we should try to implement testable and maintainable software today. Dependency Injection is a good way to achieve this goal. Technically, in Slim 4 you can still use closures (functions) as before, but I wouldn’t recommend it anymore.

Hi @odan
To tell you the truth, I am a database administrator with “some” programming knowledge.
I am trying to build an API with JWT authentication to access the database and return JSON objects.
The learning curve it would require to manage to do what you are suggesting is clearly much steeper than I thought to reach something as simple as connectingthe framework to a database. But I really agree with you about the way of coding. I should do it the right way… But it is quite frustrating as I can’t find any valuable tutorial that would take me from the total ignorant to the decent programmer. All the tutorials are missing some bits or using shortcuts as they are addressed to more advanced programmers. I’d love to find something that would take me from zero to PDO :wink:
I have tried Laravel as a proof of concept but it provides too much… I love Slim and I’ll continue to give it a try. My previous message was the voice of a frustrated guy behind his screen after a night of failures.
I need to have that API up and running and I’ll learn and sweat as much as needed to do so.
Thanks for your answers.
P.

Here we go: Slim 4 - Tutorial | Daniel Opitz - Blog :wink:

Please try this and give me a feedback.

1 Like

Hi @odan,
Did my best… Tried hard… Not there yet :slight_smile:
But I’ll try again after some sleep.
My main issue is the knowledge… Everyting goes well until the first error 500 with a highly cryptic error message that could mean that my code is wrong or that it will rain in New York City tomorrow… Obviously it is my code. But the debugging is painstaking and yet again all that just to manage to connect PHP to a database… I am not even starting to imagine the more advanced stuff and it already hurts. :slight_smile:
Thanks again for your help. If I may suggest something for the future of the Slim framework it would be great to offer some built in way to easily connect to a DB.

Built in DB connection would make Slim very opinionated on how it is used, since there are many categories of databases and even more drivers and libraries they rely on. MySQL and CouchDB are quite different in how they operate and how you manage them from application code. It might even become opinionated about what operating systems Slim can run on since different OS:es have different support for database drivers.

Once you have figured out from the tutorial linked above where DB settings like username, host and so on goes, how you read it back into a configuration in application code and hook this into a container entry you’ll probably find it very convenient to use and reuse in your application compared to how these things were commonly (and often badly) solved in the early days of PHP web development.

I think it might be overkill for your use case to apply an ORM library but having a look at some of the more popular, like Eloquent from Laravel or Doctrine from Symfony, could give you some inspiration on how to separate database access and application logic. Besides reading odan:s excellent tutorials, that is.

2 Likes

Hi,
I managed to go through the thing and to access my database successfully.
That is all thanks to @odan 's great tutorial. Really well done!
I agree with @cess11 about the db connection built in Slim.
On the other hand, as a database administrator, I also have to say that I am totally reluctant to use Eloquent and any such “easy tool” have a tendency to mask and ignore the database logic and prevents the developer to have full control of how the query is executed and makes any attempt to tune the query. I am used to work with databases containing several gigabytes (and more) of data and having thousands of requests per seconds… In this very case Eloquent works on the developer’s side as it allows you to easily write a query, connect and send the query to the database… But a query is not only that… There is an execution plan behind. As a DBA I’ve been asked ten thousand times why the app is slow?.. Then you look at the query and your heart stops.
The complexity and my struggle to write a query in Slim is probably exactly what I am looking for. Something light that leaves me the full control of my queries and both the code logic and the database optimization. So I had to struggle a bit to get the knowledge to be able to do it. But in the end it was worth it because I can write my own query, respect the data model and get back the exact data I need. On top of that, most of the database logic relies on stored procedures and eloquent is pretty (very) bad with stored procedures.
All that to say that I am happy and thankful because I asked for help, I received it and I learned something. Thank you again for your wisdom, good advice and points of view.

1 Like

I agree, ORM:s work best when the database is designed for the one chosen and most queries are fairly simple. There are usually ways to cram out decent performance also with complex or long running queries, if nothing else one can write the SQL by hand. For stored procedures I would probably write application modules that handle them separately, using the PDO or whatever connection directly in them and pass around such objects in application code where needed, or just rely on triggers or hooks in the database engine if that’s a viable way to run the stored procedures.

In my opinion it is the job of the developer to know these things and choose tools and libraries appropriately, and one has failed in that role if one has to go to the DBA and ask why the application is slow or stuttering in production rather than have a conversation about different alternatives regarding schema, indexing and optimisations. It’s commonly trivial to dump generated SQL and look at whether it uses the correct indices, has a reasonable complexity for the schema and so on, so one should know this when knocking on the DBA door and asking for help.

For a use case with thousands of requests per second I’m not so sure I would choose PHP, even with some help from opcache, FPM and whatnot it is kind of hard to keep a non-trivial application stable under such load. The JVM might be a better choice, unless the PHP project has matured in this regard since 7.1 and 7.2, which I’ve seen choke on bursts of 3-500 requests per second even though DB operations were simple CRUD and the code pretty straightforward as well.

As for choosing an ORM, I prefer Doctrine over Eloquent, I find it easier to keep business logic and database access separate, and CLI tools easier to implement compared to Eloquent. Either way, Slim makes it easy to use either or roll your own solution, especially when combined with one of the richer container libraries, like PHP-DI.

1 Like

As @odan asks for feedback about his tutorial, here is a little thing that would have helped me.
In the final example, the route is designed to accept a post request with data coming from Postman as a JSON object. I would have loved and it would really help if I had an example with a GET request with several arguments passed to the PDO query. Something like:
http://localhost:8000/api/{first_argument}/{second_argument}
That would have made the tutorial absolutely perfect.
Please don’t take it as a negative critic but rather as a constructive feedback from a reader.
Have a wonderful day.
P.

1 Like

Guys!
I’m so happy!
I now have a fully working API with JWT authentication based on my PostgreSQL database with my very own stored procedures (functions) in pl/pgsql and all that written in a decent way thanks to you.

1 Like

Hi @pierre Thanks a lot for your feedback. :+1: I am very happy to hear that you have successfully installed and deployed Slim. :slight_smile:

1 Like