Connecting to a Database

Doctrine needs one fact before any mapping matters: how to reach the database. In a Symfony application that fact is an environment variable called DATABASE_URL, written in .env and overridden per machine in .env.local.

The URL

The shape is scheme://user:password@host:port/dbname?options. The scheme chooses the driver: DoctrineBundle maps postgresql, mysql and sqlite onto pdo_pgsql, pdo_mysql and pdo_sqlite. SQLite points at a file instead of a host.

Add serverVersion. Without it DBAL has to connect early and ask the server which version it is, so that it can choose the right SQL dialect.

Where it is read

The DoctrineBundle recipe writes url: '%env(resolve:DATABASE_URL)%' into config/packages/doctrine.yaml. The resolve: prefix expands any %parameter% inside the value, which is what makes a SQLite URL containing %kernel.project_dir% work.

Prove it, then map

php bin/console doctrine:database:create creates the database named in the URL and prints Created database app for connection named default. Then run a query on purpose. The connection is lazy: nothing is opened until something asks for data, so a wrong host or password stays silent until your first page needs a row. In DoctrineBundle 3 the SQL runner is dbal:run-sql; the older name doctrine:query:sql existed up to DoctrineBundle 2 and is gone.

<?php
// src/Command/DatabasePingCommand.php

declare(strict_types=1);

namespace App\Command;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Exception as DbalException;
use Symfony\Component\Console\Attribute\AsCommand;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Output\OutputInterface;

#[AsCommand(name: 'app:database:ping', description: 'Opens the configured connection and runs one query')]
final class DatabasePingCommand
{
    public function __construct(private readonly Connection $connection)
    {
    }

    public function __invoke(OutputInterface $output): int
    {
        try {
            // The cheapest statement that still forces a real connection.
            $this->connection->fetchOne('SELECT 1');
            $name = $this->connection->getDatabase();
        } catch (DbalException $e) {
            // In DBAL 4, Doctrine\DBAL\Exception is an interface, so this one
            // catch covers every DBAL failure, connection errors included.
            $output->writeln('Connection failed: '.$e->getMessage());

            return Command::FAILURE;
        }

        $output->writeln(sprintf('Connected to "%s".', $name ?? 'unknown'));

        return Command::SUCCESS;
    }
}

// php bin/console app:database:ping
// Connected to "app".

# .env
# One line active; the others are the forms the recipe documents.
DATABASE_URL="postgresql://app:[email protected]:5432/app?serverVersion=16&charset=utf8"

# MySQL 8
# DATABASE_URL="mysql://app:[email protected]:3306/app?serverVersion=8.0.32&charset=utf8mb4"

# MariaDB: the version string has to say MariaDB, or the wrong dialect is used
# DATABASE_URL="mysql://app:[email protected]:3306/app?serverVersion=10.11.2-MariaDB&charset=utf8mb4"

# SQLite: a file, one per environment
# DATABASE_URL="sqlite:///%kernel.project_dir%/var/data_%kernel.environment%.db"

# config/packages/doctrine.yaml
doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'

# The same connection, written parameter by parameter.
# Both forms are accepted and produce one identical connection.
# doctrine:
#     dbal:
#         driver: 'pdo_pgsql'
#         host: '127.0.0.1'
#         port: 5432
#         dbname: 'app'
#         user: 'app'
#         password: '!ChangeMe!'
#         server_version: '16'
#         charset: 'utf8'

$ php bin/console doctrine:database:create
Created database app for connection named default

# DoctrineBundle 3 registers the DBAL SQL runner under this name
$ php bin/console dbal:run-sql 'SELECT 1 AS ok'
# prints a one-row result table: column ok, value 1

$ php bin/console app:database:ping
Connected to "app".