Nette Database
**************

.[perex]
Nette Database is a powerful and elegant database layer for PHP, known for its simplicity and smart features. It requires no complex configuration or entity generation, allowing you to start working with it immediately.

With Nette Database, you can work in two ways:

<div class="grid gap-3">
<div>


[Direct SQL]
============
- Safe, parameterized queries
- Precise control over the structure of SQL queries
- Ideal for writing complex queries with advanced functions
- Optimize performance using specific SQL functions

</div>

<div>


[Explorer]
==========
- Fast development without writing SQL
- Intuitive handling of relationships between tables
- Automatic query optimization
- Great for quick and convenient database interactions

</div>

</div>


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

You can download and install the library 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 follows the same format as [PDO uses |https://www.php.net/manual/en/pdo.construct.php#refsect1-pdo.construct-parameters], e.g., `host=127.0.0.1;dbname=test`. If the connection fails, it throws the `Nette\Database\ConnectionException`.

However, a more convenient method is to use [application configuration|configuration]. Add a `database` section, and the required objects will be created, including a database panel in the [Tracy |tracy:] Debug Bar.

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

After this, the connection object can be [retrieved as a service from the DI container|dependency-injection:passing-dependencies], e.g.:

```php
class Model
{
	public function __construct(
		private Nette\Database\Connection $database,
	) {
	}
}
```

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


Two Approaches to Database Work
===============================

With Nette Database, you can either write SQL queries directly (Direct approach) or let SQL be generated automatically (Explorer approach). Let’s see how both approaches solve the same tasks:

[Direct Approach|direct-sql] - Writing SQL Queries

```php
// Insert a record
$database->query('INSERT INTO books', [
	'author_id' => $authorId,
	'title' => $bookData->title,
	'published_at' => new DateTime,
]);

// Retrieve records: book authors
$result = $database->query('
	SELECT authors.*, COUNT(books.id) AS books_count
	FROM authors
	LEFT JOIN books ON authors.id = books.author_id
	WHERE authors.active = 1
	GROUP BY authors.id
');

// Display (not optimal, generates N additional queries)
foreach ($result as $author) {
	$books = $database->query('
		SELECT * FROM books
		WHERE author_id = ?
		ORDER BY published_at DESC
	', $author->id);

	echo "Author $author->name has written $author->books_count books:\n";

	foreach ($books as $book) {
		echo "- $book->title\n";
	}
}
```

[Explorer Approach|explorer] - Automatic SQL Generation

```php
// Insert a record
$database->table('books')->insert([
	'author_id' => $authorId,
	'title' => $bookData->title,
	'published_at' => new DateTime,
]);

// Retrieve records: book authors
$authors = $database->table('authors')
	->where('active', 1);

// Display (automatically generates only 2 optimized queries)
foreach ($authors as $author) {
	$books = $author->related('books')
		->order('published_at DESC');

	echo "Author $author->name has written {$books->count()} books:\n";

	foreach ($books as $book) {
		echo "- $book->title\n";
	}
}
```

The Explorer approach generates and optimizes SQL queries automatically. In the example above, the Direct approach generates N+1 queries (one for authors and one for the books of each author), while the Explorer performs only two optimized queries—one for authors and another for all their books.

You can freely combine both approaches in your application as needed.


Connection Management
=====================

When you create a `Connection` object, it connects to the database automatically. If you want to delay the connection, enable lazy mode in [configuration|configuration] by setting `lazy`, or do it like this:

```php
$database = new Nette\Database\Connection($dsn, $user, $password, ['lazy' => true]);
```

To manage the connection, use the methods `connect()`, `disconnect()`, and `reconnect()`.
- `connect()` establishes a connection if it hasn’t already been established and may throw a `Nette\Database\ConnectionException`.
- `disconnect()` disconnects from the database.
- `reconnect()` disconnects and then reconnects to the database, and may also throw a `Nette\Database\ConnectionException`.

Additionally, you can monitor connection events using the `onConnect` event, which is an array of callbacks executed after connecting to the database.

```php
// Called after connecting to the database
$database->onConnect[] = function($database) {
	echo "Connected to the database";
};
```


Tracy Debug Bar
===============

If you use [Tracy |tracy:], the Database panel in the Debug Bar is automatically enabled. It displays all executed queries, their parameters, execution time, and the location in the code where they were called.

[* db-panel.webp *]


Supported Databases
===================

Nette Database supports the following databases:

| **Database Server**  | **DSN Name** | **Explorer Support** |
|-----------------------|--------------|-----------------------|
| MySQL (>= 5.1)        | mysql        | YES                   |
| PostgreSQL (>= 9.0)   | pgsql        | YES                   |
| SQLite 3 (>= 3.8)     | sqlite       | YES                   |
| Oracle                | oci          | NO                    |
| MS SQL (PDO_SQLSRV)   | sqlsrv       | YES                   |
| MS SQL (PDO_DBLIB)    | mssql        | NO                    |
| ODBC                  | odbc         | NO                    |