Database Basics¶

Quick Tour¶

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

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

use Cake\Datasource\ConnectionManager;

$dsn = 'mysql://root:password@localhost/my_database';
ConnectionManager::setConfig('default', ['url' => $dsn]);

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

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

use Cake\Datasource\ConnectionManager;

$connection = ConnectionManager::get('default');
$results = $connection->execute('SELECT * FROM articles')->fetchAll('assoc');

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

use Cake\Datasource\ConnectionManager;
use DateTime;

$connection = ConnectionManager::get('default');
$results = $connection
    ->execute(
        'SELECT * FROM articles WHERE created >= :created',
        ['created' => new DateTime('1 day ago')],
        ['created' => 'datetime']
    )
    ->fetchAll('assoc');

// Prior to 4.5 use $connection->query() instead.
$results = $connection
    ->selectQuery('*', 'articles')
    ->where(['created >' => new DateTime('1 day ago')], ['created' => 'datetime'])
    ->order(['title' => 'DESC'])
    ->execute()
    ->fetchAll('assoc');

use Cake\Datasource\ConnectionManager;
use DateTime;

$connection = ConnectionManager::get('default');
$connection->insert('articles', [
    'title' => 'A New Article',
    'created' => new DateTime('now')
], ['created' => 'datetime']);

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

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

Configuration¶

By convention database connections are configured in config/app.php. The connection information defined in this file is fed into Cake\Datasource\ConnectionManager creating the connection configuration your application will be using. Sample connection information can be found in config/app.default.php. A sample connection configuration would look like:

'Datasources' => [
    'default' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => 'localhost',
        'username' => 'my_app',
        'password' => 'secret',
        'database' => 'my_app',
        'encoding' => 'utf8mb4',
        'timezone' => 'UTC',
        'cacheMetadata' => true,
    ]
],

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

use Cake\Datasource\ConnectionManager;

ConnectionManager::setConfig('default', [
    'className' => 'Cake\Database\Connection',
    'driver' => 'Cake\Database\Driver\Mysql',
    'persistent' => false,
    'host' => 'localhost',
    'username' => 'my_app',
    'password' => 'secret',
    'database' => 'my_app',
    'encoding' => 'utf8mb4',
    'timezone' => 'UTC',
    'cacheMetadata' => true,
]);

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

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

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

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

There are a number of keys supported in database configuration. A full list is as 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 SQL transaction mechanisms and preparing SQL statements among other things.

driver

The class name of the driver used to implement all specificities for a 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 option is not supported by SqlServer. An exception is thrown if you attempt to set persistent 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 . in your database name. Because of how it complicates identifier quoting CakePHP does not support . in database names. The path to your SQLite database should be an absolute path (for example, ROOT . DS . 'my_app.db') to avoid incorrect 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 to the server. This defaults to the database’s default encoding for all 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 are using 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 as when the connection is created.

log

Set to true to enable query logging. When enabled queries will be logged at a debug level with the queriesLog scope.

quoteIdentifiers

Set to true if you are using reserved words or special characters in your table or column names. Enabling this setting will result in queries built using the Query Builder having identifiers quoted when creating SQL. It should be noted that this decreases performance because each query needs to be traversed and manipulated before being executed.

flags

An associative array of PDO constants that should be passed to the underlying PDO instance. See the PDO documentation for the flags supported by the driver you are using.

cacheMetadata

Either boolean true, or a string containing the cache configuration to store meta data in. Having metadata caching disabled by setting it to false is not advised and can result in very poor performance. See the Metadata Caching section for more information.

mask

Set the permissions on the generated database file. (Only supported by SQLite)

cache

The cache flag to send to SQLite.

mode

The mode flag value to send to SQLite.

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

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

Read and Write Connections¶

Connections can have separate read and write roles. Read roles are expected to represent read-only replicas and write roles are expected to be the default connection and support write operations.

Read roles are configured by providing a read key in the connection config. Write roles are configured by providing a write key.

Role configurations override the values in the shared connection config. If the read and write role configurations are the same, a single connection to the database is used for both:

'default' => [
    'driver' => 'mysql',
    'username' => '...',
    'password' => '...',
    'database' => '...',
    'read' => [
        'host' => 'read-db.example.com',
    ],
    'write' => [
        'host' => 'write-db.example.com',
    ]
];

You can specify the same value for both read and write key without creating multiple connections to the database.

Managing Connections¶

class Cake\Datasource\ConnectionManager¶

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

use Cake\Datasource\ConnectionManager;

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

ConnectionManager::setConfig('my_connection', $config);
$connection = ConnectionManager::get('my_connection');

Data Types¶

class Cake\Database\TypeFactory¶

Since not every database vendor includes the same set of data types, or the same names for similar data types, CakePHP provides a set of abstracted data 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 will generate a CHAR(36) field.

binaryuuid

Maps to the UUID type if the database provides one, otherwise this will generate a BINARY(16) column

integer

