Activerecord Migrations multiple postgresql database

Question

I have multiple databases. All the migrations for the main database human_development runs fine. The second database is called animals and the migrations fail.

database.yml:

default: &default
  adapter: postgresql
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000
  encoding: unicode
  user: blah

development:
  <<: *default
  database: human_development

animals:
  <<: *default
  database: animals

Migrations that are failing:

class SomeTable < ActiveRecord::Migration[5.2]
  def change
    ActiveRecord::Base.establish_connection("animals")

    create_table :some_table, id: :uuid do |t|
      t.string :type
      t.timestamps
    end

    ActiveRecord::Base.establish_connection(Rails.env)
  end
end

I have also tried the following, non worked:

1. def connection
     ActiveRecord::Base.establish_connection("animals").connect
     #ActiveRecord::Base.establish_connection("animals".to_sym).connect
   end
   

2. establish connection out side of the change

  ActiveRecord::Base.establish_connection("animals").connect
  # also tried with to_sym

If I run "rails db:migrate", passing the database name as a string I get the following error:

rake aborted!
ActiveRecord::AdapterNotSpecified: database configuration does not specify adapter
.../db/migrate/2019_some_tables.rb:2:in

and if I run the rails db:migrate with to_sym I get the following error:

-- create_table(:some_table, {:id=>:uuid})
rake aborted!
ActiveRecord::StatementInvalid: PG::ConnectionBad: connection is closed: SELECT pg_advisory_unlock


Caused by:
PG::ConnectionBad: connection is closed


Caused by:
StandardError: An error has occurred, this and all later migrations canceled:

PG::ConnectionBad: connection is closed: ROLLBACK


Caused by:
ActiveRecord::StatementInvalid: PG::ConnectionBad: connection is closed: ROLLBACK


Caused by:
PG::ConnectionBad: connection is closed

Answer 1

@Eyeslandic Thank you so much, that helped a lot!

In case anyone else runs into the same problems. Looks like rails 6 is going to have a better solution for this, but meanwhile, here are additional changes I had to make:

1. database.yml: I wanted to keep the migrations in a separate folder:

├── db
│   ├── migrate
│   ├── schema.rb
│   └── seeds.rb
├── animals_db
│   └── migrate

animals:
  <<: *default
  database: animals
  migrations_paths: animals_db/migrate

2. Create a new environment: config/environments/animals.rb
3. Change config/secrets.yml to include new environment
4. Generate secret: RAILS_ENV=animals rake secret
5. Save the secret in .env file or export it
6. Create the database: rails db:create RAILS_ENV=animals
7. Create the migration in animals_db but here is the catch. I had to include the following in def change

def change
  create_table :some_table, id: :uuid do |t|
    enable_extension "uuid-ossp"
    enable_extension "pgcrypto"
    t.string :type
    t.timestamps
  end
end

8. Run the migration: rails db:migrate RAILS_ENV=animals

You may need to change config/cable.yml as well to include new environment

Hope this helps. Looking forward to see how rails 6 is going to improve this

Answer 2

The migration file should be the same for all databases

class SomeTable < ActiveRecord::Migration[5.2]
  def change    
    create_table :some_table, id: :uuid do |t|
      t.string :type
      t.timestamps
    end
  end
end

then in your console you run

# for the main db
rails db:migrate

# for the animals db
RAILS_ENV=animals rails db:migrate