How can I separate SQL from my PHP?

Question

Following Uncle Bob's avice in Clean Code, I'd like to have no SQL in my PHP code. Presently I'm making use of Prepared Statements and I've factored my database access code into model classes.

My initial thought is to break out my SQL into separate SQL files and then load them at runtime. Since this involves more disk IO (my gut feeling), I'm wondering if anyone has suggestions on doing it another way.

Also, I'm interested to know if anyone's tried this and what dividends (if any it pays off).

Thank you.

Answer 1

If you're simply looking to separate the SQL, why not use Stored Procedures?

Answer 2

My advice to you is not to make the mistake so many do of trying to turn PHP into something it isn't.

PHP is a Web-focused templating language allowing you to embed code inside HTML pages. It has a request lifecycle rather than being persistent (like Java servlets are). It's APIs are largely procedural in nature.

None of that is a problem.

Yet you'll find people who insist in trying to force object models, ORMs and other structures they're used to from C# or Java programming onto PHP and often it's a mistake.

To specifically answer your question, i tend to put all my queries (functions) in one file (or more if there are a lot of them, in which case I'll separate them out by database or by some functional separation) and the SQL is simply in heredocs in the functions. Not embedding SQL logic all over yoru code is useful.

You could put that SQL in text files and use file_get_contents() on it but what do you gain?

There's just no point in hand-wringing and trying to apply principles from other languages and frameworks to PHP. Use PHP for what it's good at (or use something else). You'll be happier either way.

Answer 3

What I think you are looking for is a data access layer. I haven't read "Clean Code", but what I'm guessing Uncle Bob is getting at is to not have a bunch of raw SQL and database setup/access code all over the place. Abstract out the database prepare, execute, etc. code into a separate module and work from there.

The comment about using stored procedures is also a good idea IMHO, though this is a highly debated topic with good points on both sides. I tend toward using SP's, but they aren't for everyone or every situation.