JohnMetta JohnMetta - 1 month ago 5
MySQL Question

ActiveRecord::Base SQL result object types different in MySQL and PostgreSQL

Am I missing something? Same console, same codebase, different database connections. Result: different object types returned. If MySQL is used, we get an array of arrays, if PostgreSQL is used, we get an array of hashes.

Example classes



class User < ActiveRecord::Base
...
end

class Series < ActiveRecord::Base
establish_connection postgres_database_hash
...
end


With a MySQL connection



> User.connection.execute('SELECT * from users limit 2').to_a
(211.0ms) SELECT * from users limit 2
=> [[1, "jmetta", "jmetta@gmail.com"], [2, "johnmetta", "jmetta+test@gmail.com"]]


With a Postgres connection



> Series.connection.execute('SELECT * from series limit 2').to_a
(107.1ms) SELECT * from series limit 2
=> [{"id"=>"29", "enr_id"=>"114118", "ent_id"=>"164",}, {"id"=>"30", "enr_id"=>"114110", "ent_id"=>"164"}]


Coda



It seems that, at this level of abstraction where
to_a
is asked to give a result, the result should be the same.

Answer

The thing you're missing is that execute is a very low-level connection to the underlying database driver that is most useful for sending SQL DDL (Data Definition Language) into the database to manually alter tables, add constraints and indexes that AR doesn't understand, etc. If you want to send raw queries into the database and get raw results back, you should use the slightly higher level select_rows instead:

User.connection.select_rows('SELECT * from users limit 2').each do |row|
  # `row` is an Array of Strings here
end

A select_rows call should give you an array-of-arrays-of-strings with any database.

execute will return whatever the underlying driver returns, select_rows will return something consistent.

Comments