Cycle Database

https://github.com/cycle/database
Admin
Cycle DBAL is a PHP library providing secure, multi-dialect SQL support with features like schema...
Tokens:204,645
Snippets:1,357
Trust Score:9.2
License:MIT
Update:4 weeks ago
Show doc for...
Tokens:
Raw
### Table usage example

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/table.md

Shows how to create a DatabaseManager, get a table, declare a schema, insert, read, update, delete rows, and introspect the table.

```php
use Cycle\Database\Config;
use Cycle\Database\DatabaseManager;

$dbm = new DatabaseManager(new Config\DatabaseConfig([
    'databases'   => ['default' => ['connection' => 'sqlite']],
    'connections' => [
        'sqlite' => new Config\SQLiteDriverConfig(
            connection: new Config\SQLite\MemoryConnectionConfig(),
        ),
    ],
]));
$users = $dbm->database('default')->table('users');

// schema declaration
$schema = $users->getSchema();
$schema->primary('id');
$schema->string('name');
$schema->save();

// write
$id = $users->insertOne(['name' => 'Bob', 'balance' => 10]);
$users->insertMultiple(['name', 'balance'], [['Jack', 20], ['Ann', 30]]);

// read
$rows = $users->select()->where('balance', '>=', 10)->orderBy('balance', 'DESC')->fetchAll();
foreach ($users as $row) { /* iterates SELECT * */ }
$total = $users->select()->count();
$sum = $users->sum('balance');   // via __call -> SelectQuery::sum()

// update/delete rows
$users->update(['balance' => 0], ['id' => $id])->run();
$users->delete(['balance' => 0])->run();

// introspection
if ($users->exists() && $users->hasColumn('email')) { /* ... */ }
```

--------------------------------

### Start Docker containers for testing

Source: https://github.com/cycle/database/blob/2.x/CONTRIBUTING.md

Navigates to the tests directory and starts the Docker containers required for the test environment.

```bash
cd tests/
docker compose up
```

--------------------------------

### Install cycle/database with Composer

Source: https://github.com/cycle/database/blob/2.x/README.md

Installs the cycle/database component via Composer.

```bash
composer require cycle/database
```

--------------------------------

### Usage Example

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/driver-postgres.md

Example of connecting to PostgreSQL, performing returning insert, upsert with partial-unique-index predicate, and using server-side cursor.

```APIDOC
## Usage Example

### Description
Example of connecting to PostgreSQL, performing returning insert, upsert with partial-unique-index predicate, and using server-side cursor.

### Code
php
use Cycle\Database\Config\PostgresDriverConfig;
use Cycle\Database\Config\Postgres\TcpConnectionConfig;
use Cycle\Database\DatabaseManager;
use Cycle\Database\Driver\Postgres\PostgresCursorOptions;
use Cycle\Database\Driver\Postgres\PostgresOnConflict;

$dbm = new DatabaseManager(new Config\DatabaseConfig([
    'databases' => ['default' => ['connection' => 'pgsql']],
    'connections' => [
        'pgsql' => new PostgresDriverConfig(
            connection: new TcpConnectionConfig(database: 'app', host: '127.0.0.1'),
            schema: ['public', 'tenant'],
        ),
    ],
]));
$db = $dbm->database();

// returning insert
$id = $db->insert('users')->values(['name' => 'Ann'])->returning('id')->run();

// upsert with a partial-unique-index predicate
$db->insert('users')->values(['tenant_id' => 1, 'email' => 'a@b.c'])
   ->onConflict(PostgresOnConflict::target(['tenant_id', 'email'])->targetWhere('active = true')->doUpdate())
   ->run();

// server-side cursor (inside a transaction)
$db->transaction(function ($db) {
    $gen = $db->cursor(
        $db->select()->from('users')->orderBy('id'),
        new PostgresCursorOptions(chunkSize: 500, withHold: true),
    );
    foreach ($gen as $row) { /* lazy */ }
});

```

--------------------------------

### Postgres driver usage example

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/driver-postgres.md

Shows how to configure a Postgres connection, perform a returning insert, upsert with a partial-unique-index predicate, and use a server-side cursor inside a transaction.

```php
use Cycle\Database\Config\PostgresDriverConfig;
use Cycle\Database\Config\Postgres\TcpConnectionConfig;
use Cycle\Database\DatabaseManager;
use Cycle\Database\Driver\Postgres\PostgresCursorOptions;
use Cycle\Database\Driver\Postgres\PostgresOnConflict;

$dbm = new DatabaseManager(new Config\DatabaseConfig([
    'databases' => ['default' => ['connection' => 'pgsql']],
    'connections' => [
        'pgsql' => new PostgresDriverConfig(
            connection: new TcpConnectionConfig(database: 'app', host: '127.0.0.1'),
            schema: ['public', 'tenant'],
        ),
    ],
]));
$db = $dbm->database();

// returning insert
$id = $db->insert('users')->values(['name' => 'Ann'])->returning('id')->run();

// upsert with a partial-unique-index predicate
$db->insert('users')->values(['tenant_id' => 1, 'email' => 'a@b.c'])
   ->onConflict(PostgresOnConflict::target(['tenant_id', 'email'])->targetWhere('active = true')->doUpdate())
   ->run();

// server-side cursor (inside a transaction)
$db->transaction(function ($db) {
    $gen = $db->cursor(
        $db->select()->from('users')->orderBy('id'),
        new PostgresCursorOptions(chunkSize: 500, withHold: true),
    );
    foreach ($gen as $row) { /* lazy */ }
});
```

