With many Rails projects, there is occasionally a need to bypass the normal ActiveRecord API, and get closer to the low-level SQL API. Here are a few useful methods that bypass the confines of everyday ActiveRecord.
This returns model instances for the given SQL string.
#find_by_sql also accepts parametrized values, like this:
1 2 3 4 5 6 7 8 9 10 11
This returns an array-like object of type
ActiveRecord::Result. Each item in
the collection is a
Hash representing a row in the results.
ActiveRecord::Result contains information about the table and its columns.
It knows how to convert column values to some of their corresponding Ruby
1 2 3 4 5 6 7 8
This returns an array-like object that is specific on the database driver. For
PostgreSQL, this will be a
PG::Result. Each item in the collection is a
Hash. The values of the Hash are strings and nils. No conversion is performed
to convert the values to the appropriate Ruby type, other than NULL to nil.
execute methods have no built-in mechanism for escaping
values for the SQL statement.
Something like this is bad practice:
Because we’re not sure what is in
1 2 3
Notice that the SQL statement does not have single quote marks around the email.
#quote does that automatically.
#quote can get unwieldy as more and more values need to be escaped.
There is a private method called
#sanitize_sql_array that brings back the
parameterized escape mechanism that is present in ActiveRecord’s higher-level
1 2 3 4
Sometimes you need to introspect the database connection details. Maybe you have an external utility that performs bulk operations against the database, and it bypasses ActiveRecord entirely.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
Using a database connection and not returning is like opening a file and not closing it. It can lead to connection leaks in some contexts. ActiveRecord provides a block-style pattern for using a database connection:
1 2 3 4
For times when you need to load a bunch of SQL statements from a file,
1 2 3