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.

Could not embed GitHub Gist 5587756: Bad credentials


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


  • 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.

Could not embed GitHub Gist 5587790: Bad credentials


  • 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.


  • 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

    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 :)
Comment by http://www.sorethumbsblog.com/ on Thursday April 28th at 8:16pm
Thanks for all your endeavours which you have devote this particular. very intriguing information.? An exile? s life’s simply no living.? through Leonidas concerning Tarentum.
Comment by http://www.themindstylecompany.com/ on Saturday April 30th at 8:19pm
As a brand new colostomy user this video was invaluable mate! This definitely looks like the way to go! Thank you so much for taking the time to make this vid, I now have a pretty clear conceptualization of how to irrigate. Cheers!!
Comment by car insurance on Friday May 20th at 9:33am
Those of you who dislike first-person-present-tense had better not read my latest novel, Wasted. In my view, each story needs the right tense and pov and as long as you get the right one for the story that's all that matters.
Comment by car insurance quotes on Saturday June 4th at 8:47am
RE: Kohala Bay proofI’m no math wiz, and have no access to Kohala Bay, but I wonder if you couldn’t slowly infuse Kohala Bay rum with Wray & Nephew Overproof to bring it up to 97 proof like the old Dagger Rum. Might be worth an experiment.
Comment by auto insurance on Sunday June 5th at 2:47am
p.ma quello è un errore di logica dei saggi antichi, una loro carenza.in ogi caso, NON è una differenza di impostazione: il cristiano non si suicida perchè questo è sempre un vivere meno bene. Non perchè è proibito.CiaoFrancesco
Comment by http://www./ on Wednesday July 20th at 1:44pm
Though our nation's economy has recently lost millions of jobs, the health care industry has continued to add them. Not surprisingly, unions are eager to sign up health care workers. In the last 10 years, the rate of union wins in the health care industry has grown faster than the national average. Unions are uniting to lobby for labor-friendly legislation to promote increased union membership in the health care sector.
15 Responses to “On SQL in PHP”
Write a Comment