--------------------------------

### PostgresDriver::createPDO

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/driver-postgres.md

Creates the PDO connection and sends initial setup commands.

```APIDOC
## PostgresDriver::createPDO

### Description
Creates the PDO connection and sends initial setup commands.

### Method
protected

### Signature
protected function createPDO()

### Notes
- Sends `SET NAMES 'UTF-8'`.
- Sends `SET search_path TO "schema1", "schema2"` based on configured schemas.
```

--------------------------------

### OnConflict usage examples

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/on-conflict.md

Shows various onConflict usage patterns: shorthand with single or multi-column targets, explicit DO NOTHING, partial unique index predicate for Postgres/SQLite, Postgres constraint target with returning, and MySQL row alias. Each example uses the appropriate driver-specific class and demonstrates the fluent API.

```php
use Cycle\Database\Driver\MySQL\MySQLOnConflict;
use Cycle\Database\Driver\Postgres\PostgresOnConflict;
use Cycle\Database\Driver\SQLite\SQLiteOnConflict;
use Cycle\Database\Injection\Fragment;
use Cycle\Database\Query\OnConflict;

// shorthand — single column target, DO UPDATE over every inserted column
$db->insert('users')->values(['email' => 'a@b.c'])->onConflict('email')->run();

// shorthand — multi-column target
$db->insert('tenant_users')
   ->values(['tenant_id' => 1, 'email' => 'a@b.c'])
   ->onConflict(['tenant_id', 'email'])
   ->run();

// explicit — DO NOTHING
$db->insert('users')
   ->values(['email' => 'a@b.c'])
   ->onConflict(OnConflict::target('email')->doNothing())
   ->run();

// explicit — partial unique index predicate (Postgres / SQLite)
$db->insert('users')
   ->values(['tenant_id' => 1, 'email' => 'a@b.c', 'hits' => 1])
   ->onConflict(
       SQLiteOnConflict::target(['tenant_id', 'email'])
           ->targetWhere('active IS NOT NULL')
           ->doUpdate(['hits' => new Fragment('counters.hits + EXCLUDED.hits')]),
   )
   ->run();

// Postgres constraint target + returning
$id = $db->insert('users')
   ->values(['email' => 'a@b.c'])
   ->onConflict(PostgresOnConflict::onConstraint('users_email_key')->doUpdate(['email']))
   ->returning('id')
   ->run();

// MySQL row alias
$db->insert('counters')
   ->values(['k' => 'x', 'hits' => 1])
   ->onConflict(MySQLOnConflict::target('k')->withRowAlias('src')->doUpdate([
       'hits' => new Fragment('counters.hits + src.hits'),
   ]))
   ->run();
```

--------------------------------

### Table Usage Example

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/table.md

Example demonstrating how to use the TableInterface through the DatabaseManager to perform schema declaration, CRUD operations, and introspection.

```APIDOC
## Table Usage Example

### Description
Demonstrates how to obtain a table instance and perform common operations.

### Usage

```php
use Cycle\Database\Config;
use Cycle\Database\DatabaseManager;

$dbm = new DatabaseManager(new Config\DatabaseConfig([
    'databases'   => ['default' => ['connection' => 'sqlite']],
    'connections' => [
        'sqlite' => new Config\SQLiteDriverConfig(
            connection: new Config\SQLite\MemoryConnectionConfig(),
        ),
    ],
]));
$users = $dbm->database('default')->table('users');

// schema declaration
$schema = $users->getSchema();
$schema->primary('id');
$schema->string('name');
$schema->save();

// write
$id = $users->insertOne(['name' => 'Bob', 'balance' => 10]);
$users->insertMultiple(['name', 'balance'], [['Jack', 20], ['Ann', 30]]);

// read
$rows = $users->select()->where('balance', '>=', 10)->orderBy('balance', 'DESC')->fetchAll();
foreach ($users as $row) { /* iterates SELECT * */ }
$total = $users->select()->count();
$sum = $users->sum('balance');   // via __call -> SelectQuery::sum()

// update/delete rows
$users->update(['balance' => 0], ['id' => $id])->run();
$users->delete(['balance' => 0])->run();

// introspection
if ($users->exists() && $users->hasColumn('email')) { /* ... */ }
```

### Error Handling

When inserting rows with mismatched column sets, a `BuilderException` is thrown:

```php
use Cycle\Database\Exception\BuilderException;

try {
    $users->insertMultiple(['name'], [['a', 'extra']]);
} catch (BuilderException $e) {
    // thrown while compiling: rowset size does not match the columns token
}
```

```

--------------------------------

### Insert with returning (OUTPUT)

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/driver-sqlserver.md

Example of performing an insert with a returning clause to get the inserted ID.

```APIDOC
## Insert with returning (OUTPUT)

### Description
Performs an insert operation and returns the generated ID using the OUTPUT clause.

### Usage Example
```php
$id = $db->insert('users')->values(['name' => 'Ann'])->returning('id')->run();
```

### Notes
- The `returning('id')` method specifies the column to return.
- The `run()` method executes the insert and returns the value.
```

--------------------------------

### DatabaseManager usage example

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/database-manager.md

Creates a DatabaseManager with a SQLite connection configured as the default database. Use this to obtain a Database instance for the 'default' connection.

```php
<?php

use Cycle\Database\Config;
use Cycle\Database\DatabaseManager;

$dbm = new DatabaseManager(new Config\DatabaseConfig([
    'databases' => [
        'default' => ['connection' => 'sqlite'],
    ],
]));
```

