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

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.