Good practices for your Nextcloud app

IQueryBuilder in 33

In Nextcloud, we use an query builder to create SQL request. This is quite a bit more robust than just writing SQL directly, as this enable the standard static analysis tools of PHP to run on it, avoid string concatenation and helps abstracting the various database engines by hiding some implementation details.

Our implementation of IQueryBuilder is based on the QueryBuilder from the doctrine DBAL project and is basically just a wrapper with some additional tweaks and the same API stability guarantee as the rest of Nextcloud.

In the coming Nextcloud 33, we exposed some new features from the underlying QueryBuilder to our wrapper. Namely, more type-correct APIs for fetching the results from the query builder. Now instead of:

$qb = $this->connection->getQueryBuilder();
$result = $qb->select('...')
    ->from('mytable')
    ->executeQuery();
$row = $result->fetch();

You can do:

$qb = $this->connection->getQueryBuilder();
$result = $qb->select('...')
    ->from('mytable')
    ->executeQuery();
$row = $result->fetchAssociative();

The advantage is that now static analysis engine like psalm consider $row to be either an associative array if a matching entry was found in the database or false otherwise.

Also available are IResult::fetchNumeric, IResult::fetchOne, IResult::fetchAllAssociative, IResult::fetchAllNumeric and IResult::fetchFirstColumn. Porting to the new code can be tedious, this is why we also added some rector rules to our rules collections that does that for you automatically.

Rector is a very nice tool which allow to modernize your codebase automatically, to newer version of PHP and your dependencies. You can find out to use it in your Nextcloud app in this tutorial.

In addition, we added two new methods IResult::iterateNumeric and IResult::iterateAssociative which can be an attractive replacement for IResult::fetchAll as these prevents the PHP process to hold the whole result in memory which can in some cases then exceed the memory limit of the PHP process and lead to crashes.

Finally regarding the changes for 33 related to the QueryBuilder. We finally deleted the long deprecated IQueryBuilder::execute method. You should port your app to IQueryBuilder::executeStatement or IQueryBuilder::executeQuery. This two methods have a more correct return type that the static analyser can use. When porting beware that IQueryBuilder::execute was throwing an third-party exception from doctrine, while the new methods are throwing an \OCP\DB\Exception.

Snowflake IDs

We are also thinking about deprecating and removing getLastInsertedId. This feature is not longer supported by the latest release of Doctrine in all databases we support and to use snowflake ids instead. More information about that will be published in a following post.

SQL functions

While analysis a bug report, we found out that in some places the ÌQueryBuilder::createFunction was misused in various places. While the documentation explicitely indicate that column names should be escaped, this wasn’t done before. For example, this was invalid:

$qb = $this->connection->getQueryBuilder();
$qb->select($qb->createFunction('COUNT(id)'));

And the correct way to do that with createFunction is:

$qb = $this->connection->getQueryBuilder();
$qb->select($qb->createFunction('COUNT(' . $qb->getColumnName('id') . ')'));

But a simpler way to do it, is to use the IFunctionBuilder. This is way harder to get wrong and is shorter to type.

$qb = $this->connection->getQueryBuilder();
$qb->select($qb->func()->count('id'));

There is still some legitimate uses of createFunction, for example subqueries, but many uses can be replaced by the IFunctionBuilder.

Future

For the future, there is already work ongoing to fully add type hints to the ÌQueryBuilder and related classes. There is already some work toward that on this draft pull request. This should help static analysers to find errors even more.

Additionally, there is some ongoing research about how we do entities in Nextcloud, so that app developers can avoid in many situation touching directly to the query builder. One current direction, is to provide a new Entity API based on modern PHP features like attributes and simple objects. Here is an example on how this would look like based on the current progress on this WIP pull request.

#[Entity]
#[Table(name: 'twofactor_backupcodes')]
final class BackupCode {
    #[Id(generatorClass: IGenerator::class)]
    #[Column(name: 'id', type: Types::STRING, length: 64, nullable: false)]
    public ?string $id = null;

    #[Column(name: 'user_id', type: Types::STRING, length: 64, nullable: false)]
    public string $userId;

    #[Column(name: 'code', type: Types::STRING, length: 128, nullable: false)]
    public string $code;

    #[Column(name: 'used', type: Types::SMALLINT, nullable: false, default: 0)]
    public int $used = 0;
}