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.
SQL way
- Safe, parameterized queries
- Precise control over SQL query structure
- When writing complex queries with advanced functions
- Optimize performance using specific SQL functions
Explorer
- Develop quickly without writing SQL
- Intuitive handling of relationships between tables
- Benefit from automatic query optimization
- Suitable for fast and convenient database work
Installation
Download and install the library using Composer:
composer require nette/database
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 |
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 aNette\Database\ConnectionException
.disconnect()
disconnects the current database connection.reconnect()
performs a disconnection and subsequent reconnection to the database. This method may also throw aNette\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";
};
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.
