Doctrine DBAL

https://github.com/doctrine/dbal
Admin
Doctrine DBAL is a powerful PHP database abstraction layer with comprehensive features for database...
Tokens:389,382
Snippets:2,758
Trust Score:9.7
License:MIT
Update:3 weeks ago
Show doc for...
Tokens:
Raw
### fetchOne example

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Retrieves only the value of the first column of the first result row. The example fetches a username and echoes it.

```php
<?php
$username = $conn->fetchOne('SELECT username FROM user WHERE id = ?', [1], 0);
echo $username; // jwage
```

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

### update example

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Updates all rows for the matching key-value identifiers with the given data. The example shows the generated SQL.

```php
<?php
$conn->update('user', ['username' => 'jwage'], ['id' => 1]);
// UPDATE user SET username = ? WHERE id = ? (jwage, 1)
```

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

### insert example

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Inserts a row into the given table using key-value pairs of data. The example shows the generated SQL.

```php
<?php
$conn->insert('user', ['username' => 'jwage']);
// INSERT INTO user (username) VALUES (?) (jwage)
```

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

### fetchAllAssociative example

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Executes a query and fetches all results into an array. The example selects all users and shows the expected array structure.

```php
<?php
$users = $conn->fetchAllAssociative('SELECT * FROM user');

/*
array(
  0 => array(
    'username' => 'jwage',
    'email' => 'j.wage@example.com'
  )
)
*/
```

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

### fetchNumeric example

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Retrieves the first result row with numeric indices. The example fetches a user by username and shows the expected array.

```php
<?php
$user = $conn->fetchNumeric('SELECT * FROM user WHERE username = ?', ['jwage']);

/*
array(
  0 => 'jwage',
  1 => 'j.wage@example.com'
)
*/
```

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

### fetchAllKeyValue example

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Executes a query and fetches the first two columns into an associative array as keys and values respectively. Note that additional columns are ignored.

```php
<?php
$users = $conn->fetchAllKeyValue('SELECT username, email FROM user');

/*
array(
  'jwage' => 'j.wage@example.com',
)
*/
```

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

### Prepared Statements for SQL and DQL

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/security.rst

Examples of using positional and named placeholders for both SQL and DQL to securely execute queries with user-provided parameters.

```php
<?php
// SQL Prepared Statements: Positional
$sql = "SELECT * FROM users WHERE username = ?";
$stmt = $connection->prepare($sql);
$stmt->bindValue(1, $_GET['username']);
$resultSet = $stmt->executeQuery();

// SQL Prepared Statements: Named
$sql = "SELECT * FROM users WHERE username = :user";
$stmt = $connection->prepare($sql);
$stmt->bindValue("user", $_GET['username']);
$resultSet = $stmt->executeQuery();

// DQL Prepared Statements: Positional
$dql = "SELECT u FROM User u WHERE u.username = ?1";
$query = $em->createQuery($dql);
$query->setParameter(1, $_GET['username']);
$data = $query->getResult();

// DQL Prepared Statements: Named
$dql = "SELECT u FROM User u WHERE u.username = :name";
$query = $em->createQuery($dql);
$query->setParameter("name", $_GET['username']);
$data = $query->getResult();
```

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

### fetchAssociative example

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Retrieves an associative array of the first result row. The example fetches a user by username and shows the expected array.

```php
<?php
$user = $conn->fetchAssociative('SELECT * FROM user WHERE username = ?', ['jwage']);
/*
array(
  'username' => 'jwage',
  'email' => 'j.wage@example.com'
)
*/
```

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

### Get a connection using DriverManager with connection parameters

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/configuration.rst

Creates a DBAL connection by passing an array of connection parameters to DriverManager::getConnection(). The example uses the pdo_mysql driver with host, user, password, and database name.

```php
<?php
use Doctrine\DBAL\DriverManager;

//..
$connectionParams = [
    'dbname' => 'mydb',
    'user' => 'user',
    'password' => 'secret',
    'host' => 'localhost',
    'driver' => 'pdo_mysql',
];
$conn = DriverManager::getConnection($connectionParams);
```

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

### prepare()

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Prepares a SQL statement and returns a Doctrine\DBAL\Statement instance. The example shows how to prepare a SELECT query, execute it, and fetch all rows as associative arrays.

```APIDOC
## prepare()

### Description
Prepares a given SQL statement and returns a `\Doctrine\DBAL\Statement` instance.

### Method
prepare

### Parameters
- **sql** (string) - Required - The SQL statement to prepare.

### Return Value
Returns a `\Doctrine\DBAL\Statement` instance.

### Example
```php
<?php
$statement = $conn->prepare('SELECT * FROM user');
$resultSet = $statement->executeQuery();
$users = $resultSet->fetchAllAssociative();

/*
array(
  0 => array(
    'username' => 'jwage',
    'email' => 'j.wage@example.com'
  )
)
*/
```
```

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

### iterateKeyValue example

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Executes a query and iterates over the first two columns as keys and values respectively. Additional columns are ignored.

```php
<?php
foreach ($conn->iterateKeyValue('SELECT username, email FROM user') as $username => $email) {
    // ...
}
```

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

### executeQuery with parameters

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Creates a prepared statement and executes it with parameters, returning a result set. The example fetches a user by username and shows the expected array output.

