How to manage SQL Statements in Data Layer

Question

Hi,

in a PHP project we already have separated business logic from database access. All database tasks are encapsulated in different database classes grouped by database and topic. Theses classes look very horrible, half the source are SQL strings, that get filled with params and so on. We thought of putting the SQL in "other" locations like resource files or something. What is considered best practise for this and do you know any supporting tools/libs for PHP?

Kind Regards

Stephan

Answer 1

Well, you could always use PDO for a consistent API across different databases and write portable SQL statements.

Another option would be to use a database abstraction layer such as Zend_DB.

Answer 2

You should use stored procedures wherever it is possible. That way you enhance performance, security and code maintenance. This should be your first approach.

If you still want to separate the SP queries from the DAL, why not store them in a database? It may seem odd to store SQL queries in the database for abstraction, since a query is needed to extract other queries. This is actually a quite common approach, where you can select queries matching a certain criteria and possibly (if necessary) to build up the queries dynamically.

Another approach may be to create Query-classes where queries are built up dynamically;

class FruitQuery {
    ...
    public function addTypeCriteria($type) {
        $this->internalSQLCriterias[]  = "fruit=:type";
        $this->internalSQLParameters[] = array(':type', $type);
    }
    ...
    public function create() {
        $this->internalSQLQuery = "SELECT ... FROM Fruits";

        if (sizeof($this->internalSQLCriterias) > 0) {
            $this->internalSQLQuery .= " WHERE ";
            $moreThanOne = '';

            foreach ($this->internalSQLCriterias as $criteria) {
                $this->internalSQLQuery .= $moreThanOne . $criteria;
                $moreThanOne = " AND ";
            }
        }
    }
    ...
    public function execute() {
        /* Bind the parameters to the internalSQLQuery, execute and return results (if any) */
    }
...

This class is absolutely not complete in any way, and you might want to rethink the structure of it - but you probably get the point I'm trying to make. :) Of course you have to filter the input to the Query-builder to avoid security breaches!

Answer 3

You shouldn't have almost any SQL in your database access layer as it should be merely abstract the communication with the database regardless of what actual SQL it's communicating.

In the now famous MVC pattern, your business logic is what typically contains the SQL which forms the Model layer.

Putting all these "religious" definitions aside, what you have now is moderately normal, to end up with piles of SQL. SQL has to exist, somewhere. Depending on your priorities and performance requirements here is what I would do (ordered by performance compromise):

1. If there is noticeable repetition in the SQL, I'd throw in a quick refactoring iteration to hide all the common SQL inside methods. The methods do not necessarily have to execute it, but just build it. It all depends on your application and in which way the SQL is complex. If you don't have an underlying layer which does the actual communication with the database, maybe part of the refactoring could be to add it.

2. I'd consider a Query builder. Which is a very good balance between performance and flexibility. You can only find a query builder as part of an ORM or Database Access layer (like Zend_Db and its sub components), Propel and/or Doctrine. So you can either port a query builder off one of those to your project without using the whole layer (which really shouldn't be hard as all of them are PDO-based). This shouldn't add any noticeable performance issues.

3. I'd consider the Doctrine ORM. This has considerable effect in performance though. You will end up with very maintainable code however.

Finally, I'd never consider putting the SQLs into Resources or something like that.

Answer 4

I don't know PHP, but from my experience with other languages I can tell you this much: data access layers are a prime target of architecture astronauts. There are so many "best practices" that none of them are really best. When designing a DAL, it's very easy to fall into the trap of over-abstracting. Just go as far as you need.

I almost always use stored procedures in order to avoid spaghetti code and simplify authorization in the database, not for performance reasons; performance gains from stored procs can be hard to pin down because of the complexities of when and how database engines prepare them. On the other hand, if I need to code a very flexible database operation (like on a search screen with many inputs), I will sometimes just put the SQL right in the code. Sometimes it's going to be an unreadable mess no matter where you put it. You have to do the work somewhere.

If you're not (unnecessarily) mixing SQL and procedural code, put the SQL wherever it makes the most sense for the scope and scale of your application. Sorry I can't answer your question about tools and libs for PHP, but I hope this is helpful.