Database Core

Nette Database Core is database abstraction layer and provides core functionality.

Installation

Download and install the package using Composer:

composer require nette/database

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, eg host=127.0.0.1;dbname=test. In the case of failure it throws Nette\Database\ConnectionException.

However, a more sophisticated way offers application configuration. We will add a database section and it creates the required objects and a database panel in the Tracy bar.

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

The connection object we receive as a service from a DI container, for example:

class Model
{
	// pass Nette\Database\Explorer to work with the Database Explorer layer
	public function __construct(
		private Nette\Database\Connection $database,
	) {
	}
}

For more information, see database configuration.

Queries

To query database use query() method that returns ResultSet.

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

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

echo $result->getRowCount(); // returns the number of rows if is known

Over the ResultSet is possible to iterate only once, if we need to iterate multiple times, it is necessary to convert the result to the array via fetchAll() method.

You can easily add parameters to the query, note the question mark:

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

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

$database->query('SELECT * FROM users WHERE id IN (?)', $ids); // $ids is array

$db->query('SELECT * FROM users WHERE name = ' . $name); // WRONG!!!

In the case of failure query() throws either Nette\Database\DriverException or one of its descendants:

• ConstraintViolationException – violation of any constraint
• ForeignKeyConstraintViolationException – invalid foreign key
• NotNullConstraintViolationException – violation of the NOT NULL condition
• UniqueConstraintViolationException – conflict of unique index

In addition to query(), there are other useful methods:

// returns the associative array id => name
$pairs = $database->fetchPairs('SELECT id, name FROM users');

// returns all rows as array
$rows = $database->fetchAll('SELECT * FROM users');

// returns single row
$row = $database->fetch('SELECT * FROM users WHERE id = ?', $id);

// return single field
$name = $database->fetchField('SELECT name FROM users WHERE id = ?', $id);

In case of failure, all of these methods throw Nette\Database\DriverException.

Insert, Update & Delete

The parameter that we insert into the SQL query can also be the array (in which case it is possible to skip the wildcard ?), which may be useful for the INSERT statement:

$database->query('INSERT INTO users ?', [ // here can be omitted question mark
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978)

$id = $database->getInsertId(); // returns the auto-increment of inserted row

$id = $database->getInsertId($sequence); // or sequence value

Multiple insert:

$database->query('INSERT INTO users', [
	[
		'name' => 'Jim',
		'year' => 1978,
	], [
		'name' => 'Jack',
		'year' => 1987,
	],
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978), ('Jack', 1987)

We can also pass files, DateTime objects or enumerations:

$database->query('INSERT INTO users', [
	'name' => $name,
	'created' => new DateTime, // or $database::literal('NOW()')
	'avatar' => fopen('image.gif', 'r'), // inserts file contents
	'status' => State::New, // enum State
]);

Updating rows:

$result = $database->query('UPDATE users SET', [
	'name' => $name,
	'year' => $year,
], 'WHERE id = ?', $id);
// UPDATE users SET `name` = 'Jim', `year` = 1978 WHERE id = 123

echo $result->getRowCount(); // returns the number of affected rows

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

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

Deleting:

$result = $database->query('DELETE FROM users WHERE id = ?', $id);
echo $result->getRowCount(); // returns the number of affected rows

Advanced Queries

Insert or update, if it already exists:

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

Note that Nette Database recognizes the SQL context in which the array parameter is inserted and builds the SQL code accordingly. So, from the first array he generates (id, name, year) VALUES (123, 'Jim', 1978), while the second converts to name = 'Jim', year = 1978.

We can also describe sorting using array, in keys are column names and values are boolean that determines whether to sort in ascending order:

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

If the detection did not work, you can specify the form of the assembly with a wildcard ? followed by a hint. These hints are supported:

The WHERE clause uses the ?and operator so conditions are linked by AND:

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

Which can easily be changed to OR by using the ?or wildcard:

$result = $database->query('SELECT * FROM users WHERE ?or', [
	'name' => $name,
	'year' => $year,
]);
// SELECT * FROM users WHERE `name` = 'Jim' OR `year` = 1978

We can use operators in conditions:

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

And also enumerations:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => ['Jim', 'Jack'],
	'role NOT IN' => ['admin', 'owner'], // enumeration + operator NOT IN
]);
// SELECT * FROM users WHERE
//   `name` IN ('Jim', 'Jack') AND `role` NOT IN ('admin', 'owner')

We can also include a piece of custom SQL code using the so-called SQL literal:

$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 literal also can have its 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)

Thanks to which we can create 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')

Variable Name

There is a ?name wildcard that you use if the table name or column name is a variable. (Beware, do not allow the user to manipulate the content of such a variable):

$table = 'blog.users';
$column = 'name';
$database->query('SELECT * FROM ?name WHERE ?name = ?', $table, $column, $name);
// SELECT * FROM `blog`.`users` WHERE `name` = 'Jim'

Transactions

There are three methods for dealing with transactions:

$database->beginTransaction();

$database->commit();

$database->rollback();

An elegant way is offered by the transaction() method. You pass the callback that is executed in the transaction. If an exception is thrown during execution, the transaction is dropped, if everything goes well, the transaction is committed.

$id = $database->transaction(function ($database) {
	$database->query('DELETE FROM ...');
	$database->query('INSERT INTO ...');
	// ...
	return $database->getInsertId();
});

As you can see, the transaction() method returns the return value of the callback.

The transaction() can also be nested, which simplifies the implementation of independent repositories.