Doctrine DBAL for PHP: Complete Guide with Examples

1. What It Is and Why You Need It

Doctrine DBAL (Database Abstraction Layer) is a powerful library for PHP that provides a unified interface for working with various relational databases. It is part of the Doctrine ecosystem but can be used as a standalone component. The main task of DBAL is to abstract the developer from the specifics of a particular DBMS (MySQL, PostgreSQL, SQLite, Oracle, SQL Server, etc.), allowing you to write portable code.

Why do you need Doctrine DBAL? First, it significantly simplifies performing typical operations: connecting to the database, executing queries, working with transactions. Second, it ensures security through prepared statements and automatic parameter escaping, which protects against SQL injections. Third, DBAL provides a convenient Query Builder for building complex queries without writing raw SQL. Unlike a full-fledged ORM (Object-Relational Mapper), DBAL does not require mapping objects and entities, making it an ideal choice for projects where you need direct control over SQL but with additional conveniences.

The library is actively maintained, has excellent documentation, and is widely used in industrial development. It is the foundation of Doctrine ORM and many popular PHP frameworks (Symfony, Laravel — through integrations).

2. Installation

Installing Doctrine DBAL is done via Composer — the standard package manager for PHP. Run the following in the terminal at the root of your project:

composer require doctrine/dbal

After installation, the vendor/ folder and composer.json file with the added dependency will appear in the project. To work with a specific DBMS, you may need to install the corresponding PDO driver (e.g., ext-pdo_mysql for MySQL).

3. Quick Start — Minimal Working Example

Create a file test.php and include the Composer autoloader. Example of connecting to SQLite (built-in database, no server installation required):

<?php\nrequire_once 'vendor/autoload.php';\n\nuse Doctrine\\DBAL\\DriverManager;\n\n// Connection parameters\n$connectionParams = [\n    'dbname' => 'test.db',\n    'driver' => 'pdo_sqlite',\n];\n\n// Create connection\n$conn = DriverManager::getConnection($connectionParams);\n\n// Create table\n$conn->executeStatement('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT)');\n\n// Insert data\n$conn->insert('users', ['name' => 'John Doe', 'email' => 'john@example.com']);\n\n// Select data\n$users = $conn->fetchAllAssociative('SELECT * FROM users');\nprint_r($users);

This code creates an SQLite database, a users table, inserts one record, and outputs all rows.

4. Main Methods/Functions/Classes

4.1. DriverManager::getConnection()

Signature: public static function getConnection(array $params, ?Configuration $config = null, ?EventManager $eventManager = null): Connection

What it does: Creates and returns a database connection object based on the provided parameters. This is the entry point to DBAL.

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

4.2. Connection::executeStatement()

Signature: public function executeStatement(string $sql, array $params = [], array $types = []): int

What it does: Executes an SQL query that does not return a result set (INSERT, UPDATE, DELETE, DDL). Returns the number