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

Yii has a database access layer built on top of PHP's [PDO](http://www.php.net/manual/en/ref.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 11
- [SQLite](http://sqlite.org/)
- [PostgreSQL](http://www.postgresql.org/)
- [CUBRID](http://www.cubrid.org/) (version 9.1.0 and higher).
12 13
- [Oracle](http://www.oracle.com/us/products/database/overview/index.html)
- [MSSQL](https://www.microsoft.com/en-us/sqlserver/default.aspx)
14

15

16 17 18 19 20 21 22
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
23
return [
24
	// ...
Alexander Makarov committed
25
	'components' => [
26
		// ...
Alexander Makarov committed
27
		'db' => [
28
			'class' => 'yii\db\Connection',
29 30 31
			'dsn' => 'mysql:host=localhost;dbname=mydatabase', // MySQL, MariaDB
			//'dsn' => 'sqlite:/path/to/database/file', // SQLite
			//'dsn' => 'pgsql:host=localhost;port=5432;dbname=mydatabase', // PostgreSQL
32
			//'dsn' => 'cubrid:dbname=demodb;host=localhost;port=33000', // CUBRID
33 34 35
			//'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
36
			//'dsn' => 'oci:dbname=//localhost:1521/mydatabase', // Oracle
37 38 39
			'username' => 'root',
			'password' => '',
			'charset' => 'utf8',
Alexander Makarov committed
40 41
		],
	],
42
	// ...
Alexander Makarov committed
43
];
44
```
45

46 47
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.
48

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

```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
56
than one connection component and use both at the same time if needed:
57 58 59 60 61 62

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

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

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

74

75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91
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
92
$command = $connection->createCommand('SELECT * FROM tbl_post WHERE id=1');
93
$post = $command->queryOne();
94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114
```

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
115
$command = $connection->createCommand('UPDATE tbl_post SET status=1 WHERE id=1');
116 117 118
$command->execute();
```

119
Alternatively the following syntax that takes care of proper table and column names quoting is possible:
120 121 122

```php
// INSERT
Alexander Makarov committed
123
$connection->createCommand()->insert('tbl_user', [
124 125
	'name' => 'Sam',
	'age' => 30,
Alexander Makarov committed
126
])->execute();
127 128

// INSERT multiple rows at once
Alexander Makarov committed
129 130 131 132 133
$connection->createCommand()->batchInsert('tbl_user', ['name', 'age'], [
	['Tom', 30],
	['Jane', 20],
	['Linda', 25],
])->execute();
134 135

// UPDATE
Alexander Makarov committed
136
$connection->createCommand()->update('tbl_user', ['status' => 1], 'age > 30')->execute();
137 138 139 140 141

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

142 143 144
Quoting table and column names
------------------------------

145
Most of the time you would use the following syntax for quoting table and column names:
146 147

```php
148
$sql = "SELECT COUNT([[$column]]) FROM {{$table}}";
149 150 151
$rowCount = $connection->createCommand($sql)->queryScalar();
```

152
In the code above `[[X]]` will be converted to properly quoted column name while `{{Y}}` will be converted to properly
153 154
quoted table name.

155 156 157 158 159 160 161 162 163
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();
```
164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 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

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
238
// CREATE TABLE
Alexander Makarov committed
239
$connection->createCommand()->createTable('tbl_post', [
240 241 242
	'id' => 'pk',
	'title' => 'string',
	'text' => 'text',
Alexander Makarov committed
243
];
244 245 246
```

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