```php
<?php
$resultSet = $conn->executeQuery('SELECT * FROM user WHERE username = ?', ['jwage']);
$user = $resultSet->fetchAssociative();

/*
array(
  0 => 'jwage',
  1 => 'j.wage@example.com'
)
*/
```

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

### beginTransaction()

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/transactions.rst

Starts a transaction. If already in a transaction, creates a savepoint instead of a real nested transaction.

```APIDOC
## beginTransaction()

### Description
Starts a transaction. If already in a transaction, creates a savepoint instead of a real nested transaction.

### Method
beginTransaction

### Endpoint
Doctrine\DBAL\Connection::beginTransaction()

### Parameters
None

### Request Example
```php
$conn->beginTransaction();
```

### Response
#### Success Response
No return value.

#### Response Example
```php
null
```

```

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

### beginTransaction

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/transactions.rst

Starts a new transaction on the connection. If a transaction is already active, this starts a nested transaction, increasing the nesting level.

```APIDOC
## beginTransaction

### Description
Starts a new transaction on the connection. If a transaction is already active, this starts a nested transaction, increasing the nesting level.

### Method
beginTransaction

### Behavior
- Starts a new transaction. If called while a transaction is active, it starts an inner (nested) transaction.
- The transaction nesting level is incremented.

### Example
```php
$conn->beginTransaction(); // start inner transaction, nesting level 2
```
```

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

### Retrieve a Type instance using Type::getType()

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/types.rst

Use the static factory method to get a type instance by name. The example retrieves the integer type. Type names match those listed in the reference.

```php
<?php

// Returns instance of \Doctrine\DBAL\Types\IntegerType
$type = \Doctrine\DBAL\Types\Type::getType('integer');
```

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

### SQL: Create implicit index example

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/explanation/implicit-indexes.rst

Example of a DBAL-defined implicit index with a generated name like IDX_885DBAFAA76ED395. The name fits within the platform's identifier-length limit.

```sql
CREATE INDEX IDX_885DBAFAA76ED395 ON posts (user_id)
```

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

### Build INSERT, UPDATE, and DELETE queries

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/query-builder.rst

Pass the table name to insert(), update(), or delete() to start the corresponding query type. These methods are used for non-SELECT queries.

```php
<?php

$queryBuilder
    ->insert('users')
;

$queryBuilder
    ->update('users')
;

$queryBuilder
    ->delete('users')
;
```

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

### Build a SELECT query with select() and from()

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/query-builder.rst

Start a SELECT query by invoking select() and from(). The query builder supports SELECT, INSERT, UPDATE, and DELETE queries.

```php
<?php

$queryBuilder
    ->select('id', 'name')
    ->from('users');
```

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

### UNION and UNION ALL with Query Builder

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/query-builder.rst

Shows how to combine two subqueries using union() and addUnion() with UnionType::DISTINCT (default) and UnionType::ALL. The second example adds ordering and a limit.

```php
$subQueryBuilder1
    ->select('id AS field')
    ->from('a_table');
$subQueryBuilder2
    ->select('id AS field')
    ->from('a_table');
$queryBuilder
    ->union($subQueryBuilder1)
    ->addUnion($subQueryBuilder2, UnionType::DISTINCT);
```

```php
$subQueryBuilder1
    ->select('id AS field')
    ->from('a_table');
$subQueryBuilder2
    ->select('id AS field')
    ->from('a_table');
$queryBuilder
    ->union($subQueryBuilder1)
    ->addUnion($subQueryBuilder2,UnionType::ALL)
    ->orderBy('field', 'DESC')
    ->setMaxResults(100);
```

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

### QueryBuilder::select

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/query-builder.rst

Starts building a SELECT query by specifying the columns to select. This method is used to initiate a SELECT query.

```APIDOC
## QueryBuilder::select

### Description
Starts building a SELECT query by specifying the columns to select. This method is used to initiate a SELECT query.

### Method
select

### Endpoint
QueryBuilder::select(...$columns)

### Parameters
- **$columns** (string) - Required - One or more column names to select.

### Example
```php
<?php

$queryBuilder
    ->select('id', 'name')
    ->from('users');
```
```

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

### Attempt to bind array to IN clause (will not work)

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Shows the incorrect attempt to bind an array to a single placeholder. This will not work and is provided as a cautionary example.

```php
<?php
$stmt = $conn->prepare('SELECT * FROM articles WHERE id IN (?)');
// THIS WILL NOT WORK:
$stmt->bindValue(1, [1, 2, 3, 4, 5, 6]);
$resultSet = $stmt->executeQuery();
```

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

### Binding parameters with createNamedParameter and createPositionalParameter

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/query-builder.rst

Shows how to bind values to placeholders without knowing the exact placeholder name. The first example uses a named parameter (:dcValue1), the second uses a positional parameter (?).

```php
<?php

$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->where('email = ' .  $queryBuilder->createNamedParameter($userInputEmail))
;
// SELECT id, name FROM users WHERE email = :dcValue1
```

```php
<?php

$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->where('email = ' .  $queryBuilder->createPositionalParameter($userInputEmail))
;
// SELECT id, name FROM users WHERE email = ?
```

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

### MySQL SELECT VERSION() query and output

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/configuration.rst

Run this query on your MySQL server to get the full version string, which is required for the serverVersion option. The output shows the version as reported by the server.

