Doctrine DBAL (Database Abstraction Layer) is a PHP library that provides an abstraction layer for database access. It is part of the Doctrine project (a popular ORM for PHP), but it can be used independently of the ORM.

Purpose and Benefits of Doctrine DBAL:

Doctrine DBAL offers a unified API to interact with different databases (such as MySQL, PostgreSQL, SQLite, etc.) without writing raw SQL specific to each database system.

Key Features of Doctrine DBAL:

• Connection Management

• • Easily configure and manage connections to various database systems.

  • Supports connection pooling, transactions, and more.

• SQL Query Builder

• • Build SQL queries programmatically using an object-oriented API:

$qb = $conn->createQueryBuilder();
$qb->select('u.id', 'u.name')
   ->from('users', 'u')
   ->where('u.age > :age')
   ->setParameter('age', 18);
$stmt = $qb->executeQuery();

• Database Independence

  • The same code works with different database systems (e.g., MySQL, PostgreSQL) with minimal changes.

• Schema Management

  • Tools to create, update, and compare database schemas.

  • Useful for migrations and automation.

• Data Type Conversion

  • Automatically converts data between PHP types and database-native types.

use Doctrine\DBAL\DriverManager;

$conn = DriverManager::getConnection([
    'dbname' => 'test',
    'user' => 'root',
    'password' => '',
    'host' => 'localhost',
    'driver' => 'pdo_mysql',
]);

$result = $conn->fetchAllAssociative('SELECT * FROM users');

When to Use DBAL Instead of ORM:

You might choose DBAL without ORM if:

• You want full control over your SQL.

• Your project doesn't need complex object-relational mapping.

• You're working with a legacy database or custom queries.

Summary:

Doctrine DBAL is a powerful tool for clean, portable, and secure database access in PHP. It sits between raw PDO usage and a full-featured ORM like Doctrine ORM, making it ideal for developers who want abstraction and flexibility without the overhead of ORM logic.