Ruby で DB を扱うちょっとしたスクリプトを書くとき、ActiveRecord で生SQLを使うと色々捗ることが多い。
そのためのメソッドをまとめてみた。
- 事前準備： establish_connection
- 作成・更新系： execute
- 検索系： select_all, select_one, select_rows, select_values, select_value
- プレースホルダ： sanitize_sql_array
事前準備
establish_connection
DBとのコネクションを確立する。'mysql2', 'postgresql', 'redshift' など様々なアダプタが使える。
以下は接続設定の一例。
[MySQL] (要 mysql2 gem)
require 'active_record'
config = {
adapter: 'mysql2',
host: ___hostname___,
database: ___databasename___,
port: 3306,
username: ___username___,
password: ___password___,
encoding: 'utf8',
timeout: 5000,
}
ActiveRecord::Base.establish_connection(config)
[PostgreSQL] (要 pg gem)
require 'active_record'
config = {
adapter: 'postgresql',
host: ___hostname___,
database: ___databasename___,
port: 5432,
username: ___username___,
password: ___password___,
encoding: 'utf8',
timeout: 5000,
}
ActiveRecord::Base.establish_connection(config)
[Redshift] (要 pg, activerecord4-redshift-adapter gem)
require 'active_record'
config = {
adapter: 'redshift',
host: "#{___clustername___}.#{___something___}.#{___region___}.redshift.amazonaws.com",
database: ___databasename___,
port: 5439,
username: ___username___,
password: ___password___,
encoding: 'utf8',
timeout: 30000,
}
ActiveRecord::Base.establish_connection(config)
作成・更新系
execute
任意のSQLを実行する。（DBアダプタのクエリ実行メソッドをほぼそのまま叩いている）
con = ActiveRecord::Base.connection
con.execute("INSERT INTO users(name, email) VALUES('Joe', 'joe@example.com')")
検索系クエリを実行した場合、Mysql2::Result, PG::Result などDBアダプタ依存のオブジェクトが返る。
そのため、検索系のクエリを使う場合は後述する select_all などの検索系メソッドを使った方が良い。
検索系
select_all
検索クエリ実行結果として、ActiveRecord::Result というオブジェクトを返す。
このオブジェクトに対して to_hash（to_ary, to_a でも可）を実行するとHash配列を取得できる。
con = ActiveRecord::Base.connection
result = con.select_all('SELECT name, email FROM users')
result.to_hash
# => [ {"name" => "Joe", "email" => "joe@example.com"},
# {"name" => "Alice", "email" => "alice@example.com"},
# {"name" => "Bob", "email" => "bob@example.com"} ]
ちなみに ActiveRecord::Result オブジェクトを返すようになったのはバージョン 4.0.0 から。
それ以前のバージョンではHash配列が返っていた。
select_one
検索クエリ実行結果として、最初のレコードの情報をHashオブジェクトで返す。
select_all(...).first のショートカット的なメソッド。
con = ActiveRecord::Base.connection
result = con.select_one('SELECT name, email FROM users')
# => {"name"=>"Joe", "email"=>"joe@example.com"}
select_rows
複数カラムの値の配列を返す。
カラム名はいらなくて値だけ欲しい場合に便利。
con = ActiveRecord::Base.connection
result = con.select_rows('SELECT name, email FROM users')
# => [ ["Joe", "joe@example.com"],
# ["Alice", "alice@example.com"],
# ["Bob", "bob@example.com" ] ]
select_values
最初のカラムの値の配列を返す。
select_rows(...).map(&:first) のショートカット的なメソッド。
con = ActiveRecord::Base.connection
result = con.select_values('SELECT name FROM users')
# => ["Joe", "Alice", "Bob"]
検索クエリに複数カラムが指定されていても、最初のカラムの値しか取得できないので注意。
con = ActiveRecord::Base.connection
result = con.select_values('SELECT name, email FROM users')
# => ["Joe", "Alice", "Bob"] ← email の値は取得されない
select_value
最初のレコードの最初のカラムの値を返す。
select_one(...).values.first のショートカット的なメソッド。
con = ActiveRecord::Base.connection
result = con.select_value('SELECT name FROM users')
# => "Joe"
プレースホルダ
sanitize_sql_array
protected メソッドなのが玉にキズだが、
send メソッド経由でこのメソッドを使えばプレースホルダを使ったSQL文を作成できる。
sql = ActiveRecord::Base.send(
:sanitize_sql_array,
['SELECT * from users WHERE name=?', "Bob's"]
)
# => "SELECT * from users WHERE name='Bob\\'s'"
sql = ActiveRecord::Base.send(
:sanitize_sql_array,
[ 'SELECT * from users WHERE name=:name AND email=:email',
name: 'Bob', email: 'bob@example.com' ]
)
# => "SELECT * from users WHERE name='Bob' AND email='bob@example.com'"
Array, Range オブジェクトもいい感じに使えるので便利。
ids = [1, 2, 3]
sql = ActiveRecord::Base.send(
:sanitize_sql_array,
['SELECT * from users WHERE id IN (:ids)', ids: ids]
)
# => "SELECT * from users WHERE id IN (1,2,3)"
ids = (3..10)
sql = ActiveRecord::Base.send(
:sanitize_sql_array,
['SELECT * from users WHERE id IN (:ids)', ids: ids]
)
# => "SELECT * from users WHERE id IN (3,4,5,6,7,8,9,10)"
#あわせて読みたい
複数 DB のデータや複数 RDBMS を跨いたデータを Ruby の層で扱う場合の Tips：