I've mostly taken database abstraction for granted since I started at Zend. We had a decent abstraction layer in ZF1, and improved it for ZF2. There were a lot quirks to it — you really had to dive in and look at the various SQL abstraction classes to understand how to do more complex stuff — but it worked, and was always right there and
available in the projects I worked on.
In the last couple of years, though, we came to the realization in the Laminas Project that we didn't really have anybody with the expertise or time to maintain it. We've marked it security-only twice now, and while we've managed to keep it updated to each new PHP version, it's becoming harder and harder, and whenever there's a CI issue,
it's anybody's guess as to whether or not we'll be able to get it resolved.
My alternatives have been straight PDO, or Doctrine DBAL, with the latter being my preference.
Doctrine what?
When most folks who use PHP hear "Doctrine", they immediately think "ORM"; it's how most folks use it, and what it's best known for.
Underlying the ORM is its database abstraction layer (hence "DBAL"). This library exposes an API that will work across any database it supports; this is essentially what zend-db, and later laminas-db, were doing as well. What most folks don't realize is that you can use the DBAL by itself, without the ORM.
Why no ORM?
ORMs are fine. Really. But they add an additional layer of complexity to understanding what you are actually doing. Additionally, if you want to do something that doesn't quite fit how the ORM works, you'll need to drop down to the DBAL anyways. So my take has always been: why not just use the DBAL from the beginning?
So, how does Matthew write code that interacts with the database?
I start by writing value objects that represent discrete aspects of the application. Most of my work will be in consuming or creating these. From there, I write a repository class that I use for purposes of persisting and retrieving them. I can usually extract an
interface from this, which aids in my testing, or if I decide I need a different approach to persistence later.
I push the work of mapping the data from the database to these objects, and vice versa, either in the repository, or in the value objects themselves (often via a named constructor). Using these approaches creates lean code that can be easily tested, and for which there's
no real need to understand the underlying system; it's all right there in what I've written for the application.
Some gripes about the documentation, and some tips
The Doctrine DBAL docs are a bit sparse, particularly when it comes to its SQL abstraction. And there's no "getting started" or "basic
usage" guide. In fact, it's not until the third page within the docs that you get any code examples; thankfully, at that point they give you information on how to get a database connection:
use Doctrine\DBAL\DriverManager;
$connectionParams = [
'dbname' => 'mydb',
'user' => 'user',
'password' => 'secret',
'host' => 'localhost',
'driver' => 'pdo_mysql',
];
$conn = DriverManager::getConnection($connectionParams);
They also provide a number of other approaches, including using a DSN (an acronym they never explain, but based on using PDO, likely means "data source name").
Once you have a connection, what do you do? Well the DBAL connection allows you to prepare and execute queries, including via the use of prepared statements. It provides a variety of methods for fetching individual or multiple rows, with a variety of options for how the data is returned (indexed arrays, associative arrays, individual
columns, individual values, etc.). These retrieval methods are mirrored in the result instances returned when executing prepared statements as well.
And that brings me to the SQL abstraction.
First, it's really, really good. It's minimal, but it covers just about anything you need to do. If you need to write something complex, you probably can; the beauty is that if you can't, you can always fall back to a SQL query, and using the connection's API for binding values.
But the documentation could be better.
It felt like it was written by a database admin who has forgotten more than most people ever learn about databases, and never considered that
Truncated by Planet PHP, read more at the original (another 6590 bytes)