Php – Should database queries be abstracted out of the page itself

abstractiondatabasePHPsql

When writing page generation in PHP, I often find myself writing a set of files littered with database queries. For example, I might have a query to fetch some data about a post directly from the database to display on a page, like this:

$statement = $db->prepare('SELECT * FROM posts WHERE id=:id');
$statement->bindValue(':id', $id, PDO::PARAM_INT);
$statement->execute();
$post = $statement->fetch(PDO::FETCH_ASSOC);
$content = $post['content']
// do something with the content

These quick, one-off queries are usually small, but I sometimes end up with large portions of database interaction code which begins to look pretty messy.

In some cases, I've solved this problem by creating a simple library of functions to handle my post-related db queries, shortening that block of code to a simple:

$content = post_get_content($id);

And that's great. Or at least it is until I need to do something else. Maybe I need to get the five most recent posts to display in a list. Well, I could always add another function:

$recent_posts = post_get_recent(5);
foreach ($recent_posts as $post) { ... }

But that ends up using a SELECT * query, which I usually really don't need anyway, but is often too complicated to reasonably abstract. I eventually end up with either a massive library of database interaction functions for every single use case, or a series of messy queries inside every page's code. And even once I've built these libraries, I'll find myself needing to do one tiny join that I hadn't used before, and I suddenly need to write another highly-specialized function to do the job.

Sure, I could use the functions for general use cases and queries for specific interactions, but as soon as I start writing raw queries I begin to slip back into direct access for everything. Either that, or I'll get lazy and will start doing things in PHP loops that should really be done directly in the MySQL queries, anyway.

I'd like to ask those more experienced with writing internet applications: is the maintainability boost worth the extra lines of code and possible inefficiencies that the abstractions may introduce? Or is simply using direct query strings an acceptable method for handling database interactions?

Best Answer

When you have too many specialized query functions you could try breaking them into composable bits. For instance

$posts = posts()->joinWithComments()->orderBy("post.post_date")->first(5);

There is also a hierarchy of abstraction levels you might find useful to keep in mind. You have

  1. mysql API
  2. your mysql functions, such as select("select * from posts where foo = bar"); or maybe more composable as select("posts")->where("foo = bar")->first(5)
  3. functions that are specific to your application domain, for instance posts()->joinWithComments()
  4. functions that are specific to a particular page, such as commentsToBeReviewed($currentUser)

It pays a lot in terms of ease of maintenance to respect this order of abstractions. The pages scripts should use only level 4 functions, level 4 functions should be written in terms of level 3 functions, and so on. It's true that this takes a bit more time upfront but it will help keep your maintenance costs constant over time (as opposed to "oh my gosh they want another change!!!")

Related Topic