Database access objects

Yii database access objects, commonly referred to as DAO, provide a powerful object-oriented API to work with a relational database. As the foundation for more complex database access, such as Query Builder and Active Record, DAO enables us to work directly with our database through SQL statements and PHP arrays. Consequently, it is significantly more performant to work with DAO statements than it is to work with either Active Record or Query Builder.

At the core of DAO is our yii\db\Connection class, or more commonly, our db component \Yii::$app->db. Since our db component is already properly configured for SQLite, we'll use it moving forward. With DAO, there are two general types of queries that we can run: queries that return data, such as SELECT queries, and queries that execute data, such as DELETE or UPDATE.

Tip

If you use the yii\db\Connection class directly, you'll need to explicitly call the open() method before you can run any queries against that connection.

Querying for data

The first way in which we can use DAO is to query for data. There are four main methods that are used to query for data: queryAll(), queryOne(), queryScalar(), and queryColumn().

The first method, queryAll(), is used to query for all the data in a specific table based upon the SQL statement used within the createCommand() method. Using our user table as an example, we can query for all the users in our database by running the following command:

$users = \Yii::$app->db
        ->createCommand('SELECT * FROM user;')
        ->queryAll();

After running this command, our $users variable will be populated with an array of users:

Array
(
    [0] => Array
    (
        [id] => 1
        [email] => test@example.com
        [password] => test123
        [first_name] => test
        [last_name] => user
        [created_at] => 0
        [updated_at] => 0
    )
)

The next method, queryOne(), is used to fetch a single record from the database.

$user = \Yii::$app->db
        ->createCommand('SELECT * FROM user WHERE id = 1;')
        ->queryOne();

The queryOne() method returns an array of data for a single element. In the event that no data is found, this method will return false:

Array
(
    [id] => 1
    [email] => test@example.com
    [password] => test123
    [first_name] => test
    [last_name] => user
    [created_at] => 0
    [updated_at] => 0
)

The third method, queryScalar(), is used to return the result of a SELECT query that returns a single value. For instance, if we want to count the number of users in our database, we can use queryScalar() to get the value:

$count = \Yii::$app->db
        ->createCommand('SELECT COUNT(*) FROM user;')
        ->queryScalar();

After running this command, our $count variable will be populated with the number of users in our database.

The final method, queryColumn(), is used to query a specific column in our database. For instance, if we want to know the email addresses of all the users in our database, we can use queryAll() to fetch all that data, or we can use queryColumn(), which would be significantly more efficient to use as it would query for less data:

$user = \Yii::$app->db
        ->createCommand('SELECT email FROM user;')
        ->queryColumn();

Like queryAll(), queryColumn() will return an array of results:

Array
(
    [0] => test@example.com
)

In the event that no results are found, queryColumn() will return an empty array.

With our knowledge of these methods, as an exercise, let's go back to our previous migrations and rewrite them to preserve our users across our schema change:

1. First, let's roll back our migrations to properly simulate the scenario:

   ./yii migrate/down
   

2. Then, we'll migrate our initial migration using the migrate/to command:

   ./yii migrate/to m150523_194158_init
   

3. Next, let's seed our database with some test data:

   sqlite3 /path/to/runtime/db.sqlite INSERT INTO user (email, password, name) VALUES ('test@example.com', 'test1', 'test user');
   INSERT INTO user (email, password, name) VALUES ('test2@example.com', 'test2', 'test user 2');
   

4. If we take a look at our database, we'll see that the initial schema and data is now in place.
   
5. Then, let's rewrite our migrations/…name_change.php migration to fetch our users from the database before running the initial migration that we created, and then reinsert our users back into our database. We'll do this using the queryAll() DAO method to fetch the data and the insert() method of yii\db\Migration to put it back into the database. The new code blocks have been highlighted for easy viewing:

   public function safeUp()
   {
       $users = \Yii::$app->db
                 ->createCommand('SELECT * FROM user')
                 ->queryAll();
             
       $this->dropTable('user');
       
       $this->createTable('user', [
           'id'           => Schema::TYPE_PK,
           'email'        => Schema::TYPE_STRING . ' NOT NULL',
           'password'     => Schema::TYPE_STRING . ' NOT NULL',
           'first_name'   => Schema::TYPE_STRING,
           'last_name'    => Schema::TYPE_STRING,
           'created_at'   => Schema::TYPE_INTEGER,
           'updated_at'   => Schema::TYPE_INTEGER
       ]);
       
       $this->createIndex('user_unique_email', 'user', 'email', true);
   
       foreach ($users as $user)
       {
           $this->insert('user', [
               'id' => $user['id'],
               'email' => $user['email'],
               'password' => $user['password'],
               'first_name' => $user['name'],
               'created_at' => $user['created_at'],
               'updated_at' => $user['updated_at']
           ]);
       }
   }

6. Now we can rerun our migration. If successful, we should see our original migration run and an insert call executed for each user in our database.

   ./yii migrate/up –interactive=0
   

   
7. Finally, we can query our SQLite database to preview the updated schema and see our updated users:

   sqlite3 /path/to/runtime/db.sqlite
   

   

As you can see, DAO's query method provides us with the ability to quickly and efficiently fetch data from our database.

Quoting table and column names

When writing database-agnostic SQL queries, properly quoting field names can be problematic. To avoid this problem, Yii2 provides the ability to automatically quote table and column names for you using the correct quoting rule for the specific database in use.

To automatically quote a column name, simply enclose the column name in square brackets:

[[column name]]

To automatically quote a table, simply enclose the table name in curly brackets:

{{table name}}

An example of both of these tools in action is shown as follows:

$result = \Yii::$app->db
          ->createCommand("SELECT COUNT([[id]]) FROM {{user}}")
          ->queryScalar();

Executing queries