```sql
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)
```

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

### QueryBuilder::insert

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/query-builder.rst

Starts building an INSERT query for the specified table.

```APIDOC
## QueryBuilder::insert

### Description
Starts building an INSERT query for the specified table.

### Method
insert

### Endpoint
QueryBuilder::insert($table)

### Parameters
- **$table** (string) - Required - The table name to insert into.

### Example
```php
<?php

$queryBuilder
    ->insert('users');
```
```

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

### Customize a platform using a middleware

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/platforms.rst

Shows how to override platform behavior by creating a custom platform class and injecting it via a middleware. Use this to customize SQL generation for special needs, for example using triggers or views to simulate features not portable across databases.

```php
<?php
class CustomSQLitePlatform extends SqlitePlatform {}

class CustomDriver extends AbstractDriverMiddleware
{
    public function getDatabasePlatform(ServerVersionProvider $versionProvider)
    {
        return new CustomSQLitePlatform();
    }
}

class CustomMiddleware implements Driver\Middleware
{
    public function wrap(Driver $driver): Driver
    {
        return new CustomDriver($driver);
    }
}

$config = new Doctrine\DBAL\Configuration();
$config->setMiddlewares([new CustomMiddleware()]);

$connection = DriverManager::getConnection($params, $config);
```

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

### Configure DSN parser with mapping table

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/configuration.rst

Creates a DsnParser with a mapping table to map custom schemes to DBAL drivers. The example maps 'mysql' to 'mysqli' and 'postgres' to 'pdo_pgsql', then parses a mysql:// DSN.

```php
<?php
use Doctrine\DBAL\Tools\DsnParser;

//..
$dsnParser = new DsnParser(['mysql' => 'mysqli', 'postgres' => 'pdo_pgsql']);
$connectionParams = $dsnParser
    ->parse('mysql://user:secret@localhost/mydb');
```

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

### Postgres: Get server version with SHOW server_version

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/configuration.rst

Run this query on the Postgres server to obtain the value for the serverVersion parameter. The output shows the version string to use, e.g., '15.2 (Debian 15.2-1.pgdg110+1)'.

```sql
postgres=# SHOW server_version;
         server_version
--------------------------------
 15.2 (Debian 15.2-1.pgdg110+1)
(1 row)
```

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

### SQLite connection URL with ignored authority

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/configuration.rst

Shows a URL for connecting to the same SQLite database file as the previous example, but with ignored user, password, and authority parts.

```text
pdo-sqlite://ignored:ignored@ignored:1234/somedb.sqlite
```

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

### MariaDB: Get server version with SELECT VERSION()

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/configuration.rst

Run this query on the MariaDB server to obtain the value for the serverVersion parameter. The output shows the version string to use, e.g., '10.11.2-MariaDB-1:10.11.2+maria~ubu2204'.

```sql
MariaDB [(none)]> SELECT VERSION();
+-----------------------------------------+
| VERSION()                               |
+-----------------------------------------+
| 10.11.2-MariaDB-1:10.11.2+maria~ubu2204 |
+-----------------------------------------+
1 row in set (0.001 sec)
```

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

### fetchAllAssociativeIndexed example

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Executes a query and fetches data as an associative array where the key is the first column and the value is an associative array of the remaining columns.

```php
<?php
$users = $conn->fetchAllAssociativeIndexed('SELECT id, username, email FROM user');

/*
array(
    1 => array(
      'username' => 'jwage',
      'email' => 'j.wage@example.com'
    )
)
*/
```

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

### Access the platform from a connection

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/platforms.rst

Retrieves the platform instance from a DBAL connection using the getDatabasePlatform() method. This is the starting point for using platform-specific query generation.

```php
<?php
$platform = $conn->getDatabasePlatform();
```

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

### VALUES Clause: combining values() and setValue()

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/query-builder.rst

Shows how to combine values() and setValue() in the same INSERT query. The example conditionally adds a password column only if a password is provided.

```php
<?php

$queryBuilder
    ->insert('users')
    ->values(
        [
            'name' => '?',
        ]
    )
    ->setParameter(0, $username)
;
// INSERT INTO users (name) VALUES (?)

if ($password) {
    $queryBuilder
        ->setValue('password', '?')
        ->setParameter(1, $password)
    ;
    // INSERT INTO users (name, password) VALUES (?, ?)
}
```

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

### PHP: Get server version for other platforms

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/configuration.rst

For platforms without version-specific detection, use this expression to obtain the server version string to pass as the serverVersion parameter.

```php
$connection->getWrappedConnection()->getServerVersion();
```

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

### Join Clauses: innerJoin example

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/query-builder.rst

Shows how to add an INNER JOIN to a SELECT query using the QueryBuilder. The first argument is the alias of the FROM part, followed by the join table name, alias, and ON clause.

```php
<?php
$queryBuilder
    ->select('u.id', 'u.name', 'p.number')
    ->from('users', 'u')
    ->innerJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id')
```

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

### Execute UPDATE and get affected row count

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Executes an UPDATE statement with parameters and returns the number of affected rows. The example shows the count is 1.

```php
<?php
$count = $conn->executeStatement('UPDATE user SET username = ? WHERE id = ?', ['jwage', 1]);
echo $count; // 1
```

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

### Get a connection using DsnParser to parse a DSN

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/configuration.rst

