Database Basics

The CakePHP database access layer abstracts and provides help with most aspectsof dealing with relational databases such as, keeping connections to the server,building queries, preventing SQL injections, inspecting and altering schemas,and with debugging and profiling queries sent to the database.

Quick Tour

The functions described in this chapter illustrate what is possible to do withthe lower-level database access API. If instead you want to learn more about thecomplete ORM, you can read the Query Builder andTable Objects sections.

The easiest way to create a database connection is using a DSN string:

  1. use Cake\Datasource\ConnectionManager;
  2.  
  3. $dsn = 'mysql://root:password@localhost/my_database';
  4. ConnectionManager::setConfig('default', ['url' => $dsn]);

Once created, you can access the connection object to start using it:

  1. $connection = ConnectionManager::get('default');

Supported Databases

CakePHP supports the following relational database servers:

  • MySQL 5.5+
  • SQLite 3
  • PostgreSQL 8.3+
  • SQLServer 2008+
  • Oracle (through a community plugin)

You will need the correct PDO extension installed for each of the above databasedrivers. Procedural APIs are not supported.

The Oracle database is supported through theDriver for Oracle Databasecommunity plugin.

Running Select Statements

Running raw SQL queries is a breeze:

  1. use Cake\Datasource\ConnectionManager;
  2.  
  3. $connection = ConnectionManager::get('default');
  4. $results = $connection->execute('SELECT * FROM articles')->fetchAll('assoc');

You can use prepared statements to insert parameters:

  1. $results = $connection
  2.     ->execute('SELECT * FROM articles WHERE id = :id', ['id' => 1])
  3.     ->fetchAll('assoc');

It is also possible to use complex data types as arguments:

  1. use Cake\Datasource\ConnectionManager;
  2. use DateTime;
  3.  
  4. $connection = ConnectionManager::get('default');
  5. $results = $connection
  6.     ->execute(
  7.         'SELECT * FROM articles WHERE created >= :created',
  8.         ['created' => new DateTime('1 day ago')],
  9.         ['created' => 'datetime']
  10.     )
  11.     ->fetchAll('assoc');

Instead of writing the SQL manually, you can use the query builder:

  1. $results = $connection
  2.     ->newQuery()
  3.     ->select('*')
  4.     ->from('articles')
  5.     ->where(['created >' => new DateTime('1 day ago')], ['created' => 'datetime'])
  6.     ->order(['title' => 'DESC'])
  7.     ->execute()
  8.     ->fetchAll('assoc');

Running Insert Statements

Inserting rows in the database is usually a matter of a couple lines:

  1. use Cake\Datasource\ConnectionManager;
  2. use DateTime;
  3.  
  4. $connection = ConnectionManager::get('default');
  5. $connection->insert('articles', [
  6.     'title' => 'A New Article',
  7.     'created' => new DateTime('now')
  8. ], ['created' => 'datetime']);

Running Update Statements

Updating rows in the database is equally intuitive, the following example willupdate the article with id 10:

  1. use Cake\Datasource\ConnectionManager;
  2. $connection = ConnectionManager::get('default');
  3. $connection->update('articles', ['title' => 'New title'], ['id' => 10]);

Running Delete Statements

Similarly, the delete() method is used to delete rows from the database, thefollowing example deletes the article with id 10:

  1. use Cake\Datasource\ConnectionManager;
  2. $connection = ConnectionManager::get('default');
  3. $connection->delete('articles', ['id' => 10]);

Configuration

By convention database connections are configured in config/app.php. Theconnection information defined in this file is fed intoCake\Datasource\ConnectionManager creating the connection configurationyour application will be using. Sample connection information can be found inconfig/app.default.php. A sample connection configuration would looklike:

  1. 'Datasources' => [
  2.     'default' => [
  3.         'className' => 'Cake\Database\Connection',
  4.         'driver' => 'Cake\Database\Driver\Mysql',
  5.         'persistent' => false,
  6.         'host' => 'localhost',
  7.         'username' => 'my_app',
  8.         'password' => 'secret',
  9.         'database' => 'my_app',
  10.         'encoding' => 'utf8mb4',
  11.         'timezone' => 'UTC',
  12.         'cacheMetadata' => true,
  13.     ]
  14. ],