While the query methods provide the ability to select data from our database, we often need to execute UPDATE or DELETE commands, which do not return data. To execute these commands, we can use the execute() method in general:

\Yii::$app->db
    ->createCommand('INSERT INTO user (email, password) VALUES ("test3@example.com", "test3");')
    ->execute();

If successful, the execute() method will return with true, whereas if it fails, it will return false.

Yii2 also provides convenient wrappers for insert(), update(), and delete(), which enables us to write commands without having to write raw SQL. These methods properly escape and quote table and column names and bind parameters on your behalf.

For instance, we can insert a new user into a database as follows:

// INSERT ( tablename, [ attributes => attr ] )
\Yii::$app->db
    ->createCommand()
    ->insert('user', [
      'email'      => 'test4@example.com',
      'password'   => 'changeme7',
      'first_name' => 'Test',
      'last_name'  => 'User',
      'created_at' => time(),
      'updated_at' => time()
    ])
   ->execute();

We can update all the users in our database using the update() method:

// UPDATE (tablename, [ attributes => attr ], condition )
\Yii::$app->db
    ->createCommand()
    ->update('user', [
        'updated_at' => time()
    ], '1 = 1')
   ->execute();

Tip

The last argument listed in our update command defines the where condition of our query command, which we'll cover in more detail later in the chapter. 1=1 is a common SQL idiom to update all records.

We can also delete a user in our database using the delete() method:

// DELETE ( tablename, condition )
\Yii::$app->db
    ->createCommand()
    ->delete('user', 'id = 3')
    ->execute();

Additionally, if you need to insert several rows at the same time, you can use the batchInsert() method, which can be significantly more efficient than inserting a single row at a time:

// batchInsert( tablename, [ properties ], [ rows ] )
\Yii::$app->db
    ->createCommand()
    ->batchInsert('user', ['email', 'password', 'first_name', 'last_name', 'created_at', 'updated_at'], 
    [
        ['james.franklin@example.com', 'changeme7', 'James', 'Franklin', time(), time()],
        ['linda.marks@example.com', 'changeme7', 'Linda', 'Marks', time(), time()]
        ['roger.martin@example.com', 'changeme7', 'Roger', 'Martin', time(), time()]
    ])
    ->execute();

Tip

Yii2 does not provide a batchUpdate() or batchDelete() method as bulk updates and deletes can be handled by the update() and delete() methods using a normal SQL.

Parameter binding

The number one rule when working with user-submitted data is to never trust user-submitted data. Any data that passes through our databases and has come from an end user needs to be validated, sanitized, and properly bound to our statements before they are executed against our database.

Take, for instance, the following query:

\Yii::$app->db
    ->createCommand("UPDATE user SET first_name = 'Tom' WHERE id  = " . $_GET['id'])
    ->execute();

Under normal circumstances, Yii would generate the following SQL, assuming $_GET['id'] had a value of 1:

UPDATE user SET first_name = 'Tom' WHERE id = 1;

While this is innocent enough, any user who can manipulate the $_GET['id'] variable can rewrite our query to something much more dangerous. For instance, they could drop our entire user table simply by substituting $_GET['id'] with 1; DROP TABLE user; --:

UPDATE user SET first_name = 'Tom' WHERE id = 1; DROP TABLE user; --

This kind of attack is called SQL injection. To help protect against SQL injection, Yii2 offers several different ways to bind parameters to our queries in a way that will filter our injected SQL. These three methods are bindValue(), bindValues(), and bindParam().

The first method, bindValue(), is used to bind a single parameter to a token within our SQL statement. For example, we can rewrite the previous query as follows:

\Yii::$app->db
    ->createCommand("UPDATE user SET first_name = :name WHERE id  = :id)
    ->bindValue(':name', 'Tom')
    ->bindValue(':id', $_GET['id'])
    ->execute();

Alternatively, we can use the bindValues() method to bind several parameters into a single call:

\Yii::$app->db
    ->createCommand("UPDATE user SET first_name = :name WHERE id  = :id)
    ->bindValues([ ':name' => 'Tom', ':id' => $_GET['id'] ])
    ->execute();

For convenience, the previous query can be rewritten so that the parameters are in line with the createCommand() method:

$params = [ ':name' => 'Tom', ':id' => $_GET['id'] ];
\Yii::$app->db
    ->createCommand("UPDATE user SET first_name = :name WHERE id  = :id, $params)
    ->execute();

The final method, bindParam(), is valued to bind parameters by reference rather than by value:

$id = 1;
$name = 'Tom';
$q = \Yii::$app->db
    ->createCommand("UPDATE user SET first_name = :name WHERE id  = :id)
    ->bindParam(':name', $name)
    ->bindParam(':id', $id);

Because bindParam() binds parameters by reference, we can change the bounded values to execute multiple queries. Following the previous example, we can write the following to update multiple users without having to rewrite our query each time:

$q->execute();
$id = 2;
$name = 'Kevin';
$q->execute();

Tip

Remember, the most important rule of working with user data is to never trust user-submitted data. Even in cases where you're 100% certain that SQL injection cannot happen, it's recommended that you use parameter binding rather than writing in line SQL. This will protect you against future changes to your code.

Transactions

When running multiple queries in a sequence, we often want to ensure that our database state remains consistent across these queries. Most modern databases support the use of transactions to accomplish this. In a transaction, changes are written to the database in such a way that they can be committed if everything went well or rolled back without consequence if any given query within the transaction failed. In Yii2, this looks as follows:

$transaction = \Yii::$app->db->beginTransaction();

try {
    \Yii::$app->db->createCommand($sql)->execute();
    \Yii::$app->db->createCommand($sql)->execute();
    //[ … more queries …]
    $transaction->commit();
} catch (\Exception $e) {
    $transaction->rollBack();
}