Moxley Stratton

Code Blog

Low-Level ActiveRecord

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.

#find_by_sql

1
users = User.find_by_sql("SELECT * FROM users WHERE id = 1")

This returns model instances for the given SQL string.

#find_by_sql also accepts parametrized values, like this:

1
users = User.find_by_sql(["SELECT * FROM users WHERE id = ?", 1])

#select_all

1
2
3
4
5
6
7
8
9
10
11
users = ActiveRecord::Base.connection.select_all("
  SELECT id, first_name, created_at
  FROM users
  WHERE id = 1")

users[0]
{
  "id"         => 202,
  "first_name" => "Trystan",
  "created_at" => "2015-09-05 15:44:51.597326"
}

This returns an array-like object of type ActiveRecord::Result. Each item in the collection is a Hash representing a row in the results.

The ActiveRecord::Result contains information about the table and its columns. It knows how to convert column values to some of their corresponding Ruby types.

#execute

1
2
3
4
5
6
7
8
users = ActiveRecord::Base.connection.execute("SELECT id, first_name, created_at FROM users WHERE id=1")

users[0]
{
  "id"         => "1",
  "first_name" => "Trystan",
  "created_at" => "2015-09-05 15:44:51.597326"
}

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.

#quote

The select_all and execute methods have no built-in mechanism for escaping values for the SQL statement.

Something like this is bad practice:

1
2
users = ActiveRecord::Base.connection.select_all("
  SELECT * FROM users WHERE email='#{email}'")

Because we’re not sure what is in email. It could have an SQL injection attack, or it could just have unexpected characters that will break the query. To cover these cases, ActiveRecord provides #quote:

1
2
3
conn = ActiveRecord::Base.connection
users = conn.select_all("
  SELECT * FROM users WHERE email=#{conn.quote(email)}")

Notice that the SQL statement does not have single quote marks around the email. #quote does that automatically.

#sanitize_sql_array

Using #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 API.

1
2
3
4
sql = ActiveRecord::Base.send(:sanitize_sql_array,
  ["SELECT * FROM users WHERE email=?", email])

users = ActiveRecord::Base.connection.select_all(sql)

#connection_config

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
config = Rails.application.config.database_configuration

{
  "common" => {
    "adapter"           => "postgres",
    "encoding"          => "utf8",
    "postgis_extension" => true,
    "username"          => "user",
    "password"          => "password",
    "host"              => "127.0.0.1",
    "port"              => 5432
  },
  "development" => {
    "adapter"           => "postgres",
    "encoding"          => "utf8",
    "postgis_extension" => true,
    "username"          => "user",
    "password"          => "password",
    "host"              => "127.0.0.1",
    "port"              => 5432,
    "database"          => "database_development"
  },
  "production" => {
    "adapter"           => "postgres",
    "encoding"          => "utf8",
    "postgis_extension" => true,
    "username"          => "user",
    "password"          => "password",
    "host"              => "127.0.0.1",
    "port"              => 5432,
    "database"          => "database_production"
  }
}

#with_connection

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
ActiveRecord::Base.connection_pool.with_connection do |conn|
  users = conn.select_all("
    SELECT * FROM users WHERE email=#{conn.quote(email)}")
end

#structure_load

For times when you need to load a bunch of SQL statements from a file, ActiveRecord provides #structure_load:

1
2
3
config = Rails.application.config.database_configuration['development']
sql_file = "tmp/foo.sql"
ActiveRecord::Tasks::DatabaseTasks.structure_load(config, sql_file)

Switching to Octopress From Wordpress

octopress post in atom

Wordpress has been my blogging platform for the last two years, and Drupal for a few years before that. Both are written in PHP and both were installed and maintained on a traditional shared hosting account. I would have stuck with Wordpress if it weren’t for it’s lack of robust Markdown language support. Writing a coder’s blog requires code samples, usually in multiple languages. Github’s flavor of Markdown has proved to be an excellent language for authoring content that includes code samples, and Octopress supports it by default.

Octopress is a wrapper around Jekyll, and Jekyll is well suited for publishing a blog to a Github’s Pages service. Since Jekyll only generates a static site, it can be much faster than a PHP solution like Wordpress or Drupal. When used in conjunction with the Atom text editor, Octopress becomes a powerful blogging platform.

Sometimes Clojure Is More Concise Than Ruby

I find that 90% of the time, Clojure is more verbose than Ruby. Here’s one simple example where Clojure wins a conciseness battle:

1
2
3
4
5
;; Subtract two vectors in Clojure
(def a [10 9 8])
(def b [1 2 3])
(map - a b)
;; (9 7 5)
1
2
3
4
5
# Subtract two vectors in Ruby
a = [10, 9, 8]
b = [1, 2, 3]
a.zip(b).map { |(a, b)| a - b }
# [9, 7, 5]

What is it about Ruby that makes it less concise in this example?

In my opinion, the OOP nature of Ruby gets in the way. In OOP, there is a message receiver (the object), plus zero or more arguments. In Clojure, there is no receiver, only arguments. In Ruby, the two arguments to Clojure’s map call have to be split up into the receiver and the first argument. Additionally, the arguments to Ruby’s map arrive as a single array instead of of two distinct scalar arguments. This requires the destructor parentheses inside the map block.

The syntactical division between a receiver and it’s arguments is usually not a problem. But every once in a while, it gets in the way. Another division in OOP is between class and instance. I’ve found that this division can sometimes get in the way too.

Ten Secret Key Bindings From OS X

Did you know that when using these applications:

  • Any application on OS X, except Vim and MS products
  • Bash, or any console program with Readline support

Injection

Within the world of application development, there is a conspiracy.

1
$sql = "SELECT * FROM users WHERE username='" . $username . "'";

Clojure Tutorial for the Non-Lisp Programmer

I intend to keep this tutorial as accurate and up-to-date as possible. If you have any suggestions for changes, please leave a comment at the bottom of this page.

Clojure is a functional lisp dialect that uses the Java Virtual Runtime as its platform. The language home page is at http://clojure.org/.