Database abstraction class design using PHP PDO

Question

I am in the process of designing a web application (really, it's a hobby, and I'm trying to teach myself design, and what better way is there than doing it :). Anyway, I was thinking about how I would deal with my database. I am comfortable with PDO, and I was thinking of leveraging PDO in my abstraction class. I am thinking of making a singleton, so that there's only one database connection. This singleton would create a PDO connection.

After that, I fail to see why I would need to do too much else. I can then just use the database handler to call PDO functions. I may want some helper functions, but when it gets down to it, I would just use PDO for the actual SQL queries.

Is there something wrong with this approach? It seems overly simple compared to the abstraction classes I've used.

Regolith

Database abstraction is a ridiculous myth. ORM is another story, no less evil but a real one at least. – Pescara

@Gordon, can you elaborate on why I wouldn't need the singleton? I've always heard that you would need the singleton to make sure that there aren't simultaneous writes and things like that. – Regolith

a DB Singleton wont solve any concurrency issues. If anything, it can make sure you have only one PDO instance for the request it was created in. But for that you do not need a Singleton. Just create a wrapper that lazy connects when needed in your bootstrap and set the instance to your DAL supertype. This way you dont limit yourself to only one PDO instance in case you will need a second one at some point. – Marty

Thanks Gordon - If you happen to put that in an answer, I'd gladly choose it. – Regolith

Answer 1

You don't need the Singleton.

A database Singleton won't solve any concurrency issues. If anything, it can make sure you have only one PDO instance for the request it was created in. And it provides global access, which many people consider a bad thing. In addition you have to make some extra effort when testing the Singleton.

Just create a wrapper that lazy connects and stores the instance when needed in your bootstrap and set the instance to your DAL supertype, for instance a TableDataGateway. Also, this way you don't limit yourself to only one PDO instance in case you will need a second one at some point.

Marty

Answer 2

Maybe it seems so simple to you, because PDO is essentially a database abstraction class. That means: the work is already done.

Conduplicate

True, that's why I pose this question. There are so many database abstraction classes and whizbangs out there, it just seemed like I was missing something... Glad to know that I'm not completely off my rocker. – Regolith

Answer 3

Yeah, this is a good start. PDO + singleton is an often-used and great combination. As I personally don't like all the typing involved than using singletons, I have written a very lightweight database class.

It introduces only two additional features over PDO: Access of (lazy) PDO instance using __callStatic (DB::query() instead of DB::instance()->query()) and two functions for easier quoting (DB::q('INSERT INTO table (name) VALUES (?s)', $_POST['insecure_name'])). Maybe you want to look at both, it's really handy ;)

Courtund

Answer 4

You may also be interested in the project php-pdo-wrapper-class. It's a light-weight database class that extends PDO, adding several methods - insert, update, delete, select (and a few others) - for simplifying common SQL statements. I've used this project in my development and would highly recommend.

Pase