How to Use Doctrine DBAL

Note

This article is about the Doctrine DBAL. Typically, you'll work with the higher level Doctrine ORM layer, which uses the DBAL behind the scenes to actually communicate with the database. To read more about the Doctrine ORM, see "Databases and the Doctrine ORM".

The Doctrine Database Abstraction Layer (DBAL) is an abstraction layer that sits on top of PDO and offers an intuitive and flexible API for communicating with the most popular relational databases. The DBAL library allows you to write queries independently of your ORM models, e.g. for building reports or direct data manipulations.

Tip

Read the official Doctrine DBAL Documentation to learn all the details and capabilities of Doctrine's DBAL library.

First, install the Doctrine orm Symfony pack:

        1
        $ composer require symfony/orm-pack
    

Then configure the DATABASE_URL environment variable in .env:

        1
2
3
4
        # .env (or override DATABASE_URL in .env.local to avoid committing your changes)

# customize this line!
DATABASE_URL="mysql://db_user:db_password@127.0.0.1:3306/db_name?serverVersion=8.0.37"
    

Further things can be configured in config/packages/doctrine.yaml - see Doctrine Configuration Reference (DoctrineBundle). Remove the orm key in that file if you don't want to use the Doctrine ORM.

You can then access the Doctrine DBAL connection by autowiring the Connection object:

        1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
        // src/Controller/UserController.php
namespace App\Controller;

use Doctrine\DBAL\Connection;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;

class UserController extends AbstractController
{
    public function index(Connection $connection): Response
    {
        $users = $connection->fetchAllAssociative('SELECT * FROM users');

        // ...
    }
}
    

This will pass you the database_connection service.

Using Primary/Replica Connections (Read Replicas)

When your application uses a database cluster with read replicas, you can configure Doctrine to automatically route read queries to a replica and write queries to the primary database. This reduces the load on the primary database and improves performance for read-heavy applications.

First, define a DATABASE_REPLICA_URL environment variable in .env:

        1
2
        # .env
DATABASE_REPLICA_URL="mysql://replica_user:replica_password@replica-host:3306/db_name?serverVersion=8.0.37"
    

Then configure the replicas in your Doctrine configuration:

        1
2
3
4
5
6
7
8
        # config/packages/doctrine.yaml
when@prod:
    doctrine:
        dbal:
            url: '%env(resolve:DATABASE_URL)%'
            replicas:
                replica1:
                    url: '%env(resolve:DATABASE_REPLICA_URL)%'
    

You can add as many replicas as needed (e.g. replica2, replica3). When multiple replicas are configured, Doctrine randomly selects one when connecting to a replica and keeps using it for subsequent read operations on that connection.

With this configuration, Doctrine uses the PrimaryReadReplicaConnection wrapper class from Doctrine DBAL, which decides where to route each database operation:

Read operations (e.g. fetchAllAssociative(), executeQuery()) are sent to a replica;
Write operations (e.g. executeStatement()) and transactions are sent to the primary;
Once the primary has been used, all subsequent operations on that connection use the primary too, ensuring read-your-writes consistency.

Note

The routing is based on which DBAL method your code calls, not on SQL-level detection. If you execute a write query through a read method like executeQuery(), it will be sent to a replica. Always use the appropriate DBAL methods (executeStatement() for writes, executeQuery() for reads) to ensure correct routing.

Note

In long-running processes (e.g. messenger workers), the connection instance persists across multiple messages, so the "switch to primary" behavior applies for the lifetime of that connection instance, not just a single HTTP request.

Tip

Set the keep_replica option to true to keep using the replica for read queries even after a write operation. This is useful when eventual consistency is acceptable for subsequent reads:

        1
2
3
4
5
6
7
8
9
        # config/packages/doctrine.yaml
when@prod:
    doctrine:
        dbal:
            url: '%env(resolve:DATABASE_URL)%'
            keep_replica: true
            replicas:
                replica1:
                    url: '%env(resolve:DATABASE_REPLICA_URL)%'
    

Forcing the Primary Connection

In some cases, you may need to force the primary connection for a read query (e.g. right after a write to ensure data consistency). You can do so by calling the ensureConnectedToPrimary() method:

        1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
        // src/Controller/ProductController.php
namespace App\Controller;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Connections\PrimaryReadReplicaConnection;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;

class ProductController extends AbstractController
{
    public function index(Connection $connection): Response
    {
        if ($connection instanceof PrimaryReadReplicaConnection) {
            $connection->ensureConnectedToPrimary();
        }

        // the following query will be executed on the primary
        $result = $connection->fetchAllAssociative('SELECT * FROM product');

        // ...
    }
}
    

The instanceof check ensures the code works in all environments: in production where a replica is configured, $connection is an instance of PrimaryReadReplicaConnection; in development without replicas, it is a regular Connection instance.

Registering custom Mapping Types

You can register custom mapping types through Symfony's configuration. They will be added to all configured connections. For more information on custom mapping types, read Doctrine's Custom Mapping Types section of their documentation.

        1
2
3
4
5
6
        # config/packages/doctrine.yaml
doctrine:
    dbal:
        types:
            custom_first:  App\Type\CustomFirst
            custom_second: App\Type\CustomSecond
    

Registering custom Mapping Types in the SchemaTool

The SchemaTool is used to inspect the database to compare the schema. To achieve this task, it needs to know which mapping type needs to be used for each database type. Registering new ones can be done through the configuration.

Now, map the ENUM type (not supported by DBAL by default) to the string mapping type:

        1
2
3
4
5
        # config/packages/doctrine.yaml
doctrine:
    dbal:
        mapping_types:
            enum: string
    

This work, including the code samples, is licensed under a Creative Commons BY-SA 3.0 license.

TOC

Version