Database Laravel Package

Getting Started

Know which layer you are using. On Nette Database 3.x there are two entry points: Nette\Database\Connection for raw SQL and Nette\Database\Explorer for the fluent table() / Selection / ActiveRow layer. In the 4.0 RC line the two are merged, so Connection becomes the primary entry point. If you are starting a new project, prefer the Nette DI extension and inject Explorer (3.x) or the merged Connection (4.x) rather than constructing services by hand.

Install:

composer require nette/database

Minimal standalone setup on 3.x:

use Nette\Caching\Storages\DevNullStorage;
use Nette\Database\Connection;
use Nette\Database\Explorer;
use Nette\Database\Structure;

$connection = new Connection(
    'mysql:host=127.0.0.1;dbname=app;charset=utf8mb4',
    'app',
    'secret',
    [
        'convertBoolean' => true,
        'convertDateTime' => true,
        'convertDecimal' => true,
    ],
);

$cache = new DevNullStorage();
$db = new Explorer($connection, new Structure($connection, $cache), $cache);

On 4.x, use the merged Connection directly:

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

First useful query:

$books = $db->table('book')
    ->where('published_at > ?', new DateTimeImmutable('-30 days'))
    ->order('title')
    ->limit(10);

foreach ($books as $book) {
    echo $book->title, ' by ', $book->author->name, PHP_EOL;
}

First writes and transaction:

$row = $db->table('book')->insert([
    'title' => 'New book',
    'author_id' => $authorId,
    'published_at' => new DateTimeImmutable(),
]);

$db->transaction(function ($db) use ($row) {
    $db->table('book')->where('id', $row->id)->update(['title' => 'Updated title']);
    $db->table('audit_log')->insert(['book_id' => $row->id, 'action' => 'update']);
});

Where to look first:

• Nette\Database\Explorer::table() and Nette\Database\Selection for query building.
• Nette\Database\ActiveRow and Nette\Database\GroupedSelection for relations.
• The repository’s docs/ directory — especially notes on lazy execution, accessed-column narrowing, and N+1 behavior.
• Nette\Database\Diagnostics\ConnectionPanel in development; in Nette DI it is normally wired automatically when Tracy is present.
• The Structure, Table, Column, Index, and ForeignKey reflection classes when you need schema metadata.

Implementation Patterns

Build queries with Selection, not string concatenation. Values are bound as parameters; identifiers are not. That distinction matters every day.

$selection = $db->table('book')
    ->select('book.id, book.title, author.name AS author_name')
    ->joinWhere('author', 'book.author_id = author.id')
    ->where('book.published_at > ?', $since)
    ->where('author.active', true)
    ->order('book.title')
    ->limit(20);

Common day-to-day patterns:

// Array values become IN (...)
$db->table('book')->where('id', [1, 2, 3]);

// OR conditions
$db->table('book')->whereOr([
    'title' => 'PHP',
    'author_id' => 7,
]);

// Backjoin through a belongs-to relation
$db->table('book')->where(':author.name', 'John');

// NULL handling
$db->table('book')->where('deleted_at', null); // IS NULL

// Raw SQL still uses the preprocessor
$db->query('SELECT * FROM book WHERE id IN (?)', $ids)->fetchAll();

Use Selection immutability deliberately. where(), order(), limit(), etc. return a modified clone. Forgetting to assign the result is a classic silent bug:

$selection->where('active', true);        // DISCARDED
$selection = $selection->where('active', true); // correct

Use the result API that matches the shape you need.

$row   = $db->table('book')->get(1);          // ActiveRow|null by primary key
$row   = $db->table('book')->where(...)->fetch(); // ActiveRow|null
$rows  = $db->table('book')->fetchAll();      // ActiveRow[]
$pairs = $db->table('book')->fetchPairs('id', 'title');
$field = $db->table('book')->fetchField('title');
$list  = $db->table('book')->fetchList('title');

fetchPairs() accepts a callback in newer 3.2.x releases, which is useful for projecting rows without an extra loop. fetchAssoc() returns an associative array by default, and fetchList() is an alias for fetchFields() in 3.2.5+.

Prefer explicit joins or backjoins over per-row relation access. The magic relation API is convenient, but it is the main N+1 trap:

// N+1: one author query per book
foreach ($db->table('book') as $book) {
    echo $book->author->name;
}

