phpstan-dba makes your phpstan static code analysis jobs aware of datatypes within your database. With this information at hand phpstan-dba is able to detect type inconsistencies between your domain model and database-schema. Additionally errors in code handling the results of sql queries can be detected.

This extension provides the following features:

• inspect sql queries, detect syntax errors and placeholder/bound value mismatches
• type-inferrence for Doctrine\DBAL\Result, PDOStatement and mysqli_result
• builtin support for doctrine/dbal, mysqli, and PDO
• API to built the same features for your custom sql based database access layer

In case you are using Doctrine ORM, you might use phpstan-dba in tandem with phpstan-doctrine.

Note: At the moment only mysql/mariadb databases are supported. Technically it's not a big problem to support other databases though.

see the unit-testsuite to get a feeling about the current featureset.

see the 'Files Changed' tab of the DEMO-PR for a quick glance.

First, use composer to install:

composer require --dev staabm/phpstan-dba

Second, create a phpstan-dba-bootstrap.php file, which allows to you to configure phpstan-dba (this optionally includes database connection details, to introspect the database; if you would rather not do this see Record and Replay below):

<?php // phpstan-dba-bootstrap.php

use staabm\PHPStanDba\QueryReflection\RuntimeConfiguration;
use staabm\PHPStanDba\QueryReflection\MysqliQueryReflector;
use staabm\PHPStanDba\QueryReflection\QueryReflection;
use staabm\PHPStanDba\QueryReflection\RecordingQueryReflector;
use staabm\PHPStanDba\QueryReflection\ReplayQueryReflector;
use staabm\PHPStanDba\QueryReflection\ReflectionCache;

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

$cacheFile = __DIR__.'/.phpstan-dba.cache';

$config = new RuntimeConfiguration();
// $config->debugMode(true);

QueryReflection::setupReflector(
    new RecordingQueryReflector(
        ReflectionCache::create(
            $cacheFile
        ),
        // TODO: Put your database credentials here
        new MysqliQueryReflector(new mysqli('hostname', 'username', 'password', 'database'))
    ),
    $config
);

Third, create or update your phpstan.neon file so bootstrapFiles includes phpstan-dba-bootstrap.php.

If you are not using phpstan/extension-installer, you will also need to include dba.neon.

Your phpstan.neon might look something like:

parameters:
  level: 9
  paths:
    - public
  bootstrapFiles:
    - phpstan-dba-bootstrap.php

includes:
  - ./vendor/staabm/phpstan-dba/config/dba.neon

Finally, run phpstan, e.g.

./vendor/bin/phpstan analyse -c phpstan.neon

Within your phpstan-dba-bootstrap.php file you can configure phpstan-dba so it knows about global runtime configuration state, which cannot be detect automatically. Use the RuntimeConfiguration builder-object and pass it as a second argument to QueryReflection::setupReflector().

If not configured otherwise, the following defaults are used:

• when analyzing a php8 codebase, PDO::ERRMODE_EXCEPTION error handling is assumed.
• when analyzing a php8.1 codebase, mysqli_report(\MYSQLI_REPORT_ERROR | \MYSQLI_REPORT_STRICT); error handling is assumed.

In case you don't want to depend on a database at PHPStan analysis time, you can use the RecordingQueryReflector to record the reflection information. With this cache file you can utilize ReplayQueryReflector to replay the reflection information, without the need for a active database connection.

<?php // phpstan-dba-bootstrap.php

use staabm\PHPStanDba\QueryReflection\RuntimeConfiguration;
use staabm\PHPStanDba\QueryReflection\MysqliQueryReflector;
use staabm\PHPStanDba\QueryReflection\QueryReflection;
use staabm\PHPStanDba\QueryReflection\RecordingQueryReflector;
use staabm\PHPStanDba\QueryReflection\ReplayQueryReflector;
use staabm\PHPStanDba\QueryReflection\ReflectionCache;

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

$cacheFile = __DIR__.'/.phpstan-dba.cache';

$config = new RuntimeConfiguration();
// $config->debugMode(true);

QueryReflection::setupReflector(
    new ReplayQueryReflector(
        ReflectionCache::load(
            $cacheFile
        )
    ),
    $config
);

This might be usefull if your CI pipeline cannot connect to your development database server for whatever reason.

The GitHubActions setup of phpstan-dba is using this technique to replay the reflection information.

Note: In case you commit the generated cache files into your repository, consider marking them as generated files, so they don't show up in Pull Requests.

Reuse the SyntaxErrorInPreparedStatementMethodRule within your PHPStan configuration to detect syntax errors in prepared queries, by registering a service:

services:
    -
        class: staabm\PHPStanDba\Rules\SyntaxErrorInPreparedStatementMethodRule
        tags: [phpstan.rules.rule]
        arguments:
            classMethods:
                - 'My\Connection::preparedQuery'
                - 'My\PreparedStatement::__construct'

the callable format is class::method. phpstan-dba assumes the method takes a query-string as a 1st and the parameter-values as a 2nd argument.

Reuse the SyntaxErrorInQueryMethodRule within your PHPStan configuration to detect syntax errors in queries, by registering a service:

services:
    -
        class: staabm\PHPStanDba\Rules\SyntaxErrorInQueryMethodRule
        tags: [phpstan.rules.rule]
        arguments:
            classMethods:
                - 'myClass::query#0'
                - 'anotherClass::takesAQuery#2'

the callable format is class::method#parameterIndex, while the parameter-index defines the position of the query-string argument.

Reuse the SyntaxErrorInQueryFunctionRule within your PHPStan configuration to detect syntax errors in queries, by registering a service:

services:
    -
        class: staabm\PHPStanDba\Rules\SyntaxErrorInQueryFunctionRule
        tags: [phpstan.rules.rule]
        arguments:
            functionNames:
                - 'Deployer\runMysqlQuery#0'

the callable format is funtionName#parameterIndex, while the parameter-index defines the position of the query-string argument.

• support a PDO based QueryReflector
• security rule: detect possible sql injections
• performance rule: detect queries not using indexes