SQL Way

Nette Database offers two ways of working: you can write SQL queries yourself (SQL way), or have them generated automatically (see Explorer). The SQL way gives you full control over the queries while ensuring they are constructed securely.

Details on database connection and configuration can be found in the Connection and Configuration chapter.

Basic Querying

The query() method is used for database querying. It returns a ResultSet object, which represents the query result. If the query fails, the method throws an exception. You can iterate through the query result using a foreach loop, or use one of the helper methods.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
}

To securely insert values into SQL queries, use parameterized queries. Nette Database makes this extremely simple: just add a comma and the value after the SQL query:

$database->query('SELECT * FROM users WHERE name = ?', $name);

With multiple parameters, you have two options: You can either interleave the SQL query with parameters:

$database->query('SELECT * FROM users WHERE name = ?', $name, 'AND age > ?', $age);

Or write the entire SQL query first and then append all the parameters:

$database->query('SELECT * FROM users WHERE name = ? AND age > ?', $name, $age);

Protection Against SQL Injection

Why is it important to use parameterized queries? Because they protect you from an attack called SQL injection, where an attacker could inject their own SQL commands and thereby gain access to or damage data in the database.

Never insert variables directly into an SQL query! Always use parameterized queries, which protect you from SQL injection.

// ❌ DANGEROUS CODE - vulnerable to SQL injection
$database->query("SELECT * FROM users WHERE name = '$name'");

// ✅ Secure parameterized query
$database->query('SELECT * FROM users WHERE name = ?', $name);

Familiarize yourself with potential security risks.

Querying Techniques

WHERE Conditions

You can write WHERE conditions as an associative array, where keys are column names and values are the data for comparison. Nette Database automatically selects the most suitable SQL operator based on the value type.

$database->query('SELECT * FROM users WHERE', [
	'name' => 'John',
	'active' => true,
]);
// WHERE `name` = 'John' AND `active` = 1

You can also explicitly specify the comparison operator in the key:

$database->query('SELECT * FROM users WHERE', [
	'age >' => 25,          // uses the > operator
	'name LIKE' => '%John%', // uses the LIKE operator
	'email NOT LIKE' => '%example.com%', // uses the NOT LIKE operator
]);
// WHERE `age` > 25 AND `name` LIKE '%John%' AND `email` NOT LIKE '%example.com%'

Nette automatically handles special cases like null values or arrays.

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // uses the = operator
	'category_id' => [1, 2, 3], // uses IN
	'description' => null,      // uses IS NULL
]);
// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL

For negative conditions, use the NOT operator:

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // uses the <> operator
	'category_id NOT' => [1, 2, 3], // uses NOT IN
	'description NOT' => null,      // uses IS NOT NULL
	'id' => [],                     // skipped
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

By default, conditions are joined using the AND operator. This can be changed using the ?or placeholder.

ORDER BY Rules

The ORDER BY clause can be written using an array. Specify columns in the keys, and use a boolean value to indicate ascending (true) or descending (false) order:

