On SQL in PHP

Wednesday May 15th, 2013

There is no right or wrong way[1], but no matter what there is no *pretty* way to do SQL inside of a PHP application. I have been having a personal debate with myself all week about how to make SQL statements nicer in an application without going to a huge DBAL package like Doctrine.

[1] – There are actually plenty of wrong ways to write SQL. For example, “SELECT * FROM table WHERE id={$_GET['id']};”

Separation of Church and SQL.

My second idea (after formatting SQL inline of PHP) was to completely remove the SQL from the app all together and load it separately (with application caching) from separate query files. These files would literally just contain the SQL that would be dumped straight into database object.

Pros:

  • No SQL in PHP. Fairly nice to read.
  • People have told me they’ve done this before.

Cons:

  • Now I have to open a 2nd file to update this application. The first being the programming which tells me which SQL file is used, second being the actual SQL file.
  • My query parameters are disconnected from the code so even if I remembered which file had the query I needed, I might not remember what all the arguments in the query are named without opening the query file.
  • It is yet more disk IO first time the query is used.
  • You can’t procedurally generate that shit.

We Can Build Anything.

My third idea then was for simpler queries to provide a query building object which would generate the SQL as soon as we tried to read it out.

Pros:

  • All the methods can take multiple arguments [e.g. From('table1','table2')] and because the SQL is not generated until we try and read it, we can actually do stuff later and then From(‘table3′) and the end query will be FROM table1,table2,table3 allowing us to procedurally generate queries.
  • It looks nicer than trying to format multi line SQL strings in the code.

Cons:

  • It is basically no easier or harder to read than the actual SQL.
  • Saves no keystrokes in the end.
  • It is more work (memory use, cpu use, (object creation, method lookups, jumps, things that don’t really matter 95% of the time)) to do such a simple thing.

In the end I really have not decided what to do about it. I am interested in opinions and comments.

Comment by Jake Smith on Thursday May 16th at 10:24am
#1 What you are doing seems no different/better than using stored procedures on the DB server. Only plus side is it is less of a pain to update your queries by having them in your repo. * I am NOT advocating using stored procedures *

#2 I think this is the best idea, but there are already so many libraries for Query Builders. I think you're re-inventing the wheel if you build it yourself. If you want to do it more so for research and enjoyment, then go for it.
    Comment by bob on Thursday May 16th at 10:51am
    @Jake Smith:

    The only reason I would continue my query builder (I actually have a working class from that code example above) would be to keep dependencies down. ATM like Aura I have the whole "0 dependency" thing going on. Not so much the ability to snap the system apart though.
Comment by Jeff Madsen on Thursday May 16th at 10:42am
Agree with Jake completely - there are so many good libraries for this, don't understand what else you would really want. Look at http://auraphp.com/, Laravel's Eloquent, or dozens of other projects of different shapes & sizes
    Comment by bob on Thursday May 16th at 10:49am
    @Jeff Madsen:

    Aura is on my todo list to checkout today for sure, Daniel had told me about it last night too.
Comment by xaxo on Thursday May 16th at 10:51am
I like the background picture. Is that from some game or your houseplace? Use framework with performative build-in RAR layer, e.g. Yii or 3rd party ORM libs like propel/doctrine/adodb.
    Comment by bob on Thursday May 16th at 10:53am
    @xaxo:

    It is a photo I took off my balcony here in downtown Dallas. ;)
Comment by Rasmus Schultz on Thursday May 16th at 10:59am
In my previous job, I was happy for years, simply encapsulating ugly query construction in factory-classes - this approach lets you manually optimize the SQL queries to the extreme, and while these factory-classes are definitely ugly on the inside, they can be made superbly elegant on the outside, and this leads to more domain-specific language in controllers and service classes, as compared to the typical (lazy) use of object/relational-mappers and accompanying query-builders.

What I mean by that is, once you're using an object/relational-mapper that lets you do things like User::query()->with('profile')->select() and encapsulates pre-defined relations etc., it quickly becomes natural and automatic practice to simply construct queries using an Active Record API, directly in your controllers and services, instead of encapsulating this stuff in a real data-access layer.

Because your generic AR/ODM library does the encapsulation for you, right? Wrong. It encapsulates general facilities for query construction and transactions etc. - but it does not generally expose any of this to your controllers/services using APIs with domain-specific language. You still see this code littered with things like join() and where() method-calls that take SQL snippets as arguments.

It's a matter of personal preference of course. But I'm a minimalist, and having used both approaches for many years, I have to say, I prefer the control, flexibility and performance of simple factory classes, and I was perfectly happy encapsulating the ugly parts in those.

That said, of course you can build data-access APIs that use domain-specific language, with an AR/ODM library inside those - there just isn't much of a tendency to do so, and people who subscribe to AR as a "lifestyle" tend to think a data-access layer on top of AR is superfluous.

I tend to think that domain language is more important than almost anything else - join statements and where clauses most likely have no direct relevance to your business domain, and I prefer to see it neatly packed away in domain-specific APIs.

It's all a matter of preference though :-)
    Comment by bob on Thursday May 16th at 11:38am
    @Rasmus Schultz:

    Aye preference is what it always seems to boil down to. I shy away from some of the larger abstraction packages because I feel like some of the database interactions may be more complex than they might be able to optimize for. Nature of the beast and all that. I also like you prefer having tight control over things behind-the-scenes.
Comment by mailo on Friday May 17th at 3:41am
Since in MVC apps the model is responsible for example, for the database communication, you can store your SQL in there (cons: model won't be database agnostic).

Then you just do something similar to your first idea: Model::getVehicleListByOwner().

Even if you don't use any framework, storing SQL in this kind of method should improve code maintainability.

Tip: you can use query builder inside that method, so as a result your model would be database agnostic.

And yeah, _do not_ reinvent the wheel :)
Regards!
Comments
9 Responses to “On SQL in PHP”
Write a Comment

Twitter

 
Coderwall