--------------------------------

### DatabaseConfig with multiple databases and read/write connections

Source: https://github.com/cycle/database/blob/2.x/_autodocs/configuration.md

Example configuration for multiple databases with separate read and write connections. Shows the per-database keys: 'connection'/'write'/'driver' for the write driver, 'readConnection'/'read'/'readDriver' for the read-only connection, and 'tablePrefix'/'prefix' for table prefixing. The 'aliases' map lets you reference a database by an alternative name.

```php
new Config\DatabaseConfig([
    'default' => 'default',
    'aliases' => ['primary' => 'default'],
    'databases' => [
        'default' => [
            'connection'   => 'mysql',
            'readConnection' => 'mysql_read',
            'tablePrefix'  => '',
        ],
        'analytics' => [
            'connection' => 'pgsql_readonly',
            'prefix'     => 'an_',
        ],
    ],
    'connections' => [
        'mysql' => new Config\MySQLDriverConfig(...),
        'mysql_read' => new Config\MySQLDriverConfig(readonly: true, ...),
        'pgsql_readonly' => new Config\PostgresDriverConfig(readonly: true, ...),
    ],
]);
```

--------------------------------

### Basic database operations with Cycle Database

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/database.md

Shows how to insert, select, and execute raw SQL using the DatabaseInterface. Includes transactions with rollback on failure and a server-side cursor example for Postgres/SQLite/SQLServer.

```php
use Cycle\Database\Database;

// $db implements DatabaseInterface (e.g. from DatabaseManager->database())
$db->table('users')->insertOne(['name' => 'test', 'balance' => 10]);
$rows = $db->table('users')->select()->where('balance', '>', 5)->fetchAll();

// raw SQL on the write/read driver
$affected = $db->execute('UPDATE users SET balance = balance + 1 WHERE id = ?', [1]);
foreach ($db->query('SELECT * FROM users WHERE id = ?', [1]) as $row) { /* ... */ }

// transactions with rollback on failure
$db->transaction(function (DatabaseInterface $db) {
    $db->table('accounts')->update(['balance' => 0], ['id' => 7]);
    $db->table('log')->insertOne(['event' => 'reset']);
}, Database::ISOLATION_SERIALIZABLE);

// server-side cursor (Postgres/SQLite/SQLServer, requires active transaction)
try {
    $gen = $db->cursor($db->select()->from('users'), new PostgresCursorOptions(chunkSize: 500));
    foreach ($gen as $row) { /* lazy rows */ }
} catch (\Cycle\Database\Exception\DriverException $e) {
    // driver lacks cursor support or no active transaction
}
```

--------------------------------

### DatabaseManager with SQLServerDriverConfig

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/driver-sqlserver.md

Example of configuring and using the Cycle Database manager with a SQL Server connection.

```APIDOC
## DatabaseManager with SQLServerDriverConfig

### Description
Demonstrates how to set up a DatabaseManager with a SQL Server driver configuration and perform basic operations.

### Usage Example
```php
use Cycle\Database\Config\SQLServerDriverConfig;
use Cycle\Database\Config\SQLServer\TcpConnectionConfig;
use Cycle\Database\DatabaseManager;
use Cycle\Database\Driver\SQLServer\CursorType;
use Cycle\Database\Driver\SQLServer\SQLServerCursorOptions;

$dbm = new DatabaseManager(new Config\DatabaseConfig([
    'databases' => ['default' => ['connection' => 'sqlsrv']],
    'connections' => [
        'sqlsrv' => new SQLServerDriverConfig(
            connection: new TcpConnectionConfig(database: 'app', host: 'localhost', user: 'sa', password: ''),
        ),
    ],
]));
$db = $dbm->database();
```

### Notes
- The `SQLServerDriverConfig` accepts a `TcpConnectionConfig` for connection settings.
- The `DatabaseManager` is initialized with a database configuration array.
```

--------------------------------

### MySQLOnConflict

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/driver-mysql.md

Documentation for the MySQLOnConflict class, including its methods and usage example.

```APIDOC
## [MySQLOnConflict] MySQLOnConflict

### Description
Provides MySQL-specific ON DUPLICATE KEY UPDATE behavior with configurable row alias.

### Method
N/A (PHP class)

### Endpoint
N/A

### Parameters
#### Constructor / Methods
- **withRowAlias(string $alias): self** - Required - Sets the row alias used in the ON DUPLICATE KEY UPDATE clause. The alias must be a non-empty string.
- **getRowAlias(): string** - Required - Returns the current row alias.

### Request Example
```php
$onConflict = MySQLOnConflict::target('email')->withRowAlias('src')->doUpdate([
    'hits' => new \Cycle\Database\Injection\Fragment('users.hits + src.hits'),
]);
```

### Response
#### Success Response
Returns an instance of `MySQLOnConflict` for method chaining.

### Error Handling
- `from()` accepts only base `OnConflict`; anything else throws `BuilderException`.
- Custom update expressions must reference the configured row alias (default `new_row`).
```

--------------------------------

### Transaction handling with savepoints

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/driver.md

Demonstrates starting a transaction with isolation level 'SERIALIZABLE', executing a query, committing, and rolling back on exception. Uses beginTransaction, commitTransaction, and rollbackTransaction methods.

```php
// transactions with nested savepoints
$driver->beginTransaction('SERIALIZABLE');
try {
    $driver->execute('...');
    $driver->commitTransaction();
} catch (\Throwable $e) {
    $driver->rollbackTransaction();
}
```

--------------------------------

