nette-database
Provides Nette Database conventions and API reference. Use when working with database entities, Selection API, ActiveRow, SQL queries, or database configuration in Nette.
SKILL.mdplugins/nette/skills/nette-database
GitHubDatabase
Uses Nette Database, typically with MySQL, PostgreSQL or SQLite as the backend.
composer require nette/database
See the Explorer documentation for the full ActiveRow/Selection API. See the SQL way documentation for direct SQL queries.
Database Conventions
- Table names use singular form (e.g.,
usernotusers) - Use TINYINT(1) for booleans
- Use
idfor primary keys - Character encoding:
utf8mb4with appropriate collation (e.g.utf8mb4_0900_ai_ci, orutf8mb4_cs_0900_ai_cifor Czech) - Standard timestamp fields:
created_atDATETIME DEFAULT CURRENT_TIMESTAMP- never use TIMESTAMP for date/time fields
Entity Mapping
Nette Database has a built-in EntityMapping interface (since 3.2) that the Explorer uses to resolve an ActiveRow subclass for each table and (optionally) translate between column names and PHP property names. The default implementation (DefaultEntityMapping) supports:
- Table-to-class map with wildcards. Keys may be exact names (
special_table), wildcard patterns (forum_*) or a bare*catch-all. Class names may contain*, which is replaced with the PascalCase of the captured portion:forum_post+App\Forum\*Row→App\Forum\PostRow. A class without*is a fixed class (log_*: App\Logging\LogRowmaps everylog_*table to the sameLogRow). Schema prefixes likepublic.are stripped before PascalCase conversion. Exact keys take precedence; wildcard entries are tried in declaration order, so put more specific patterns first and the bare*last. - camelCase column ↔ property translation — when enabled, column
author_idis exposed as propertyauthorIdeverywhere: property access, iteration,toArray(),insert(),update(), and column references inwhere()/order().
Activate via the mapping config option (see Database Configuration below).
Entity Design Strategy
All entities in App\Entity with consistent Row suffix (matches the App\Entity\*Row convention):
producttable →ProductRoworder_itemtable →OrderItemRowvariant_expirationtable →VariantExpirationRow
Why flat: Entities are data structures that cross domain boundaries. A ProductRow might be used in catalog, orders, inventory, and reporting contexts. Subdividing entities by domain forces you to either pick one arbitrary "home" domain or duplicate references.
Entity Organization
All entities in single App\Entity namespace - avoid domain subdivision:
app/Entity/
├── ProductRow.php ← Core business entities
├── OrderItemRow.php ← Relationship entities
└── StockTransferRow.php ← Operational entities
Entity Documentation Patterns
use Nette\Database\Table;
/**
* @property-read int $id
* @property-read string $title
* @property-read bool $active
* @property-read ?CategoryRow $category ← nullable relationship
* @property-read UserRow $author ← required relationship
*/
final class ProductRow extends Table\ActiveRow
{
}
Documentation rules:
- Document ALL accessible properties (including inherited id)
- Use nullable types for optional foreign keys
- Include relationship properties for IDE navigation
- Match database schema exactly
Entity Relationships in phpDoc
Foreign key patterns:
@property-read ?CategoryRow $categoryfor optional relationships@property-read UserRow $authorfor required relationships@property-read Selection<OrderItemRow> $order_itemsfor back-references
Naming convention: Follow Nette Database relationship naming (foreign key without _id suffix).
With camelCase: true mapping: scalar columns map to camelCase properties ($firstName, $createdAt). Relationship property names ($author, $category) are resolved by Conventions against database columns, so they aren't affected by the camelCase setting.
When to Use Selection API
Use for:
- Simple filtering and sorting
- Standard CRUD operations
- Queries that benefit from lazy loading
- When you need to chain conditions dynamically
return $this->db->table('product')
->where('active', true)
->where('category_id', $categoryId)
->order('name');
When to Use Raw SQL
Use for:
- Complex analytics and reporting
- Recursive queries (WITH RECURSIVE)
- Performance-critical queries
- Complex joins that are awkward in Selection API
return $this->db->query('
WITH RECURSIVE category_tree AS (...)
SELECT ...
', $params)->fetchAll();
Query Building Patterns
Build queries by progressive refinement – start with a base method, then add conditions. Always use generic types for Selection returns:
/** @return Selection<ProductRow> */
public function getProducts(): Selection
{
return $this->db->table('product');
}
/** @return Selection<ProductRow> */
public function getActiveProducts(): Selection
{
return $this->getProducts()->where('active', true);
}
/** @return Selection<ProductRow> */
public function getProductsInCategory(int $categoryId): Selection
{
return $this->getActiveProducts()
->where(':product_category.category_id', $categoryId);
}
Relationship Navigation
Use colon notation for efficient joins:
// Forward relationship (via foreign key)
->where('category.slug', $categorySlug)
// Back-reference (reverse relationship)
->where(':order_item.quantity >', 1)
// Deep relationships
->where('category.parent.name', 'Root Category')
Traps in where() and SQL Fragments
The array KEY is SQL, the value is a bound parameter. where() splits the key on the first
space into a column and an operator and emits both into the query, so a key built from user
input is an injection hole – and mass-assigning a request array into where() or insert()
lets the client choose columns and operators:
$db->table('user')->where(['id >' => 5]); // fine: key is yours, value is bound
$db->table('user')->where($_GET['filter']); // NEVER: keys come from the client
Write SQL keywords in UPPERCASE inside fragments. Explorer quotes lowercase words as
identifiers, so where('name like ?', $x) compiles to `name` `like` ? and fails; uppercase
words are left alone:
->where('name LIKE ?', "%$q%") // correct
->where('name like ?', "%$q%") // `like` gets quoted as a column name
An empty array flips the meaning of a condition. where('id', []) becomes
IS NULL AND FALSE (matches nothing), while where('id NOT', []) becomes IS NULL OR TRUE
(matches everything). Guard empty filter lists explicitly. Mixing operators without
parentheses raises Possible SQL query corruption. Add parentheses around operators.
Fetching Strategies by Use Case
Single optional result: ->fetch()
All results as array: ->fetchAll()
Key-value pairs: ->fetchPairs('key_column', 'value_column')
Single scalar value: ->fetchField() (first column of first row)
Count only: ->count('*')
Structured data with fetchAssoc:
// Key by column value
$byId = $db->table('product')->fetchAssoc('id');
// [1 => ProductRow, 2 => ProductRow, ...]
// Group by column
$byCategory = $db->table('product')->fetchAssoc('category_id[]');
// [5 => [ProductRow, ProductRow], 8 => [ProductRow, ...]]
// Nested grouping
$nested = $db->table('product')->fetchAssoc('category_id|active');
// [5 => [true => ProductRow, false => ProductRow], ...]
The path string uses [] for array grouping, | for nested keys, and = to extract a single value.
Schema and Constraints
Use direct SQL migrations rather than ORM-style migrations – store schema in sql/db.sql with manual migration scripts. Rely on database constraints (foreign keys, unique, check) for data integrity and handle constraint violations in services with meaningful business exceptions.
Transactions
Wrap multi-step writes in transactions to ensure consistency:
$this->db->transaction(function () use ($data, $items) {
$order = $this->db->table('order')->insert($data);
foreach ($items as $item) {
$order->related('order_item')->insert($item);
}
});
The callback approach automatically commits on success and rolls back on exception, and it returns whatever the callback returns, so you can produce a value out of it.
Nested transaction() calls are counted, and only the outermost one actually commits. Calling
beginTransaction(), commit() or rollBack() manually inside the callback throws
LogicException – pick one style per code path and stay with it.
Anti-Patterns to Avoid
Don't create separate Repository classes – in Nette, services combine data access with business logic. A separate repository layer adds indirection without benefit because Nette Database Explorer already provides a clean query API. The service IS the repository.
Error Handling
Transform database errors to business exceptions:
try {
$customer->update(['email' => $newEmail]);
} catch (Nette\Database\UniqueConstraintViolationException) {
throw new EmailAlreadyExistsException();
}
Handle at service boundary – presenters should receive business exceptions, not database exceptions. This keeps the UI layer independent of the storage layer and produces meaningful error messages.
Database Configuration
database:
dsn: 'mysql:host=127.0.0.1;dbname=myapp'
user: root
password: secret
# Entity mapping (table → ActiveRow subclass, optional column ↔ property)
mapping:
tables: App\Entity\*Row # string shortcut: equivalent to { '*': 'App\Entity\*Row' }
options:
lazy: true # Connect on first query
charset: utf8mb4 # Default
convertBoolean: true # TINYINT(1) to bool
newDateTime: true # Return DateTimeImmutable
Full mapping form when you need explicit overrides or camelCase properties:
database:
dsn: 'mysql:host=127.0.0.1;dbname=myapp'
mapping:
tables:
special_table: App\Entity\SpecialRow # exact match wins over wildcards
forum_*: App\Forum\*Row # wildcard in key (* captured as PascalCase)
"*": App\Entity\*Row # catch-all fallback (must be last)
camelCase: true # column author_id ↔ property authorId
Multiple connections:
database:
main:
dsn: 'mysql:host=127.0.0.1;dbname=app'
user: root
password: secret
logs:
dsn: 'mysql:host=127.0.0.1;dbname=logs'
user: logs
password: secret
autowired: false # Must reference explicitly
Reference non-autowired connection:
services:
- LogService(@database.logs.connection)
Documentation
The full Nette documentation for this topic is bundled with the plugin. Read a page when you need details beyond this skill:
- Nette Database – overview and choosing between SQL and Explorer
- Configuration – connections in NEON
- SQL Way – queries, placeholders, fetching
- Database Explorer – the Selection and ActiveRow API
- Transactions – transaction handling
- Type Conversion – how column values are converted
- Structure Reflection – reading tables, columns, keys
- Exceptions – the exception hierarchy
- Security Risks – what is and is not escaped
- Supported Databases – the list of drivers
- Pagination – paginating results with Paginator