Class yii\db\Connection

Connection represents a connection to a database via PDO.

Connection works together with yii\db\Command, yii\db\DataReader and yii\db\Transaction to provide data access to various DBMS in a common set of APIs. They are a thin wrapper of the PDO PHP extension.

Connection supports database replication and read-write splitting. In particular, a Connection component can be configured with multiple $masters and $slaves. It will do load balancing and failover by choosing appropriate servers. It will also automatically direct read operations to the slaves and write operations to the masters.

To establish a DB connection, set $dsn, $username and $password, and then call open() to connect to the database server. The current state of the connection can be checked using $isActive.

The following example shows how to create a Connection instance and establish the DB connection:

$connection = new \yii\db\Connection([
    'dsn' => $dsn,
    'username' => $username,
    'password' => $password,
]);
$connection->open();

After the DB connection is established, one can execute SQL statements like the following:

$command = $connection->createCommand('SELECT * FROM post');
$posts = $command->queryAll();
$command = $connection->createCommand('UPDATE post SET status=1');
$command->execute();

One can also do prepared SQL execution and bind parameters to the prepared SQL. When the parameters are coming from user input, you should use this approach to prevent SQL injection attacks. The following is an example:

$command = $connection->createCommand('SELECT * FROM post WHERE id=:id');
$command->bindValue(':id', $_GET['id']);
$post = $command->query();

For more information about how to perform various DB queries, please refer to yii\db\Command.

If the underlying DBMS supports transactions, you can perform transactional SQL queries like the following:

$transaction = $connection->beginTransaction();
try {
    $connection->createCommand($sql1)->execute();
    $connection->createCommand($sql2)->execute();
    // ... executing other SQL statements ...
    $transaction->commit();
} catch (Exception $e) {
    $transaction->rollBack();
}

You also can use shortcut for the above like the following:

$connection->transaction(function () {
    $order = new Order($customer);
    $order->save();
    $order->addItems($items);
});

If needed you can pass transaction isolation level as a second parameter:

$connection->transaction(function (Connection $db) {
    //return $db->...
}, Transaction::READ_UNCOMMITTED);

Connection is often used as an application component and configured in the application configuration like the following:

'components' => [
    'db' => [
        'class' => '\yii\db\Connection',
        'dsn' => 'mysql:host=127.0.0.1;dbname=demo',
        'username' => 'root',
        'password' => '',
        'charset' => 'utf8',
    ],
],

Public Properties

Public Methods

Protected Methods

Events

Property Details

$attributes public property

PDO attributes (name => value) that should be set when calling open() to establish a DB connection. Please refer to the PHP manual for details about available attributes.

public array $attributes = null

$charset public property

The charset used for database connection. The property is only used for MySQL, PostgreSQL and CUBRID databases. Defaults to null, meaning using default charset as configured by the database.

For Oracle Database, the charset must be specified in the $dsn, for example for UTF-8 by appending ;charset=UTF-8 to the DSN string.

The same applies for if you're using GBK or BIG5 charset with MySQL, then it's highly recommended to specify charset via $dsn like 'mysql:dbname=mydatabase;host=127.0.0.1;charset=GBK;'.

public string $charset = null

$commandClass public property (available since version 2.0.7)

The class used to create new database yii\db\Command objects. If you want to extend the yii\db\Command class, you may configure this property to use your extended version of the class.

See also createCommand().

public string $commandClass = 'yii\db\Command'

$driverName public property

Name of the DB driver

public string getDriverName ( )
public void setDriverName ( $driverName )

$dsn public property

The Data Source Name, or DSN, contains the information required to connect to the database. Please refer to the PHP manual on the format of the DSN string.

For SQLite you may use a path alias for specifying the database path, e.g. sqlite:@app/data/db.sql.

See also $charset.

public string $dsn = null

$emulatePrepare public property

Whether to turn on prepare emulation. Defaults to false, meaning PDO will use the native prepare support if available. For some databases (such as MySQL), this may need to be set true so that PDO can emulate the prepare support to bypass the buggy native prepare support. The default value is null, which means the PDO ATTR_EMULATE_PREPARES value will not be changed.