Parses a DSN string using DsnParser and passes the resulting connection parameters to DriverManager::getConnection(). The example uses a mysqli:// DSN.

```php
<?php
use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\Tools\DsnParser;

//..
$dsnParser = new DsnParser();
$connectionParams = $dsnParser
    ->parse('mysqli://user:secret@localhost/mydb');

$conn = DriverManager::getConnection($connectionParams);
```

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

### delete example

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Deletes all rows of a table matching the given identifier, where keys are column names. The example shows the generated SQL.

```php
<?php
$conn->delete('user', ['id' => 1]);
// DELETE FROM user WHERE id = ? (1)
```

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

### Before: Using PDO constants with DBAL

Source: https://github.com/doctrine/dbal/blob/4.5.x/UPGRADE.md

Shows the previous way to configure fetch case and use PDO constants, which is no longer supported.

```php
use Doctrine\DBAL\Portability\Connection;

$params = array(
    'wrapperClass' => Connection::class,
    'fetch_case' => PDO::CASE_LOWER,
);

$stmt->bindValue(1, 1, PDO::PARAM_INT);
$stmt->fetchAll(PDO::FETCH_COLUMN);
```

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

### Configure Portable Connection Middleware

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/portability.rst

Sets up a Doctrine DBAL configuration with the PortableMiddleware to handle portability concerns: rtrimming strings, converting empty strings to null, and returning associative keys in lower-case. This uses PDO native functionality or PHP userland for OCI8. Note that this handling is expensive as all result rows and columns are looped in PHP.

```php
<?php

use Doctrine\DBAL\ColumnCase;
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\Portability\Connection as PortableConnection;
use Doctrine\DBAL\Portability\Middleware as PortableMiddleware;

$configuration = new Configuration();
$configuration->setMiddlewares([
    // Other middlewares
    //...
    new PortableMiddleware(PortableConnection::PORTABILITY_ALL, ColumnCase::LOWER),
]);
```

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

### Fetching rows with fetchAssociative()

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Iterates over the statement result set using fetchAssociative() to retrieve each row until no more rows are available. This example follows the basic query example.

```php
<?php

while (($row = $stmt->fetchAssociative()) !== false) {
    echo $row['headline'];
}
```

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

### Override Schema Manager with Custom Factory

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/schema-manager.rst

Shows how to create a custom schema manager factory that returns a custom MySQL schema manager, and configure it on the connection.

```php
<?php
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\Platforms\AbstractMySQLPlatform;
use Doctrine\DBAL\Schema\DefaultSchemaManagerFactory;
use Doctrine\DBAL\Schema\MySQLSchemaManager;
use Doctrine\DBAL\Schema\SchemaManagerFactory;

class MyCustomMySQLSchemaManager extends MySQLSchemaManager
{
    // .. your custom logic.
}

final class MySchemaManagerFactory implements SchemaManagerFactory
{
    private readonly SchemaManagerFactory $defaultFactory;

    public function __construct()
    {
        $this->defaultFactory = new DefaultSchemaManagerFactory();
    }

    public function createSchemaManager(Connection $connection): AbstractSchemaManager
    {
        $platform = $connection->getDatabasePlatform();
        if ($platform instanceof AbstractMySQLPlatform) {
            return new MyCustomMySQLSchemaManager($connection, $platform);
        }

        return $this->defaultFactory->createSchemaManager($connection);
    }
}

$configuration = new Configuration();
$configuration->setSchemaManagerFactory(new MySchemaManagerFactory());

$connection = DriverManager::getConnection([/* your connection parameters */], $configuration);
```

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

### commit

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/transactions.rst

Commits the current transaction. If called on a nested transaction, it commits that inner transaction without starting a new one. If called on the outermost transaction, it commits and immediately starts a new transaction when auto-commit is disabled.

```APIDOC
## commit

### Description
Commits the current transaction. If called on a nested transaction, it commits that inner transaction without starting a new one. If called on the outermost transaction, it commits and immediately starts a new transaction when auto-commit is disabled.

### Method
commit

### Behavior
- Commits the current transaction.
- If the committed transaction is the outermost transaction and auto-commit is disabled, a new transaction is started automatically.
- Committing a nested transaction does not start a new transaction.

### Example
```php
$conn->commit(); // commits inner transaction, does not start a new one
// ...
$conn->commit(); // commits outer transaction, and immediately starts a new one
```
```

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

### Before: Using 'url' connection parameter

Source: https://github.com/doctrine/dbal/blob/4.5.x/UPGRADE.md

Shows the old way of configuring a database connection with the 'url' parameter, which has been removed in DBAL 4.0.

```php
$connection = DriverManager::getConnection(
    ['url' => 'mysql://my-user:t0ps3cr3t@my-host/my-database']
);
```

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

### rollBack

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/transactions.rst

Rolls back the current transaction. If called on a nested transaction, it rolls back that inner transaction without starting a new one. If called on the outermost transaction, it rolls back and immediately starts a new transaction when auto-commit is disabled.

```APIDOC
## rollBack

### Description
Rolls back the current transaction. If called on a nested transaction, it rolls back that inner transaction without starting a new one. If called on the outermost transaction, it rolls back and immediately starts a new transaction when auto-commit is disabled.

### Method
rollBack

### Behavior
- Rolls back the current transaction.
- If the rolled back transaction is the outermost transaction and auto-commit is disabled, a new transaction is started automatically.
- Rolling back a nested transaction does not start a new transaction.

### Example
```php
$conn->rollBack(); // rolls back inner transaction, does not start a new one
// ...
$conn->rollBack(); // rolls back outer transaction, and immediately starts a new one
```
```

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