The above will create a ‘default’ connection, with the provided parameters. Youcan define as many connections as you want in your configuration file. You canalso define additional connections at runtime usingCake\Datasource\ConnectionManager::setConfig(). An example of thatwould be:

  1. use Cake\Datasource\ConnectionManager;
  2.  
  3. ConnectionManager::setConfig('default', [
  4.     'className' => 'Cake\Database\Connection',
  5.     'driver' => 'Cake\Database\Driver\Mysql',
  6.     'persistent' => false,
  7.     'host' => 'localhost',
  8.     'username' => 'my_app',
  9.     'password' => 'secret',
  10.     'database' => 'my_app',
  11.     'encoding' => 'utf8mb4',
  12.     'timezone' => 'UTC',
  13.     'cacheMetadata' => true,
  14. ]);

Configuration options can also be provided as a DSN string. This isuseful when working with environment variables or PaaS providers:

  1. ConnectionManager::setConfig('default', [
  2.     'url' => 'mysql://my_app:sekret@localhost/my_app?encoding=utf8&timezone=UTC&cacheMetadata=true',
  3. ]);

When using a DSN string you can define any additional parameters/options asquery string arguments.

By default, all Table objects will use the default connection. Touse a non-default connection, see Configuring Connections.

There are a number of keys supported in database configuration. A full list isas follows:

  • className
  • The fully namespaced class name of the class that represents the connection to a database server.This class is responsible for loading the database driver, providing SQLtransaction mechanisms and preparing SQL statements among other things.
  • driver
  • The class name of the driver used to implements all specificities fora database engine. This can either be a short classname using plugin syntax,a fully namespaced name, or a constructed driver instance.Examples of short classnames are Mysql, Sqlite, Postgres, and Sqlserver.
  • persistent
  • Whether or not to use a persistent connection to the database. This optionis not supported by SqlServer. An exception is thrown if you attempt to setpersistent to true with SqlServer.
  • host
  • The database server’s hostname (or IP address).
  • username
  • The username for the account.
  • password
  • The password for the account.
  • database
  • The name of the database for this connection to use. Avoid using . inyour database name. Because of how it complicates identifier quoting CakePHPdoes not support . in database names. The path to your SQLite databaseshould be an absolute path (e.g. ROOT . DS . 'my_app.db') to avoidincorrect paths caused by relative paths.
  • port (optional)
  • The TCP port or Unix socket used to connect to the server.
  • encoding
  • Indicates the character set to use when sending SQL statements tothe server. This defaults to the database’s default encoding forall databases other than DB2.
  • timezone
  • Server timezone to set.
  • schema
  • Used in PostgreSQL database setups to specify which schema to use.
  • unix_socket
  • Used by drivers that support it to connect via Unix socket files. If you areusing PostgreSQL and want to use Unix sockets, leave the host key blank.
  • ssl_key
  • The file path to the SSL key file. (Only supported by MySQL).
  • ssl_cert
  • The file path to the SSL certificate file. (Only supported by MySQL).
  • ssl_ca
  • The file path to the SSL certificate authority. (Only supported by MySQL).
  • init
  • A list of queries that should be sent to the database server aswhen the connection is created.
  • log
  • Set to true to enable query logging. When enabled queries will be loggedat a debug level with the queriesLog scope.
  • quoteIdentifiers
  • Set to true if you are using reserved words or special characters inyour table or column names. Enabling this setting will result in queriesbuilt using the Query Builder having identifiers quoted whencreating SQL. It should be noted that this decreases performance becauseeach query needs to be traversed and manipulated before being executed.
  • flags
  • An associative array of PDO constants that should be passed to theunderlying PDO instance. See the PDO documentation for the flags supportedby the driver you are using.
  • cacheMetadata
  • Either boolean true, or a string containing the cache configuration tostore meta data in. Having metadata caching disabled by setting it to falseis not advised and can result in very poor performance. See theMetadata Caching section for more information.
  • mask
  • Set the permissions on the generated database file. (Only supported by SQLite)

At this point, you might want to take a look at theCakePHP Conventions. The correct naming for your tables (and the additionof some columns) can score you some free functionality and help you avoidconfiguration. For example, if you name your database table big_boxes, yourtable BigBoxesTable, and your controller BigBoxesController, everything willwork together automatically. By convention, use underscores, lower case, andplural forms for your database table names - for example: bakers,pastry_stores, and savory_cakes.