public boolean $emulatePrepare = null

$enableLogging public property (available since version 2.0.12)

Whether to enable logging of database queries. Defaults to true. You may want to disable this option in a production environment to gain performance if you do not need the information being logged.

See also $enableProfiling.

public boolean $enableLogging = true

$enableProfiling public property (available since version 2.0.12)

Whether to enable profiling of database queries. Defaults to true. You may want to disable this option in a production environment to gain performance if you do not need the information being logged.

See also $enableLogging.

public boolean $enableProfiling = true

$enableQueryCache public property

Whether to enable query caching. Note that in order to enable query caching, a valid cache component as specified by $queryCache must be enabled and $enableQueryCache must be set true. Also, only the results of the queries enclosed within cache() will be cached.

See also:

• $queryCache
• cache()
• noCache()

public boolean $enableQueryCache = true

$enableSavepoint public property

Whether to enable savepoint. Note that if the underlying DBMS does not support savepoint, setting this property to be true will have no effect.

public boolean $enableSavepoint = true

$enableSchemaCache public property

Whether to enable schema caching. Note that in order to enable truly schema caching, a valid cache component as specified by $schemaCache must be enabled and $enableSchemaCache must be set true.

See also:

• $schemaCacheDuration
• $schemaCacheExclude
• $schemaCache

public boolean $enableSchemaCache = false

$enableSlaves public property

Whether to enable read/write splitting by using $slaves to read data. Note that if $slaves is empty, read/write splitting will NOT be enabled no matter what value this property takes.

public boolean $enableSlaves = true

$isActive public read-only property

Whether the DB connection is established

public boolean getIsActive ( )

$lastInsertID public read-only property

The row ID of the last row inserted, or the last value retrieved from the sequence object

public string getLastInsertID ( $sequenceName = '' )

$master public read-only property

The currently active master connection. null is returned if there is no master available.

public yii\db\Connection getMaster ( )

$masterConfig public property

The configuration that should be merged with every master configuration listed in $masters. For example,

[
    'username' => 'master',
    'password' => 'master',
    'attributes' => [
        // use a smaller connection timeout
        PDO::ATTR_TIMEOUT => 10,
    ],
]

public array $masterConfig = []

$masterPdo public read-only property

The PDO instance for the currently active master connection.

public PDO getMasterPdo ( )

$masters public property

List of master connection configurations. Each configuration is used to create a master DB connection. When open() is called, one of these configurations will be chosen and used to create a DB connection which will be used by this object. Note that when this property is not empty, the connection setting (e.g. "dsn", "username") of this object will be ignored.

See also:

• $masterConfig
• $shuffleMasters

public array $masters = []

$password public property

The password for establishing DB connection. Defaults to null meaning no password to use.

public string $password = null

$pdo public property

The PHP PDO instance associated with this DB connection. This property is mainly managed by open() and close() methods. When a DB connection is active, this property will represent a PDO instance; otherwise, it will be null.

See also $pdoClass.

public PDO $pdo = null

$pdoClass public property

Custom PDO wrapper class. If not set, it will use PDO or yii\db\mssql\PDO when MSSQL is used.

See also $pdo.

public string $pdoClass = null

$queryBuilder public read-only property

The query builder for the current DB connection.

public yii\db\QueryBuilder getQueryBuilder ( )

$queryCache public property

The cache object or the ID of the cache application component that is used for query caching.

See also $enableQueryCache.

public yii\caching\CacheInterface|string $queryCache = 'cache'

$queryCacheDuration public property

The default number of seconds that query results can remain valid in cache. Defaults to 3600, meaning 3600 seconds, or one hour. Use 0 to indicate that the cached data will never expire. The value of this property will be used when cache() is called without a cache duration.

See also:

• $enableQueryCache
• cache()

public integer $queryCacheDuration = 3600

$schema public read-only property

The schema information for the database opened by this connection.

public yii\db\Schema getSchema ( )

$schemaCache public property

The cache object or the ID of the cache application component that is used to cache the table metadata.

See also $enableSchemaCache.

public yii\caching\CacheInterface|string $schemaCache = 'cache'