Maps to the INTEGER type provided by the database. BIT is not yet supported at 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 MySQL TINYINT(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 precision option can be used to define the precision used.

decimal

Maps to the DECIMAL type. Supports the length and precision options. Values for decimal type ares be represented as strings (not as float as some might expect). This is because decimal types are used to represent exact numeric values in databases and using float type for them in PHP can potentially lead to precision loss.

If you want the values to be float in your PHP code then consider using FLOAT or DOUBLE type columns in your database. Also, depending on your use case you can explicitly map your decimal columns to float type in your table schema.

boolean

Maps to BOOLEAN except in MySQL, where TINYINT(1) is used to represent booleans. 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 column type is Cake\I18n\Date which extends the native DateTime class.

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 CakePHP provides, and schema generation features CakePHP uses when using test fixtures.

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

// in config/bootstrap.php
use Cake\Database\TypeFactory;
use Cake\Database\Type\DateTimeFractionalType;

// Overwrite the default datetime type with a more precise one.
TypeFactory::map('datetime', DateTimeFractionalType::class);

// in config/bootstrap.php
use Cake\Database\TypeFactory;
use Cake\Database\Type\DateTimeTimezoneType;

// Overwrite the default datetime type with a more precise one.
TypeFactory::map('datetime', DateTimeTimezoneType::class);

// in src/Database/Type/JsonType.php

namespace App\Database\Type;

use Cake\Database\DriverInterface;
use Cake\Database\Type\BaseType;
use PDO;

class JsonType extends BaseType
{
    public function toPHP($value, DriverInterface $driver)
    {
        if ($value === null) {
            return null;
        }

        return json_decode($value, true);
    }

    public function marshal($value)
    {
        if (is_array($value) || $value === null) {
            return $value;
        }

        return json_decode($value, true);
    }

    public function toDatabase($value, DriverInterface $driver)
    {
        return json_encode($value);
    }

    public function toStatement($value, DriverInterface $driver)
    {
        if ($value === null) {
            return PDO::PARAM_NULL;
        }

        return PDO::PARAM_STR;
    }
}

use Cake\Database\TypeFactory;

TypeFactory::map('json', 'App\Database\Type\JsonType');

class WidgetsTable extends Table
{
    public function getSchema(): TableSchemaInterface
    {
        $this->getSchema()->setColumnType('widget_prefs', 'json');
    }
}

// in src/Database/Type/JsonType.php

namespace App\Database\Type;

use Cake\Database\DriverInterface;
use Cake\Database\Type\BaseType;
use Cake\Database\Type\ColumnSchemaAwareInterface;
use Cake\Database\Schema\TableSchemaInterface;
use PDO;

class JsonType extends BaseType
    implements ColumnSchemaAwareInterface
{
    // other methods from earlier

    /**
     * Convert abstract schema definition into a driver specific
     * SQL snippet that can be used in a CREATE TABLE statement.
     *
     * Returning null will fall through to CakePHP's built-in types.
     */
    public function getColumnSql(
        TableSchemaInterface $schema,
        string $column,
        DriverInterface $driver
    ): ?string {
        $data = $schema->getColumn($column);
        $sql = $driver->quoteIdentifier($column);
        $sql .= ' JSON';
        if (isset($data['null') && $data['null'] === false) {
            $sql .= ' NOT NULL';
        }

        return $sql;
    }

    /**
     * Convert the column data returned from schema reflection
     * into the abstract schema data.
     *
     * Returning null will fall through to CakePHP's built-in types.
     */
    public function convertColumnDefinition(
        array $definition,
        DriverInterface $driver
    ): ?array {
        return [
            'type' => $this->_name,
            'length' => null,
        ];
    }

// in src/Database/Point.php
namespace App\Database;

// Our value object is immutable.
class Point
{
    protected $_lat;
    protected $_long;

    // Factory method.
    public static function parse($value)
    {
        // Parse the WKB data from MySQL.
        $unpacked = unpack('x4/corder/Ltype/dlat/dlong', $value);

        return new static($unpacked['lat'], $unpacked['long']);
    }

    public function __construct($lat, $long)
    {
        $this->_lat = $lat;
        $this->_long = $long;
    }

    public function lat()
    {
        return $this->_lat;
    }

    public function long()
    {
        return $this->_long;
    }
}

namespace App\Database\Type;

use App\Database\Point;
use Cake\Database\DriverInterface;
use Cake\Database\Expression\FunctionExpression;
use Cake\Database\ExpressionInterface;
use Cake\Database\Type\BaseType;
use Cake\Database\Type\ExpressionTypeInterface;

class PointType extends BaseType implements ExpressionTypeInterface
{
    public function toPHP($value, DriverInterface $d)
    {
        return $value === null ? null : Point::parse($value);
    }

    public function marshal($value)
    {
        if (is_string($value)) {
            $value = explode(',', $value);
        }
        if (is_array($value)) {
            return new Point($value[0], $value[1]);
        }

        return null;
    }

    public function toExpression($value): ExpressionInterface
    {
        if ($value instanceof Point) {
            return new FunctionExpression(
                'POINT',
                [
                    $value->lat(),
                    $value->long()
                ]
            );
        }
        if (is_array($value)) {
            return new FunctionExpression('POINT', [$value[0], $value[1]]);
        }
        // Handle other cases.
    }

    public function toDatabase($value, DriverInterface $driver)
    {
        return $value;
    }
}

TypeFactory::build('datetime')->useImmutable();
TypeFactory::build('date')->useImmutable();
TypeFactory::build('time')->useImmutable();
TypeFactory::build('timestamp')->useImmutable();

Connection Classes¶

class Cake\Database\Connection¶

Connection classes provide a simple interface to interact with database connections in a consistent way. They are intended as a more abstract interface to the driver layer and provide features for executing queries, logging queries, and doing transactional operations.

$statement = $connection->query('UPDATE articles SET published = 1 WHERE id = 2');

$statement = $connection->execute(
    'UPDATE articles SET published = ? WHERE id = ?',
    [1, 2]
);

$statement = $connection->execute(
    'UPDATE articles SET published_date = ? WHERE id = ?',
    [new DateTime('now'), 2],
    ['date', 'integer']
);

// Prior to 4.5 use $articles->query() instead.
$query = $connection->updateQuery('articles')
    ->set(['published' => true])
    ->where(['id' => 2]);
$statement = $query->execute();

// Prior to 4.5 use $articles->query() instead.
$query = $connection
    ->selectQuery('*', 'articles')
    ->where(['published' => true]);

foreach ($query as $row) {
    // Do something with the row.
}

$connection->begin();
$connection->execute('UPDATE articles SET published = ? WHERE id = ?', [true, 2]);
$connection->execute('UPDATE articles SET published = ? WHERE id = ?', [false, 4]);
$connection->commit();

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

Interacting with Statements¶

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

// Statements from execute will have values bound to them already.
$statement = $connection->execute(
    'SELECT * FROM articles WHERE published = ?',
    [true]
);

// Statements from prepare will be parameters for placeholders.
// You need to bind parameters before attempting to execute it.
$statement = $connection->prepare('SELECT * FROM articles WHERE published = ?');

$statement = $connection->prepare(
    'SELECT * FROM articles WHERE published = ? AND created > ?'
);

// Bind multiple values
$statement->bind(
    [true, new DateTime('2013-01-01')],
    ['boolean', 'date']
);

// Bind a single value
$statement->bindValue(1, true, 'boolean');
$statement->bindValue(2, new DateTime('2013-01-01'), 'date');

$statement = $connection->prepare(
    'SELECT * FROM articles WHERE published = :published AND created > :created'
);

// Bind multiple values
$statement->bind(
    ['published' => true, 'created' => new DateTime('2013-01-01')],
    ['published' => 'boolean', 'created' => 'date']
);

// Bind a single value
$statement->bindValue('published', true, 'boolean');
$statement->bindValue('created', new DateTime('2013-01-01'), 'date');

$statement->execute();

// Read one row.
$row = $statement->fetch('assoc');

// Read all rows.
$rows = $statement->fetchAll('assoc');

// Read rows through iteration.
foreach ($statement as $row) {
    // Do work
}

$rowCount = count($statement);
$rowCount = $statement->rowCount();

$code = $statement->errorCode();
$info = $statement->errorInfo();

Query Logging¶

Query logging can be enabled when configuring your connection by setting the log option to true. You can also toggle query logging at runtime, using enableQueryLogging:

// Turn query logging on.
$connection->enableQueryLogging(true);

// Turn query logging off
$connection->enableQueryLogging(false);

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

use Cake\Log\Log;

// Console logging
Log::setConfig('queries', [
    'className' => 'Console',
    'stream' => 'php://stderr',
    'scopes' => ['queriesLog']
]);

// File logging
Log::setConfig('queries', [
    'className' => 'File',
    'path' => LOGS,
    'file' => 'queries.log',
    'scopes' => ['queriesLog']
]);

Identifier Quoting¶

By default CakePHP does not quote identifiers in generated SQL queries. The reason 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’s conventions there is no reason to quote identifiers.

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

$connection->getDriver()->enableAutoQuoting();

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

Metadata Caching¶

CakePHP’s ORM uses database reflection to determine the schema, indexes and foreign keys your application contains. Because this metadata changes infrequently and can be expensive to access, it is typically cached. By default, metadata is stored in the _cake_model_ cache configuration. You can define a custom cache configuration using the cacheMetatdata option in your datasource configuration:

'Datasources' => [
    'default' => [
        // Other keys go here.

        // Use the 'orm_metadata' cache config for metadata.
        'cacheMetadata' => 'orm_metadata',
    ]
],

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

// Disable the cache
$connection->cacheMetadata(false);

// Enable the cache
$connection->cacheMetadata(true);

// Use a custom cache config
$connection->cacheMetadata('orm_metadata');

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

Creating Databases¶

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

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

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

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