active record vs bare sql

Question

I'm kind of proud not to accept concepts if there is no good reason. But I'm in doubt about using active record pattern. Currently I'm using zend but say code Igniter has active record.

I dont use.Because

sql is sql it has own syntax.

you can copy to sql editor and it works (if it is working!)

you dont learn another syntax

you dont need to kill your script to gather if active record is writing sql the way you expected

but active record has

you pretend writing like objective php.

When you need to move another db(oracle>mysql :p), you dont need to change rand function to random, active record can make it for you.

does active record have much more capability that I am missing? Can you give some example cases where active record could be a life saver?

score 2 · Accepted Answer · edited May 23 '17 at 12:20

An ActiveRecord 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 CodeIgniter uses. CI's AR is a basic query object.

The main benefit of an ActiveRecord is it's simplicity. If your application is mainly doing simple CRUD operations and your Table structure matches the ActiveRecord very closely, then it's a good choice. It's easy to abstract CRUD in that case. And you can still add handcrafted SQL to it for certain more complex row manipulations.

class User
{
    protected static $dbAdapter;
    protected $username;
    …

    public static function findById($id)
    {
        $result = self::$dbAdapter->query(
            sprintf('SELECT * FROM users WHERE id = %d', $id)
        );
        if($result) {
            return new User($result);
        }
    }

    public function create()
    {
        try {
            return self::$dbAdapter->query(
                sprintf(
                    'INSERT into users …', 
                    $this->username, 
                    …
                );
            );
        } catch …
    }

    …
}

You'd use that in your application like this:

$john = User::findById(123);
echo $user->username; // John

$jane = new User(array(
    'username' => 'Jane'
));
$jane->create();

You definitely don't want to use ActiveRecord if the your rows and the AR don't match closely. AR is an object representing a database row. AR couples the object design to the database design. AR is not an ORM. Trying to put that into it is not practical. If you find you are in need of more juicy Domain Models, you won't be happy with it, because it will ultimately hamper your development due to object-relational impedance mismatch.

Additional readings:

active record vs bare sql

1 Answers1