How to specify correct return type of function that queries database?

[theodorejb]

I am working with a reusable query function from an external library which takes an arbitrary select query and returns the results as a list:

/**
 * @param list $params
 * @return list<array<array-key, float|int|null|string>>
 */
function query(mysqli $db, string $sql, array $params = []): array
{
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}

It can't specify a more exact shape for the returned list because it depends on the query. In my own code, I want to write more specific functions which do specify the shape of the returned array. E.g.:

/**
 * @return list<array{id: int, name: string}>
 */
function getUsers(mysqli $db, string $role): array
{
    return query($db, "SELECT id, name FROM users WHERE role = ?", [$role]);
}

However, Psalm doesn't allow this - it produces an InvalidReturnStatement error saying that the inferred type does not match the declared return type: https://psalm.dev/r/44dd283558.

Is there a way to specify the exact array shape returned by my getUsers function without a Psalm error?

[weirdan]

No easy way. It would require parsing the query, mapping its field list to actual DB fields and finding out their types. Theoretically, a plugin could do that.

[theodorejb]

I found a way to do it using the @psalm-type and @var annotations:

/**
 * @psalm-type UserList = list<array{id: int, name: string}>
 * @return UserList
 */
function getUsers(mysqli $db, string $role): array
{
    /** @var UserList */
    return query($db, "SELECT id, name FROM users WHERE role = ?", [$role]);
}

It's a little more boilerplate-y than I would like, but it works!

[weirdan]

Just note that it overrides any inference Psalm may have had about the result of the query() function. E.g. if query() starts returning null Psalm won't catch that with the annotation you added.

[theodorejb]

I don't think I have any choice, though, other than overriding the type or suppressing type errors everywhere that getUsers is called (which seems strictly worse). Any other suggestions are welcome!

[M1ke]

If you are doing this, here's a pretty basic script that shortcuts the whole "parse the query" bit for you: https://gist.github.com/M1ke/e8af5cb394d045a04a28b640b021c3bf