Note

If your MySQL server is configured with skip-character-set-client-handshakethen you MUST use the flags config to set your charset encoding. For e.g.:

  1. 'flags' => [\PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8']

Managing Connections

  • class Cake\Datasource\ConnectionManager

The ConnectionManager class acts as a registry to access databaseconnections your application has. It provides a place that other objects can getreferences to existing connections.

Accessing Connections

  • static Cake\Datasource\ConnectionManager::get($name)

Once configured connections can be fetched usingCake\Datasource\ConnectionManager::get(). This method willconstruct and load a connection if it has not been built before, or return theexisting known connection:

  1. use Cake\Datasource\ConnectionManager;
  2.  
  3. $conn = ConnectionManager::get('default');

Attempting to load connections that do not exist will throw an exception.

Creating Connections at Runtime

Using setConfig() and get() you can create new connections that are notdefined in your configuration files at runtime:

  1. ConnectionManager::setConfig('my_connection', $config);
  2. $conn = ConnectionManager::get('my_connection');

See the Configuration for more information on the configurationdata used when creating connections.

Data Types

  • class Cake\Database\Type

Since not every database vendor includes the same set of data types, orthe same names for similar data types, CakePHP provides a set of abstracteddata types for use with the database layer. The types CakePHP supports are:

  • string
  • Maps to VARCHAR type. In SQL Server the NVARCHAR types are used.
  • char
  • Maps to CHAR type. In SQL Server the NCHAR type is used.
  • text
  • Maps to TEXT types.
  • uuid
  • Maps to the UUID type if a database provides one, otherwise this willgenerate a CHAR(36) field.
  • binaryuuid
  • Maps to the UUID type if the database provides one, otherwise this willgenerate a BINARY(16) column
  • integer
  • Maps to the INTEGER type provided by the database. BIT is not yet supportedat this moment.
  • smallinteger
  • Maps to the SMALLINT type provided by the database.
  • tinyinteger
  • Maps to the TINYINT or SMALLINT type provided by the database. In MySQLTINYINT(1) is treated as a boolean.
  • biginteger
  • Maps to the BIGINT type provided by the database.
  • float
  • Maps to either DOUBLE or FLOAT depending on the database. The precisionoption can be used to define the precision used.
  • decimal
  • Maps to the DECIMAL type. Supports the length and precisionoptions.
  • boolean
  • Maps to BOOLEAN except in MySQL, where TINYINT(1) is used to representbooleans. BIT(1) is not yet supported at this moment.
  • binary
  • Maps to the BLOB or BYTEA type provided by the database.
  • date
  • Maps to a native DATE column type. The return value of this columntype is Cake\I18n\Date which extends the native DateTimeclass.
  • datetime
  • See DateTime Type.
  • datetimefractional
  • See DateTime Type.
  • timestamp
  • Maps to the TIMESTAMP type.
  • timestampfractional
  • Maps to the TIMESTAMP(N) type.
  • time
  • Maps to a TIME type in all databases.
  • json
  • Maps to a JSON type if it’s available, otherwise it maps to TEXT.

These types are used in both the schema reflection features that CakePHPprovides, and schema generation features CakePHP uses when using test fixtures.

Each type can also provide translation functions between PHP and SQLrepresentations. These methods are invoked based on the type hints provided whendoing queries. For example a column that is marked as ‘datetime’ willautomatically convert input parameters from DateTime instances into atimestamp or formatted datestrings. Likewise, ‘binary’ columns will accept filehandles, and generate file handles when reading data.

DateTime Type

  • class Cake\Database\DateTimeType

Maps to a native DATETIME column type. In PostgreSQL, and SQL Serverthis turns into a TIMESTAMP type. The default return value of this columntype is Cake\I18n\FrozenTime which extends the built-inDateTimeImmutable class and Chronos.

  • Cake\Database\DateTimeType::setTimezone(string|DateTimeZone|null $timezone)

If your database server’s timezone does not match your application’s PHP timezonethen you can use this method to specify your database’s timezone. This timezonewill then used when converting PHP objects to database’s datetime string andvice versa.

  • class Cake\Database\DateTimeFractionalType

Can be used to map datetime columns that contain microseconds such asDATETIME(6) in MySQL. To use this type you need to add it as a mapped type:

  1. // in confib/bootstrap.php
  2. use Cake\Database\TypeFactory;
  3. use Cake\Database\Type\DateTimeFractionalType;
  4.  
  5. // Overwrite the default datetime type with a more precise one.
  6. TypeFactory::map('datetime', DateTimeFractionalType::class);
  • class Cake\Database\DateTimeTimezoneType

Can be used to map datetime columns that contain time zones such asTIMESTAMPTZ in PostgreSQL. To use this type you need to add it as a mapped type:

  1. // in confib/bootstrap.php
  2. use Cake\Database\TypeFactory;
  3. use Cake\Database\Type\DateTimeTimezoneType;
  4.  
  5. // Overwrite the default datetime type with a more precise one.
  6. TypeFactory::map('datetime', DateTimeTimezoneType::class);

Adding Custom Types

  • static Cake\Database\DateTimeTimezoneType::map($name, $class)

If you need to use vendor specific types that are not built into CakePHP you canadd additional new types to CakePHP’s type system. Type classes are expected toimplement the following methods:

  • toPHP: Casts given value from a database type to a PHP equivalent.
  • toDatabase: Casts given value from a PHP type to one acceptable by a database.
  • toStatement: Casts given value to its Statement equivalent.
  • marshal: Marshals flat data into PHP objects.

An easy way to fulfill the basic interface is to extendCake\Database\Type. For example if we wanted to add a JSON type,we could make the following type class:

  1. // in src/Database/Type/JsonType.php
  2.  
  3. namespace App\Database\Type;
  4.  
  5. use Cake\Database\Driver;
  6. use Cake\Database\Type;
  7. use PDO;
  8.  
  9. class JsonType extends Type
  10. {
  11.     public function toPHP($value, Driver $driver)
  12.     {
  13.         if ($value === null) {
  14.             return null;
  15.         }
  16.         return json_decode($value, true);
  17.     }
  18.  
  19.     public function marshal($value)
  20.     {
  21.         if (is_array($value) || $value === null) {
  22.             return $value;
  23.         }
  24.         return json_decode($value, true);
  25.     }
  26.  
  27.     public function toDatabase($value, Driver $driver)
  28.     {
  29.         return json_encode($value);
  30.     }
  31.  
  32.     public function toStatement($value, Driver $driver)
  33.     {
  34.         if ($value === null) {
  35.             return PDO::PARAM_NULL;
  36.         }
  37.         return PDO::PARAM_STR;
  38.     }
  39. }

By default the toStatement() method will treat values as strings which willwork for our new type. Once we’ve created our new type, we need to add it intothe type mapping. During our application bootstrap we should do the following:

  1. use Cake\Database\Type;
  2.  
  3. Type::map('json', 'App\Database\Type\JsonType');

We can then overload the reflected schema data to use our new type, andCakePHP’s database layer will automatically convert our JSON data when creatingqueries. You can use the custom types you’ve created by mapping the types inyour Table’s _initializeSchema() method:

  1. use Cake\Database\Schema\TableSchema;
  2.  
  3. class WidgetsTable extends Table
  4. {
  5.     protected function _initializeSchema(TableSchema $schema)
  6.     {
  7.         $schema->setColumnType('widget_prefs', 'json');
  8.         return $schema;
  9.     }
  10. }

Mapping Custom Datatypes to SQL Expressions

The previous example maps a custom datatype for a ‘json’ column type which iseasily represented as a string in a SQL statement. Complex SQL datatypes cannot be represented as strings/integers in SQL queries. When workingwith these datatypes your Type class needs to implement theCake\Database\Type\ExpressionTypeInterface interface. This interface letsyour custom type represent a value as a SQL expression. As an example, we’llbuild a simple Type class for handling POINT type data out of MySQL. Firstwe’ll define a ‘value’ object that we can use to represent POINT data inPHP:

  1. // in src/Database/Point.php
  2. namespace App\Database;
  3.  
  4. // Our value object is immutable.
  5. class Point
  6. {
  7.     protected $_lat;
  8.     protected $_long;
  9.  
  10.     // Factory method.
  11.     public static function parse($value)
  12.     {
  13.         // Parse the data from MySQL.
  14.         return new static($value[0], $value[1]);
  15.     }
  16.  
  17.     public function __construct($lat, $long)
  18.     {
  19.         $this->_lat = $lat;
  20.         $this->_long = $long;
  21.     }
  22.  
  23.     public function lat()
  24.     {
  25.         return $this->_lat;
  26.     }
  27.  
  28.     public function long()
  29.     {
  30.         return $this->_long;
  31.     }
  32. }

With our value object created, we’ll need a Type class to map data into thisvalue object and into SQL expressions:

  1. namespace App\Database\Type;
  2.  
  3. use App\Database\Point;
  4. use Cake\Database\Expression\FunctionExpression;
  5. use Cake\Database\Type as BaseType;
  6. use Cake\Database\Type\ExpressionTypeInterface;
  7.  
  8. class PointType extends BaseType implements ExpressionTypeInterface
  9. {
  10.     public function toPHP($value, Driver $d)
  11.     {
  12.         return Point::parse($value);
  13.     }
  14.  
  15.     public function marshal($value)
  16.     {
  17.         if (is_string($value)) {
  18.             $value = explode(',', $value);
  19.         }
  20.         if (is_array($value)) {
  21.             return new Point($value[0], $value[1]);
  22.         }
  23.         return null;
  24.     }
  25.  
  26.     public function toExpression($value)
  27.     {
  28.         if ($value instanceof Point) {
  29.             return new FunctionExpression(
  30.                 'POINT',
  31.                 [
  32.                     $value->lat(),
  33.                     $value->long()
  34.                 ]
  35.             );
  36.         }
  37.         if (is_array($value)) {
  38.             return new FunctionExpression('POINT', [$value[0], $value[1]]);
  39.         }
  40.         // Handle other cases.
  41.     }
  42. }

The above class does a few interesting things:

  • The toPHP method handles parsing the SQL query results into a valueobject.
  • The marshal method handles converting, data such as given request data, into our value object.We’re going to accept string values like '10.24,12.34 and arrays for now.
  • The toExpression method handles converting our value object into theequivalent SQL expressions. In our example the resulting SQL would besomething like POINT(10.24, 12.34).

Once we’ve built our custom type, we’ll need to connect our typeto our table class.

Enabling Immutable DateTime Objects

Because Date/Time objects are easily mutated in place, CakePHP allows you toenable immutable value objects. This is best done in your application’sconfig/bootstrap.php file:

  1. Type::build('datetime')->useImmutable();
  2. Type::build('date')->useImmutable();
  3. Type::build('time')->useImmutable();
  4. Type::build('timestamp')->useImmutable();

Note

New applications will have immutable objects enabled by default.

Connection Classes

  • class Cake\Database\Connection

Connection classes provide a simple interface to interact with databaseconnections in a consistent way. They are intended as a more abstract interface tothe driver layer and provide features for executing queries, logging queries, and doingtransactional operations.

Executing Queries

  • Cake\Database\Connection::query($sql)

Once you’ve gotten a connection object, you’ll probably want to issue somequeries with it. CakePHP’s database abstraction layer provides wrapper featureson top of PDO and native drivers. These wrappers provide a similar interface toPDO. There are a few different ways you can run queries depending on the type ofquery you need to run and what kind of results you need back. The most basicmethod is query() which allows you to run already completed SQL queries:

  1. $stmt = $conn->query('UPDATE articles SET published = 1 WHERE id = 2');
  • Cake\Database\Connection::execute($sql, $params, $types)

The query() method does not allow for additional parameters. If you needadditional parameters you should use the execute() method, which allows forplaceholders to be used:

  1. $stmt = $conn->execute(
  2.     'UPDATE articles SET published = ? WHERE id = ?',
  3.     [1, 2]
  4. );

Without any type hinting information, execute will assume all placeholdersare string values. If you need to bind specific types of data, you can use theirabstract type names when creating a query:

  1. $stmt = $conn->execute(
  2.     'UPDATE articles SET published_date = ? WHERE id = ?',
  3.     [new DateTime('now'), 2],
  4.     ['date', 'integer']
  5. );
  • Cake\Database\Connection::newQuery()

This allows you to use rich data types in your applications and properly convertthem into SQL statements. The last and most flexible way of creating queries isto use the Query Builder. This approach allows you to build complex andexpressive queries without having to use platform specific SQL:

  1. $query = $conn->newQuery();
  2. $query->update('articles')
  3.     ->set(['published' => true])
  4.     ->where(['id' => 2]);
  5. $stmt = $query->execute();

When using the query builder, no SQL will be sent to the database server untilthe execute() method is called, or the query is iterated. Iterating a querywill first execute it and then start iterating over the result set:

  1. $query = $conn->newQuery();
  2. $query->select('*')
  3.     ->from('articles')
  4.     ->where(['published' => true]);
  5.  
  6. foreach ($query as $row) {
  7.     // Do something with the row.
  8. }

Note

When you have an instance of Cake\ORM\Query you can useall() to get the result set for SELECT queries.

Using Transactions

The connection objects provide you a few simple ways you do databasetransactions. The most basic way of doing transactions is through the begin(),commit() and rollback() methods, which map to their SQL equivalents:

  1. $conn->begin();
  2. $conn->execute('UPDATE articles SET published = ? WHERE id = ?', [true, 2]);
  3. $conn->execute('UPDATE articles SET published = ? WHERE id = ?', [false, 4]);
  4. $conn->commit();
  • Cake\Database\Connection::transactional(callable $callback)

In addition to this interface connection instances also provide thetransactional() method which makes handling the begin/commit/rollback callsmuch simpler:

  1. $conn->transactional(function ($conn) {
  2.     $conn->execute('UPDATE articles SET published = ? WHERE id = ?', [true, 2]);
  3.     $conn->execute('UPDATE articles SET published = ? WHERE id = ?', [false, 4]);
  4. });

In addition to basic queries, you can execute more complex queries using eitherthe Query Builder or Table Objects. The transactional method willdo the following:

  • Call begin.
  • Call the provided closure.
  • If the closure raises an exception, a rollback will be issued. The originalexception will be re-thrown.
  • If the closure returns false, a rollback will be issued.
  • If the closure executes successfully, the transaction will be committed.

Interacting with Statements

When using the lower level database API, you will often encounter statementobjects. These objects allow you to manipulate the underlying prepared statementfrom the driver. After creating and executing a query object, or usingexecute() you will have a StatementDecorator instance. It wraps theunderlying basic statement object and provides a few additional features.

Preparing a Statement

You can create a statement object using execute(), or prepare(). Theexecute() method returns a statement with the provided values bound to it.While prepare() returns an incomplete statement:

  1. // Statements from execute will have values bound to them already.
  2. $stmt = $conn->execute(
  3.     'SELECT * FROM articles WHERE published = ?',
  4.     [true]
  5. );
  6.  
  7. // Statements from prepare will be parameters for placeholders.
  8. // You need to bind parameters before attempting to execute it.
  9. $stmt = $conn->prepare('SELECT * FROM articles WHERE published = ?');

Once you’ve prepared a statement you can bind additional data and execute it.

Binding Values

Once you’ve created a prepared statement, you may need to bind additional data.You can bind multiple values at once using the bind() method, or bindindividual elements using bindValue:

  1. $stmt = $conn->prepare(
  2.     'SELECT * FROM articles WHERE published = ? AND created > ?'
  3. );
  4.  
  5. // Bind multiple values
  6. $stmt->bind(
  7.     [true, new DateTime('2013-01-01')],
  8.     ['boolean', 'date']
  9. );
  10.  
  11. // Bind a single value
  12. $stmt->bindValue(1, true, 'boolean');
  13. $stmt->bindValue(2, new DateTime('2013-01-01'), 'date');

When creating statements you can also use named array keys instead ofpositional ones:

  1. $stmt = $conn->prepare(
  2.     'SELECT * FROM articles WHERE published = :published AND created > :created'
  3. );
  4.  
  5. // Bind multiple values
  6. $stmt->bind(
  7.     ['published' => true, 'created' => new DateTime('2013-01-01')],
  8.     ['published' => 'boolean', 'created' => 'date']
  9. );
  10.  
  11. // Bind a single value
  12. $stmt->bindValue('published', true, 'boolean');
  13. $stmt->bindValue('created', new DateTime('2013-01-01'), 'date');

Warning

You cannot mix positional and named array keys in the same statement.

Executing & Fetching Rows

After preparing a statement and binding data to it, you can execute it and fetchrows. Statements should be executed using the execute() method. Onceexecuted, results can be fetched using fetch(), fetchAll() or iteratingthe statement:

  1. $stmt->execute();
  2.  
  3. // Read one row.
  4. $row = $stmt->fetch('assoc');
  5.  
  6. // Read all rows.
  7. $rows = $stmt->fetchAll('assoc');
  8.  
  9. // Read rows through iteration.
  10. foreach ($stmt as $row) {
  11.     // Do work
  12. }

Note

Reading rows through iteration will fetch rows in ‘both’ mode. This meansyou will get both the numerically indexed and associatively indexed results.

Getting Row Counts

After executing a statement, you can fetch the number of affected rows:

  1. $rowCount = count($stmt);
  2. $rowCount = $stmt->rowCount();

Checking Error Codes

If your query was not successful, you can get related error informationusing the errorCode() and errorInfo() methods. These methods work thesame way as the ones provided by PDO:

  1. $code = $stmt->errorCode();
  2. $info = $stmt->errorInfo();

Query Logging

Query logging can be enabled when configuring your connection by setting thelog option to true. You can also toggle query logging at runtime, usingenableQueryLogging:

  1. // Turn query logging on.
  2. $conn->enableQueryLogging(true);
  3.  
  4. // Turn query logging off
  5. $conn->enableQueryLogging(false);

When query logging is enabled, queries will be logged toCake\Log\Log using the ‘debug’ level, and the ‘queriesLog’ scope.You will need to have a logger configured to capture this level & scope. Loggingto stderr can be useful when working on unit tests, and logging tofiles/syslog can be useful when working with web requests:

  1. use Cake\Log\Log;
  2.  
  3. // Console logging
  4. Log::setConfig('queries', [
  5.     'className' => 'Console',
  6.     'stream' => 'php://stderr',
  7.     'scopes' => ['queriesLog']
  8. ]);
  9.  
  10. // File logging
  11. Log::setConfig('queries', [
  12.     'className' => 'File',
  13.     'path' => LOGS,
  14.     'file' => 'queries.log',
  15.     'scopes' => ['queriesLog']
  16. ]);

Note

Query logging is only intended for debugging/development uses. You shouldnever leave query logging on in production as it will negatively impact theperformance of your application.

Identifier Quoting

By default CakePHP does not quote identifiers in generated SQL queries. Thereason for this is identifier quoting has a few drawbacks:

  • Performance overhead - Quoting identifiers is much slower and complex than not doing it.
  • Not necessary in most cases - In non-legacy databases that follow CakePHP’sconventions there is no reason to quote identifiers.

If you are using a legacy schema that requires identifier quoting you can enableit using the quoteIdentifiers setting in yourConfiguration. You can also enable this feature at runtime:

  1. $conn->getDriver()->enableAutoQuoting();

When enabled, identifier quoting will cause additional query traversal thatconverts all identifiers into IdentifierExpression objects.

Note

SQL snippets contained in QueryExpression objects will not be modified.

Metadata Caching

CakePHP’s ORM uses database reflection to determine the schema, indexes andforeign keys your application contains. Because this metadata changesinfrequently and can be expensive to access, it is typically cached. By default,metadata is stored in the cake_model cache configuration. You can definea custom cache configuration using the cacheMetatdata option in yourdatasource configuration:

  1. 'Datasources' => [
  2.     'default' => [
  3.         // Other keys go here.
  4.  
  5.         // Use the 'orm_metadata' cache config for metadata.
  6.         'cacheMetadata' => 'orm_metadata',
  7.     ]
  8. ],

You can also configure the metadata caching at runtime with thecacheMetadata() method:

  1. // Disable the cache
  2. $connection->cacheMetadata(false);
  3.  
  4. // Enable the cache
  5. $connection->cacheMetadata(true);
  6.  
  7. // Use a custom cache config
  8. $connection->cacheMetadata('orm_metadata');

CakePHP also includes a CLI tool for managing metadata caches. See theSchema Cache Tool chapter for more information.

Creating Databases

If you want to create a connection without selecting a database you can omitthe database name:

  1. $dsn = 'mysql://root:password@localhost/';

You can now use your connection object to execute queries that create/modifydatabases. For example to create a database:

  1. $connection->query("CREATE DATABASE IF NOT EXISTS my_database");

Note

When creating a database it is a good idea to set the character set andcollation parameters. If these values are missing, the database will setwhatever system default values it uses.