### SQL Server driver configuration and usage with returning insert and cursor

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/driver-sqlserver.md

Shows how to configure a SQL Server connection and use returning inserts (OUTPUT) and server-side cursors. The cursor requires an active transaction; the example wraps it in a transaction closure and uses SQLServerCursorOptions with CursorType::Static.

```php
use Cycle\Database\Config\SQLServerDriverConfig;
use Cycle\Database\Config\SQLServer\TcpConnectionConfig;
use Cycle\Database\DatabaseManager;
use Cycle\Database\Driver\SQLServer\CursorType;
use Cycle\Database\Driver\SQLServer\SQLServerCursorOptions;

$dbm = new DatabaseManager(new Config\DatabaseConfig([
    'databases' => ['default' => ['connection' => 'sqlsrv']],
    'connections' => [
        'sqlsrv' => new SQLServerDriverConfig(
            connection: new TcpConnectionConfig(database: 'app', host: 'localhost', user: 'sa', password: ''),
        ),
    ],
]));
$db = $dbm->database();

// returning insert (OUTPUT)
$id = $db->insert('users')->values(['name' => 'Ann'])->returning('id')->run();

// server-side cursor (transaction required)
$db->transaction(function ($db) {
    $gen = $db->cursor(
        $db->select()->from('users')->orderBy('id'),
        new SQLServerCursorOptions(type: CursorType::Static),
    );
    foreach ($gen as $row) { /* lazy rows */ }
});
```

--------------------------------

### Basic driver usage with execute, query, fetch, and lastInsertID

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/driver.md

Shows how to use a driver obtained from DatabaseManager to execute an UPDATE, run a SELECT query, fetch a row, and get the last insert ID. Catches StatementException to access the failing SQL via $e->getQuery().

```php
use Cycle\Database\Driver\Driver;
use Cycle\Database\Exception\DriverException;
use Cycle\Database\Exception\ReadonlyConnectionException;
use Cycle\Database\Exception\StatementException;

// $driver obtained from DatabaseManager::driver('sqlite') — implements DriverInterface
try {
    $count = $driver->execute('UPDATE users SET balance = ? WHERE id = ?', [0, 1]);
    $stmt = $driver->query('SELECT * FROM users WHERE id = ?', [1]);
    $row = $stmt->fetch();
    $id = $driver->lastInsertID();
} catch (StatementException $e) {
    // $e->getQuery() contains the failing SQL
}
```

--------------------------------

### Quoter identifier and quote examples

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/compiler.md

Demonstrates Quoter usage with a prefix 'app_' and quote character '``'. Shows how identifier() and quote() handle table names, qualified names, expressions, and aliases.

```php
use Cycle\Database\Driver\Quoter;

$q = new Quoter('app_', '``');

$q->identifier('users');          // ``users``
$q->quote('users', true);         // ``app_users``
$q->quote('users.id');            // ``users``.``id``
$q->quote('COUNT(*)');            // COUNT(*) — expression passthrough
$q->quote('users AS u', true);    // ``app_users`` AS ``u`` (alias registered)
```

--------------------------------

### Catching DBAL exceptions

Source: https://github.com/cycle/database/blob/2.x/_autodocs/errors.md

Example of catching all DBAL-related exceptions, including configuration, builder, compiler, driver, or statement failures.

```APIDOC
## Catching DBAL exceptions

### Description
Demonstrates how to catch all DBAL-related exceptions when accessing a database.

### Code Example
```php
use Cycle\Database\Exception\DBALException;

// catch everything DBAL-related
try {
    $dbm->database('missing');
} catch (DBALException $e) {
    // config, builder, compiler, driver or statement failure
}
```

### Exceptions
- **DBALException** - Base exception for all DBAL-related failures, including configuration, builder, compiler, driver, or statement errors.
```

--------------------------------

### Handling SchemaException on drop

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/schema-table.md

Example of catching SchemaException when trying to drop a non-existent table, with the error message 'Unable to drop non existed table'.

```php
use Cycle\Database\Exception\SchemaException;

try {
    $users->declareDropped();
} catch (SchemaException $e) {
    // 'Unable to drop non existed table' — status was STATUS_NEW
}
```

--------------------------------

### Custom Compiler with limit() override

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/compiler.md

Example of a custom compiler extending Compiler and overriding the abstract limit() method. It returns an empty string when both limit and offset are null, otherwise formats a LIMIT/OFFSET clause.

```php
use Cycle\Database\Driver\Compiler;
use Cycle\Database\Driver\Quoter;
use Cycle\Database\Query\QueryParameters;

final class MyCompiler extends Compiler
{
    protected function limit(QueryParameters $params, Quoter $q, ?int $limit = null, ?int $offset = null): string
    {
        if ($limit === null && $offset === null) {
            return '';
        }
        return sprintf('LIMIT %d OFFSET %d', $limit ?? 0, $offset ?? 0);
    }
}
```

--------------------------------

### Constructor

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/database.md

Creates a new Database instance with the given name, table prefix, write driver, and optional read driver.

```APIDOC
## Constructor

### Description
Creates a new Database instance with the given name, table prefix, write driver, and optional read driver.

### Method
Constructor

### Signature
```php
public function __construct(
    private string $name,
    private string $prefix,
    private DriverInterface $driver,
    private ?DriverInterface $readDriver = null,
)
```

### Parameters
- **$name** (`string`) - Required - Internal database name/id
- **$prefix** (`string`) - Required - Table prefix applied to all table identifiers
- **$driver** (`DriverInterface`) - Required - Write connection driver
- **$readDriver** (`?DriverInterface`) - Optional - Read-only driver; when null the write driver is used for reads too

### Request Example
```php
new Database('default', 'app_', $driver, $readDriver);
```

### Response
No return value (constructor).
```

