Database Core
*************

.[perex]
Nette Database Core is database abstraction layer and provides core functionality.


Installation
============

Download and install the package using [Composer|best-practices:composer]:

```shell
composer require nette/database
```


Connection and Configuration
============================

To connect to the database, simply create an instance of the [api:Nette\Database\Connection] class:

```php
$database = new Nette\Database\Connection($dsn, $user, $password);
```

The `$dsn` (data source name) parameter is [the same as used by PDO |https://www.php.net/manual/en/pdo.construct.php#refsect1-pdo.construct-parameters], eg `host=127.0.0.1;dbname=test`. In the case of failure it throws `Nette\Database\ConnectionException`.

However, a more sophisticated way offers [application configuration |configuration]. We will add a `database` section and it creates the required objects and a database panel in the [Tracy |tracy:] bar.

```neon
database:
	dsn: 'mysql:host=127.0.0.1;dbname=test'
	user: root
	password: password
```

The connection object we [receive as a service from a DI container |dependency-injection:passing-dependencies], for example:

```php
class Model
{
	// pass Nette\Database\Explorer to work with the Database Explorer layer
	public function __construct(
		private Nette\Database\Connection $database,
	) {
	}
}
```

For more information, see [database configuration|configuration].


Queries
=======

To query database use `query()` method that returns [ResultSet |api:Nette\Database\ResultSet].

```php
$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
}

echo $result->getRowCount(); // returns the number of rows if is known
```

.[note]
Over the `ResultSet` is possible to iterate only once, if we need to iterate multiple times, it is necessary to convert the result to the array via `fetchAll()` method.

You can easily add parameters to the query, note the question mark:

```php
$database->query('SELECT * FROM users WHERE name = ?', $name);

$database->query('SELECT * FROM users WHERE name = ? AND active = ?', $name, $active);

$database->query('SELECT * FROM users WHERE id IN (?)', $ids); // $ids is array
```

<div class=warning>
WARNING, never concatenate strings to avoid [SQL injection vulnerability |https://en.wikipedia.org/wiki/SQL_injection]!
/--
$db->query('SELECT * FROM users WHERE name = ' . $name); // WRONG!!!
\--
</div>

In the case of failure `query()` throws either `Nette\Database\DriverException` or one of its descendants:

- [ConstraintViolationException |api:Nette\Database\ConstraintViolationException] - violation of any constraint
- [ForeignKeyConstraintViolationException |api:Nette\Database\ForeignKeyConstraintViolationException] - invalid foreign key
- [NotNullConstraintViolationException |api:Nette\Database\NotNullConstraintViolationException] - violation of the NOT NULL condition
- [UniqueConstraintViolationException |api:Nette\Database\UniqueConstraintViolationException] - conflict of unique index

In addition to `query()`, there are other useful methods:

```php
// returns the associative array id => name
$pairs = $database->fetchPairs('SELECT id, name FROM users');

// returns all rows as array
$rows = $database->fetchAll('SELECT * FROM users');

// returns single row
$row = $database->fetch('SELECT * FROM users WHERE id = ?', $id);

// return single field
$name = $database->fetchField('SELECT name FROM users WHERE id = ?', $id);
```

In case of failure, all of these methods throw `Nette\Database\DriverException.`


Insert, Update & Delete
=======================

The parameter that we insert into the SQL query can also be the array (in which case it is possible to skip the wildcard `?`), which may be useful for the `INSERT` statement:

```php
$database->query('INSERT INTO users ?', [ // here can be omitted question mark
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978)

$id = $database->getInsertId(); // returns the auto-increment of inserted row

$id = $database->getInsertId($sequence); // or sequence value
```

Multiple insert:

```php
$database->query('INSERT INTO users', [
	[
		'name' => 'Jim',
		'year' => 1978,
	], [
		'name' => 'Jack',
		'year' => 1987,
	],
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978), ('Jack', 1987)
```

We can also pass files, DateTime objects or [enumerations |https://www.php.net/enumerations]:

```php
$database->query('INSERT INTO users', [
	'name' => $name,
	'created' => new DateTime, // or $database::literal('NOW()')
	'avatar' => fopen('image.gif', 'r'), // inserts file contents
	'status' => State::New, // enum State
]);
```

Updating rows:

```php
$result = $database->query('UPDATE users SET', [
	'name' => $name,
	'year' => $year,
], 'WHERE id = ?', $id);
// UPDATE users SET `name` = 'Jim', `year` = 1978 WHERE id = 123

echo $result->getRowCount(); // returns the number of affected rows
```

For UPDATE, we can use operators `+=` and `-=`:

```php
$database->query('UPDATE users SET', [
	'age+=' => 1, // note +=
], 'WHERE id = ?', $id);
// UPDATE users SET `age` = `age` + 1
```

Deleting:

```php
$result = $database->query('DELETE FROM users WHERE id = ?', $id);
echo $result->getRowCount(); // returns the number of affected rows
```


Advanced Queries
================

Insert or update, if it already exists:

```php
$database->query('INSERT INTO users', [
	'id' => $id,
	'name' => $name,
	'year' => $year,
], 'ON DUPLICATE KEY UPDATE', [
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`id`, `name`, `year`) VALUES (123, 'Jim', 1978)
//   ON DUPLICATE KEY UPDATE `name` = 'Jim', `year` = 1978
```

Note that Nette Database recognizes the SQL context in which the array parameter is inserted and builds the SQL code accordingly. So, from the first array he generates `(id, name, year) VALUES (123, 'Jim', 1978)`, while the second converts to `name = 'Jim', year = 1978`.

We can also describe sorting using array, in keys are column names and values are boolean that determines whether to sort in ascending order:

```php
$database->query('SELECT id FROM author ORDER BY', [
	'id' => true, // ascending
	'name' => false, // descending
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC
```

If the detection did not work, you can specify the form of the assembly with a wildcard `?` followed by a hint. These hints are supported:

| ?values | (key1, key2, ...) VALUES (value1, value2, ...)
| ?set    | key1 = value1, key2 = value2, ...
| ?and    | key1 = value1 AND key2 = value2 ...
| ?or     | key1 = value1 OR key2 = value2 ...
| ?order  | key1 ASC, key2 DESC

The WHERE clause uses the `?and` operator so conditions are linked by `AND`:

```php
$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	'year' => $year,
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND `year` = 1978
```

Which can easily be changed to `OR` by using the `?or` wildcard:

```php
$result = $database->query('SELECT * FROM users WHERE ?or', [
	'name' => $name,
	'year' => $year,
]);
// SELECT * FROM users WHERE `name` = 'Jim' OR `year` = 1978
```

We can use operators in conditions:

```php
$result = $database->query('SELECT * FROM users WHERE', [
	'name <>' => $name,
	'year >' => $year,
]);
// SELECT * FROM users WHERE `name` <> 'Jim' AND `year` > 1978
```

And also enumerations:

```php
$result = $database->query('SELECT * FROM users WHERE', [
	'name' => ['Jim', 'Jack'],
	'role NOT IN' => ['admin', 'owner'], // enumeration + operator NOT IN
]);
// SELECT * FROM users WHERE
//   `name` IN ('Jim', 'Jack') AND `role` NOT IN ('admin', 'owner')
```

We can also include a piece of custom SQL code using the so-called SQL literal:

```php
$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	'year >' => $database::literal('YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (`year` > YEAR())
```

Alternatively:

```php
$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (year > YEAR())
```

SQL literal also can have its parameters:

```php
$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > ? AND year < ?', $min, $max),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (year > 1978 AND year < 2017)
```

Thanks to which we can create interesting combinations:

```php
$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('?or', [
		'active' => true,
		'role' => $role,
	]),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (`active` = 1 OR `role` = 'admin')
```


Variable Name
=============

There is a `?name` wildcard that you use if the table name or column name is a variable. (Beware, do not allow the user to manipulate the content of such a variable):

```php
$table = 'blog.users';
$column = 'name';
$database->query('SELECT * FROM ?name WHERE ?name = ?', $table, $column, $name);
// SELECT * FROM `blog`.`users` WHERE `name` = 'Jim'
```


Transactions
============

There are three methods for dealing with transactions:

```php
$database->beginTransaction();

$database->commit();

$database->rollback();
```

An elegant way is offered by the `transaction()` method. You pass the callback that is executed in the transaction. If an exception is thrown during execution, the transaction is dropped, if everything goes well, the transaction is committed.

```php
$id = $database->transaction(function ($database) {
	$database->query('DELETE FROM ...');
	$database->query('INSERT INTO ...');
	// ...
	return $database->getInsertId();
});
```

As you can see, the `transaction()` method returns the return value of the callback.

The transaction() can also be nested, which simplifies the implementation of independent repositories.


Reflection .{data-version:3.2.1}
================================

Nette Database provides tools for introspecting database structure through the [api:Nette\Database\Reflection] class. This class allows you to retrieve information about tables, columns, indexes, and foreign keys. You can use reflection to generate schemas, create flexible applications that work with databases, or build general database tools.

You can obtain a reflection object from a database connection instance:

```php
$reflection = $database->getReflection();
```


Working with Tables
-------------------

Using reflection, you can iterate over all tables in the database:

```php
// List the names of all tables
foreach ($reflection->tables as $tableName => $table) {
    echo $tableName . "\n";
}

// Check if a table exists
if ($reflection->hasTable('users')) {
    echo "The 'users' table exists";
}

// Retrieve a specific table
$table = $reflection->getTable('users');
```


Column Information
------------------

For each table, you can get detailed information about its columns:

```php
// Iterate over all columns
foreach ($table->columns as $column) {
    echo "Column: " . $column->name . "\n";
    echo "Type: " . $column->nativeType . "\n";
    echo "Nullable: " . ($column->nullable ? 'Yes' : 'No') . "\n";
    echo "Default value: " . ($column->default ?? 'None') . "\n";
    echo "Primary key: " . ($column->primary ? 'Yes' : 'No') . "\n";
    echo "Auto-increment: " . ($column->autoIncrement ? 'Yes' : 'No') . "\n";
}

// Retrieve a specific column
$idColumn = $table->getColumn('id');
```


Indexes and Primary Keys
------------------------

Reflection provides information about indexes and primary keys:

```php
$listColumnNames = fn(array $columns) => implode(', ', array_map(fn($col) => $col->name, $columns));

// List all indexes
foreach ($table->indexes as $index) {
    echo "Index: " . ($index->name ?? 'Unnamed') . "\n";
    echo "Columns: " . $listColumnNames($index->columns) . "\n";
    echo "Unique: " . ($index->unique ? 'Yes' : 'No') . "\n";
    echo "Primary key: " . ($index->primary ? 'Yes' : 'No') . "\n";
}

// Retrieve the primary key
if ($table->primaryKey) {
    echo "Primary key: " . $listColumnNames($table->primaryKey->columns) . "\n";
}
```


Foreign Keys
------------

You can also get information about foreign keys:

```php
foreach ($table->foreignKeys as $fk) {
    echo "Foreign key: " . ($fk->name ?? 'Unnamed') . "\n";
    echo "Local columns: " . $listColumnNames($fk->localColumns) . "\n";
    echo "References table: {$fk->foreignTable->name}\n";
    echo "References columns: " . $listColumnNames($fk->foreignColumns) . "\n";
}
```