Is it a good idea to use CodeIgniters Active Record library to manipulate MySQL databases or should I just use SQL?

Question

I'm starting to get to grips with CodeIgniter and came across it's support for the Active Record pattern.

I like the fact that it generates the SQL code for you so essentially you can retrieve, update and insert data in to a database without tying your application to a specific database engine.

It makes simple queries very simple but my concern is that it makes complex queries more complex if not impossible (e.g. if need for engine specific functions).

My Questions

What is your opinion of this pattern especially regarding CodeIgniters implementation?

Are there any speed issues with wrapping the database in another layer?

Does it (logic) become messy when trying to build very complex queries?

Do the advantages out way the disadvantages?

Answer 1

What is your opinion (sic) of this pattern especially regarding CodeIgniters implementation?

Can't say much about CI's implementation. Generally I avoid AR for anything but the simplest applications. If the table does not match 1:1 to my business objects, I don't use AR, as it will make modeling the application difficult. I also don't like the idea of coupling the persistence layer to my business objects. It's a violation of separation of concerns. Why should a Product know how to save itself? Futher reading: http://kore-nordmann.de/blog/why_active_record_sucks.html

EDIT after the comment of @kemp, I looked at the CI User Guide to see how they implemented AR:

As you can see in PoEAA an AR is an object that wraps a row in a database table or view, encapsulates the database access, and adds domain logic on that data. This is not what CI does though. It just provides an API to build queries. I understood that there is a Model class which extends AR and which can be used to build business objects, but that would be more like a Row Data Gateway then. Check out PHPActiveRecord for an alternate implementation.

Are there any speed issues with wrapping the database in another layer?

Whenever you abstract or wrap something into something else, you can be sure this comes with a performance impact over doing it raw. The question is, is it acceptable for your application. The only way to find out is by benchmarking. Further Reading: http://stackoverflow.com/search?q=orm+slow

EDIT In case of CI's simple query building API, I'd assume the performance impact to be neglectable. Assembling the queries will logically take some more time than just using passing a raw SQL string to the db adapter, but that should be microseconds only. And you as far as I have seen it in the User Guide, you can also cache query strings. But when in doubt, benchmark.

Does it (logic) become messy when trying to build very complex queries?

Depends on your queries. I've seen pretty messy SQL queries. Those don't get prettier when expressed through an OO interface. Depending on the API, you might find queries you won't be able to express through it. But then again, that depends on your queries.

Do the advantages out way the disadvantages?

That only you can decide. If it makes your life as a programmer easy, sure why not. If it fits your programming needs, yes. Ruby on Rails is build heavily on that (AR) concept, so it can't be all that bad (although we could argue about this, too :))

Answer 2

Ok, First of all 99% of your queries will be simple select/insert/update/delete. For this active record is great. It provides simple syntax that can be easily changed. For more complex queries you should just use the query method. Thats what its for.

Second, It provides escaping & security for those queries. Face it, your application probably will have hundreds if not thousands of places where queries take place. Your bound to screw up and forget to properly escape some of them. Active record does not forget.

Third, performance in my experience is not dramatically affected. Of course it is but its probably around .00001 per query. I think that is perfectly acceptable for the added security and sanity checks it does for you.

Lastly, I think its clear that i believe the advantages are far greater than the disadvantages. Having secure queries that even your most junior developer can understand and not screw up is a great thing.