Nette Database

Nette Database is a powerful and elegant database layer for PHP with a focus on simplicity and smart features. It offers two ways to work with your database: the Explorer for rapid application development, or the SQL way for direct query manipulation.

Installation

Download and install the library using Composer:

composer require nette/database

Supported Databases

Nette Database supports the following databases:

Two Approaches to Database Work

Nette Database gives you a choice: you can either write SQL queries directly (SQL way), or let them be generated automatically (Explorer). Let's see how both approaches handle the same tasks:

SQL way – 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 generates N+1 queries (one for authors and then one for the books of each author), while Explorer automatically optimizes queries and executes only two – one for authors and one for all their books.

Both approaches can be freely combined 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 is the same as used by PDO, e.g., host=127.0.0.1;dbname=test. In case of failure, it throws a Nette\Database\ConnectionException.

However, a more convenient method is offered by application configuration, where you just need to add a database section. This creates the necessary objects and also a database panel in the Tracy bar.

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

Then, the connection object can be obtained as a service from the DI container, e.g.:

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

More information about database configuration.

Manual Creation of Explorer

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

// 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 a Connection object is created, the connection is automatically established. If you want to delay the connection, use lazy mode – enable it in the configuration by setting lazy, or like this:

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

To manage the connection, use the connect(), disconnect(), and reconnect() methods.

• connect() creates a connection if one does not already exist, and may throw a Nette\Database\ConnectionException.
• disconnect() disconnects the current database connection.
• reconnect() performs a disconnection and subsequent reconnection to the database. This method may also throw a Nette\Database\ConnectionException.

Additionally, you can monitor events related to the connection using the onConnect event, which is an array of callbacks called after the database connection is established.

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

The onQuery event works similarly – it is an array of callbacks invoked after each executed query (and when a query fails), useful for logging or profiling.

Tracy Debug Bar

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