database-basics.md 7.05 KB
Newer Older
1 2 3
Database basics
===============

4
Yii has a database access layer built on top of PHP's [PDO](http://www.php.net/manual/en/book.pdo.php). It provides
5 6 7
uniform API and solves some inconsistencies between different DBMS. By default Yii supports the following DBMS:

- [MySQL](http://www.mysql.com/)
8
- [MariaDB](https://mariadb.com/)
9 10
- [SQLite](http://sqlite.org/)
- [PostgreSQL](http://www.postgresql.org/)
Qiang Xue committed
11
- [CUBRID](http://www.cubrid.org/): version 9.1.0 or higher.
12
- [Oracle](http://www.oracle.com/us/products/database/overview/index.html)
Qiang Xue committed
13 14
- [MSSQL](https://www.microsoft.com/en-us/sqlserver/default.aspx): version 2012 or above is required if you
  want to use LIMIT/OFFSET.
15

16

17 18 19 20 21 22 23
Configuration
-------------

In order to start using database you need to configure database connection component first by adding `db` component
to application configuration (for "basic" web application it's `config/web.php`) like the following:

```php
Alexander Makarov committed
24
return [
25
	// ...
Alexander Makarov committed
26
	'components' => [
27
		// ...
Alexander Makarov committed
28
		'db' => [
29
			'class' => 'yii\db\Connection',
30 31 32
			'dsn' => 'mysql:host=localhost;dbname=mydatabase', // MySQL, MariaDB
			//'dsn' => 'sqlite:/path/to/database/file', // SQLite
			//'dsn' => 'pgsql:host=localhost;port=5432;dbname=mydatabase', // PostgreSQL
33
			//'dsn' => 'cubrid:dbname=demodb;host=localhost;port=33000', // CUBRID
34 35 36
			//'dsn' => 'sqlsrv:Server=localhost;Database=mydatabase', // MS SQL Server, sqlsrv driver
			//'dsn' => 'dblib:host=localhost;dbname=mydatabase', // MS SQL Server, dblib driver
			//'dsn' => 'mssql:host=localhost;dbname=mydatabase', // MS SQL Server, mssql driver
37
			//'dsn' => 'oci:dbname=//localhost:1521/mydatabase', // Oracle
38 39 40
			'username' => 'root',
			'password' => '',
			'charset' => 'utf8',
Alexander Makarov committed
41 42
		],
	],
43
	// ...
Alexander Makarov committed
44
];
45
```
46

47 48
Please refer to the [PHP manual](http://www.php.net/manual/en/function.PDO-construct.php) for more details
on the format of the DSN string.
49

50
After the connection component is configured you can access it using the following syntax:
51 52 53 54 55 56

```php
$connection = \Yii::$app->db;
```

You can refer to [[\yii\db\Connection]] for a list of properties you can configure. Also note that you can define more
57
than one connection component and use both at the same time if needed:
58 59 60 61 62 63

```php
$primaryConnection = \Yii::$app->db;
$secondaryConnection = \Yii::$app->secondDb;
```

64
If you don't want to define the connection as an application component you can instantiate it directly:
65 66

```php
Alexander Makarov committed
67
$connection = new \yii\db\Connection([
68 69 70
	'dsn' => $dsn,
 	'username' => $username,
 	'password' => $password,
Alexander Makarov committed
71
]);
72 73 74
$connection->open();
```

75

76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95
> **Tip**: if you need to execute additional SQL queries right after establishing a connection you can add the
> following to your application configuration file:
>
```php
return [
	// ...
	'components' => [
		// ...
		'db' => [
			'class' => 'yii\db\Connection',
			// ...
			'on afterOpen' => function($event) {
				$event->sender->createCommand("SET time_zone = 'UTC'")->execute();
			}
		],
	],
	// ...
];
```

96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112
Basic SQL queries
-----------------

Once you have a connection instance you can execute SQL queries using [[\yii\db\Command]].

### SELECT

When query returns a set of rows:

```php
$command = $connection->createCommand('SELECT * FROM tbl_post');
$posts = $command->queryAll();
```

When only a single row is returned:

```php
113
$command = $connection->createCommand('SELECT * FROM tbl_post WHERE id=1');
114
$post = $command->queryOne();
115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135
```

When there are multiple values from the same column:

```php
$command = $connection->createCommand('SELECT title FROM tbl_post');
$titles = $command->queryColumn();
```

When there's a scalar value:

```php
$command = $connection->createCommand('SELECT COUNT(*) FROM tbl_post');
$postCount = $command->queryScalar();
```

### UPDATE, INSERT, DELETE etc.

If SQL executed doesn't return any data you can use command's `execute` method:

```php
136
$command = $connection->createCommand('UPDATE tbl_post SET status=1 WHERE id=1');
137 138 139
$command->execute();
```

140
Alternatively the following syntax that takes care of proper table and column names quoting is possible:
141 142 143

```php
// INSERT
Alexander Makarov committed
144
$connection->createCommand()->insert('tbl_user', [
145 146
	'name' => 'Sam',
	'age' => 30,
Alexander Makarov committed
147
])->execute();
148 149

// INSERT multiple rows at once
Alexander Makarov committed
150 151 152 153 154
$connection->createCommand()->batchInsert('tbl_user', ['name', 'age'], [
	['Tom', 30],
	['Jane', 20],
	['Linda', 25],
])->execute();
155 156

// UPDATE
Alexander Makarov committed
157
$connection->createCommand()->update('tbl_user', ['status' => 1], 'age > 30')->execute();
158 159 160 161 162

// DELETE
$connection->createCommand()->delete('tbl_user', 'status = 0')->execute();
```

163 164 165
Quoting table and column names
------------------------------

166
Most of the time you would use the following syntax for quoting table and column names:
167 168

```php
169
$sql = "SELECT COUNT([[$column]]) FROM {{$table}}";
170 171 172
$rowCount = $connection->createCommand($sql)->queryScalar();
```

173
In the code above `[[X]]` will be converted to properly quoted column name while `{{Y}}` will be converted to properly
174 175
quoted table name.

176 177 178 179 180 181 182 183 184
The alternative is to quote table and column names manually using [[\yii\db\Connection::quoteTableName()]] and
[[\yii\db\Connection::quoteColumnName()]]:

```php
$column = $connection->quoteColumnName($column);
$table = $connection->quoteTableName($table);
$sql = "SELECT COUNT($column) FROM $table";
$rowCount = $connection->createCommand($sql)->queryScalar();
```
185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258

Prepared statements
-------------------

In order to securely pass query parameters you can use prepared statements:

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

Another usage is performing a query multiple times while preparing it only once:

```php
$command = $connection->createCommand('DELETE FROM tbl_post WHERE id=:id');
$command->bindParam(':id', $id);

$id = 1;
$command->execute();

$id = 2;
$command->execute();
```

Transactions
------------

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

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

Working with database schema
----------------------------

### Getting schema information

You can get a [[\yii\db\Schema]] instance like the following:

```php
$schema = $connection->getSchema();
```

It contains a set of methods allowing you to retrieve various information about the database:

```php
$tables = $schema->getTableNames();
```

For the full reference check [[\yii\db\Schema]].

### Modifying schema

Aside from basic SQL queries [[\yii\db\Command]] contains a set of methods allowing to modify database schema:

- createTable, renameTable, dropTable, truncateTable
- addColumn, renameColumn, dropColumn, alterColumn
- addPrimaryKey, dropPrimaryKey
- addForeignKey, dropForeignKey
- createIndex, dropIndex

These can be used as follows:

```php
259
// CREATE TABLE
Alexander Makarov committed
260
$connection->createCommand()->createTable('tbl_post', [
261 262 263
	'id' => 'pk',
	'title' => 'string',
	'text' => 'text',
Alexander Makarov committed
264
]);
265 266 267
```

For the full reference check [[\yii\db\Command]].