Connecting to databases

The primary component required in order to work with databases is the yii\db\Connection class. Through this class, we can connect to a variety of different database types, ranging from local SQLite databases to clustered MySQL databases. The simplest way to establish a connection to a database is to create a SQLite database connection, as follows:

$connection = new \yii\db\Connection([
    'dsn' => 'sqlite:/' . \Yii::getAlias('@app') . '/runtime/db.sqlite',
    'charset' => 'utf8'
]);

$connection->open();

Normally, however, we'll want to use a single database connection across our entire application. We can keep our application DRY by putting our database configuration into the db component of our web or console configuration file. Following the examples laid out in the previous chapters, this component will reference the config/env/<ENV>/db.php file. As an example, establishing a SQLite connection in this file will be done as follows:

<?php return [
    'dsn' => 'sqlite:/' . \Yii::getAlias('@app') . '/runtime/db.sqlite',
    'class' => 'yii\db\Connection',
    'charset' => 'utf8'
];

By storing our database configuration in the db component of our application, it can easily be shared between both our web and console applications without any additional effort on our part. Furthermore, since Yii2 loads components only when required, it can keep our application lean and performant.

Tip

In Yii2, components are only loaded when required. This process is often called lazy loading. Unless a component is preloaded, Yii2 will not create an instance of that component until it is first used. After being initially instantiated, Yii will then reuse the same component across your application rather than creating multiple instances of that component. Lazy loading is one of the primary reasons Yii is so performant.

With our database configuration stored within our configuration file, we can now access the database connection, as follows:

\Yii::$app->db;

This connection will also be shared to any Active Record models used in our application, which we'll discuss in Chapter 4, Active Record, Models, and Forms.

As stated earlier, Yii2 can connect to several different database types. As Yii2 binds on top of PHP's PDO library, it can connect to the same sources a native PDO driver can connect to. A few examples of the data source names (DSNs) that Yii2 supports are listed here:

Tip

If you're connecting to a MS SQL server, you'll need to have either the sqlsrv, dblib or mssql PHP drivers installed on your system. More information on these base drivers can be found within the PHP manual at https://php.net/manual/en/pdo.drivers.php.

Additionally, Oracle connections will require the installation of Oracle's OCI8 driver. More information on this driver can be found in the PHP manual at https://php.net/manual/en/book.oci8.php.

Note that Yii2 will not be able to connect to any database unless the appropriate PHP drivers are properly installed and configured. If you aren't certain which drivers you have installed, the native phpinfo() function can output a list of all the currently installed PHP extensions.

In addition to the base drivers listed earlier, Yii2 can also connect to databases over Open Database Connectivity (ODBC). When connecting to a database via ODBC, you'll need to specify the $driverName property within your db connection component so that Yii2 can properly connect to your database:

'components' => [
    // [...]
    'db' => [
        'class' => 'yii\db\Connection',
        'driverName' => 'mysql', 'dsn' => 'odbc:Driver={MySQL};Server=localhost;Database=test',
        'username' => 'username',
      'password' => 'password',
  ]
]

As shown previously, some database configurations may require you to specify a username or password to connect to them. Within the db component, simply specify the username and password attributes that are appropriate for your database.

Additional configuration options

In addition to the basic db component options listed previously, Yii2 also provides several additional options that can be used to either enhance the performance of your application or deal with a known issue within the native PHP drivers. While many of these options can be found in the Yii guide and the API documentation, some of them will most likely be used more often than others. These properties are $emulatePrepare, $enableQueryCache, and $enableSchemaCache.

Tip

A complete list of the available methods and properties for the yii\db\Connection class can be found at http://www.yiiframework.com/doc-2.0/yii-db-connection.html.

The first common attribute, $emulatePrepare, can be used to alleviate common issues identified by the Yii team when preparing database statements. By default, Yii2 will try to use the native prepare support built into the native PDO driver. To help alleviate issues with a few of the native PDO drivers (mainly, the MS SQL drivers), the $emulatePrepare attribute may need to be set to true in order to allow Yii2 to handle the prepare statements.

The next common property often enabled in our db component is $enableQueryCache. To improve the performance of our application, we can set this value to true and allow Yii to cache commonly executed queries. In an application that mostly performs read actions, enabling this attribute can greatly increase the performance of your application.

To completely enable this component, however, the additional properties we'll mention now must be set as well. The first property, $queryCache, specifies the named cache object that the query cache should use. If unset, this will simply default to the cache component in our application. The second property is $queryCacheDuration, and it determines how long any database query result will be cached for. By default, the query cache will be valid for 3,600 seconds, or 60 minutes:

'components' => [
  //[...
  'db' => [
        'dsn' => 'sqlite:/' . \Yii::getAlias('@app') . '/runtime/db.sqlite',
        'class' => 'yii\db\Connection',
        'charset' => 'utf8',
 'enableQueryCache' => true,
 'queryCache' => 'filecache',
 'queryCacheDuration' => 60
    ],
    'filecache' => [
        'class' => 'yii\caching\FileCache',
    ],
]

The final common property that often will be added to our db component is $enableSchemaCache. Before Yii accesses the database, it will often need to determine the database schema. This schema information is used to assist Yii when running validators and working with relational models, such as related Active Record models. Rather than having Yii try to determine our database schema on every request, we can tell it that our schema isn't changing by setting $enableSchemaCache to true.

Similar to the $enableCache parameter outlined previously, we'll also need to define the $schemaCache parameter, which will tell Yii what cache component to use. We'll also need to define the $schemaCacheDuration parameter so that Yii2 knows how long the schema cache is valid for in seconds:

'components' => [
  // [...]
  'db' => [
        'dsn' => 'sqlite:/' . \Yii::getAlias('@app') . '/runtime/db.sqlite',
        'class' => 'yii\db\Connection',
        'charset' => 'utf8',
 'enableSchemaCache' => true,
 'schemaCache' => 'filecache',
 'schemaCacheDuration' => 3600
    ],
    'filecache' => [
        'class' => 'yii\caching\FileCache',
    ],
]

As the majority of our controller actions will most likely result in a database operation, enabling these properties can greatly improve the performance of our application.

Tip

Remember that because $enableSchemaCache and $enableQueryCache are enabled, Yii2 will not perform common checks against the database. Any change to the underlying data or schema in your database may cause your application to return bad data or crash entirely. If you change the data in your database directly rather than through Yii2, or if you change the database schema, ensure that you flush the relevant cache components defined by $enableSchemaCache or $enableQueryCache to ensure that your application functions correctly.