Getting started
Routing and controllers
Command line
Databases (SQL)
Databases (NoSQL)
Security
Packages
Learn more
- Array helper
- Caching
- Collections
- Command bus
- Date and time
- Events
- File system
- HTML helper
- Humanizer
- Image manipulation
- Internationalization
- Logging
- Number helper
- Pagination
- Sessions
- String helper
- URL builder
- UUID helper
- Validation
- Views
Official packages
Databases
The database connection manager provides a simple way of handling database connections in your application.
Connections
Basics
Creating a database connection is done using the ConnectionManager::connection()
method.
// Returns connection object using the "default" database configuration defined in the config file
$connection = $this->database->connection();
// Returns connection object using the "mydb" database configuration defined in the config file
$connection = $this->database->connection('mydb');
The Connection::query()
method lets you execute a query. It returns true
on success and false
on failure.
$connection->query('INSERT INTO `foo` (`bar`, `baz`) VALUES (?, ?)', ['fruit', 'banana']);
The Connection::first()
method executes a query and returns the first row of the result set.
$row = $connection->first('SELECT * FROM `foo` WHERE `bar` = ?', [$bar]);
The Connection::all()
method executes a query and returns an array containing all of the result set rows.
$rows = $connection->all('SELECT * FROM `foo` WHERE `bar` = ?', [$bar]);
// There's also a handy syntax for assigning arrays for use in "IN" clauses
$rows = $connection->all('SELECT * FROM `foo` WHERE `bar` IN ([?])', [['banana', 'apple']]);
The Connection::yield()
method executes a query and returns a generator that lets you iterate over the result set rows. This is very useful if you want to process a large dataset without having to worry about memory consumption.
$rows = $connection->yield('SELECT * FROM `foo` WHERE `bar` = ?', [$bar]);
Note that when using MySQL you might have to configure PDO to use unbuffered queries for this to work as expected.
The Connection::column()
method executes a query and returns the value of the first column of the first row of the result set.
$email = $connection->column('SELECT `email` FROM `users` WHERE `id` = ?', [1]);
The Connection::columns()
method executes a query and returns an array containing the values of the first column.
$email = $connection->columns('SELECT `email` FROM `users`');
The Connection::pairs()
method will return an array where the first column is used as array keys and the second column is used as array values.
$pairs = $connection->pairs('SELECT `id`, `email` FROM `users`');
The Connection::queryAndCount()
method will return the number of rows modified by the query.
$count = $connection->queryAndCount('UPDATE `users` SET `email` = ?', ['foo@example.org']);
$count = $connection->queryAndCount('DELETE FROM `users`');
Connection status
You can check if a connection is still alive using the Connection::isAlive()
method. It will return true
if it is and false
if not.
$isConnectionAlive = $connection->isAlive();
You can attempt to reconnect using the Connection::reconnect()
method.
$connection->reconnect();
You can configure the connection to automatically reconnect in the
app/config/database.php
configuration file. Note that Mako will not attempt to automatically reconnect if the connection was lost during a transaction.
Magic shortcut
You can access the default database connection directly without having to go through the connection
method thanks to the magic __call
method.
$this->database->query('INSERT INTO `foo` (`bar`, `baz`) VALUES (?, ?)', ['fruit', 'banana']);
Transactions
Basics
Transactions only work if the storage engine you're using supports them.
You begin a transaction using the Connection::beginTransaction()
method.
$connection->beginTransaction();
Committing the transaction is done using the Connection::commitTransaction()
method.
$connection->commitTransaction();
Rolling back the transaction is done using the Connection::rollBackTransaction()
method.
$connection->rollBackTransaction();
You can check whether or not you're already in a transaction using the Connection::inTransaction()
method.
$inTransaction = $connection->inTransaction();
The Connection::transaction()
method provides a handy shortcut for performing simple database transactions. Any failed queries in the closure will automatically roll back the transaction.
$connection->transaction(function($connection)
{
$connection->builder()->table('accounts')->where('user_id', '=', 10)->decrement('cash', 100);
$connection->builder()->table('accounts')->where('user_id', '=', 20)->increment('cash', 100);
});
Savepoints
Nested transactions are also supported using savepoints.
In the example below we'll create a user named foo
. The nested transaction that would have created a second user named bar
will fail since the table name is misspelled.
The parent transaction is unaffected and foo
user is still created. If you want your entire transaction to roll back when the nested transaction fails then you can just re-throw the exception.
try
{
$connection->beginTransaction();
$connection->builder()->table('users')->insert(['username' => 'foo']);
{
$connection->beginTransaction();
try
{
$connection->builder()->table('usesr')->insert(['username' => 'bar']);
$connection->commitTransaction();
}
catch(PDOException $e)
{
$connection->rollbackTransaction();
// throw $e; // Re-throw the exception to roll back the parent transaction as well
}
}
$connection->commitTransaction();
}
catch(PDOException $e)
{
$connection->rollbackTransaction();
}
You can get the transaction nesting level at any point using the Connection::getTransactionNestingLevel()
method.
$nestingLevel = $connection->getTransactionNestingLevel();
Transaction nesting is also possible when using the Connection::transaction()
method but keep in mind that the entire transaction will be rolled back if any of the nested transactions fail.
Query builder
The Connection::builder()
method returns an instance of the query builder.
$rows = $connection->builder()->table('foo')->where('bar', '=', $bar)->all();
You can also use the Connection::table()
convenience method if you want to skip the call to the Connection::builder()
method.
$rows = $connection->table('foo')->where('bar', '=', $bar)->all();
Accessing the underlying PDO instance
You can also access the PDO object directly when needed.
$serverVersion = $connection->getPDO()->getAttribute(PDO::ATTR_SERVER_VERSION);