--------------------------------

### Error Handling

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/on-conflict.md

Error handling examples for the OnConflict class.

```APIDOC
## Error Handling

### Description
Exceptions thrown when using the `OnConflict` class incorrectly.

### Exceptions
- `BuilderException` - Thrown when the conflict target is empty or when narrowing between incompatible driver-specific classes.

### Examples
```php
use Cycle\Database\Exception\BuilderException;

try {
    OnConflict::target();
} catch (BuilderException $e) {
    // 'Conflict target must contain at least one column.'
}

try {
    MySQLOnConflict::from(PostgresOnConflict::target('email'));   // incompatible narrowing
} catch (BuilderException $e) {
    // driver-specific: 'Cannot narrow ... to ...'
}
```
```

--------------------------------

### DatabasePartial::__construct

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/config.md

Creates a partial database configuration with a name, prefix, and driver. Optionally specifies a separate read driver.

```APIDOC
## DatabasePartial::__construct

### Description
Creates a partial database configuration with a name, prefix, and driver. Optionally specifies a separate read driver.

### Method
Constructor

### Signature
```php
public function __construct(
    private string $name,        // @psalm-param non-empty-string
    private string $prefix,
    private string $driver,      // @psalm-param non-empty-string
    private ?string $readDriver = null,  // @psalm-param non-empty-string|null
)
```

### Parameters
- **$name** (string) - Required - Database name (non-empty string)
- **$prefix** (string) - Required - Table prefix
- **$driver** (string) - Required - Driver name (non-empty string)
- **$readDriver** (?string) - Optional - Read driver name (nullable)

### Getters
- `getName(): string` - Returns the database name
- `getPrefix(): string` - Returns the table prefix
- `getDriver(): string` - Returns the driver name
- `getReadDriver(): ?string` - Returns the read driver name or null
```

--------------------------------

### Run the full test suite

Source: https://github.com/cycle/database/blob/2.x/CONTRIBUTING.md

Runs the full test suite for the project.

```bash
./vendor/bin/phpunit
```

--------------------------------

### State::setName / getName

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/schema-table.md

Sets or gets the name of the table state.

```APIDOC
## State::setName / getName

### Description
Sets or gets the name of the table state.

### Method
setName: void
getName: string

### Signature
- `public function setName(string $name): void`
- `public function getName(): string`

### Parameters
#### setName
- **$name** (string) - Required - The new name for the state.

### Return Value
- setName: void
- getName: string - The current name.

```

--------------------------------

### Schema table creation and alteration with Reflector

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/schema-table.md

Shows how to create a table if it doesn't exist, alter an existing table, and batch save with dependency ordering using Reflector.

```php
use Cycle\Database\Driver\HandlerInterface;
use Cycle\Database\Schema\Reflector;

$users = $db->table('users')->getSchema();
$profiles = $db->table('profiles')->getSchema();

if (!$users->exists()) {
    $users->primary('id');
    $users->string('name');
    $users->datetime('created_at');
    $users->save();
} else {
    $users->column('name')->string(191);          // alter existing
    $users->renameColumn('name', 'full_name');
    $users->dropIndex(['old_idx']);
    $users->save(HandlerInterface::DO_ALL);
}

// dependency-ordered batch save
$r = new Reflector();
$r->addTable($profiles);
$r->addTable($users);
$r->run();
```

--------------------------------

### Connect to SQLite, create schema, insert and select data using Cycle DBAL

Source: https://github.com/cycle/database/blob/2.x/README.md

Demonstrates connecting to a SQLite database, defining a table schema, inserting a row, and querying it back with Cycle DBAL. Requires the 'vendor/autoload.php' from Composer and the 'runtime/' directory to be writable.

```php
<?php
declare(strict_types=1);

require_once "vendor/autoload.php";

use Cycle\Database\Config;
use Cycle\Database\DatabaseManager;

$dbm = new DatabaseManager(new Config\DatabaseConfig([
    'databases'   => [
        'default' => ['connection' => 'sqlite'],
    ],
    'connections' => [
        'sqlite' => new Config\SQLiteDriverConfig(
            connection: new Config\SQLite\FileConnectionConfig(
                database: 'runtime/database.db'
            ),
        ),
    ],
]));

$users = $dbm->database('default')->table('users');

// create or update table schema
$schema = $users->getSchema();
$schema->primary('id');
$schema->string('name');
$schema->datetime('created_at');
$schema->datetime('updated_at');
$schema->save();

// insert data
$users->insertOne([
    'name'       => 'test',
    'created_at' => new DateTimeImmutable(),
    'updated_at' => new DateTimeImmutable(),  
]);

// select data
foreach ($users->select()->where(['name' => 'test']) as $u) {
    print_r($u);
}
```

--------------------------------

### fetchAll method

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/table.md

Shortcut for $this->select()->fetchAll().

```php
public function fetchAll(): array
```

--------------------------------

### Error handling for cursor without transaction

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/driver-sqlserver.md

Example of catching a DriverException when using a cursor without an active transaction.

```APIDOC
## Error handling for cursor without transaction

### Description
Shows how to handle the DriverException thrown when a cursor is used without an active transaction.

### Usage Example
```php
use Cycle\Database\Exception\DriverException;