// Better: fetch the needed author data in the same query
$books = $db->table('book')
    ->select('book.*, author.name AS author_name')
    ->joinWhere('author', 'book.author_id = author.id')
    ->where('author.active', true);

For has-many relations, ActiveRow::related() returns a GroupedSelection:

$author = $db->table('author')->get(1);

$latestBooks = $author->related('book')
    ->where('published', true)
    ->order('published_at DESC')
    ->limit(5);

That limit(5) is per author/group, not a global limit. It can generate correlated subqueries, so check the SQL and EXPLAIN when the relation is large. GroupedSelection::refreshData() exists for long-running loops where cached parent data may go stale.

Keep Selection inside repositories. A practical service-layer pattern is to inject Explorer (or merged Connection) and expose only arrays, DTOs, or narrowly typed collections:

final class BookRepository
{
    public function __construct(private Nette\Database\Explorer $db)
    {
    }

    /** @return Nette\Database\ActiveRow[] */
    public function latest(int $limit): array
    {
        return $this->db->table('book')
            ->select('id, title, published_at')
            ->order('published_at DESC')
            ->limit($limit)
            ->fetchAll();
    }
}

For domain boundaries, map ActiveRow to DTOs rather than leaking the database layer throughout the application.

Use transactions for multi-statement writes and retry specific failures. In 3.1.2+ transaction() is nestable and passes the connection/explorer as the callback argument. Do not call beginTransaction(), commit(), or rollBack() inside it — those calls are forbidden in that context.

try {
    $db->transaction(function ($db) use ($order) {
        $db->table('orders')->insert($order);
        $db->table('stock')->where('product_id', $order['product_id'])
            ->update(['count -=' => 1]);
    });
} catch (Nette\Database\DeadlockException | Nette\Database\LockTimeoutException $e) {
    // retry with backoff
}

The 3.2.9 release added dedicated DeadlockException, LockTimeoutException, CheckConstraintViolationException, and ConnectionLostException types. Catch those instead of parsing generic driver messages.

Configure it through Nette DI when possible.

extensions:
    database: Nette\Database\DI\DatabaseExtension

database:
    dsn: 'mysql:host=127.0.0.1;dbname=app;charset=utf8mb4'
    user: app
    password: secret
    options:
        convertBoolean: true
        convertDateTime: true
        convertDecimal: true

Inject Nette\Database\Explorer in 3.x. For multiple connections, name them and inject the specific service alias, e.g. @database.default.explorer and @database.analytics.explorer. The DI extension creates .explorer as an alias to .context in 3.1.5+, so older code referencing .context usually keeps working during migration.

Use the Tracy panel as your primary debugging workflow. In Nette DI it appears automatically in debug mode when Tracy is available. Standalone initialization is via Nette\Database\Diagnostics\ConnectionPanel::initialize() (replacing the older Helpers::initializeTracy()). The panel shows queries, parameters, stack traces, source resolution, and performance colors. For quick one-off inspection, Helpers::dumpSql() can show the SQL with parameters substituted.

Test against the real driver when behavior matters. The 3.2.9 release added a Docker Compose setup for spinning up test databases locally. Use SQLite in-memory for fast unit tests, but run integration tests against MySQL/PostgreSQL/MSSQL/Oracle if your production behavior depends on driver-specific SQL, type conversion, or limit/offset handling. Use a DevNullStorage or test cache for Structure reflection to avoid stale metadata between tests.

Gotchas and Tips