### Nested transactions with auto-commit disabled

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/transactions.rst

Demonstrates nested transaction handling when auto-commit is disabled. Committing or rolling back inner transactions does not start a new one, but committing or rolling back the outer transaction immediately starts a new transaction.

```php
<?php
// we are not in auto-commit mode, transaction is active

try {
    // do stuff

    $conn->beginTransaction(); // start inner transaction, nesting level 2

    try {
        // do stuff
        $conn->commit(); // commits inner transaction, does not start a new one
    } catch (\Exception $e) {
        $conn->rollBack(); // rolls back inner transaction, does not start a new one
    }

    // do stuff

    $conn->commit(); // commits outer transaction, and immediately starts a new one
} catch (\Exception $e) {
    $conn->rollBack(); // rolls back outer transaction, and immediately starts a new one
}
```

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

### QueryBuilder::delete

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/query-builder.rst

Starts building a DELETE query for the specified table.

```APIDOC
## QueryBuilder::delete

### Description
Starts building a DELETE query for the specified table.

### Method
delete

### Endpoint
QueryBuilder::delete($table)

### Parameters
- **$table** (string) - Required - The table name to delete from.

### Example
```php
<?php

$queryBuilder
    ->delete('users');
```
```

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

### QueryBuilder::update

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/query-builder.rst

Starts building an UPDATE query for the specified table.

```APIDOC
## QueryBuilder::update

### Description
Starts building an UPDATE query for the specified table.

### Method
update

### Endpoint
QueryBuilder::update($table)

### Parameters
- **$table** (string) - Required - The table name to update.

### Example
```php
<?php

$queryBuilder
    ->update('users');
```
```

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

### fetchAllKeyValue()

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Execute the query and fetch the first two columns into an associative array as keys and values respectively.

```APIDOC
## fetchAllKeyValue()

### Description
Execute the query and fetch the first two columns into an associative array as keys and values respectively.

### Method
PHP method

### Signature
`$conn->fetchAllKeyValue($sql, $params, $types)`

### Parameters
- **$sql** (string) - Required - The SQL query to execute.
- **$params** (array) - Optional - The parameters to bind to the query.
- **$types** (array) - Optional - The PDO or Doctrine Type constants for type conversions.

### Request Example
```php
<?php
$users = $conn->fetchAllKeyValue('SELECT username, email FROM user');
```

### Response
#### Success Response
Returns an associative array where the first column is the key and the second column is the value.

#### Response Example
```php
/*
array(
  'jwage' => 'j.wage@example.com',
)
*/
```

### Notes
All additional columns will be ignored and are only allowed to be selected by DBAL for its internal purposes.
```

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

### After: Using DBAL constants instead of PDO

Source: https://github.com/doctrine/dbal/blob/4.5.x/UPGRADE.md

Shows the new way to configure fetch case and use DBAL constants, replacing the removed PDO constants.

```php
use Doctrine\DBAL\ColumnCase;
use Doctrine\DBAL\FetchMode;
use Doctrine\DBAL\ParameterType;
use Doctrine\DBAL\Portability\Connection;

$params = array(
    'wrapperClass' => Connection::class,
    'fetch_case' => ColumnCase::LOWER,
);

$stmt->bindValue(1, 1, ParameterType::INTEGER);
$stmt->fetchAll(FetchMode::COLUMN);
```

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

### Auto-commit mode with beginTransaction()

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/transactions.rst

Shows that in auto-commit mode (the default), a transaction is not started until beginTransaction() is called.

```php
<?php
// define connection parameters $params and initialize driver $driver

$conn = new \Doctrine\DBAL\Connection($params, $driver);

// we are in auto-commit mode
$conn->beginTransaction();
```

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

### iterateAssociativeIndexed example

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Executes a query and iterates over the result with the key representing the first column and the value being an associative array of the rest.

```php
<?php
foreach ($conn->iterateAssociativeIndexed('SELECT id, username, email FROM user') as $id => $data) {
    // ...
}
```

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

### After: Using Comparator::compareTables() and TableDiff::isEmpty()

Source: https://github.com/doctrine/dbal/blob/4.5.x/UPGRADE.md

Shows the new recommended approach with optional isEmpty() check. Use this to migrate from diffTable().

```php
$diff = $comparator->compareTables($oldTable, $newTable);

// optional check
if (! $diff->isEmpty()) {
    // we have a diff
}
```

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

### setAutoCommit($autoCommit)

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/transactions.rst

Sets the auto-commit mode for the connection. When disabled, a new transaction is automatically started on connect() and after each commit() or rollBack().

```APIDOC
## setAutoCommit($autoCommit)

### Description
Sets the auto-commit mode for the connection. When disabled, a new transaction is automatically started on connect() and after each commit() or rollBack().

### Method
setAutoCommit

### Endpoint
Doctrine\DBAL\Connection::setAutoCommit($autoCommit)

### Parameters
#### Parameter
- **$autoCommit** (bool) - Required - Whether to enable or disable auto-commit mode.

### Request Example
```php
$conn->setAutoCommit(false);
```

### Response
#### Success Response
No return value.

#### Response Example
```php
null
```

```

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

