[PHP PDO] Database abstraction class design question

Question

Hi SO,

I am in the process of designing a web app (really it's a hobby, I'm trying to teach myself design, and what better way is there than doing it :). Anyways, 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 DB 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 DB 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.

(Forgive me if this has been asked, I couldn't find it when searching)

Thanks SO!

Answer 1

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

Answer 2

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 DB 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 ;)

Answer 3

You dont need the Singleton.

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. 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 dont limit yourself to only one PDO instance in case you will need a second one at some point.