Built on top of PDO, Yii DAO (Database Access Objects) provides an object-oriented API for accessing relational databases. It is the foundation for other more advanced database access methods, including query builder and active record.
When using Yii DAO, you mainly need to deal with plain SQLs and PHP arrays. As a result, it is the most efficient way to access databases. However, because SQL syntax may vary for different databases, using Yii DAO also means you have to take extra effort to create a database-agnostic application.
In Yii 2.0, DAO supports the following databases out of the box:
* MySQL
* MariaDB
* SQLite
* PostgreSQL: version 8.4 or higher
* CUBRID: version 9.3 or higher.
* Oracle
* MSSQL: version 2008 or higher.
Note : New version of pdo_oci for PHP 7 currently exists only as the source code. Follow instruction provided by community to compile it or use PDO emulation layer.
Creating DB Connections : To access a database, you first need to connect to it by creating an instance of yii\db\Connection:
$db = new yii\db\Connection([
'dsn' => 'mysql:host=localhost;dbname=example',
'username' => 'root',
'password' => '',
'charset' => 'utf8',
]);
Because a DB connection often needs to be accessed in different places, a common practice is to configure it in terms of an application component like the following :
return [
// ...
'components' => [
// ...
'db' => [
'class' => 'yii\db\Connection',
'dsn' => 'mysql:host=localhost;dbname=example',
'username' => 'root',
'password' => '',
'charset' => 'utf8',
],
],
// ...
];
You can then access the DB connection via the expression Yii::$app->db.
Tip : You can configure multiple DB application components if your application needs to access multiple databases.
When configuring a DB connection, you should always specify its Data Source Name (DSN) via the dsn property. The format of DSN varies for different databases. Please refer to the PHP manual for more details. Below are some examples:
MySQL, MariaDB : mysql:host=localhost;dbname=mydatabase
SQLite : sqlite:/path/to/database/file
PostgreSQL : pgsql:host=localhost;port=5432;dbname=mydatabase
CUBRID : cubrid:dbname=demodb;host=localhost;port=33000
MS SQL Server (via sqlsrv driver) : sqlsrv:Server=localhost;Database=mydatabase
MS SQL Server (via dblib driver) : dblib:host=localhost;dbname=mydatabase
MS SQL Server (via mssql driver) : mssql:host=localhost;dbname=mydatabase
Oracle : oci:dbname=//localhost:1521/mydatabase
Note that if you are connecting with a database via ODBC, you should configure the yii\db\Connection::$driverName property so that Yii can know the actual database type. For example :
'db' => [
'class' => 'yii\db\Connection',
'driverName' => 'mysql',
'dsn' => 'odbc:Driver={MySQL};Server=localhost;Database=test',
'username' => 'root',
'password' => '',
],