### Prepare and fetch all rows

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Prepares a SELECT statement, executes it, and fetches all rows as associative arrays. The output shows the structure of the returned array.

```php
<?php
$statement = $conn->prepare('SELECT * FROM user');
$resultSet = $statement->executeQuery();
$users = $resultSet->fetchAllAssociative();

/*
array(
  0 => array(
    'username' => 'jwage',
    'email' => 'j.wage@example.com'
  )
)
*/
```

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

### Returning a value from transactional() closure

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/transactions.rst

The closure passed to transactional() can return any value, which is then returned by transactional(). This example returns the result of a fetchOne() call.

```php
<?php
$one = $conn->transactional(function(Connection $conn): int {
    // do stuff
    return $conn->fetchOne('SELECT 1');
});
```

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

### Create a SchemaManager instance with createSchemaManager()

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/schema-manager.rst

Use the connection's createSchemaManager() method to obtain a SchemaManager instance for introspecting the database schema.

```php
<?php
$schemaManager = $conn->createSchemaManager();
```

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

### Limit Clause: setFirstResult and setMaxResults

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/query-builder.rst

Shows how to set the offset and limit for a SELECT query using setFirstResult() and setMaxResults(). This works across all vendors even if they do not natively support LIMIT.

```php
<?php
$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->setFirstResult(10)
    ->setMaxResults(20);
```

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

### Prepared statement with multiple positional parameters

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Binds multiple positional parameters in order using bindValue(). The numerical parameters start with 1.

```php
<?php
// $conn instanceof Doctrine\DBAL\Connection

$sql = "SELECT * FROM articles WHERE id = ? AND status = ?";
$stmt = $conn->prepare($sql);
$stmt->bindValue(1, $id);
$stmt->bindValue(2, $status);
$resultSet = $stmt->executeQuery();
```

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

### SQL String Concatenation Vulnerability

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/security.rst

An example of insecure SQL query construction using direct concatenation of user input, which is vulnerable to SQL injection.

```php
<?php
// Very wrong!
$sql = "SELECT * FROM users WHERE name = '" . $_GET['username']. "'";
```

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

### Run integration tests against MySQL using PDO driver

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/testing.rst

Command to run the PHPUnit test suite with a custom configuration file for MySQL using the PDO driver. Requires a running MySQL server and the pdo_mysql extension enabled.

```console
$ phpunit -c ci/github/pdo_mysql.xml
```

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

### VALUES Clause: values() with parameters

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/query-builder.rst

Shows how to set multiple column values for an INSERT using values() and then bind parameters with setParameter(). Each subsequent call to values() overwrites previous values.

```php
<?php

$queryBuilder
    ->insert('users')
    ->values(
        [
            'name' => '?',
            'password' => '?',
        ]
    )
    ->setParameter(0, $username)
    ->setParameter(1, $password)
;
// INSERT INTO users (name, password) VALUES (?, ?)
```

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

### setAutoCommit

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/transactions.rst

Enables or disables auto-commit mode on a Doctrine DBAL connection. Disabling auto-commit starts a new transaction, while enabling it commits the current transaction.

```APIDOC
## setAutoCommit

### Description
Enables or disables auto-commit mode on a Doctrine DBAL connection. Disabling auto-commit starts a new transaction, while enabling it commits the current transaction.

### Method
setAutoCommit

### Parameters
- **autoCommit** (bool) - Required - Whether to enable or disable auto-commit mode.

### Behavior
- Calling `setAutoCommit(false)` disables auto-commit and starts a new transaction if none is active.
- Calling `setAutoCommit(true)` enables auto-commit and commits the currently active transaction.
- Calling `setAutoCommit(false)` when auto-commit is already disabled is a no-op.

### Example
```php
// disable auto-commit, commits currently active transaction
$conn->setAutoCommit(false); // also causes a new transaction to be started

// no-op as auto-commit is already disabled
$conn->setAutoCommit(false);

// enable auto-commit again, commits currently active transaction
$conn->setAutoCommit(true); // does not start a new transaction automatically
```
```

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

### Create schema and generate create/drop SQL

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/schema-representation.rst

Builds a schema with two tables ('user' and 'post'), a unique index, a foreign key with ON UPDATE CASCADE, and a sequence, then generates create and drop SQL for a platform. Uses the editor API from Doctrine DBAL Schema classes and requires the platform instance to generate queries.

