JohnMetta JohnMetta - 1 year ago 72
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

class Series < ActiveRecord::Base
establish_connection postgres_database_hash

With a MySQL connection

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

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"}]


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

Answer Source

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

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.