The cycle/database component is included by default in the Web and GRPC builds. The DBAL focuses mainly on unifying
database access rather than trying to get 100% of a specific DBMS feature set.
However, you can always use direct queries to bypass the spiral abstractions.
Note
Spiral DBAL supports MySQL, MariaDB, SQLite, PostgresSQL, and SQLServer (Windows) databases.
To install the component in alternative bundles or as a standalone library:
composer require cycle/cycle-bridge
Activate the bootloader Spiral\Cycle\Bootloader\DatabaseBootloader in your application:
protected const LOAD = [
// ...
\Spiral\Cycle\Bootloader\DatabaseBootloader::class,
// ...
];
To enable the migrations component:
protected const LOAD = [
// ...
\Spiral\Cycle\Bootloader\MigrationsBootloader::class,
// ...
];
By default, the database configuration is located in the app/config/database.php file. The configuration includes a set of
options for each database driver, database-driver association, and database aliases.
<?php
declare(strict_types=1);
use Cycle\Database\Config;
return [
/**
* Database logger configuration
*/
'logger' => [
'default' => null, // Default log channel for all drivers (The lowest priority)
'drivers' => [
// By driver name (The highest priority)
// See https://spiral.dev/docs/extension-monolog
'runtime' => 'sql_logs',
// By driver class (Medium priority)
\Cycle\Database\Driver\MySQL\MySQLDriver::class => 'console',
],
],
/**
* Default database connection
*/
'default' => env('DB_DEFAULT', 'default'),
/**
* 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 a new one into
* "databases" section below.
*/
'databases' => [
'default' => [
'driver' => 'runtime',
],
],
/**
* 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.
*/
'drivers' => [
'runtime' => new Config\SQLiteDriverConfig(
connection: new Config\SQLite\FileConnectionConfig(
database: env('DB_DATABASE', directory('root') . 'runtime/app.db')
),
queryCache: true,
),
// ...
],
];
To create a new database connection, add a new section or alter the existing options in the drivers section of your configuration,
you can use the env function to keep your passwords and usernames separately.
<?php
declare(strict_types=1);
use Cycle\Database\Config;
return [
'default' => 'default',
'databases' => [
'default' => [
'driver' => env('DATABASE_DRIVER', 'mysql')
],
],
'drivers' => [
'mysql' => new Config\MySQLDriverConfig(
connection: new Config\MySQL\TcpConnectionConfig(
database: env('DB_NAME'),
host: env('DB_HOST', '127.0.0.1'),
port: env('DB_PORT', 3306),
user: env('DB_USERNAME'),
password: env('DB_PASSWORD'),
),
queryCache: true
),
'postgres' => new Config\PostgresDriverConfig(
connection: new Config\Postgres\TcpConnectionConfig(
database: env('DB_NAME'),
host: env('DB_HOST', '127.0.0.1'),
port: env('DB_PORT', 5432),
user: env('DB_USERNAME'),
password: env('DB_PASSWORD'),
),
schema: 'public',
queryCache: true,
),
'runtime' => new Config\SQLiteDriverConfig(
connection: new Config\SQLite\FileConnectionConfig(
database: directory('runtime') . 'runtime.db'
),
queryCache: true,
),
'sqlServer' => new Config\SQLServerDriverConfig(
connection: new Config\SQLServer\TcpConnectionConfig(
database: env('DB_NAME'),
host: env('DB_HOST', '127.0.0.1'),
port: env('DB_PORT', 1433),
user: env('DB_USERNAME'),
password: env('DB_PASSWORD'),
),
queryCache: true,
),
]
];
Note
Use connection the optionoptionsto set PDO specific attributes.
In order to access a connected database, we must first add it to the databases section:
<?php
declare(strict_types=1);
// ...
return [
'default' => 'primary',
'databases' => [
'primary' => [
'driver' => 'mysql',
'prefix' => 'primary_'
],
'secondary'=> [
'driver' => 'mysql',
'prefix' => 'secondary_'
]
],
'drivers' => [
// ...
]
];
Your application and modules can access the database in multiple different ways. Database aliasing allows you to use separate databases with relation to one physical database.
Note
Use aliases to configure IoC auto wiring.
Controller constructor example:
public function __construct(Database $db, Database $other)
{
}
To point db and other to a specific database instance:
<?php
declare(strict_types=1);
// ...
return [
'default' => 'primary',
'aliases' => [
'db' => 'primary',
'other' => 'secondary'
],
'databases' => [
'primary' => [
'driver' => 'mysql',
'prefix' => 'primary_'
],
'secondary'=> [
'driver' => 'mysql',
'prefix' => 'secondary_'
]
],
'drivers' => [
// ...
]
];
The default Web and GRPC bundles include a set of console commands to view the database schema.
Activate the bootloader Spiral\Cycle\Bootloader\CommandBootloader in your application:
protected const LOAD = [
// ...
\Spiral\Cycle\Bootloader\CommandBootloader::class,
// ...
];
To view available databases, drivers, and tables:
php app.php db:list
The output:
+------------+------------+---------+---------+-----------+---------+----------------+
| Name (ID): | Database: | Driver: | Prefix: | Status: | Tables: | Count Records: |
+------------+------------+---------+---------+-----------+---------+----------------+
| default | runtime.db | SQLite | --- | connected | users | 0 |
| | | | | | posts | 0 |
+------------+------------+---------+---------+-----------+---------+----------------+
To view the details about a particular table:
php app.php db:table posts
The output:
Columns of default.posts:
+---------+----------------+----------------+-----------+----------------+
| Column: | Database Type: | Abstract Type: | PHP Type: | Default Value: |
+---------+----------------+----------------+-----------+----------------+
| id | int | primary | int | --- |
| title | string (255) | text | string | --- |
| user_id | int | integer | int | --- |
+---------+----------------+----------------+-----------+----------------+
Indexes of default.posts:
+-----------------------------------+-------+----------+
| Name: | Type: | Columns: |
+-----------------------------------+-------+----------+
| posts_index_user_id_5e32b9642a0ff | INDEX | user_id |
+-----------------------------------+-------+----------+
Foreign Keys of default.posts:
+------------------+---------+----------------+-----------------+------------+------------+
| Name: | Column: | Foreign Table: | Foreign Column: | On Delete: | On Update: |
+------------------+---------+----------------+-----------------+------------+------------+
| posts_user_id_fk | user_id | users | id | CASCADE | CASCADE |
+------------------+---------+----------------+-----------------+------------+------------+
Note
Read how to configure a database here.