```php
<?php

use Doctrine\DBAL\Schema\Column;
use Doctrine\DBAL\Schema\ForeignKeyConstraint;
use Doctrine\DBAL\Schema\ForeignKeyConstraint\ReferentialAction;
use Doctrine\DBAL\Schema\Index;
use Doctrine\DBAL\Schema\Index\IndexType;
use Doctrine\DBAL\Schema\PrimaryKeyConstraint;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\DBAL\Schema\Sequence;
use Doctrine\DBAL\Schema\Table;

$user = Table::editor()
    ->setUnquotedName('user')
    ->addColumn(
        Column::editor()
            ->setUnquotedName('id')
            ->setTypeName('integer')
            ->setUnsigned(true)
            ->create()
    )
    ->addColumn(
        Column::editor()
            ->setUnquotedName('username')
            ->setTypeName('string')
            ->setLength(32)
            ->create()
    )
    ->addPrimaryKeyConstraint(
        PrimaryKeyConstraint::editor()
            ->setUnquotedColumnNames('id')
            ->create()
    )
    ->addIndex(
        Index::editor()
            ->setUnquotedName('idx_username')
            ->setUnquotedColumnNames('username')
            ->setType(IndexType::UNIQUE)
            ->create()
    )
    ->setComment('User table')
    ->create();

$post = Table::editor()
    ->setUnquotedName('post')
    ->addColumn(
        Column::editor()
            ->setUnquotedName('id')
            ->setTypeName('integer')
            ->create()
    )
    ->addColumn(
        Column::editor()
            ->setUnquotedName('user_id')
            ->setTypeName('integer')
            ->create()
    )
    ->addForeignKeyConstraint(
        ForeignKeyConstraint::editor()
            ->setUnquotedName('fk_user_id')
            ->setUnquotedReferencingColumnNames('user_id')
            ->setUnquotedReferencedTableName('user')
            ->setUnquotedReferencedColumnNames('id')
            ->setOnUpdateAction(ReferentialAction::CASCADE)
            ->create()
    )
    ->create();

$schema = Schema::editor()
    ->setTables($user, $post)
    ->addSequence(
        Sequence::editor()
            ->setUnquotedName('my_table_seq')
            ->create()
    )
    ->create();

$createSQL = $schema->toSql($myPlatform);     // get queries to create this schema
$dropSQL   = $schema->toDropSql($myPlatform); // get queries to drop this schema
```

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

### Compare Schemas and Generate SQL

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/schema-manager.rst

Compares two schema instances using createComparator() and compareSchemas(), then generates SQL statements to apply the differences.

```php
<?php
$statements = $schemaManager->createComparator()
    ->compareSchemas($fromSchema, $toSchema)
    ->toSql($conn->getDatabasePlatform());
```

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

### Before: Using deprecated Comparator::diffTable()

Source: https://github.com/doctrine/dbal/blob/4.5.x/UPGRADE.md

Shows the old approach with mandatory false check. Use this to understand the deprecated pattern before migrating.

```php
$diff = $comparator->diffTable($oldTable, $newTable);

// mandatory check
if ($diff !== false) {
    // we have a diff
}
```

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

### Binding Parameters to Placeholders

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/query-builder.rst

Shows how to bind values to named or positional placeholders using createNamedParameter() and createPositionalParameter().

```APIDOC
## Binding Parameters to Placeholders

### Description
Bind values to placeholders without knowing the exact placeholder names. Use `createNamedParameter()` for named placeholders and `createPositionalParameter()` for positional placeholders.

### Methods
- `createNamedParameter($value)` - Returns a named placeholder (e.g., `:dcValue1`).
- `createPositionalParameter($value)` - Returns a positional placeholder (e.g., `?`).

### Example (Named Parameter)
```php
$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->where('email = ' . $queryBuilder->createNamedParameter($userInputEmail));
// SELECT id, name FROM users WHERE email = :dcValue1
```

### Example (Positional Parameter)
```php
$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->where('email = ' . $queryBuilder->createPositionalParameter($userInputEmail));
// SELECT id, name FROM users WHERE email = ?
```
```

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

### executeStatement()

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Executes a prepared statement with the given SQL and parameters and returns the affected rows count. The example shows updating a user's username and echoing the count.

```APIDOC
## executeStatement()

### Description
Executes a prepared statement with the given SQL and parameters and returns the affected rows count.

### Method
executeStatement

### Parameters
- **sql** (string) - Required - The SQL statement to execute.
- **params** (array) - Optional - The parameters to bind to the statement.
- **types** (array) - Optional - The types of the parameters.

### Return Value
Returns the number of affected rows (integer).

### Example
```php
<?php
$count = $conn->executeStatement('UPDATE user SET username = ? WHERE id = ?', ['jwage', 1]);
echo $count; // 1
```
```

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

### Disabling auto-commit mode with setAutoCommit(false)

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/transactions.rst

Shows how to disable auto-commit so that a new transaction is started on connect() and after each commit() or rollBack(). The connection remains transactional after the try-catch block.

```php
<?php
// define connection parameters $params and initialize driver $driver

$conn = new \Doctrine\DBAL\Connection($params, $driver);

$conn->setAutoCommit(false); // disables auto-commit
$conn->connect(); // connects and immediately starts a new transaction

try {
    // do stuff
    $conn->commit(); // commits transaction and immediately starts a new one
} catch (\Exception $e) {
    $conn->rollBack(); // rolls back transaction and immediately starts a new one
}

// still transactional
```

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

### Union Queries

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/query-builder.rst

Demonstrates how to combine multiple SELECT queries using the union() and addUnion() methods, with support for DISTINCT and ALL union types.

```APIDOC
## Union Queries

### Description
Combine multiple SELECT queries into a single result set using the `union()` and `addUnion()` methods. The `UnionType::DISTINCT` and `UnionType::ALL` constants control duplicate handling.

### Methods
- `union(QueryBuilder $subQueryBuilder)` - Adds a UNION DISTINCT subquery.
- `addUnion(QueryBuilder $subQueryBuilder, UnionType $type)` - Adds a UNION subquery with specified type.

### Example (DISTINCT)
```php
$subQueryBuilder1
    ->select('id AS field')
    ->from('a_table');
