Rails multiple databases, migrations and transactions
mouse 3821 · person cloud · link
DB
Dev
Rails
Last update
2017-07-26
2017
07-26
« — »

Start by configuring your rails app for multiple DBs:

1
2
3
4
5
6
7
8
9
10
11
12
default: &default
  adapter: sqlite3
  pool: 5
  timeout: 5000

production:    { <<: *default, database: db/production.sqlite3       }
development:   { <<: *default, database: db/development.sqlite3      }

# additional DB
:cash:
  production:  { <<: *default, database: db/production_cash.sqlite3  }
  development: { <<: *default, database: db/development_cash.sqlite3 }

Share a common connection between desired models (in the example I use namespaced models1):

1
2
3
4
5
6
7
# /app/models/cash/shared_base.rb
class Cash::SharedBase < ActiveRecord::Base
  establish_connection configurations[:cash][Rails.env]
  self.abstract_class = true
end
class Cash::Account  < Cash::SharedBase; ...; end # /app/models/cash/account.rb
class Cash::Movement < Cash::SharedBase; ...; end # /app/models/cash/movement.rb

Modify your migrations so they can use your custom connection and save the current schema version to the main DB:

1
2
3
4
5
# /db/migrate/XXXXXXXXXXXXXXXX_create_cash_accounts.rb
class CreateCashAccounts < ActiveRecord::Migration
  def connection; Cash::SharedBase.connection; end
  def change; ...; end
end

downside note: migration rollback will not work.

If you want to create a unique db/schema.rb dump, you can use this simple rake task:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# /lib/tasks/db.rake
namespace :db do
  desc 'Dump a unique schema for all databases'
  task 'schema:dump_all': :environment do
    def dump_conn(c)
      f = StringIO.new
      ActiveRecord::SchemaDumper.dump c, f # https://github.com/rails/rails/issues/3497#issuecomment-5419288
      f.string.split("\n")
    end

    # dump all schemas in memory
    arb      = ActiveRecord::Base
    schemas  = [ dump_conn(arb.connection) ]
    schemas += arb.configurations.keys.reject{|k| k.is_a? String }.
      map{|db| dump_conn arb.establish_connection(arb.configurations[db][Rails.env]).connection }

    # join schemas
    lines = []
    schemas.each_with_index do |schema, i|
      lines += schema.grep(/^ActiveRecord::Schema/) if lines.empty? # save header
      lines += schema.grep(/^\s/)                                   # append rows
      lines << "\n  # #{'-'*76}\n" if (i+1) < schemas.size          # separator
    end
    lines << 'end' # close block

    File.open(Rails.root.join('db/schema.rb').to_s, 'w'){|f| f.puts lines.join("\n") }
  end # dump_all

  # ----------------------------------------------------------------------------

  Rake::Task["db:schema:dump"].enhance do  # hook into existing task
    Rake::Task["db:schema:dump_all"].invoke
  end
end

Enable ActiveRecord's query cache for the new connections:

1
2
3
4
5
6
7
8
9
10
11
12
class ApplicationController < ActionController::Base
  around_action :cache_other_db_connections

  private

  def cache_other_db_connections
    Cash::SharedBase.connection.cache { yield }
    # if you have more connections make more around filters of just nest the calls:
    # M1.connection.cache{ M2.connection.cache { ... {yield} } }
  end
end

And last but not least, rails uses a transaction for each create/update so pay attention to multi-DB transactions as the ActiveRecord::Rollback exception does not propagate and thus it will not rollback the outer transaction if raised within an inner transaction (read Rails Multi-Database Best Practices Roundup for a more in depth explanation).

References:

  1. You can generate namespaced models with the standard rails generator: rails g model NamespaceName::ModelName .... ↩