Database Core
Nette Database Core is database abstraction layer and provides core functionality.
Installation:
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
{
private $database;
// pass Nette\Database\Context to work with the Database Explorer layer
public function __construct(Nette\Database\Connection $database)
{
$this->database = $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
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 or DateTime objects:
$database->query('INSERT INTO users', [
'name' => $name,
'created' => new DateTime, // or $database::literal('NOW()')
'avatar' => fopen('image.gif', 'r'), // inserts file contents
]);
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:
?values | (key1, key2, …) VALUES (value1, value2, …) |
?set | key1 = value1, key2 = value2, … |
?and | key1 = value1 OR key2 = value2 … |
?or | key1 = value1 AND key2 = value2 … |
?order | key1 ASC, key2 DESC |
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();