Nette Database

Nette Database is a powerful and elegant database layer for PHP with a focus on simplicity and smart features. It offers two complementary ways to work with your data – using the Explorer for rapid development, or the SQL way for full control over queries.

Installation

You can download and install the library using Composer:

composer require nette/database

Supported Databases

Nette Database supports the following databases:

Two Approaches to Database Work

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

SQL way – Writing SQL Queries

// 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 way – Automatic SQL Generation

// 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 SQL way example 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 and Configuration

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

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

The $dsn (Data Source Name) parameter follows the same format as PDO uses, 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. Add a database section, and the required objects will be created, including a database panel in the Tracy Debug Bar.

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, e.g.:

class Model
{
	public function __construct(
		// or Nette\Database\Explorer
		private Nette\Database\Connection $database,
	) {
	}
}

For more information, see database configuration.

Manual Creation of Explorer

If you're not using the Nette DI container, you can manually create an instance of Nette\Database\Explorer:

// database connection
$connection = new Nette\Database\Connection('mysql:host=127.0.0.1;dbname=mydatabase', 'user', 'password');
// cache storage, implements Nette\Caching\Storage, e.g.:
$storage = new Nette\Caching\Storages\FileStorage('/path/to/temp/dir');
// handles database structure reflection
$structure = new Nette\Database\Structure($connection, $storage);
// defines rules for mapping table names, columns, and foreign keys
$conventions = new Nette\Database\Conventions\DiscoveredConventions($structure);
$explorer = new Nette\Database\Explorer($connection, $structure, $conventions, $storage);

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 by setting lazy, or do it like this:

$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.

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

Tracy Debug Bar

If you use 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.