try {
    $db->cursor($db->select()->from('users'));   // no active transaction
} catch (DriverException $e) {
    // 'SQLServer cursor requires an active transaction. …'
}
```

### Notes
- The exception message indicates that a transaction is required.
```

--------------------------------

### Server-side cursor (transaction required)

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/driver-sqlserver.md

Example of using a server-side cursor within a transaction for lazy row fetching.

```APIDOC
## Server-side cursor (transaction required)

### Description
Demonstrates how to use a server-side cursor with a transaction to lazily fetch rows.

### Usage Example
```php
$db->transaction(function ($db) {
    $gen = $db->cursor(
        $db->select()->from('users')->orderBy('id'),
        new SQLServerCursorOptions(type: CursorType::Static),
    );
    foreach ($gen as $row) { /* lazy rows */ }
});
```

### Notes
- A transaction is required for server-side cursors.
- The `SQLServerCursorOptions` allows specifying the cursor type (e.g., `CursorType::Static`).
```

--------------------------------

### Catching statement exceptions

Source: https://github.com/cycle/database/blob/2.x/_autodocs/errors.md

Example of catching mapped statement exceptions when executing a query, with access to the failed query via getQuery().

```APIDOC
## Catching statement exceptions

### Description
Demonstrates how to catch mapped statement exceptions thrown by the database driver when executing a statement.

### Code Example
```php
use Cycle\Database\Exception\DBALException;
use Cycle\Database\Exception\StatementException;
use Cycle\Database\Exception\StatementException\ConnectionException;
use Cycle\Database\Exception\StatementException\ConstrainException;

// catch the mapped statement exceptions — the query is always available
try {
    $db->execute('INSERT INTO users (email) VALUES (?)', ['dup@example.com']);
} catch (ConstrainException $e) {
    // unique-constraint / integrity violation
    $failedQuery = $e->getQuery();
} catch (ConnectionException $e) {
    // lost connection — already retried once internally
} catch (StatementException $e) {
    // any other statement failure
}
```

### Exceptions
- **ConstrainException** - Thrown on unique-constraint or integrity violation. Provides `getQuery()` to retrieve the failed query.
- **ConnectionException** - Thrown on lost connection; already retried once internally.
- **StatementException** - Base exception for any other statement failure.
```

--------------------------------

### addDatabase

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/database-manager.md

Registers an already-built Database instance.

```APIDOC
## addDatabase

### Description
Registers an already-built Database instance.

### Method
addDatabase

### Signature
```php
/**
 * @throws DBALException
 */
public function addDatabase(Database $database): void
```

### Parameters
- **$database** (Database) - Required - The Database instance to register

### Exceptions
- **Cycle\Database\Exception\DBALException** - Thrown if the name is already taken.
```

--------------------------------

### State::setPrimaryKeys / getPrimaryKeys

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/schema-table.md

Sets or gets the primary keys of the table. The getter merges explicit keys with columns marked as primary, smallPrimary, or bigPrimary.

```APIDOC
## State::setPrimaryKeys / getPrimaryKeys

### Description
Sets or gets the primary keys of the table. The getter merges explicit keys with columns marked as primary, smallPrimary, or bigPrimary.

### Method
- setPrimaryKeys: void
- getPrimaryKeys: array

### Signature
- `public function setPrimaryKeys(array $columns): void`
- `public function getPrimaryKeys(): array`

### Parameters
#### setPrimaryKeys
- **$columns** (array) - Required - The column names to set as primary keys.

### Return Value
- setPrimaryKeys: void
- getPrimaryKeys: array - The merged primary key columns.

```

--------------------------------

### Constructor

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/database-manager.md

Creates a new DatabaseManager instance with the given configuration and optional logger factory.

```APIDOC
## Constructor

### Description
Creates a new DatabaseManager instance.

### Method
__construct

### Signature
```php
public function __construct(
    private DatabaseConfig $config,
    private ?LoggerFactoryInterface $loggerFactory = null,
)
```

### Parameters
- **$config** (DatabaseConfig) - Required - Global database/connection configuration
- **$loggerFactory** (?LoggerFactoryInterface) - Optional - PSR-3 logger factory resolved per driver
```

--------------------------------

### Define table schema with columns, indexes, and foreign keys

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/schema.md

Shows how to obtain a table schema and use the fluent API to declare columns, indexes, and foreign keys, then save the schema.

```php
$table = $db->table('users')->getSchema();   // AbstractTable

// declare a column and index through the fluent schema element API
$table->column('id')->primary();
$table->column('email')->string(191)->nullable(false);
$table->column('role')->enum(['admin', 'user'])->defaultValue('user');
$table->column('balance')->decimal(12, 2)->defaultValue(0);
$table->column('tags')->json()->nullable();

$table->index(['email'])->unique();
$table->foreignKey(['user_id'])->references('users', ['id'])->onDelete('CASCADE');

$table->save();
```

--------------------------------

### SelectQuery constructor with from and columns parameters

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/query-builder.md

Initializes a new SelectQuery instance with an optional initial set of table names and columns to fetch.

```php
/**
 * @param array $from Initial set of table names.
 * @param array $columns Initial set of columns to fetch.
 */
public function __construct(array $from = [], array $columns = [])
```

--------------------------------

### Fragment class for raw SQL injection

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/injection.md

Represents a raw SQL fragment injected verbatim, unquoted. Variadic parameters become bound placeholders (positional `?`). Example: `->where('time_created', '>', new Fragment('NOW()'))`.

