ActiveRecord and MySQL

The way we interact with data from the rails side of things is conveniently object-oriented. Groups of objects are stored in tables, along with information about how they are connected to other objects. A table contains all instances of a given class as rows (one row per object), and is always pluralized. So we might have a table containing all of our ArchEnemy objects, called arch_enemies. (By the way, there is a touch of rails magic happening here - rails knows that “people” is the plural of “person”, for example.)

Without rails, however, the convenience of object-orientation is not built in to the database. Chances are the database is a relational database (like MySQL, Postgres, or SQLite). Without the convenience of rails, data can be accessed using the ruby MySQL module, which has built in methods to establish connections, run queries, and interact with the resulting datasets returned by queries.

If we want to interact with the database without having to write SQL queries, we need a layer of logic to map classes to tables, objects to rows, and attributes to columns, so that we can interact with the database as if it were object-oriented. The layer that handles this is doing object-relational mapping, or ORM. In rails, the built in ORM layer is ActiveRecord. Using ActiveRecord you can easily find information using methods like first, find, and find_all, and change information in the database using save, or update_attribute (which I use often for test purposes to directly change an attribute while circumventing a save and its associated validations.)

Accessing data through the ActiveRecord layer is fine in many cases, but let’s say you need to add, retrieve, or modify a large amount of data, or maybe you want to use a massive CSV from an outside source, and are only interested in extracting portions of the data. In these cases, the usual methods can be unreasonably slow, and you will probably want to use SQL directly. This can be done using

ActiveRecord::Base.execute(some_sql)

To safeguard against SQL injection attacks, it’s a good idea to first run your query through sanitize_sql as such:

ActiveRecord::Base.sanitize_sql(some_sql))

ActiveRecord's execute method returns a Mysql::Result object, which is a result set. Result sets consists of a set of rows from the database, and a cursor that points to the current row. In order to access data from a row in a result set, that row must be the current row, and the cursor must iterate over all previous rows first. This makes it extremely expensive time-wise to access a particular attribute repeatedly (say inside a loop), and it can be very advantageous to assign data to a local variable if it is going to be needed multiple times during a loop.