Yii Database (yiisoft/db)

Yii DB is a DAO (Data Access Object) layer providing an object-oriented API for relational databases. It is designed to be database-agnostic and extensible.

Core Components:

• Connection: Manages the link to the database (supports MSSQL, MySQL, MariaDB, Oracle, PostgreSQL, and SQLite).
• Command: Represents an SQL statement to be executed (supports DDL and DML).
• Query Builder: A tool for constructing complex SQL statements programmatically.
• Schema: Provides metadata inspection (table names, column names, etc.) and supports schema caching.
• Logger & Profiler: Optional tools for monitoring and debugging queries.

The Oracle driver automatically configures the session to use ISO 8601 date and time formats immediately after opening a connection. This ensures correct conversion of date and time values retrieved from the database. The driver executes the following SQL to set the session parameters:

ALTER SESSION SET
    NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF'
    NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFFTZH:TZM'
    NLS_TIME_FORMAT = 'HH24:MI:SSXFF'
    NLS_TIME_TZ_FORMAT = 'HH24:MI:SSXFFTZH:TZM'
    NLS_DATE_FORMAT = 'YYYY-MM-DD'

To write database-agnostic SQL that works across different DBMS (which have different quoting rules), use Yii DB's special quoting syntax:

• [[column name]]: Encloses a column name in double square brackets to ensure it is quoted correctly.
• {{%table name}}: Encloses a table name in double curly brackets. The % character is replaced by the configured table prefix.

Yii DB automatically converts these constructs into the appropriate quoted identifiers for your specific database driver.

use Yiisoft//Db/Connection/ConnectionInterface;

/** @var ConnectionInterface $db */

// This generates valid SQL for any supported database
$result = $db->createCommand('SELECT COUNT([[id]]) FROM {{%employee}}')->queryScalar();

When using function expression classes, the library treats string values as column names by default. To ensure a string is treated as a literal value rather than a column name, you must wrap it in a Value object.

Exception: If a string contains a parenthesis (, it is treated as a raw SQL expression. To avoid ambiguity and ensure correct quoting, always use Value for literal strings.

Example: new Longest(new Value('short'), 'column_name') renders as LONGEST('short', "column_name").

use Yiisoft\Db\Expression\Value\Value;
use Yiisoft\Db\Expression\Function\Longest;

new Longest(new Value('short'), 'column_name');

Constraint handling has been simplified. The generic Constraint class has been removed.

Existing constraints have been renamed and moved to a more direct naming convention:

• CheckConstraint $\rightarrow$ Check
• DefaultValueConstraint $\rightarrow$ DefaultValue
• ForeignKeyConstraint $\rightarrow$ ForeignKey
• IndexConstraint $\rightarrow$ Index

Implementation Note: Properties are now public readonly and must be initialized via the constructor. All previous methods have been removed; use the constructor for initialization and public properties to retrieve values.

The library provides classes implementing Yiisoft\Db\Expression\ExpressionInterface to represent complex SQL expressions. These expressions can be passed into various methods of Yiisoft\Db\Query\QueryInterface and Yiisoft\Db\Command\CommandInterface to inject raw or structured SQL logic instead of simple values.

Common integration points include:

• QueryInterface: select(), where(), having(), orderBy(), etc.
• CommandInterface: insert(), update(), upsert(), etc.

The Yiisoft\Db\Expression\Expression class allows you to include raw SQL fragments in your queries that do not require escaping or quoting. This is essential for using database functions (like NOW()), operators, or specific SQL constructs that the standard query builder methods do not support directly.

use Yiisoft\Db\Expression\Expression;

// Using a database function
new Expression('NOW()');

// Using column identifiers and operators
new Expression('[[column1]] + [[column2]]');

In the yiisoft/db package, there is a distinction between Yiisoft\Db\Query\Query and Yiisoft\Db\QueryBuilder\QueryBuilder:

• Yiisoft\Db\Query\Query: This is the primary class intended for end-user use. It provides a high-level, fluent interface to represent SQL statements. When you call an execution method (like all()), it implicitly invokes the QueryBuilder.
• Yiisoft\Db\QueryBuilder\QueryBuilder: This is the underlying engine responsible for the heavy lifting. It translates the abstract representation from Query into specific, DBMS-dependent SQL statements (e.g., handling the specific syntax for SELECT, FROM, WHERE, etc., for your specific database driver).