Ruby/Postgres ActiveRecord without Rails: Undefined Table ERROR: relation "..." does not exist

Question

Working on a small Ruby script to create and store data to a Postgres database. The app works just fine when it comes to creating and dropping the database, or running migrations. However, it fails when I attempt to run rake db:seed. The error message indicates that the table doesn't exist...but meanwhile if I run the same SQL script referenced in the error message, but in pgAdmin, it returns valid results. I'm wondering if there is something going on with the privileges granted to the user named in the database.yml, and if so, how would I address that in the script? Any help understanding this would be appreciated.

Here is the error message I'm getting in the terminal:

acmecorp$ rake db:seed
rake aborted!
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "applications" does not exist
LINE 8:                WHERE a.attrelid = '"applications"'::regclass
                                          ^
:               SELECT a.attname, format_type(a.atttypid, a.atttypmod),
                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod,
                     c.collname, col_description(a.attrelid, a.attnum) AS comment
                FROM pg_attribute a
                LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
                LEFT JOIN pg_type t ON a.atttypid = t.oid
                LEFT JOIN pg_collation c ON a.attcollation = c.oid AND a.attcollation <> t.typcollation
               WHERE a.attrelid = '"applications"'::regclass
                 AND a.attnum > 0 AND NOT a.attisdropped
               ORDER BY a.attnum
/Users/thisguy/repositories/acmecorp/db/seeds.rb:1:in `<top (required)>'
/Users/thisguy/repositories/acmecorp/rakefile.rb:30:in `require_relative'
/Users/thisguy/repositories/acmecorp/rakefile.rb:30:in `block (2 levels) in <top (required)>'

Caused by:
PG::UndefinedTable: ERROR:  relation "applications" does not exist
LINE 8:                WHERE a.attrelid = '"applications"'::regclass
                                          ^
/Users/thisguy/repositories/acmecorp/db/seeds.rb:1:in `<top (required)>'
/Users/thisguy/repositories/acmecorp/rakefile.rb:30:in `require_relative'
/Users/thisguy/repositories/acmecorp/rakefile.rb:30:in `block (2 levels) in <top (required)>'
Tasks: TOP => db:seed
(See full trace by running task with --trace)

My database.yml file:

# config/database.yml
default: &default
  host: 'localhost'
  adapter: 'postgresql'
  encoding: utf-8

development:
  <<: *default
  database: 'acmecorp_dev'
  username: 'acmecorpapp'

The Gemfile is pretty simple:

# Gemfile
source 'https://rubygems.org'

gem 'activerecord'
gem 'require_all'
gem 'pg'

I have a rakefile.rb to control the various tasks (edited to remove irrelevant items):

# rakefile.rb
require "active_record"
require "require_all"
require "yaml"

namespace :db do
  env = 'development' || ENV['env']

  db_config       = YAML::load(File.open('config/database.yml'))[env]
  db_config_admin = db_config.merge({'database' => 'postgres', 'schema_search_path' => 'public'})

  ...

  desc "Seed the database"
  task :seed do
    ActiveRecord::Base.establish_connection(db_config_admin)
    require_all 'models/*.rb'
    require_relative 'db/seeds.rb'
  end

  ...

end

As noted, all of the other tasks in the rakefile work without problem--it's just the :seed task that burps. I'm fairly confident it has something to do with the way the script is interacting with the database.

Here is the seed file:

# db/seeds.rb
Application.create({
  ApplicationNumber:  1,
  AccountNumber: 1234,
  ApplVer:  1,
  CreateDateTime:  "1/2/2018",
  ExpirationDateTime:  "1/5/2019",
  ApplicationStatus:  "In process",
  ApprovedAmount:     1992.92,
  AcceptedAmount:     92.92,
  HomeAddressLine1:   "1 Main Street",
  HomeAddressLine2:   "",
  HomeAddressCity:    "Thibodaux",
  HomeAddressState:   "LA",
  HomeAddressZipCode: "12345"
})

and finally, I have a simple model for the application, which the seeds.rb file references:

# models/application.rb
class Application < ActiveRecord::Base
end

Now the error message noted above is stating that `relation "applications" does not exist"...but when I run the same SQL quoted in the message, directly in pgAdmin, here's what I get (apologies for formatting - couldn't figure out how to get it into a table efficiently):

attname|format_type|pg_get_expr|attnotnull|atttypid|atttypmod|collname|comment
id|integer|nextval('applications_id_seq'::regclass)|t|23|-1||
ApplicationNumber|integer||f|23|-1||
AccountNumber|integer||f|23|-1||
ApplVer|integer||f|23|-1||
CreateDateTime|timestamp without time zone||f|1114|-1||
ExpirationDateTime|timestamp without time zone||f|1114|-1||
ApplicationStatus|character varying||f|1043|-1||
ApprovedAmount|numeric||f|1700|-1||
AcceptedAmount|numeric||f|1700|-1||
HomeAddressLine1|character varying||f|1043|-1||
HomeAddressLine2|character varying||f|1043|-1||
HomeAddressCity|character varying||f|1043|-1||
HomeAddressState|character varying||f|1043|-1||
HomeAddressZipCode|character varying||f|1043|-1||
FileCreatedDate|timestamp without time zone||f|1114|-1||
created_at|timestamp without time zone||t|1114|-1||
updated_at|timestamp without time zone||t|1114|-1||

So the bottom line seems to be: - the script is properly configured to work with PG - at least as far as creating, dropping, and migrating databases - the rake db:seed task is failing - the SQL reported in the error message on the command line works just fine when it is run through pgAdmin

I'm not sure why the issue. Could it have something to do with privileges available to the acmecorpapp user that was assigned to the database in the database.yml..?

Answer 1

Okay, figured this out, after Chivorn Kouch's suggestion above. Solution requires an additional db_config variable, and a modification to the :seed task.

In the rakefile.rb:

# rakefile.rb
namespace :db do
  env = 'development' || ENV['env']

  db_config       = YAML::load(File.open('config/database.yml'))[env]
  db_config_admin = db_config.merge({ 'database' => 'postgres','schema_search_path' => 'public' })
  # add following line to create variable for the :seed task
  db_config_seed = db_config.merge({ 'schema_search_path' => 'public' })

  ...

  desc "Seed the database"
  task :seed do
    # change variable referenced in the connection line below
    ActiveRecord::Base.establish_connection(db_config_seed)
    require_all 'models/*.rb'
    require_relative 'db/seeds.rb'
    puts "Database seeded."
  end

  ...

end

With those changes, the commands all work as expected.

Answer 2

Please check file rakefile.rb. It seems to you merge the wrong database. the database for development is acmecorp_dev, and you already have it in db_config. I think you don't have to merge database anymore. Try this:

require "active_record"
require "require_all"
require "yaml"

namespace :db do
  env = 'development' || ENV['env']

  db_config       = YAML::load(File.open('config/database.yml'))[env]
  db_config_admin = db_config.merge({ 'schema_search_path' => 'public'})

  ...

  desc "Seed the database"
    task :seed do
    ActiveRecord::Base.establish_connection(db_config_admin)
    require_all 'models/*.rb'
    require_relative 'db/seeds.rb'
 end

 ...

end