$database->query('SELECT id FROM author ORDER BY', [
	'id' => true, // ascending
	'name' => false, // descending
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC

Inserting Data (INSERT)

The SQL INSERT command is used for inserting records.

$values = [
	'name' => 'John Doe',
	'email' => 'john@example.com',
];
$database->query('INSERT INTO users ?', $values);
$userId = $database->getInsertId();

The getInsertId() method returns the ID of the last inserted row. For some databases (e.g., PostgreSQL), it is necessary to specify the name of the sequence from which the ID should be generated as a parameter, using $database->getInsertId($sequenceId).

You can also pass special values, such as files, DateTime objects, or enum types, as parameters.

Inserting multiple records at once:

$database->query('INSERT INTO users ?', [
	['name' => 'User 1', 'email' => 'user1@mail.com'],
	['name' => 'User 2', 'email' => 'user2@mail.com'],
]);

A multi-record INSERT is much faster because only a single database query is executed, instead of many individual ones.

Security Note: Never use unvalidated data as $values. Familiarize yourself with possible risks.

Updating Data (UPDATE)

The SQL UPDATE command is used for updating records.

// Update a single record
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

The number of affected rows is returned by $result->getRowCount().

For UPDATE, we can use the += and -= operators:

$database->query('UPDATE users SET ? WHERE id = ?', [
	'login_count+=' => 1, // increment login_count
], 1);

Example of inserting or updating a record if it already exists. We use the ON DUPLICATE KEY UPDATE technique:

$values = [
	'name' => $name,
	'year' => $year,
];
$database->query('INSERT INTO users ? ON DUPLICATE KEY UPDATE ?',
	$values + ['id' => $id],
	$values,
);
// INSERT INTO users (`id`, `name`, `year`) VALUES (123, 'Jim', 1978)
//   ON DUPLICATE KEY UPDATE `name` = 'Jim', `year` = 1978

Notice that Nette Database recognizes the context in which an array parameter is used within the SQL command and constructs the SQL code accordingly. So, from the first array, it constructed (id, name, year) VALUES (123, 'Jim', 1978), while it converted the second into the form name = 'Jim', year = 1978. We discuss this in more detail in the section SQL Construction Hints.

Deleting Data (DELETE)

The SQL DELETE command is used for deleting records. Example of obtaining the number of deleted rows:

$count = $database->query('DELETE FROM users WHERE id = ?', 1)
	->getRowCount();

SQL Construction Hints

A hint is a special placeholder in an SQL query that specifies how the parameter value should be converted into an SQL expression:

The ?name placeholder is used for dynamically inserting table and column names into the query. Nette Database handles the correct quoting of identifiers according to the database conventions (e.g., enclosing in backticks in MySQL).

$table = 'users';
$column = 'name';
$database->query('SELECT ?name FROM ?name WHERE id = 1', $column, $table);
// SELECT `name` FROM `users` WHERE id = 1 (in MySQL)

Warning: Only use the ?name placeholder for validated table and column names. Otherwise, you risk security vulnerabilities.

Other hints usually do not need to be specified, as Nette uses smart autodetection when constructing the SQL query (see the third column of the table). But you can use it, for example, in a situation where you want to join conditions using OR instead of AND:

$database->query('SELECT * FROM users WHERE ?or', [
	'name' => 'John',
	'email' => 'john@example.com',
]);
// SELECT * FROM users WHERE `name` = 'John' OR `email` = 'john@example.com'

Special Values

In addition to common scalar types (string, int, bool), you can also pass special values as parameters:

• files: fopen('image.gif', 'r') inserts the binary content of the file
• date and time: DateTime and DateTimeImmutable objects are converted to the database format
• enum types: instances of enum are converted to their value
• SQL literals: created using Connection::literal('NOW()') are inserted directly into the query

$database->query('INSERT INTO articles ?', [
	'title' => 'My Article',
	'published_at' => new DateTimeImmutable, // or new DateTime
	'content' => fopen('image.png', 'r'),
	'state' => Status::Draft,
]);

For databases that do not have native support for the datetime data type (like SQLite and Oracle), DateTime and DateTimeImmutable objects are converted to a value specified in the database configuration by the formatDateTime item (default value is U – Unix timestamp).

SQL Literals

In some cases, you need to pass raw SQL code as a value, which should not be treated as a string and escaped. Objects of the Nette\Database\SqlLiteral class are used for this purpose. They are created by the Connection::literal() method.

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	'year >' => $database::literal('YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (`year` > YEAR())

Alternatively:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (year > YEAR())

SQL literals can contain parameters:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > ? AND year < ?', $min, $max),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (year > 1978 AND year < 2017)

This allows for interesting combinations:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('?or', [
		'active' => true,
		'role' => $role,
	]),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (`active` = 1 OR `role` = 'admin')

Fetching Data

Shortcuts for SELECT Queries

To simplify data retrieval, Connection offers several shortcuts that combine a query() call with a subsequent fetch*() call. These methods accept the same parameters as query(), i.e., an SQL query and optional parameters. A full description of the fetch*() methods can be found below.

Example:

// fetchField() - returns the value of the first cell
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

foreach – Iterating Over Rows

After executing a query, a ResultSet object is returned, which allows iterating through the results in several ways. The easiest way to execute a query and retrieve rows is by iterating in a foreach loop. This method is the most memory-efficient, as it fetches data row by row and does not load the entire result set into memory at once.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
	// ...
}

The ResultSet can only be iterated once. If you need to iterate repeatedly, you must first load the data into an array, for example, using the fetchAll() method.

fetch(): ?Row

Returns a row as a Row object. If no more rows exist, it returns null. Advances the internal pointer to the next row.

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // loads the first row
if ($row) {
	echo $row->name;
}

fetchAll(): array

Returns all remaining rows from the ResultSet as an array of Row objects.

$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // loads all rows
foreach ($rows as $row) {
	echo $row->name;
}

fetchPairs (string|int|null $key = null, string|int|null $value = null): array

Returns the result set as an associative array. The first argument specifies the column to use as keys, and the second argument specifies the column to use as values:

$result = $database->query('SELECT id, name FROM users');
$names = $result->fetchPairs('id', 'name');
// [1 => 'John Doe', 2 => 'Jane Doe', ...]

If only the first parameter ($key) is provided, the entire row (Row object) will be used as the value:

$rows = $result->fetchPairs('id');
// [1 => Row(id: 1, name: 'John'), 2 => Row(id: 2, name: 'Jane'), ...]

In case of duplicate keys, the value from the last row is used. Using null as the key results in a numerically indexed array (starting from zero), preventing key collisions:

$names = $result->fetchPairs(null, 'name');
// [0 => 'John Doe', 1 => 'Jane Doe', ...]

fetchPairs (Closure $callback): array

Alternatively, you can provide a callback that processes each row. The callback can return a single value or a key-value pair.

$result = $database->query('SELECT * FROM users');
$items = $result->fetchPairs(fn($row) => "$row->id - $row->name");
// ['1 - John', '2 - Jane', ...]

// The callback can also return an array with a key & value pair:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Returns the value of the first column from the current row. If no more rows exist, it returns null. Advances the internal pointer to the next row.

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // loads the name from the first row

fetchList(): ?array

Returns the row as an indexed array. If no more rows exist, it returns null. Advances the internal pointer to the next row.

$result = $database->query('SELECT name, email FROM users');
$row = $result->fetchList(); // ['John', 'john@example.com']

getRowCount(): ?int

Returns the number of affected rows from the last UPDATE or DELETE query. For SELECT queries, it returns the number of rows in the result set. However, this might not always be known, in which case the method returns null.

getColumnCount(): ?int

Returns the number of columns in the ResultSet.

Query Information

For debugging purposes, we can obtain information about the last executed query:

echo $database->getLastQueryString();   // prints the SQL query

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // prints the SQL query
echo $result->getTime();           // prints the execution time in seconds

To display the result as an HTML table, you can use:

$result = $database->query('SELECT * FROM articles');
$result->dump();

ResultSet provides information about column types:

$result = $database->query('SELECT * FROM articles');
$types = $result->getColumnTypes();

foreach ($types as $column => $type) {
	echo "$column is of type $type->type"; // e.g., 'id is of type int'
}

Query Logging

We can implement custom query logging. The onQuery event is an array of callbacks that are called after each executed query:

$database->onQuery[] = function ($database, $result) use ($logger) {
	$logger->info('Query: ' . $result->getQueryString());
	$logger->info('Time: ' . $result->getTime());

	if ($result->getRowCount() > 1000) {
		$logger->warning('Large result set: ' . $result->getRowCount() . ' rows');
	}
};