What’s new in Dbal 5.0 (RC1)

The last major Dbal 4.0 release happened on Sep 14, 2020, which is more than 2 and a half years. A lot happened, I planned to release new versions earlier, but sadly some difficulties in Orm delayed this a lot. But now it is the time we will move forward with Dbal & Orm. Nextras Dbal 5 is an RC1 right now.

Let’s explore what’s new and improved.

PHP 8.1+

To minimize maintenance costs we’re updating Orm & Dbal to PHP 8.1+ only code. The main motivation is easier maintenance. Also, having the new language features allow us to think differently about the API – usage of enums, strictly typed properties, read-only properties, and named arguments greatly shifts the point of view on how to shape the code.

For example, take a look at the new Column class used for database reflection:

  • Newly the Column is represented by a class, not just as a key-value array. All properties are typed not (only) by PHPDoc annotation but (also) by PHP type.
  • Properties are read-only. The immutability was natural to the old array approach, but conversion to the class would suffer from possible dangerous side effects. With the read-only modifier, you can be more sure of what’s happening in your code without introducing some verbose dummy getters.
  • Having so many “inputs” could be previously dangerous when we were creating the instance. Now we can nicely utilize the named args and be super sure we don’t switch isPrimary and isUnsigned values.
class Column
{
	public function __construct(
		public readonly string $name,
		public readonly string $type,
		public readonly ?int $size,
		public readonly ?string $default,
		public readonly bool $isPrimary,
		public readonly bool $isAutoincrement,
		public readonly bool $isUnsigned,
		public readonly bool $isNullable,
		/**
		 * @var mixed[]
		 * @phpstan-var array<string, mixed>
		 */
		public readonly array $meta = [],
	) {}
}

PDO drivers

Until now, Dbal supported three databases – MySQL, Postgres, and SQL server. All of them through native extensions: mysqli, pgsql, sqlsrv respectively.

Newly, we are introducing their PDO counterparts: pdo_mysql, pdo_pgsql, pdo_sqlsrv. Personally, I don’t find many reasons to use PDO, but you may want to share the connection with different libraries — they often use PDO, so now you can do so!

The configuration stays the same – no need to construct “connection strings”, just switch the driver name:

connection:
    # driver: mysqli
    driver: pdo_mysql
    username: test
    password: test

Having PDO support also means that we can continue with Sqlite support. Which is almost there, though there are some open questions about supported types and their conversions.

With PDO we had to come up also with new buffering support, as PDO may not support repeated reads (e.g. a repeated iteration over the result). Nextras Dbal handles this for you automatically and you don’t have to worry about working with the result. But, sometimes it may not be convenient to cache the data on the PHP side and you may opt out of buffering.

$unbuffered = $connection
    ->query('SELECT * FROM books ORDER BY id')
    ->unbuffered();

DateTime & LocalDateTime

There are no date-time-related semantical changes in Dbal 5.0, but we have renamed the %dts modifier. Previously, the modifier was a shortcut for “DateTime simple”, but nowadays this basic type is usually called LocalDateTime. (It is a date-time that does not consider time zones, e.g. a date of birth.)

The new modifier for Local DateTime is therefore %ldt.

Custom Processor Modifier Resolution

Until now, custom modifiers were possible, but you could use them only explicitly. Auto-type resolution was not possible. This was quite limiting especially if you had custom class types.

Now you can override resolution for both – explicit and implicit any type. This way, you can wire your modifier for your type automatically.

$processor->setCustomModifier(
	'myboolean',
	function (SqlProcessor $processor, $value) {
		assert($value instanceof MyBoolean);
		return $processor->processModifier('bool', $boolean->value);
	}
);

class MyBooleanModifierResolver implements ISqlProcessorModifierResolver
{
	public function resolve($value): ?string
	{
		if ($value instanceof \MyBoolean) {
			return 'myboolean';
		}
		return null;
	}
}

$processor->addModifierResolver(new MyBooleanModifierResolver());

$connection->query('...WHERE published = %any', new MyBoolean(true));

See the documentation on how to set up the custom modifier. The example shows an explicit usage of any type, but this is especially valueable when processing an array of data, e.g. when inserting:

$connection->query(
	'INSERT INTO data VALUES %values', ['published' => new MyBoolean(true)]
); 

Spread Modifier

In Postgres, it is not uncommon to work with array data types. To ease escaping, we are introducing a new variation of modifiers – a spread operator. Without the spread operator, the values would be properly escaped, but wrapped with ( ) round parenthesis. This may be unwanted, for example in this SQL expression.

$connection->query(
    'WHERE [roles.privileges] ?| ARRAY[%...s[]]',
    ['backend', 'frontend']
);
// WHERE "roles"."privileges" ?| ARRAY['backend', 'frontend']

The solution uses a triple dot before the modifier name (letter). Such modifiers will produce the comma-separated escaped values, but the wrapping is up to you.

New DB Reflection

We’ve improved the API of database reflection. Now the results are not just arrays, but fully typed objects. What’s more, we are paying attention to fully qualified name representations with a dedicated type.

Overall, this should help you to use it. It is not only type-safe now, but also IDE will nicely autocomplete all the possible properties.

PHPStan enhancements & literal-string

Nowadays, if you are not using a static analyzer, you’re doing something wrong. So we are taking an advantage of such tools and the source code is properly annotated with literal-string types. This will let you know if you’re using “dynamic”/user-entered input, instead of your custom manually written query.

Let’s take a look at what will be reported when you will use Dbal in the wrong way:

private function isNickAvailable(string $nick): bool
{
    return $this->connection
        ->query("SELECT nick FROM users WHERE nick = $nick")
        ->fetch() === null;
}

PHPStan will report then:

Parameter #1 $expression of method Nextras\Dbal\Connection::query() expects literal-string, non-falsy-string given

This should help you to notice the mistake and fix it using modifiers:

private function isNickAvailable(string $nick): bool
{
    return $this->connection
        ->query("SELECT nick FROM users WHERE nick = %s", $nick)
        ->fetch() === null;
}

PHP 8.1 BackedEnum Support

PHP 8.1 delivered Enum support. In the context of Dbal, we are interested in BackedEnums, i.e. enum cases that have an associated string|int value.

Those enum “instances” can be now passed for %i and %s modifiers.

enum UserType: int
{
    case Basic = 0;
    case Admin = 1;
}

$connection->query("SELECT * FROM users WHERE type = %i", UserType::Admin);

New SQL File Importer

The old implementation for multi-statement SQL file processing was a just static class, combining all “dialects” together. We have extracted this behavior to a new nextras/multi-query-parser repository that provides parsers for currently three SQL dialects – MySQL, Postgres, and SQL Server.

So don’t forget to add this dependency to your project.

composer require nextras/multi-query-parser

Then you can obtain the parser instance and fetch the queries and execute them:

$parser = $connection->getPlatform()->createMultiQueryParser();
foreach ($parser->parseFile(__DIR__ . "/migration.sql") as $query) {
	$connection->queryArgs($query);
}

Conclusion

First of all, thanks to all users, contributors & reporters. Without you, there would be no point in developing the library. My great gratitude goes to all financial backers. ๐Ÿ’™ Thank you. All others are welcome to join.

The next step is releasing Orm 5.0-rc1 and finally making another great milestone in this PHP database stack.

If you find any bugs, useless or undocumented bc breaks, please report them. Thank you.