Revision: Thu, 25 Apr 2024 11:06:45 GMT

Connect to Database

Cycle ORM requires at least one connection to the database in order to operate. The DBAL functionality is provided by the package cycle/database.

Note
Make sure to install all required dependencies listed in the previous section.

Instantiate DBAL

In order to start, we have to initialize the DatabaseManager service used to automatically create and manage a set of application databases. The list of available connections and databases can be provided in the initial configuration.

php
use Cycle\Database;
use Cycle\Database\Config;

$dbConfig = new Config\DatabaseConfig([
    'default' => 'default',
    'databases' => [
        'default' => [
            'connection' => 'sqlite'
        ]
    ],
    'connections' => [
        'sqlite' => new Config\SQLiteDriverConfig(
            connection: new Config\SQLite\MemoryConnectionConfig(),
            queryCache: true,
        ),
    ]
]);

$dbal = new Database\DatabaseManager($dbConfig);

Note
You can instantiate DBAL with an empty connection list and configure it in runtime if needed.

Configure Databases

The Cycle/Database module provides support to manage multiple databases in one application, use read/write connections and logically separate multiple databases within one connection using prefixes.

To register a new database simply add it into databases section:

php
'default' => [
  'connection' => 'sqlite'
]

To use a database-specific prefix use the prefix option (all the queries will be affected):

php
'default' => [
  'connection' => 'sqlite',
  'prefix'     => 'secondary_'
]

To use read/write connections use sections connection and readConnection accordingly:

php
'default' => [
  'connection'     => 'mysql',
  'readConnection' => 'mysqlSlave',
  'prefix'         => 'secondary_'
]

Connections

Each database instance must have an associated connection object. Connections used to provide low-level functionality and wrap different database drivers. To register a new connection you have to specify the driver class and its connection options:

For SQLite:

php
'sqlite_memory' => new Config\SQLiteDriverConfig(
    connection: new Config\SQLite\MemoryConnectionConfig(),
    queryCache: true,
),

'sqlite_file' => new Config\SQLiteDriverConfig(
    connection: new Config\SQLite\FileConnectionConfig(
        database:  __DIR__.'./runtime/database.sqlite'
    ),
    queryCache: true,
),

'sqlite_dsn' => new Config\SQLiteDriverConfig(
    connection: new Config\SQLite\DsnConnectionConfig(
        dsn: 'sqlite:runtime/database.db',
    ),
    queryCache: true,
),

For MySQL and MariaDB:

php
'mysql' => new Config\MySQLDriverConfig(
    connection: new Config\MySQL\TcpConnectionConfig(
        database: 'spiral',
        host: '127.0.0.1',
        port: 3306,
        user:'spiral',
        password: '',
    ),
    queryCache: true
),

'mysql_dsn' => new Config\MySQLDriverConfig(
    connection: new Config\MySQL\DsnConnectionConfig(
        dsn: 'mysql:host=127.0.0.1;port=3306;dbname=spiral',
        user:'spiral',
        password: '',
    ),
    queryCache: true
),

'mysql_socket' => new Config\MySQLDriverConfig(
    connection: new Config\MySQL\SocketConnectionConfig(
        database: 'spiral',
        socket: '/tmp/mysql.sock',
        dsn: 'mysql:host=127.0.0.1;port=3306;dbname=spiral',
        user: 'spiral',
        password: '',
    ),
    queryCache: true
),

For PostgresSQL:

php
'postgres' => new Config\PostgresDriverConfig(
    connection: new Config\Postgres\TcpConnectionConfig(
        database: 'spiral',
        host: '127.0.0.1',
        port: 5432,
        user: 'spiral',
        password: '',
    ),
    schema: 'public',
    queryCache: true,
),

'postgres_dsn' => new Config\PostgresDriverConfig(
    connection: new Config\Postgres\DsnConnectionConfig(
        dsn: 'pgsql:host=127.0.0.1;port=5432;dbname=spiral',
        user:'spiral',
        password: '',
    ),
    queryCache: true
),

For SQLServer:

php
'sqlServer' => new Config\SQLServerDriverConfig(
    connection: new Config\SQLServer\TcpConnectionConfig(
        database: 'spiral',
        host: '127.0.0.1',
        port: 5432,
        user: 'spiral',
        password: '',
    ),
    queryCache: true,
),

'sqlServer_dsn' => new Config\SQLServerDriverConfig(
    connection: new Config\SQLServer\DsnConnectionConfig(
        dsn: 'sqlsrv:Server=127.0.0.1,1521;Database=spiral',
        user:'spiral',
        password: '',
    ),
    queryCache: true,
),

Note
Make sure to install the proper PDO extensions!

Additional connection options

There are multiple connection options you can use to customize the behavior.

Options Value Description
timezone string Default driver timezone (all DateTimeInterface query parameters will be converted into it). Defaults to UTC.
reconnect bool Allow the driver to automatically reconnect. Defaults to false.
options array Since cycle/database v2.3.0.
An array of additional driver options:
- withDatetimeMicroseconds - Boolean option. Allows to change the datetime format from Y-m-d H:i:s to Y-m-d H:i:s.u and store the datetime with microseconds.
- logQueryParameters - Boolean option. Enables interpolating in the logged SQL queries. This option can be helpful for debugging, but it is essential to consider the security implications of logging sensitive information.

Access Database

To access the database using the DatabaseManager, use the method database:

php
print_r($dbal->database('default'));

The database will be automatically connected on the first SQL request.

Note
DBAL will use the database specified in the default config option if the name is null.

Direct SQL queries are possible from this moment:

php
$dbal->database('default')->table('users')->select()->fetchAll();

Runtime Configuration

In addition to config driven setup you are able to configure your database connections in runtime:

php
use Cycle\Database;
use Cycle\Database\Config;

$dbal->addDatabase(new Database\Database(
  'name',
  'prefix_',
  new Database\Driver\SQLite\SQLiteDriver(
    new Config\PostgresDriverConfig(
        connection: new Config\Postgres\TcpConnectionConfig(
            database: 'spiral',
            host: '127.0.0.1',
            port: 5432,
            user: 'spiral',
            password: '',
        ),
        schema: 'public',
        queryCache: true,
    )
  )
));

Note
This approach can be useful to test your application using database mocks. Attention, DBAL would not allow you to overwrite already exists database, you must explicitly configure empty DatabaseManager.

2.x
Edit this page