$subQueryBuilder2
    ->select('id AS field')
    ->from('a_table');
$queryBuilder
    ->union($subQueryBuilder1)
    ->addUnion($subQueryBuilder2, UnionType::DISTINCT);
```

### Example (ALL with ordering and limit)
```php
$subQueryBuilder1
    ->select('id AS field')
    ->from('a_table');
$subQueryBuilder2
    ->select('id AS field')
    ->from('a_table');
$queryBuilder
    ->union($subQueryBuilder1)
    ->addUnion($subQueryBuilder2, UnionType::ALL)
    ->orderBy('field', 'DESC')
    ->setMaxResults(100);
```
```

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

### executeQuery()

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Creates a prepared statement for the given SQL and passes the parameters to the executeQuery method, then returning the result set.

```APIDOC
## executeQuery()

### Description
Creates a prepared statement for the given SQL and passes the parameters to the executeQuery method, then returning the result set.

### Method
PHP method

### Signature
`$conn->executeQuery($sql, $params, $types)`

### Parameters
- **$sql** (string) - Required - The SQL query to execute.
- **$params** (array) - Optional - The parameters to bind to the query.
- **$types** (array) - Optional - The PDO or Doctrine Type constants for type conversions.

### Request Example
```php
<?php
$resultSet = $conn->executeQuery('SELECT * FROM user WHERE username = ?', ['jwage']);
$user = $resultSet->fetchAssociative();
```

### Response
#### Success Response
Returns a result set object that can be used to fetch data.

#### Response Example
```php
/*
array(
  0 => 'jwage',
  1 => 'j.wage@example.com'
)
*/
```
```

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

### DQL String Concatenation Vulnerability

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/security.rst

An example of insecure DQL query construction using direct concatenation, which remains vulnerable to injection despite DQL's abstraction layer.

```php
<?php
// DQL is not safe against arbitrary user-input as well:
$dql = "SELECT u FROM User u WHERE u.username = '" . $_GET['username'] . "'"
```

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

### Provide QueryCacheProfile to executeQuery and executeCacheQuery

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/caching.rst

Shows how to pass a QueryCacheProfile to both executeQuery() (optional) and executeCacheQuery() (required) to cache the result set. The profile uses a lifetime of 0 and a cache key 'some key'.

```php
<?php
$stmt = $conn->executeQuery($query, $params, $types, new QueryCacheProfile(0, "some key"));
$stmt = $conn->executeCacheQuery($query, $params, $types, new QueryCacheProfile(0, "some key"));
```

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

### Get namespace qualifier using getObjectName()->getQualifier()

Source: https://github.com/doctrine/dbal/blob/4.5.x/UPGRADE.md

Replacement for deprecated AbstractAsset namespace methods. Returns null if no namespace, otherwise an Identifier with the namespace name and quoting info.

```php
$qualifier = $table->getObjectName()->getQualifier();
```

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

### After: Using getNativeConnection() to register custom functions

Source: https://github.com/doctrine/dbal/blob/4.5.x/UPGRADE.md

Shows the recommended way to register custom functions by accessing the native PDO connection via getNativeConnection() and calling sqliteCreateFunction().

```php
$connection = DriverManager::getConnection([
    'driver' => 'pdo_sqlite',
    'path' => '/path/to/file.db',
]);

$connection->getNativeConnection()
    ->sqliteCreateFunction('my_function', [SomeClass::class, 'someMethod'], 2);
```

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

### After: Using DsnParser to parse database URL

Source: https://github.com/doctrine/dbal/blob/4.5.x/UPGRADE.md

Shows the new way of configuring a database connection using the DsnParser class, which parses the URL into connection parameters. This replaces the removed 'url' parameter.

```php
$dsnParser  = new DsnParser(['mysql' => 'pdo_mysql']);
$connection = DriverManager::getConnection(
    $dsnParser->parse('mysql://my-user:t0ps3cr3t@my-host/my-database')
);
```

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

### Basic query with executeQuery()

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/data-retrieval-and-manipulation.rst

Executes a simple SQL query without parameters using the connection's executeQuery() method. This approach has drawbacks: no dynamic parameters, tedious quoting, and repeated query optimization.

```php
<?php
use Doctrine\DBAL\DriverManager;

$conn = DriverManager::getConnection($params, $config);

$sql = "SELECT * FROM articles";
$stmt = $conn->executeQuery($sql); // Simple, but has several drawbacks
```

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

### SQLite connection URL with relative path

Source: https://github.com/doctrine/dbal/blob/4.5.x/docs/en/reference/configuration.rst

Shows a URL for connecting to an SQLite database file in the current working directory. The authority portion is omitted.

```text
pdo-sqlite:///somedb.sqlite
```

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

### Configure schema manager factory

Source: https://github.com/doctrine/dbal/blob/4.5.x/UPGRADE.md

Opt in to the new schema manager creation behavior by setting the schema manager factory. Ensure custom platform implementations implement createSchemaManager() to avoid connection failures.

```php
$configuration = new Configuration();
$configuration->setSchemaManagerFactory(new DefaultSchemaManagerFactory());

$connection = DriverManager::getConnection(
    [/* your parameters */],
    $configuration,
);
```
Tokens:
Raw