```php
class Fragment implements FragmentInterface, \Stringable
{
    /** @param non-empty-string $fragment */
    public function __construct(string $fragment, mixed ...$parameters)

    public function getType(): int        // CompilerInterface::FRAGMENT
    public function getTokens(): array    // ['fragment' => string, 'parameters' => ParameterInterface[]]
    public function __toString(): string  // the raw fragment string
    public static function __set_state(array $an_array): self
}
```

--------------------------------

### SQLServerDriver::create

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/driver-sqlserver.md

Static factory method that creates a new SQLServerDriver instance with the provided configuration.

```APIDOC
## SQLServerDriver::create

### Description
Creates a new SQLServerDriver instance with the given configuration.

### Method
static create

### Signature
public static function create(DriverConfig $config): static

### Parameters
- **$config** (DriverConfig) - Required - The driver configuration.

### Return Value
- static - A new SQLServerDriver instance.

### Notes
Internally constructs the driver with SQLServerHandler, SQLServerCompiler, and a QueryBuilder containing SQLServer-specific query classes.
```

--------------------------------

### Clone the cycle/database repository

Source: https://github.com/cycle/database/blob/2.x/CONTRIBUTING.md

Clones the cycle/database repository to set up a local development environment for testing.

```bash
git clone git@github.com:cycle/database.git
```

--------------------------------

### TableInterface Methods

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/table.md

The TableInterface defines methods for table introspection and metadata retrieval. These methods allow checking if a table exists, getting its name, primary keys, columns, indexes, foreign keys, and dependencies.

```APIDOC
## TableInterface

### Description
Interface representing a database table, providing methods for introspection and metadata access.

### Methods

#### exists(): bool
- **Description**: Checks if the table exists in the database.
- **Returns**: `bool` - `true` if the table exists, `false` otherwise.

#### getName(): string
- **Description**: Returns the table name.
- **Returns**: `non-empty-string` - The table name.

#### getFullName(): string
- **Description**: Returns the fully qualified table name (including schema/prefix if applicable).
- **Returns**: `string` - The full table name.

#### getPrimaryKeys(): array
- **Description**: Returns the list of primary key column names.
- **Returns**: `list<non-empty-string>` - Array of primary key column names.

#### hasColumn(string $name): bool
- **Description**: Checks if a column with the given name exists in the table.
- **Parameters**:
  - `$name` (string) - Required - The column name to check.
- **Returns**: `bool` - `true` if the column exists, `false` otherwise.

#### getColumns(): array
- **Description**: Returns all columns of the table.
- **Returns**: `ColumnInterface[]` - Array of ColumnInterface objects.

#### hasIndex(array $columns = []): bool
- **Description**: Checks if an index exists on the given columns.
- **Parameters**:
  - `$columns` (array) - Optional - List of column names to check for index.
- **Returns**: `bool` - `true` if an index exists, `false` otherwise.

#### getIndexes(): array
- **Description**: Returns all indexes of the table.
- **Returns**: `IndexInterface[]` - Array of IndexInterface objects.

#### hasForeignKey(array $columns): bool
- **Description**: Checks if a foreign key exists on the given columns.
- **Parameters**:
  - `$columns` (array) - Required - List of column names to check for foreign key.
- **Returns**: `bool` - `true` if a foreign key exists, `false` otherwise.

#### getForeignKeys(): array
- **Description**: Returns all foreign keys of the table.
- **Returns**: `ForeignKeyInterface[]` - Array of ForeignKeyInterface objects.

#### getDependencies(): array
- **Description**: Returns the dependencies of the table (e.g., tables that reference this table).
- **Returns**: `array` - Array of dependency information.

```

--------------------------------

### PostgresCursorOptions class definition

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/driver-postgres.md

Defines the PostgresCursorOptions class extending CursorOptions with chunkSize, withHold, and name properties. The constructor throws 
InvalidArgumentException when $name is not a valid PostgreSQL identifier (must start with letter or underscore and be at most 63 characters).

```php
final class PostgresCursorOptions extends CursorOptions
{
    public function __construct(
        public readonly int $chunkSize = 1000,   // @param int<1, max> rows per FETCH FORWARD round-trip
        public readonly bool $withHold = false,
        public readonly ?string $name = null,
    )
    // throws \
InvalidArgumentException when $name is not a valid PostgreSQL identifier
    // ('must be a valid PostgreSQL identifier: start with letter or underscore, …≤63 characters')

    public static function from(CursorOptions $options): self
}
```

--------------------------------

### PostgresDriver::create

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/driver-postgres.md

Creates a new PostgresDriver instance with the provided configuration, setting up the handler, compiler, query builder, and schemas.

```APIDOC
## PostgresDriver::create

### Description
Creates a new PostgresDriver instance with the provided configuration, setting up the handler, compiler, query builder, and schemas.

### Method
static

### Signature
public static function create(DriverConfig $config): static

### Parameters
- **$config** (DriverConfig) - Required - The driver configuration.

### Return Value
- **static** - A new PostgresDriver instance.

### Implementation Details
- Instantiates `new PostgresDriver($config, new PostgresHandler(), new PostgresCompiler('"'), new QueryBuilder(new PostgresSelectQuery(), new PostgresInsertQuery(), new PostgresUpdateQuery(), new PostgresDeleteQuery()))`.
- Calls `defineSchemas()` from `$config->schema`.
```

--------------------------------

### Expression class for quoted SQL expressions

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/injection.md

Like `Fragment`, but every table/column identifier inside the statement is quoted and prefixed during compilation. Example: `new Expression("table.column = table.column + 1")`. `__toString()` prefixes `exp:`.

