Revision: Wed, 19 Jan 2022 11:39:05 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.

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.

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);

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:

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

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

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

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

'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:

'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:

'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:

'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:

'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,
),

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.
profiling bool Enable SQL profiling (logging). Defaults to false.

Access Database

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

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

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

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

Direct SQL queries are possible from this moment:

$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:

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,
    )
  )
));

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.

Edit this page