Explore Yii Database 2.0: New Features, Installation & Quick Start Guide

Yii Database 2.0

Yii Database 2.0 is a major release that modernizes the database abstraction layer, adds PHP 8.1+ features, stronger type safety and new capabilities. Drivers for PostgreSQL, MySQL/MariaDB, MSSQL, Oracle and SQLite are provided.

New Features

ColumnInterface implementation based on table column data types for faster type conversion.

ConnectionProvider for connection management.

ColumnBuilder for column creation.

CaseX expression for CASE‑WHEN‑THEN‑ELSE statements.

New conditions: All, None, ArrayOverlaps, JsonOverlaps.

Support for PHP fallback enums.

User‑defined type conversion.

ServerInfoInterface and its implementations.

Enhanced Capabilities

Optimized SQL generation and query building.

Psalm annotations to improve type safety.

Fluent method chaining on column classes.

Better exception messages.

Refactored core components for maintainability.

Support for PHP 8.5.

Installation

Install via Composer:

composer require yiisoft/db yiisoft/db-sqlite

Note: you must install yiisoft/db and at least one driver package (e.g., yiisoft/db-mysql , yiisoft/db-pgsql , yiisoft/db-sqlite ) to actually connect to a database.

Basic Usage

Create a connection instance for the appropriate driver:

use Yiisoft\Db\Sqlite\Connection;
use Yiisoft\Db\Sqlite\Driver;

// $cache is a PSR‑16 cache implementation
$db = new Connection(
    new Driver('sqlite:memory:'),
    new SchemaCache($cache),
);

Use the $db object to run queries, manage transactions, etc. Example query builder, insert, and transaction:

use Yiisoft\Db\Connection\ConnectionInterface;

// Query builder
$rows = $db
    ->select(['id', 'email'])
    ->from('{{%user}}')
    ->where(['last_name' => 'Smith'])
    ->limit(10)
    ->all();

// Insert
$db->createCommand()
    ->insert('{{%user}}', [
        'email' => '[email protected]',
        'first_name' => 'Mike',
        'last_name' => 'Smith',
    ])
    ->execute();

// Transaction
$db->transaction(static function (ConnectionInterface $db) {
    $db->createCommand()
        ->update('{{%user}}', ['status' => 'active'], ['id' => 1])
        ->execute();

    $db->createCommand()
        ->update('{{%profile}}', ['visibility' => 'public'], ['user_id' => 1])
        ->execute();
});