$schemaCacheDuration public property

Number of seconds that table metadata can remain valid in cache. Use 0 to indicate that the cached data will never expire.

See also $enableSchemaCache.

public integer $schemaCacheDuration = 3600

$schemaCacheExclude public property

List of tables whose metadata should NOT be cached. Defaults to empty array. The table names may contain schema prefix, if any. Do not quote the table names.

See also $enableSchemaCache.

public array $schemaCacheExclude = []

$schemaMap public property

Mapping between PDO driver names and yii\db\Schema classes. The keys of the array are PDO driver names while the values the corresponding schema class name or configuration. Please refer to Yii::createObject() for details on how to specify a configuration.

This property is mainly used by getSchema() when fetching the database schema information. You normally do not need to set this property unless you want to use your own yii\db\Schema class to support DBMS that is not supported by Yii.

public array $schemaMap = ['pgsql' => 'yii\db\pgsql\Schema', 'mysqli' => 'yii\db\mysql\Schema', 'mysql' => 'yii\db\mysql\Schema', 'sqlite' => 'yii\db\sqlite\Schema', 'sqlite2' => 'yii\db\sqlite\Schema', 'sqlsrv' => 'yii\db\mssql\Schema', 'oci' => 'yii\db\oci\Schema', 'mssql' => 'yii\db\mssql\Schema', 'dblib' => 'yii\db\mssql\Schema', 'cubrid' => 'yii\db\cubrid\Schema']

$serverRetryInterval public property

The retry interval in seconds for dead servers listed in $masters and $slaves. This is used together with $serverStatusCache.

public integer $serverRetryInterval = 600

$serverStatusCache public property

The cache object or the ID of the cache application component that is used to store the health status of the DB servers specified in $masters and $slaves. This is used only when read/write splitting is enabled or $masters is not empty.

public yii\caching\CacheInterface|string $serverStatusCache = 'cache'

$shuffleMasters public property (available since version 2.0.11)

Whether to shuffle $masters before getting one.

See also $masters.

public boolean $shuffleMasters = true

$slave public read-only property

The currently active slave connection. null is returned if there is no slave available and $fallbackToMaster is false.

public yii\db\Connection getSlave ( $fallbackToMaster = true )

$slaveConfig public property

The configuration that should be merged with every slave configuration listed in $slaves. For example,

[
    'username' => 'slave',
    'password' => 'slave',
    'attributes' => [
        // use a smaller connection timeout
        PDO::ATTR_TIMEOUT => 10,
    ],
]

public array $slaveConfig = []

$slavePdo public read-only property

The PDO instance for the currently active slave connection. null is returned if no slave connection is available and $fallbackToMaster is false.

public PDO getSlavePdo ( $fallbackToMaster = true )

$slaves public property

List of slave connection configurations. Each configuration is used to create a slave DB connection. When $enableSlaves is true, one of these configurations will be chosen and used to create a DB connection for performing read queries only.

See also:

• $enableSlaves
• $slaveConfig

public array $slaves = []

$tablePrefix public property

The common prefix or suffix for table names. If a table name is given as {{%TableName}}, then the percentage character % will be replaced with this property value. For example, {{%post}} becomes {{tbl_post}}.

public string $tablePrefix = ''

$transaction public read-only property

The currently active transaction. Null if no active transaction.

public yii\db\Transaction getTransaction ( )

$username public property

The username for establishing DB connection. Defaults to null meaning no username to use.

public string $username = null

Method Details

// The customer will be fetched from cache if available.
// If not, the query will be made against DB and cached for use next time.
$customer = $db->cache(function (Connection $db) {
    return $db->createCommand('SELECT * FROM customer WHERE id=1')->queryOne();
});

$db->cache(function (Connection $db) {

    // ... queries that use query cache ...

    return $db->noCache(function (Connection $db) {
        // this query will not use query cache
        return $db->createCommand('SELECT * FROM customer WHERE id=1')->queryOne();
    });
});

$result = $db->useMaster(function ($db) {
    return $db->createCommand('SELECT * FROM user LIMIT 1')->queryOne();
});

Event Details