To determine types involved in SQL based apis phpstan-dba needs to know your sql queries:

use Doctrine\DBAL\Connection;

class Foo
{
    /** @return array{email: string, adaid: int} */
    public function fetchByEmail(Connection $conn, string $email)
    {
        $query = 'SELECT email, adaid FROM ada WHERE email = :email');
        // phpstan-dba can infer the array shape based on the database schema and the sql query
        // => array{email: string, adaid: int}
        return $conn->fetchOne($query, ['email' => $email]); 
    }
}

This even works for conditional queries, as long as the query strings can be figured out at analysis time:

use Doctrine\DBAL\Connection;

class Foo2
{
    /** @return array{email: string, adaid: int} */
    public function fetchByEmail(Connection $conn, string $email, bool $onlyActive, int $typeId)
    {
        $query = 'SELECT email, adaid FROM ada WHERE email = :email');
        $params = ['email' => $email];
        
        if ($onlyActive) {
            $query .= ' AND active = 1';
        }
        
        if ($typeId > 0) {
            $query .= ' AND type_id = :typeId';
            $params['typeId'] = $typeId;
        }
        
        // phpstan-dba can infer the array shape based on the database schema and the sql query
        // because all possible combinations of queries strings are known at analysis time.
        // => array{email: string, adaid: int}
        return $conn->fetchOne($query, $params); 
    }
}

The golden phpstan-dba rules

As a rule of thumb:

  • separate user input from your sql query (use prepared statements)
  • make sure the sql query used is built from scalar values, but does not contain regular string
  • when string is involved try to use literal-string and numeric-string
  • if there is no way around string, check whether @phpstandba-inference-placeholder can help you out (see below)

If you stick to these rules phpstan-dba can figure out the query and therefore provide the following features:

Handle more dynamic queries: meet @phpstandba-inference-placeholder

In rare cases SQL queries can get pretty complex or depend on external configuration, which cannot be built without using a string type:

use Doctrine\DBAL\Connection;

class Foo3
{
    /** @return array{email: string, adaid: int} */
    public function fetchByType(Connection $conn, string $typeId, array $filters)
    {
        $query = 'SELECT email, adaid FROM ada WHERE type_id = :typeId');
        $params = ['typeId' => $typeId];
        
        // because of concat a `string` into the SQL query, phpstan-dba can't know the SQL query at analysis time.
        // => the return type cannot be inferred, sql validation cannot happen.
        $query .= $this->builtFilters($filters)
        return $conn->fetchOne($query, $params); 

    }
    
    /**
     * Builts common filter logic meant for re-use 
     */
    private function builtFilters(array $filter):string {
        $conditions = [];
        
        if (array_key_exists('active', $filter)) {
            $conditions[] = 'active = 1';
        }
        
        if (array_key_exists('deleted', $filter)) {
            $conditions[] = 'deleted = 1';
        }
        
        return implode(' AND ', $conditions);
    }
}

Since version 0.2.42 you can use @phpstandba-inference-placeholder to give phpstan-dba a hint about the query involved, so type inference can still be done:

use Doctrine\DBAL\Connection;

class Foo4
{
    /** @return array{email: string, adaid: int} */
    public function fetchByType(Connection $conn, string $typeId, array $filters)
    {
        $query = 'SELECT email, adaid FROM ada WHERE type_id = :typeId');
        $params = ['typeId' => $typeId];
        
        // the SQL query will be inferred as 'SELECT email, adaid FROM ada WHERE type_id = :typeId AND 1=1',
        // because of the used `@phpstandba-inference-placeholder`.
        // => array{email: string, adaid: int}
        $query .= $this->builtFilters($filters)
        return $conn->fetchOne($query, $params); 

    }
    
    /**
     * Builts common filter logic meant for re-use
     * 
     * @phpstandba-inference-placeholder ' AND 1=1'
     */
    private function builtFilters(array $filter):string {
        $conditions = [];
        
        if (array_key_exists('active', $filter)) {
            $conditions[] = 'active = 1';
        }
        
        if (array_key_exists('deleted', $filter)) {
            $conditions[] = 'deleted = 1';
        }
        
        return implode(' AND ', $conditions);
    }
}

At analysis time the SQL expression given in @phpstandba-inference-placeholder will be picked up and used in the analysis. Since the resulting SQL query is executable and can be EXPLAIN‘ed, phpstan-dba can do its magic.

As things stand right now, using @phpstandba-inference-placeholder is limited to same-class private method calls.

Found a bug? Please help improve this article.

<
Previous Post
rexstan meets phpstan-dba
>
Next Post
Placeholder validation in sql queries