• Selection is immutable. Every builder method returns a clone. $selection->where(...) without assignment does nothing.
• Execution is lazy. A Selection does not hit the database until you iterate, fetch*(), get(), or call an aggregate. This makes it easy to accidentally run the same query twice or to debug timing in the wrong place.
• Beware N+1 with ActiveRow relations. $book->author, $book->related(...), and repeated GroupedSelection access can produce one query per row. Use joins/backjoins or batch-load related data.
• GroupedSelection::limit() is per group. It is not a global limit. Inspect the generated SQL for correlated subqueries and test with realistic data volumes.
• Identifiers are not parameterized. select(), order(), group(), table names, and column names are SQL fragments. Never interpolate request data into them; whitelist allowed columns/directions.
• Array binding has sharp edges. where('id', [1, 2, 3]) becomes IN (...), and an empty array has special false-like semantics. IN ? and IN (?) are supported by the preprocessor, but do not hand-build IN lists.
• Multi-row inserts are key-dependent. When inserting an array of arrays, use uniform keys. Version 3.0.4 explicitly made multi-insert key-dependent; mismatched keys can produce surprising column lists.
• insert() return values differ by shape. Single-row insert returns an ActiveRow; multi-row insert returns an int-like result in 3.x. Prefer the returned row’s primary key over relying on lastInsertId().
• getInsertId() changed type in 4.0. It now returns an integer. Code that expected a string may need adjustment.
• fetch() returns null, not false, in 3.x+. The return type changed in 3.0. Do not write if ($row === false).
• Type conversion is driver-sensitive. convertBoolean, convertDateTime, and convertDecimal affect normalization. In 4.0, convertBoolean and newDateTime are enabled by default. MySQL TINYINT(1) becomes bool when boolean conversion is on; DECIMAL with precision 0 becomes int; zero dates may become null; time columns may reset the date to 0001-01-01. Test the exact values your application receives.
• newDateTime returns an immutable Nette\Database\DateTime. If your code mutates date objects in place, check whether you are receiving the immutable variant.
• 4.0 removes the PDO escape hatch. Connection::getPdo() and getDsn() are gone, and IRow / IRowContainer were removed. If you need direct PDO access, keep a separate PDO connection or implement a custom driver. Update type hints to ActiveRow and Selection.
• Transactions have rules. Use transaction() for nesting; do not call beginTransaction()/commit()/rollBack() inside the callback. Catch the specific deadlock/lock-timeout exception types for retries.
• DriverException is more useful than it looks. It carries query parameters in 3.0.5+ and mimics the original file/line of PDOException. Use it in logs, but avoid dumping sensitive parameters in production.
• Connection::getLastQueryString() is your quick debugger. It is useful when the Tracy panel is unavailable or when you need to log the last query in a worker/CLI context.
• EXPLAIN and the preprocessor interact. In 3.0.7+ the panel bypasses the preprocessor for EXPLAIN to avoid double processing. If you manually build an EXPLAIN with placeholders, verify what is actually sent.
• SqlPreprocessor only parameterizes certain statements. It enables database parameters for SELECT, INSERT, UPDATE, DELETE, REPLACE, and EXPLAIN. Do not assume placeholders are bound in DDL or arbitrary statements.
• IN() may inline parameters. In 3.2.7 the preprocessor inserts IN() parameters directly and bypasses binding. This is still safer than manual concatenation, but very large ID lists can bloat the query and hurt plan caching.
• Decimal handling is not uniform. Numeric/decimal detection was refined in 3.2.0, including DECIMAL with precision 0 as int. If your application depends on exact decimal strings/floats, assert the behavior per driver and per version.
• Structure reflection is cached. Reflection queries are expensive. Configure a real cache storage in production, and clear it after migrations or schema changes in development. PostgreSQL column-type caching was added to avoid excessive queries.
• Driver quirks leak through. SQL Server support in 3.2.4+ assumes SQL Server 2012+ for limit/offset. Oracle applyLimit() with offset consistency was fixed in 3.2.8. Avoid raw LIMIT syntax; let Selection apply the driver-specific limit.
• Selection::fixedAssoc() is deprecated. Use fetchAssoc(), fetchPairs(), or explicit mapping.
• Enums are converted on input, not output. A BackedEnum passed into a query is converted to its scalar value by SqlTranslator (3.1.5+). Result values are not automatically hydrated back into enums; use a row normalizer or map in your repository.
• Custom row normalization is the extension point for value objects. Newer 3.x releases expose a row normalizer hook; use it for custom scalar/date/enum hydration, but keep it deterministic and driver-independent.
• update() supports operator keys. ['count +=' => 1] works, but the column name is still an SQL identifier. Never source it from user input.
• quote() throws on failure. In 2.4.8+ it throws instead of silently returning false. Do not use it as a substitute for parameter binding.
• fetchPairs() and fetchAssoc() are convenient but hide types. They return arrays, not ActiveRow objects. This is good at boundaries and bad when you later need row methods such as update() or related().
• Static analysis is better in 3.2.4+. Selection gained generics support. Use the PHPDoc generics if you want PHPStan/Psalm to understand repository return types; runtime behavior is unchanged.
• Debug panels do not appear in production. ConnectionPanel::initialize() does not create a panel in production mode. Do not rely on it for production diagnostics; log DriverException details instead.