Connect to existing Postgresql database with ActiveRecord without rails

Question

I am not using rails, and I'm trying to use ActiveRecord to make it easier to work with an existing database. This is just a script so I have no database.yml or any other files. I've set up my database connection using

ActiveRecord::Base.establish_connection(
  adapter:    'postgresql',
  host:       'thehosthere',
  database:   'management',
  username:   'management_readonly',
  password:   '',
  port:       '5432'
)

I'm not really familiar with databases, so I'll just state what I know about this one. There are quite a few schemas. The schema I'm interested in is the management schema that has the table "host". I've created a class Host in the script like this:

class Host < ActiveRecord::Base
    self.table_name = "host"
end

I'm then trying to pull all rows that match the criteria with this query and to store it into an array.

servers = Host.where(:realm => 'stage', :status => 'UP').pluck(:hostname)

but I am getting this error every single time.

 ruby environments_are_okDev.rb
/usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec': PG::UndefinedTable: ERROR:  relation "host" does not exist (ActiveRecord::StatementInvalid)
LINE 5:                WHERE a.attrelid = '"host"'::regclass
                                          ^
:               SELECT a.attname, format_type(a.atttypid, a.atttypmod),
                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid,
a.atttypmod
                FROM pg_attribute a LEFT JOIN pg_attrdef d
                  ON a.attrelid = d.adrelid AND a.attnum = d.adnum
               WHERE a.attrelid = '"host"'::regclass
                 AND a.attnum > 0 AND NOT a.attisdropped
               ORDER BY a.attnum
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_no_cache'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:138:in `block in exec_query'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:425:in `block in log'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activesupport-4.0.0/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:420:in `log'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:137:in `exec_query'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:915:in `column_definitions'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql/schema_statements.rb:174:in `columns'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/connection_adapters/schema_cache.rb:114:in `block in prepare_default_proc'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/connection_adapters/schema_cache.rb:56:in `yield'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/connection_adapters/schema_cache.rb:56:in `default'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/connection_adapters/schema_cache.rb:56:in `columns'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/model_schema.rb:208:in `columns'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/model_schema.rb:247:in `column_names'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/relation/delegation.rb:60:in `block in method_missing'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/relation.rb:270:in `scoping'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/relation/delegation.rb:60:in `method_missing'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/relation/calculations.rb:152:in `block in pluck'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/relation/calculations.rb:151:in `map!'
        from /usr/local/rvm/gems/ruby-1.9.3-p448/gems/activerecord-4.0.0/lib/active_record/relation/calculations.rb:151:in `pluck'
        from environments_are_okDev.rb:51:in `run'
        from environments_are_okDev.rb:97:in `<main>'

I don't really understand why this is happening, so I tried to see what the database looks like with this command ActiveRecord::Base.connection.tables but all I got was an empty array. I'm not sure what could be happening. I'm obviously doing something wrong, I just can't figure out what. Keep in mind, this is all in a single file script.

Edit:

So I can see the schemas if I use ActiveRecord::Base.connection.schema_names. This gives me an array => ["db_stats", "management", "management_audit", "public"] Using pgadmin, I know that the table I want is in management, so how do I access that?

Answer 1

Try adding schema_search_path when you call establish_connection:

ActiveRecord::Base.establish_connection(
  adapter:    'postgresql',
  host:       'thehosthere',
  database:   'management',
  username:   'management_readonly',
  password:   '',
  port:       5432,
  schema_search_path: 'management' # <-- ???
)