Database
Nette Framework provides a powerful layer for accessing your database easily.
- composes SQL queries with ease
- easily fetches data
- uses efficient queries and does not transmit unnecessary data
The Nette\Database\Connection class is a wrapper around the PDO and represents a connection to the database. Nette\Database\Table layer orivudes an enhanced layer for table querying.
Creating a connection
To create a new database connection just create a new instance of Nette\Database\Connection class:
use Nette\Database\Connection;
$connection = new Connection($dsn, $user, $password);
Nette\Database will create create own proper internal driver based on your database server. This internal driver adjusts the Nette\Database behavior for your database server. Nette supports this database servers:
Database server | DSN name | Database support | Database\Table support |
---|---|---|---|
MySQL | mysql | YES | YES |
PostgreSQL | pgsql | YES | YES |
Sqlite 3 | sqlite | YES | YES |
Sqlite 2 | sqlite2 | YES | – |
Oracle | oci | YES | – |
MS SQL (PDO_SQLSRV) | sqlsrv | YES | – |
MS SQL (PDO_DBLIB) | mssql | YES | – |
ODBC | odbc | YES | – |
The 4th optional parameter of the connection constructor allows you to specify some options. These options are passed to the PDO constructor and the internal driver constructor, so they can contain a configuration for both the PDO instance and the driver instance.
All connections are created as “lazy” by default. This means the connection is established when it's needed, not when you
create a Connection
instance. You can disable this behavior by passing 'lazy' => FALSE
configuration.
The internal driver class name is detected by provided DSN. However, you can provide you own implementation of an internal
driver. To do this, pass your driver name as an option with the driverClass
key.
$connection = new Connection($dsn, $user, $password, array(
'lazy' => FALSE,
'driverClass' => 'App\YourSpecificDriverImplementationName'
));
The easiest way to create a connection is to configure one in your application config. NetteExtension allows you create multiple connections. NetteExtension helps you to add database panel to Tracy debug bar.
nette:
database:
default:
dsn: "mysql:host=127.0.0.1;dbname=test"
user: "root"
password: "password"
options: [PDO::MYSQL_ATTR_COMPRESS = true]
debugger: true # debugger bar panel
explain: true # explain queries in debugger bar
reflection: discovered # or conventional or classname, default is discovered
autowired: true
anotherConnection:
dsn: ...
autowired: false
Queries
$connection->query('INSERT INTO users', array( // an array can be a parameter
'name' => 'Jim',
'created' => new DateTime, // or a DateTime object
'avatar' => fopen('image.gif', 'r'), // or a file
), ...); // it is even possible to use multiple inserts
$connection->query('UPDATE users SET ? WHERE id=?', $data, $id);
$connection->query('SELECT * FROM categories WHERE id=?', 123)->dump();