How to set up global database connection for "atk4\dsql" in Symfony5?

Question

I would like to test the SQL Builder library atk4/dsql with Symfony 5 and am trying to set up the database connection. I tried following these instructions in the official docs.

I do not have enough experience with Symfony to find out how to set up a global class so the Atk4 library can use the database connection details (I did enter the db details in the .env file).

I have code like this:

<?php
namespace App\Repository;

use Atk4\Dsql\Query;

class UserRepository
{
    public function getUser($username) {
        $query = new Query();
        $query  ->table('user')
                ->where('username', $username)
                ;

        // Debug Output
        // print_r($query); die;
        return $query->getOne();
    }
}

But when running this I only get "Call to a member function execute() on null".

I have checked this (fantastic) collection of explanations for PHP Errors, but could not find one for this problem. The closest I could find is "Fatal error: Call to a member function ... on a non-object or null". There it explains that this error could occur if - as in the example above - $query were not an object. But $query is definitely an object (I got double confirmation by using gettype($query)).

Therefore I assume that it is my missing database connection definition.

How do I set up the database connection and make DSQL use it for every query in Symfony 5? Or am I possibly overlooking something else?

Answer 1

Well, first you have to setup Connection for your database and described here https://dsql.readthedocs.io/en/develop/connection.html

$connection = \atk4\dsql\Connection::connect($dsn, $user, $pass);

and then pass this Connection object to Query or initialize query from Connection object.

// initialize Query object from Connection object
$query = $connection->dsql();

or in your specific case you can do something like this to pass connection to your query

use Atk4\Dsql\Connection;

class UserRepository
{
    /** @var Connection */
    public $connection;

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

    public function getUser(string $username): ?array {
        return $this->connection->dsql()
            ->table('user')
            ->where('username', $username)
            ->getRow();
    }
}

// usage
$connection = \atk4\dsql\Connection::connect($dsn, $user, $pass);
$repo = new UserRepository($connection);
var_dump($repo->getUser('John'));

P.S. If you want to return just user Id or some other field, then you can use getOne() method, but have to also use field($fieldname) method to define which single field to select.

P.P.S. Where and how you get your $dsn, $user and $pass from ENV file or any other place where you set them up is up to you.