

The Last PHP PDO Library You Will Ever Need - leftnode
http://leftnode.com/entry/the-last-php-pdo-library-you-will-ever-need

======
ironkeith
There are no comment on the blog, so I'm just going to put this here and hope
the author sees it (as they appear to have submitted the post):

You don't want to write SQL in your controllers. It's a recipe for future pain
and suffering.

\- If a second action needs the same data, it's likely that you'll end up
duplicating your query. Especially if you work with other people, and they
don't know about the queries that exist in all of the different actions.

\- If you change the structure of the data in the DB, you need to find every
affected query, in every action. It's extremely fragile, and prone to
breaking.

If you have a model, with a nice public API, that interacts with the DB, you
know that you've isolated the change to just that one place. All of the
controllers that call $model->some_data(); will continue to work no matter how
your change your data source so long as you obey the API.

There are million different ways you can approach that, but I strongly
recommend that you find one that works for you, and stick with it.

~~~
jiggy2011
Yes , totally agree with this but I still often see people put SQL (or HSQL or
whatever) into controller/view level code.

I think part of the reason for this is it appears in allot of tutorials for
different web frameworks, which I think they do because it makes the code
smaller and therefor their framework look simpler.

------
wulczer
Amen. There's already a well-proven and quite expressive DSL for accessing
data stored in a relational database: SQL.

I see that this has been submitted before, so I'll just mention it here:
[http://thoughts.j-davis.com/2011/09/25/sql-the-successful-
co...](http://thoughts.j-davis.com/2011/09/25/sql-the-successful-cousin-of-
haskell/)

~~~
vog
Thanks for pointing to that interesting article. However, a link to the
corresponding HN discussion would have been more useful, at least to me.

~~~
wulczer
Wasn't very successful, 3 points and 0 comments:
<http://news.ycombinator.com/item?id=3059460>

I found it with hnsearch, by the way.

~~~
vog
Now it has 4 points. :-)

------
mweibel
Generally I agree doing SQL queries on your own. I'm also not really an ORM-
Fan because it's really hard to debug and difficult queries are more difficult
to construct using an ORM.

But doing your SQL Statements in the Controller is just not the way to go..
What if you need the same query on different places?

What I like about good ORMs (like Doctrine or the ActiveRecord of RoR) is the
migration part. Maybe there should be a tool which only supports you in
creating the tables and handling migrations.

~~~
mhitza
Install Propel, define the schema and only generate migration scripts with it.
Problem solved.

------
agildehaus
Always good to have something like this. Throw it up on GitHub and give it a
descriptive name :)

Lately I've been using RedBean ORM for my projects. It lets you write your SQL
(no query builders or anything stupid), but creates tables, columns, and
foreign keys on the fly.

Here's some RedBean code from one of my projects:

<http://pastebin.com/hpLB1h37>

~~~
leftnode
That looks interesting. I'll give it a look.

------
hippich
It is so simple:

\- If you do quick ad hoc script/page - use straight sql with mysql_query

\- If you extend it - use something like PDO

\- If you build real application, you have to use Model layer. And no matter
what you prefer at this point - you'd better to put everything behind Model be
it PDO-based queries, some dynamic objectish query builders or full-scale ORM
like DBIx::Class (sorry, this is for Perl, not very familiar with something
like this in PHP. If someone know good analogs in PHP - please post links to
these).

Right tool for right task - this is what important to keep in mind.

PS: Sorry, but seeing straight SQL in what looks like an action, is really
hurt my eyes. If you opted for MVC framework, you'd probably want to push all
SQL into model and leave meaningful calls to it.

------
bni
After about 10-15 years working with many different systems that one way or
another store their data in a RDBMS, all I can say is that I agree with what
you wrote. ORM-wed code is a nightmare, 3-5 years on. When the ORM trends have
shifted to something else, and the original people who implemented it or
selected the framework is long gone.

Ill take a messy system with hand coded SQL over that any day, atleast then
you have a chance to refactor.

Also, its not that hard to implement a pluggable data access layer (that uses
hand written SQL). The most important thing is to keep your RDBMS data model
separate from your application model. These two will be intermingled when you
are using a ORM, in my experience.

------
lux
I'm a fan of a hybrid approach (a few wrapper functions around PDO itself that
just abstract the repetitive stuff in PDO but not the SQL, very similar to the
post), and very thin ORM too.

I like a basic Model class with a few helpers that I can extend to keep my
logic separated from my controllers, but that's about it. Inside any given
Model method, SQL is fair game.

------
misterbwong
I'm a fan of the recent move towards Micro-ORM's. As others have said, SQL is
quite expressive and pretty easy to learn (for most things). I suspect that
the reason why many app developers shy away from it is because it is set based
rather than object oriented.

------
voidr
I really enjoy Flourish's ActiveRecord model
<http://flourishlib.com/docs/fActiveRecord>

It feels natural, also it let's you do SQL if you really need it.

~~~
jonathanza
I've been using the library for the last few months on a number of projects.
It's sort of become my Swiss Army knife for PHP. It's saved me a ton of time.

------
randallsquared
This sentence took me several reads to parse: "I will refer to any library or
framework for interacting with a database besides writing straight SQL as an
ORM in this article."

Maybe it's just me. :)

~~~
bni
But It was very informative, as he explains what he is talking about since the
definition of an ORM seems to be quite different for different people.

For me it also means anything that tries to generate SQL.

~~~
leftnode
Yeah, that's exactly what I meant by it.

------
ralmeida
I would just add a fetch_scalar() function. It makes some common operations
get very readable:

$total_users = $db->fetch_scalar("SELECT COUNT(*) FROM users");

~~~
leftnode
fetch_column() does essentially that. You can pass in the column number you
want fetched, in this case, it would be the 0th column.

~~~
ralmeida
I know. I would at least add it as an alias to $fetch_column(0), out of sheer
readability preciosism =D

------
leftnode
I'm interested in your counter arguments too. I'm sure there is some component
or aspect I'm missing.

~~~
Ramone
It's really all about factoring. You'll notice now that you have model logic
in your controller. If you want to reuse that elsewhere, you'll first have to
extract it to another class. That class is effectively a SQL generation
library, except it's not useful in a general sense and it's not as well
tested. The more you do this, the more you converge on reinventing a full
blown ORM.

I'm not saying there aren't downsides to ORMs, but the counter-arguments to
SQL in your controller are pretty obvious.

~~~
wvenable
I disagree. Yes, this should be refactored and placed in a model; but that
would be something like Clients::getReadMetrics() that would contain the same
SQL. I don't see that heading towards a full ORM or even remotely like SQL
generation.

