Getting started

Yii DB is DAO (Data Access Object) layer for applications using PHP. It provides a set of classes that help you access relational databases. It's designed to be flexible and extensible, so that it can be used with different databases and different database schemas. Its database agnostic nature makes it easy to switch from one database to another.

Yii DB provides an object-oriented API for accessing relational databases. It's the foundation for other more advanced database access methods, including Query Builder.

When using Yii DB, you mainly need to deal with plain SQLs and PHP arrays. As a result, it's the most efficient way to access databases. However, because SQL syntax may vary for different databases, using Yii DB also means you have to take extra effort to create a database agnostic application.

Yii DB supports the following databases out of the box:

DB	Version
MSSQL	2017, 2019, 2022
MySQL	5.7 - 8.0
MariaDB	10.4 - 10.9
Oracle	12c - 21c
PostgreSQL	9.6 - 17
SQLite	3.3 or higher

Installation

To install Yii DB, you must select the driver you want to use and install it with Composer.

For MSSQL:

composer require yiisoft/db-mssql

For MySQL/MariaDB:

composer require yiisoft/db-mysql

For Oracle:

composer require yiisoft/db-oracle

For PostgreSQL:

composer require yiisoft/db-pgsql

For SQLite:

composer require yiisoft/db-sqlite

[!TIP] To cache database schema information, install PSR-16 implementation. For example, yiisoft/cache or one of the other cache handlers. It's strongly recommended for production.

Configure schema cache

First, you need to configure database schema cache.

Create connection

You can create a database connection instance using a DI container or without it.

• MSSQL Server
• MySQL/MariaDB Server
• Oracle Server
• PostgreSQL Server
• SQLite Server

[!NOTE] When you create a DB connection instance, the actual connection to the database isn't established until you execute the first SQL or call the Yiisoft\Db\Connection\ConnectionInterface::open() method explicitly.

Initialize ConnectionProvider if needed.

Table prefix

A connection uses no table prefix by default. If your tables share a prefix, set it after creating the connection:

use Yiisoft\Db\Connection\ConnectionInterface;

/** @var ConnectionInterface $db */
$db->setTablePrefix('app_');

Use % inside the special table quoting syntax to substitute the prefix:

$users = $db->createCommand('SELECT * FROM {{%user}}')->queryAll();

In this example, {{%user}} resolves to the app_user table. The configured prefix isn't applied to table names that don't contain %.

Logger and profiler

Logger and profiler are optional. You can use them if you need to log and profile your queries.

• Logger
• Profiler

Execute SQL queries

Once you have a database connection instance, you can execute an SQL query by taking the following steps:

1. Create a command and fetch data
2. Bind parameters
3. Execute a command
4. Execute many commands in a transaction

Quote table and column names

When writing a database-agnostic code, quoting table and column names is often a headache because different databases have different names quoting rules.

To overcome this problem, you may use the following quoting syntax introduced by Yii DB:

• [[column name]]: enclose a column name to quote in double square brackets.
• {{%table name}}: enclose a table name to quote in double curly brackets, and the percentage character % will be replaced with the table prefix.

Yii DB will automatically convert such constructs into the corresponding quoted column or table names using the DBMS-specific syntax.

For example, the following code will generate an SQL statement that's valid for all supported databases:

use Yiisoft\Db\Connection\ConnectionInterface;

/** @var ConnectionInterface $db */

$result = $db->createCommand('SELECT COUNT([[id]]) FROM {{%employee}}')->queryScalar()

Query Builder

Yii DB provides a Query Builder that helps you create SQL statements in a more convenient way. It's a powerful tool to create complex SQL statements in a simple way.

Commands

Yii DB provides a Command class that represents an SQL statement to be executed against a database.

You can use it to execute SQL statements that don't return any result set, such as INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, CREATE INDEX, DROP INDEX, etc.

• DDL commands
• DML commands

Schema

Yii DB provides a way to inspect the metadata of a database, such as table names, column names, etc. You can do it via schema:

• Reading database schema
• Configuring schema cache
• Casting values

Extensions

The following extensions are available for Yii DB.

• Active Record.
• Cache DB
• Data DB
• Log Target DB
• Rbac DB
• Translator Message DB
• Yii DB Migration