```php
class Expression implements FragmentInterface
{
    /** @param non-empty-string $statement */
    public function __construct(string $statement, mixed ...$parameters)

    public function getType(): int        // CompilerInterface::EXPRESSION
    public function getTokens(): array    // ['expression' => string, 'parameters' => ParameterInterface[]]
    public function __toString(): string  // 'exp:' . $expression
    public static function __set_state(array $an_array): self
}
```

--------------------------------

### CachingCompilerInterface PHP interface with hashLimit method

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/compiler.md

Defines the CachingCompilerInterface that extends CompilerInterface. Drivers whose compilers implement this interface get automatic query caching, wrapped by the Driver constructor. Use this interface when implementing a driver compiler that should support query caching.

```php
interface CachingCompilerInterface extends CompilerInterface
{
    public function hashLimit(QueryParameters $params, array $tokens): string;
}
```

--------------------------------

### DriverConfig::__construct

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/config.md

Configures a database driver with connection settings, driver class, and various behavioral options.

```APIDOC
## DriverConfig::__construct

### Description
Configures a database driver with connection settings, driver class, and various behavioral options.

### Method
Constructor

### Signature
```php
public function __construct(
    public ConnectionConfig $connection,
    public string $driver,
    public bool $reconnect = true,
    public string $timezone = 'UTC',
    public bool $queryCache = true,
    public bool $readonlySchema = false,
    public bool $readonly = false,
    array $options = [],
)
```

### Parameters
- **$connection** (ConnectionConfig) - Required - Driver-specific connection config
- **$driver** (string) - Required - Concrete driver class to instantiate (class-string of DriverInterface)
- **$reconnect** (bool) - Optional - Allow singular reconnects on lost connections (default: true)
- **$timezone** (string) - Optional - All datetime objects are converted relative to this timezone (must match the DB timezone) (default: 'UTC')
- **$queryCache** (bool) - Optional - Enables query caching (CompilerCache) (default: true)
- **$readonlySchema** (bool) - Optional - Disable schema modifications (ReadonlyHandler wraps the schema handler) (default: false)
- **$readonly** (bool) - Optional - Disable write expressions (Driver::execute() throws ReadonlyConnectionException) (default: false)
- **$options** (array) - Optional - Options merged over defaults: withDatetimeMicroseconds, logInterpolatedQueries, logQueryParameters (default: [])

### Default Options
```php
protected array $defaultOptions = [
    'withDatetimeMicroseconds' => false,
    'logInterpolatedQueries' => false,
    'logQueryParameters' => false,
];
```
```

--------------------------------

### SQLite driver configuration and usage

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/driver-sqlite.md

Shows how to configure a SQLite driver (file or memory) and perform upsert, returning insert, and cursor operations. The upsert uses SQLiteOnConflict::target with a predicate and doUpdate. The cursor iterates lazily over a memory database inside a transaction.

```php
use Cycle\Database\Config\SQLiteDriverConfig;
use Cycle\Database\Config\SQLite\FileConnectionConfig;
use Cycle\Database\Config\SQLite\MemoryConnectionConfig;
use Cycle\Database\DatabaseManager;
use Cycle\Database\Driver\SQLite\SQLiteOnConflict;

$dbm = new DatabaseManager(new Config\DatabaseConfig([
    'databases' => ['default' => ['connection' => 'sqlite']],
    'connections' => [
        'sqlite' => new SQLiteDriverConfig(
            connection: new FileConnectionConfig(database: 'runtime/db.sqlite'),
        ),
        // or: new SQLiteDriverConfig() — in-memory database
    ],
]));
$db = $dbm->database();

// upsert with index-inference predicate
$db->insert('users')->values(['tenant_id' => 1, 'email' => 'a@b.c', 'hits' => 1])
   ->onConflict(SQLiteOnConflict::target(['tenant_id', 'email'])->targetWhere('active = 1')->doUpdate())
   ->run();

// returning insert
$id = $db->insert('users')->values(['name' => 'Bob'])->returning('id')->run();

// cursor over a memory database inside a transaction
$db->transaction(function ($db) {
    foreach ($db->cursor($db->select()->from('users')) as $row) { /* lazy */ }
});
```

--------------------------------

### PDOConnectionConfig (abstract)

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/config.md

Abstract class extending ConnectionConfig, adding PDO options and abstract methods for name and DSN.

```APIDOC
## PDOConnectionConfig (abstract)

### Description
Abstract class extending ConnectionConfig, adding PDO options and abstract methods for name and DSN.

### Constructor
```php
public function __construct(
    ?string $user = null,
    ?string $password = null,
    public array $options = [],
)
```

### Parameters
- **user** (?string) - Optional - Database username.
- **password** (?string) - Optional - Database password.
- **options** (array) - Optional - PDO options merged over DEFAULT_PDO_OPTIONS via array_replace.

### Methods
- **getName(): string** - Abstract. Returns the driver name (non-empty-string).
- **getOptions(): array** - Returns the merged PDO options.
- **getDsn(): string** - Abstract. Returns the DSN string.

### Default PDO Options
```php
protected const DEFAULT_PDO_OPTIONS = [
    \PDO::ATTR_CASE => \PDO::CASE_NATURAL,
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES => false,
];
```
```

--------------------------------

### getDatabase method

Source: https://github.com/cycle/database/blob/2.x/_autodocs/api-reference/table.md

Returns the DatabaseInterface instance associated with the table.

```php
public function getDatabase(): DatabaseInterface
```
Tokens:
Raw