Jan Mikoláš
Glossary
Doctrine DBAL
Doctrine DBAL gives a PHP application a direct but structured path to a relational database. It is suitable for explicit SQL, batch processing, and transactional operations where object mapping is not the most precise tool.
Short definition
A database layer between the application and a specific SQL driver.
Doctrine DBAL, short for Database Abstraction Layer, provides database connections, parameterised queries, value conversions, result handling, transactions, and QueryBuilder. The application does not have to handle PDO details or some database-platform differences manually for every driver, while still remaining close to SQL and the real table model.
DBAL belongs to the Doctrine ecosystem and is also used by Doctrine ORM, but it can be used independently. An ORM maps objects and associations to tables and tracks their changes; DBAL instead lets the application specify precisely which SELECT, INSERT, or UPDATE the database should execute. A database entity is therefore not automatically a domain object, and using SQL through DBAL is not an architectural failure.
What it is used for
Queries whose shape and cost should remain visible
DBAL is practical where an application needs to work with relational data directly and safely without building an entire object map.
- reporting and aggregate queries over orders, inventory, or payments
- batch imports and exports where streaming data and a small memory footprint matter
- atomic changes to inventory, states, or idempotent integration records
- specialised SQL functions and optimisations for a specific database platform
- repositories and read models where loading a complete graph of ORM entities would be unnecessary
Practical example
A summary of paid orders without assembling entities
An administration interface needs to display the number of paid orders for a particular store quickly. This read model does not require loading orders, items, and customers as entities. One parameterised query returns a scalar value, while the Connection dependency is passed through the constructor so the class can be substituted in a test or verified through an integration test.
The store code and order status are passed as parameters rather than concatenated into the SQL string. If the column name or ordering were selected dynamically, a parameter would not be enough: the identifier must come from a fixed list of permitted options.
<?php
use Doctrine\DBAL\Connection;
final class OrderStatistics
{
public function __construct(private Connection $connection)
{
}
public function paidCountForStore(string $storeCode): int
{
return (int) $this->connection->fetchOne(
'SELECT COUNT(*) FROM orders WHERE store_code = :store AND status = :status',
['store' => $storeCode, 'status' => 'paid'],
);
}
}How it works
From a connection to a result or committed change
DBAL separates building a query, passing its values, executing it, and defining any transaction boundary.
- Connection The application obtains a Connection configured with the driver, database platform, and any mapped types.
- Query SQL can be written directly or assembled with QueryBuilder. QueryBuilder builds syntax but does not replace knowledge of SQL or the data model.
- Parameters and types Values from a user or integration are passed with placeholders and parameters; DBAL prepares them for the driver and converts their type when needed.
- Execution executeQuery and fetch methods return data from reads. executeStatement performs a write and returns the number of affected rows.
- Transaction Related changes are enclosed in a transaction. On an error, the changes must be rolled back correctly or the exception allowed to propagate to the coordinating layer.
Main components and concepts
Explicit database work does not mean manually concatenating strings.
DBAL provides small building blocks. Using them correctly depends on whether the task is a read, write, concurrent operation, or schema change.
Connection and platform
Connection provides access to the driver, transactions, and database platform. The abstraction helps with common operations, but does not erase database-specific SQL or performance characteristics.
Parameterised queries
Placeholders separate SQL structure from values. They protect values from SQL injection; table names, column names, and sort directions cannot be parameterised and must be controlled with an allowlist.
QueryBuilder
Makes it easier to assemble conditional query sections and parameters. It does not make user input safe by itself; values must still be bound through parameters.
Results and types
fetchOne, fetchAssociative, and iterable results support different read shapes. Type conversion is useful for dates or identifiers, but returned data still needs to be handled deliberately.
Transactions
DBAL offers explicit begin, commit, and rollback as well as callback-based transactional work. A transaction protects a local database unit, not communication with a payment gateway or another API.
Benefits and limitations
Direct SQL provides control but requires responsibility.
Benefits
- visible SQL and precise control over loaded data
- parameters, types, and transactions without manual work for every driver
- suitable for aggregations, batches, and performance-sensitive read models
- the option to use an ORM and DBAL side by side according to the operation
Limitations and mistakes
- QueryBuilder does not automatically prevent unvalidated values from being inserted into SQL
- the abstraction does not guarantee full portability of database-specific SQL
- a long transaction or N+1 query remains a problem even with DBAL
- DBAL does not replace constraints, indexes, monitoring, or understanding the query plan
When it makes sense
Use DBAL where SQL is an important part of the solution.
DBAL is a good choice for order imports, reporting, batch jobs, targeted inventory updates, or a specialised PostgreSQL query. In these cases, it is useful to keep the parameters, transaction, and returned data visible in one place instead of forcing a hard-to-read compromise out of a general ORM model.
An ORM may be clearer for straightforward work with a rich object. Conversely, direct SQL through DBAL should not become a universal replacement for domain rules in the application layer. The criteria are clarity, measurable operational value, and the ability to test the change safely—not a preference for a particular style.
What to keep in mind
Review SQL, transactions, and the schema as one whole.
A well-written DBAL query is small, parameterised, measurable, and has a clear responsibility.
- bind every variable value as a parameter and select identifiers only from an allowlist
- use the appropriate method for reads and writes and explicitly name the expected result shape
- keep transactions short and do not call a slow external API from inside them
- verify indexes, constraints, and plans for important queries against realistic data
- integration-test SQL against the actual supported database engine
Common questions
DBAL alongside an ORM and database
Is Doctrine DBAL the same as Doctrine ORM?
No. DBAL provides connections, SQL, parameters, and transactions. Doctrine ORM uses DBAL underneath and adds entity mapping, associations, and change tracking.
Is QueryBuilder automatically safe from SQL injection?
No. Values are protected only when placeholders and parameters are used. QueryBuilder must not receive unvalidated input as an SQL fragment, column name, or ORDER BY direction.
When should I use DBAL instead of an ORM?
Typically for targeted aggregations, batch imports, explicit updates, or a query where SQL matters to performance and readability. There is no rule that one layer must win everywhere.
Does DBAL create a transaction by itself?
Not automatically for an arbitrary set of operations. The boundary must be chosen deliberately with transaction methods or a callback, with error handling and possible conflict retries.
How I work with databases in practice
I choose the database layer according to the operation, not habit.
In e-commerce and integrations, I design the relational model, explicit queries, transactions, and the operational impact of data changes.